Bug #94857 | execute statement doesn't return correct result | ||
---|---|---|---|
Submitted: | 1 Apr 2019 20:37 | Modified: | 4 Apr 2019 13:03 |
Reporter: | Weidong Yu | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Prepared statements | Severity: | S2 (Serious) |
Version: | 5.7.23 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | execute statement |
[1 Apr 2019 20:37]
Weidong Yu
[4 Apr 2019 13:02]
MySQL Verification Team
https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_in > To comply with the SQL standard, IN returns NULL not only if the > expression on the left hand side is NULL, but also if no match is > found in the list and one of the expressions in the list is NULL. I think we can safely say this bug is verified as return is not consistent with manual nor with itself :(. Thanks for the report Bogdan --- mysql [localhost:5725] {msandbox} (test) > create table t (a int); Query OK, 0 rows affected (0.09 sec) mysql [localhost:5725] {msandbox} (test) > prepare st1 from "select NULL in (select 1 from t)"; Query OK, 0 rows affected (0.01 sec) Statement prepared mysql [localhost:5725] {msandbox} (test) > execute st1; +---------------------------+ | NULL in (select 1 from t) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (0.00 sec) mysql [localhost:5725] {msandbox} (test) > insert into t values (1); Query OK, 1 row affected (0.00 sec) mysql [localhost:5725] {msandbox} (test) > execute st1; +---------------------------+ | NULL in (select 1 from t) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (0.00 sec) mysql [localhost:5725] {msandbox} (test) > drop table t; Query OK, 0 rows affected (0.09 sec) mysql [localhost:5725] {msandbox} (test) > create table t (a int); Query OK, 0 rows affected (0.06 sec) mysql [localhost:5725] {msandbox} (test) > select NULL in (select 1 from t); +---------------------------+ | NULL in (select 1 from t) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (0.00 sec) mysql [localhost:5725] {msandbox} (test) > insert into t values (1); Query OK, 1 row affected (0.01 sec) mysql [localhost:5725] {msandbox} (test) > select NULL in (select 1 from t); +---------------------------+ | NULL in (select 1 from t) | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec) mysql [localhost:5725] {msandbox} (test) > select * from t; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql [localhost:5725] {msandbox} (test) > select 1 in (select 1 from t); +------------------------+ | 1 in (select 1 from t) | +------------------------+ | 1 | +------------------------+ 1 row in set (0.00 sec) mysql [localhost:5725] {msandbox} (test) > select 2 in (select 1 from t); +------------------------+ | 2 in (select 1 from t) | +------------------------+ | 0 | +------------------------+ 1 row in set (0.00 sec) mysql [localhost:5725] {msandbox} (test) > select null in (select 1 from t); +---------------------------+ | null in (select 1 from t) | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.00 sec) mysql [localhost:5725] {msandbox} (test) >