[MDEV-28027] Never SELECT the first row when using RAND function Created: 2022-03-09  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.7.3, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: John Jove Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: innodb
Environment:

OS: Ubuntu-20.04


 Description   

The function RAND() with a string type argument is TRUE in WHERE clause, but the SELECT statements with this WHERE clause never select the first row.

/* init */ DROP TABLE IF EXISTS t;
 
/* init */ CREATE TABLE t(c1 INT);
 
/* init */ INSERT INTO t(c1) VALUES (3), (2), (1);
 
 
 
mariadb> SELECT RAND('t');
 
+---------------------+
 
| RAND('t')           |
 
+---------------------+
 
| 0.15522042769493574 |
 
+---------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
mariadb> SELECT * FROM t WHERE RAND('t');
 
+------+
 
| c1   |
 
+------+
 
|    2 |
 
|    1 |
 
+------+
 
2 rows in set, 1 warning (0.00 sec)



 Comments   
Comment by Alice Sherepa [ 2022-03-09 ] 

MariaDB [test]> SELECT * FROM t where rand('t');
 
+------+
 
| c1   |
 
+------+
 
|    2 |
 
|    1 |
 
+------+
 
2 rows in set, 1 warning (0.002 sec)
 
 
 
Warning (Code 1292): Truncated incorrect INTEGER value: 't'

The value 't' was truncated to 0. RAND with the seed returns the same repeatable sequence:

MariaDB [test]> select rand(0) from t;
 
+---------------------+
 
| rand(0)             |
 
+---------------------+
 
| 0.15522042769493574 |
 
|   0.620881741513388 |
 
|  0.6387474552157777 |
 
+---------------------+
 
3 rows in set (0.002 sec)

Currently, if the result of the where expression is >=0.5 then it is evaluated to TRUE, else - FALSE
That is why the first row was not selected, 0.1552204< 0.5

MariaDB [test]> select * from t where rand('t')+0.5;
 
+------+
 
| c1   |
 
+------+
 
|    1 |
 
|    2 |
 
|    3 |
 
+------+
 
3 rows in set, 1 warning (0.002 sec)

It is different with constants, they are always evaluated as true (except 0/false):

MariaDB [test]> SELECT 1 from dual where 1e-1;
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
+---+
 
1 row in set (0.001 sec)


MariaDB [test]> SELECT 1 from dual where 1e-1 + rand()*0;
 
Empty set (0.001 sec)

Comment by John Jove [ 2022-03-11 ]

Thank you for your reply.

But, "the where expression is >=0.5 then it is evaluated to TRUE" is weired. In MySQL, "SELECT * FROM t WHERE RAND('t');" returns all the records. Please see:

/* init */ DROP TABLE IF EXISTS t;
 
/* init */ CREATE TABLE t(c1 INT);
 
/* init */ INSERT INTO t(c1) VALUES (3), (2), (1);
 
 
 
mysql> SELECT RAND('t');
 
+---------------------+
 
| RAND('t')           |
 
+---------------------+
 
| 0.15522042769493574 |
 
+---------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
mysql> SELECT RAND(0) FROM t;
 
+---------------------+
 
| RAND(0)             |
 
+---------------------+
 
| 0.15522042769493574 |
 
|   0.620881741513388 |
 
|  0.6387474552157777 |
 
+---------------------+
 
3 rows in set (0.00 sec)
 
 
 
mysql> SELECT * FROM t WHERE RAND('t');
 
+------+
 
| c1   |
 
+------+
 
|    3 |
 
|    2 |
 
|    1 |
 
+------+
 
3 rows in set, 1 warning (0.00 sec)

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