[MDEV-26666] Inconsistent behaviors of RAND function Created: 2021-09-23  Updated: 2021-12-20  Resolved: 2021-12-20

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.5.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: dinary Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: innodb
Environment:

OS: Ubuntu-20.04


 Description    

/* init */ create table t(a int);
 
/* init */ insert into t values (0), (1), (2);
 
 
 
mariadb> select * from t where rand('test');
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
|    2 |
 
+------+
 
2 rows in set, 1 warning (0.01 sec)
 
 
 
mariadb> update t set a = + 1 where rand('test');
 
ERROR 1292 (22007): Truncated incorrect INTEGER value: 'test'

The function RAND() with a string type argument has different meanings in SELECT and UPDATE statements, which can cause inconsistent problems.



 Comments   
Comment by Sergei Golubchik [ 2021-12-20 ]

that's how SQL_MODE=STRICT_ALL_TABLES works now.

The first statement does not modify any tables, so you only get a warning about truncated value. The second statement modifies a table, so under "strict" sql_mode the warning is converted into an error

Generated at Thu Feb 08 09:47:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.