[MDEV-17042] prepared statement does not return error with SQL_MODE STRICT_TRANS_TABLES. Created: 2018-08-23  Updated: 2019-07-12  Resolved: 2019-07-12

Status: Closed
Project: MariaDB Server
Component/s: Prepared Statements
Affects Version/s: 5.5, 10.0
Fix Version/s: 5.5.65

Type: Bug Priority: Critical
Reporter: Keita Kimura Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 6.10 on VirtualBox 5.2.18
MariaDB 10.0.36


 Description    

CREATE TABLE `a` (
 
  `id` int(11) NOT NULL,
 
  `count` int(11) NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8


> set session sql_mode = 'STRICT_TRANS_TABLES';
 
> update a set count = count + 1 where id = '1bad';
 
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1bad'
 
> prepare stmt from 'update a set count = count + 1 where id = ?';
 
Query OK, 0 rows affected (0.03 sec)
 
Statement prepared
 
 
 
> set @a = '1bad';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
> execute stmt using @a;
 
Query OK, 1 row affected (0.39 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0

sql_mode is not worked with prepared statement.
it should be return error both, but prepared statement return OK.

and if binlog_format is MIXED, sql_mode is saved on binlog.

BEGIN
 
/*!*/;
 
# at 1559
 
#180823 18:18:13 server id 2  end_log_pos 1673  Query   thread_id=21    exec_time=1     error_code=0
 
use `10_0_36`/*!*/;
 
SET TIMESTAMP=1535015893/*!*/;
 
SET @@session.pseudo_thread_id=21/*!*/;
 
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 
SET @@session.sql_mode=2097152/*!*/;
 
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 
/*!\C utf8 *//*!*/;
 
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
 
SET @@session.lc_time_names=0/*!*/;
 
SET @@session.collation_database=DEFAULT/*!*/;
 
update a set count = count + 1 where id = '1bad'
 
/*!*/;
 
# at 1673
 
#180823 18:18:13 server id 2  end_log_pos 1700  Xid = 169
 
COMMIT/*!*/;
 
DELIMITER ;
 
# End of log file

So SQL thread failure:

Last_SQL_Errno: 1292
 
Last_Error: Error 'Truncated incorrect DOUBLE value: '1bad'' on query. Default database: '10_0_36'. Query: 'update a set count = count + 1 where id = '1bad''



 Comments   
Comment by Alice Sherepa [ 2018-08-23 ]

Thanks for the report!
Repeatable on Mariadb 5.5,10.0 (not on 10.1-10.3)

set sql_mode = 'STRICT_TRANS_TABLES';
 
CREATE TABLE a (id int, count int);
 
insert into a values (1,1),(0,2);
 
--error 1292
 
update a set count = count + 1 where id = '1bad';
 
 
 
 prepare stmt from 'update a set count = count + 1 where id = ?';
 
 set @a = '1bad';
 
 execute stmt using @a; ##error 1292 expected

Comment by Oleksandr Byelkin [ 2019-07-11 ] 

set @save_sql_mode=@@sql_mode;
 
set sql_mode='STRICT_TRANS_TABLES';
 
CREATE TABLE t1 (id int, count int);
 
insert into t1 values (1,1),(0,2);
 
--error ER_TRUNCATED_WRONG_VALUE
 
update t1 set count = count + 1 where id = '1bad';
 
 
 
prepare stmt from 'update t1 set count = count + 1 where id = ?';
 
set @a = '1bad';
 
--error ER_TRUNCATED_WRONG_VALUE
 
execute stmt using @a;
 
deallocate prepare stmt;
 
drop table t1;
 
set sql_mode=@save_sql_mode;

Comment by Oleksandr Byelkin [ 2019-07-11 ]

It is probleme of parameters assignment, following works as it should:

prepare stmt from "update t1 set count = count + 1 where id = '1bad'";
 
--error ER_TRUNCATED_WRONG_VALUE
 
execute stmt;
 
deallocate prepare stmt;

Comment by Oleksandr Byelkin [ 2019-07-12 ]

commit 2b62f4e5a3e257fafc0173431697d7350f08ef5f (HEAD > bb-5.5MDEV-17042, origin/bb-5.5-MDEV-17042)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Fri Jul 12 10:03:33 2019 +0200

MDEV-17042: prepared statement does not return error with SQL_MODE STRICT_TRANS_TABLES.

Use for parameters value conversion functions which issue warnings.

Comment by Alexander Barkov [ 2019-07-12 ]

Please add a test for InnoDB, as in the bug report.
Also, in the tests for MyISAM, please change:

set session sql_mode = 'STRICT_TRANS_TABLES';

to

set session sql_mode = 'STRICT_all_TABLES';

Otherwise OK to push.

Generated at Thu Feb 08 08:33:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.