| Bug #74105 | Qualified comparisons return true when subquery contains nulls | ||
|---|---|---|---|
| Submitted: | 26 Sep 2014 12:16 | Modified: | 26 Sep 2014 12:41 |
| Reporter: | Александр Изъюров | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.5.38-0ubuntu0.14.04.1, 5.5.41, 5.1.74 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[26 Sep 2014 12:40]
MySQL Verification Team
Hello Александр, Thank you for the report and test case. Verified as described. Thanks, Umesh
[26 Sep 2014 12:41]
MySQL Verification Team
// 5.5.41
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.5.41 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.41-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql> create table source(s varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> create table pattern(p varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into source values ('a'), ('x');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into pattern values ('b'), (null);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select s from source where s < all(select p from pattern);
+------+
| s |
+------+
| a |
+------+
1 row in set (0.00 sec)
// 5.6.22
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.22 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.22-enterprise-commercial-advanced-log |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> create table source(s varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> create table pattern(p varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> insert into source values ('a'), ('x');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into pattern values ('b'), (null);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select s from source where s < all(select p from pattern);
Empty set (0.00 sec)
[26 Sep 2014 12:44]
MySQL Verification Team
5.7 not affected (5.1 yes). Let's see 5.6.
C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.1 > use test
Database changed
mysql 5.1 > create table source(s varchar(10));
Query OK, 0 rows affected (0.08 sec)
mysql 5.1 > create table pattern(p varchar(10));
Query OK, 0 rows affected (0.11 sec)
mysql 5.1 >
mysql 5.1 > insert into source values ('a'), ('x');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.1 > insert into pattern values ('b'), (null);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.1 >
mysql 5.1 > select s from source where s < all(select p from pattern);
+------+
| s |
+------+
| a |
+------+
1 row in set (0.00 sec)
mysql 5.1 > exit
Bye
C:\dbs>net start mysqld57
The mysqld57 service is starting....
The mysqld57 service was started successfully.
C:\dbs>57
C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.6-m16 Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > use test
Database changed
mysql 5.7 > create table source(s varchar(10));
Query OK, 0 rows affected (0.30 sec)
mysql 5.7 > create table pattern(p varchar(10));
Query OK, 0 rows affected (0.30 sec)
mysql 5.7 > insert into source values ('a'), ('x');
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.7 > insert into pattern values ('b'), (null);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.7 > select s from source where s < all(select p from pattern);
Empty set (0.00 sec)
mysql 5.7 >
[26 Sep 2014 12:46]
MySQL Verification Team
So only <5.5 are affected:
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22 Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.6 > use test
Database changed
mysql 5.6 > create table source(s varchar(10));
Query OK, 0 rows affected (0.55 sec)
mysql 5.6 > create table pattern(p varchar(10));
Query OK, 0 rows affected (0.69 sec)
mysql 5.6 > insert into source values ('a'), ('x');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.6 > insert into pattern values ('b'), (null);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.6 > select s from source where s < all(select p from pattern);
Empty set (0.03 sec)
mysql 5.6 >
Description: select * from a where a.x <= ALL( subquery ) returns non-empty result set when subquery contains NULLs. It happens for <, <=, >, >=. = ALL and <> ALL results are correct when subquery contains nulls. How to repeat: create table source(s varchar(10)); create table pattern(p varchar(10)); insert into source values ('a'), ('x'); insert into pattern values ('b'), (null); select s from source where s < all(select p from pattern); Expected: empty result set Actual: +------+ | s | +------+ | a | +------+