1#
2# MDEV-17042: prepared statement does not return error with
3# SQL_MODE STRICT_TRANS_TABLES. (Part 2)
4#
5set @save_sql_mode=@@sql_mode;
6set sql_mode='STRICT_TRANS_TABLES';
7CREATE TABLE t1 (id int, count int) engine=innodb;
8insert into t1 values (1,1),(0,2);
9update t1 set count = count + 1 where id = '1bad';
10ERROR 22007: Truncated incorrect DOUBLE value: '1bad'
11prepare stmt from "update t1 set count = count + 1 where id = '1bad'";
12execute stmt;
13ERROR 22007: Truncated incorrect DOUBLE value: '1bad'
14deallocate prepare stmt;
15prepare stmt from 'update t1 set count = count + 1 where id = ?';
16set @a = '1bad';
17execute stmt using @a;
18ERROR 22007: Truncated incorrect DOUBLE value: '1bad'
19deallocate prepare stmt;
20drop table t1;
21CREATE TABLE t1 (id decimal(10,5), count int) engine=innodb;
22insert into t1 values (1,1),(0,2);
23update t1 set count = count + 1 where id = '1bad';
24ERROR 22007: Truncated incorrect DECIMAL value: '1bad'
25prepare stmt from "update t1 set count = count + 1 where id = '1bad'";
26execute stmt;
27ERROR 22007: Truncated incorrect DECIMAL value: '1bad'
28deallocate prepare stmt;
29prepare stmt from 'update t1 set count = count + 1 where id = ?';
30set @a = '1bad';
31execute stmt using @a;
32ERROR 22007: Truncated incorrect DECIMAL value: '1bad'
33deallocate prepare stmt;
34drop table t1;
35CREATE TABLE t1 (id double, count int) engine=innodb;
36insert into t1 values (1,1),(0,2);
37update t1 set count = count + 1 where id = '1bad';
38ERROR 22007: Truncated incorrect DOUBLE value: '1bad'
39prepare stmt from "update t1 set count = count + 1 where id = '1bad'";
40execute stmt;
41ERROR 22007: Truncated incorrect DOUBLE value: '1bad'
42deallocate prepare stmt;
43prepare stmt from 'update t1 set count = count + 1 where id = ?';
44set @a = '1bad';
45execute stmt using @a;
46ERROR 22007: Truncated incorrect DOUBLE value: '1bad'
47deallocate prepare stmt;
48drop table t1;
49CREATE TABLE t1 (id date, count int) engine=innodb;
50insert into t1 values ("2019-06-11",1),("2019-06-12",2);
51update t1 set count = count + 1 where id = '1bad';
52ERROR 22007: Truncated incorrect datetime value: '1bad'
53prepare stmt from "update t1 set count = count + 1 where id = '1bad'";
54execute stmt;
55ERROR 22007: Truncated incorrect datetime value: '1bad'
56deallocate prepare stmt;
57prepare stmt from 'update t1 set count = count + 1 where id = ?';
58set @a = '1bad';
59execute stmt using @a;
60ERROR 22007: Truncated incorrect datetime value: '1bad'
61deallocate prepare stmt;
62drop table t1;
63set sql_mode=@save_sql_mode;
64# End of 5.5 tests
65