| Bug #100209 | Using LEAST function results as where condition returns wrong result | ||
|---|---|---|---|
| Submitted: | 14 Jul 2020 6:55 | Modified: | 14 Jul 2020 14:07 |
| Reporter: | Yushan ZHANG | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | FUNCTION, LEAST | ||
[14 Jul 2020 13:40]
MySQL Verification Team
Hi Mr. ZHANG, Thank you for your bug report. However, this is not a bug. First of all, I ran your test case and the first query returns the same as your query, while the second one returns an empty set. This is expected behaviour, since your first query does not have any filtering on the table. Hence, all rows are returned. Not a bug.
[14 Jul 2020 14:01]
Yushan ZHANG
I checked again this is reproducible in the newest docker container and also the container with tag 5.7.31, see: https://hub.docker.com/layers/mysql/mysql-server/5.7.31-1.1.17/images/sha256-6d6fdd5bd3125... mysql> -- incorrect mysql> SELECT `col0` FROM t2 WHERE LEAST( `col0`, 4115067113428877640, ( -> 3893438203380391466 < `col0` ), `col0`, ( POWER( `col0`, 3 ) ) ) ORDER -> BY `col0`; +--------------------------------------+ | col0 | +--------------------------------------+ | 14648816-5fab-4247-a36b-4cdf983cb147 | +--------------------------------------+ 1 row in set, 12 warnings (0.00 sec) mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper Connection id: 3 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.31 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 2 min 39 sec Threads: 1 Questions: 44 Slow queries: 0 Opens: 111 Flush tables: 1 Open tables: 29 Queries per second avg: 0.276
[14 Jul 2020 14:05]
Yushan ZHANG
The second query returns wrong result in 5.7.31, it returns correct result in 8.0.21.
[14 Jul 2020 14:08]
MySQL Verification Team
Hi Mr. ZHANG, You are actually correct. This is a bug only in 5.7. Verified as reported.
Description: -- correct mysql> SELECT LEAST( `col0`, 4115067113428877640, ( 3893438203380391466 < `col0` ), `col0`, ( POWER( `col0`, 3 ) ) ) FROM t2; +--------------------------------------------------------------------------------------------------------+ | LEAST( `col0`, 4115067113428877640, ( 3893438203380391466 < `col0` ), `col0`, ( POWER( `col0`, 3 ) ) ) | +--------------------------------------------------------------------------------------------------------+ | 0 | | 0 | | 0 | +--------------------------------------------------------------------------------------------------------+ 3 rows in set, 12 warnings (0.00 sec) -- incorrect, should return empty set mysql> SELECT `col0` FROM t2 WHERE LEAST( `col0`, 4115067113428877640, ( 3893438203380391466 < `col0` ), `col0`, ( POWER( `col0`, 3 ) ) ) ORDER BY `col0`; +--------------------------------------+ | col0 | +--------------------------------------+ | 14648816-5fab-4247-a36b-4cdf983cb147 | +--------------------------------------+ 1 row in set, 12 warnings (0.00 sec) How to repeat: drop table if exists t2; create table t2 (col0 CHAR(74), col1 DOUBLE NOT NULL, col2 TEXT); insert into t2 values ('385d3104-1a51-4b85-a774-1095bba75a52', '498.40643610657617', 'x'); insert into t2 values ('a4c8b07c-88dc-4a91-b0fb-2dfd2278d862', '-785.8119347627421', 'x'); insert into t2 values ('14648816-5fab-4247-a36b-4cdf983cb147', '-38.19581534807992', 'e'); -- correct SELECT LEAST( `col0`, 4115067113428877640, ( 3893438203380391466 < `col0` ), `col0`, ( POWER( `col0`, 3 ) ) ) FROM t2; -- incorrect SELECT `col0` FROM t2 WHERE LEAST( `col0`, 4115067113428877640, ( 3893438203380391466 < `col0` ), `col0`, ( POWER( `col0`, 3 ) ) ) ORDER BY `col0`; drop table t2;