| Bug #79576 | Equivalent subqueries returns different results | ||
|---|---|---|---|
| Submitted: | 9 Dec 2015 14:30 | Modified: | 14 Dec 2015 8:55 |
| Reporter: | Su Dylan | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[9 Dec 2015 14:30]
Su Dylan
[9 Dec 2015 15:15]
MySQL Verification Team
Thank you for the bug report. 5.7 now behaves according SQL Standard please upgrade: [miguel@miguel-xps ~]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.7.10 MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> drop table if exists t1,t2; insert into t1 values(1,1),(2,2);Query OK, 0 rows affected (0.35 sec) mysql> create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.30 sec) mysql> insert into t1 values(1,1),(2,2); Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> create table t2(c1 int, c2 int); Query OK, 0 rows affected (0.33 sec) mysql> insert into t2 values(1,1),(2,2); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select t1.c2, t1.c2 is null, max(t1.c1), (select count(*) from t2), (select count(*) from t2 where t1.c2 is null) from t1 where t1.c1 = 10; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.t1.c2'; this is incompatible with sql_mode=only_full_group_by mysql>
[10 Dec 2015 2:03]
Su Dylan
Hi Miguel, Please try to recreate with sql_mode 'STRICT_ALL_TABLES': set sql_mode='strict_all_tables'; drop table if exists t1,t2; create table t1(c1 int, c2 int); insert into t1 values(1,1),(2,2); create table t2(c1 int, c2 int); insert into t1 values(1,1),(2,2); select t1.c2, t1.c2 is null, max(t1.c1), (select count(*) from t2), (select count(*) from t2 where t1.c2 is null) from t1 where t1.c1 = 10; Output: ====== mysql> set sql_mode='strict_all_tables'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop table if exists t1,t2; Query OK, 0 rows affected (0.00 sec) mysql> create table t1(c1 int, c2 int); insert into t1 values(1,1),Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(1,1),(2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> create table t2(c1 int, c2 int); rt into t1 values(1,1),(2,2); select t1.c2, t1.c2 is null, max(t1Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(1,1),(2,2); selectQuery OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select t1.c2, t1.c2 is null, max(t1.c1), (select count(*) from t2), (select count(*) from t2 where t1.c2 is null) from t1 where t1.c1 = 10; +------+---------------+------------+---------------------------+-----------------------------------------------+ | c2 | t1.c2 is null | max(t1.c1) | (select count(*) from t2) | (select count(*) from t2 where t1.c2 is null) | +------+---------------+------------+---------------------------+-----------------------------------------------+ | NULL | 1 | NULL | 0 | NULL | +------+---------------+------------+---------------------------+-----------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.8-rc | +-----------+ 1 row in set (0.00 sec)
[10 Dec 2015 12:17]
MySQL Verification Team
Prior to 5.6 version was handled how you expect, however still a non-Standard SQL compliance so let us our development folks to decide. Thanks. C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.48 Source distribution PULL: 2015-DEC-05 Copyright (c) 2000, 2015, 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.5 > use test Database changed mysql 5.5 > drop table if exists t1,t2; Query OK, 0 rows affected (0.14 sec) mysql 5.5 > create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql 5.5 > insert into t1 values(1,1),(2,2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.5 > create table t2(c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql 5.5 > insert into t2 values(1,1),(2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.5 > select t1.c2, t1.c2 is null, max(t1.c1), (select count(*) from t2), (select count(*) from t2 where t1.c2 is null) from t1 where t1.c1 = 10; +------+---------------+------------+---------------------------+-----------------------------------------------+ | c2 | t1.c2 is null | max(t1.c1) | (select count(*) from t2) | (select count(*) from t2 where t1.c2 is null) | +------+---------------+------------+---------------------------+-----------------------------------------------+ | NULL | 1 | NULL | 2 | 2 | +------+---------------+------------+---------------------------+-----------------------------------------------+ C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.29 Source distribution PULL: 2015-DEC-05 Copyright (c) 2000, 2015, 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 > drop table if exists t1,t2; Query OK, 0 rows affected (0.41 sec) mysql 5.6 > create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.08 sec) mysql 5.6 > insert into t1 values(1,1),(2,2); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.6 > create table t2(c1 int, c2 int); Query OK, 0 rows affected (0.03 sec) mysql 5.6 > insert into t2 values(1,1),(2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.6 > select t1.c2, t1.c2 is null, max(t1.c1), (select count(*) from t2), (select count(*) from t2 where t1.c2 is null) from t1 where t1.c1 = 10; +------+---------------+------------+---------------------------+-----------------------------------------------+ | c2 | t1.c2 is null | max(t1.c1) | (select count(*) from t2) | (select count(*) from t2 where t1.c2 is null) | +------+---------------+------------+---------------------------+-----------------------------------------------+ | NULL | 1 | NULL | 2 | NULL | +------+---------------+------------+---------------------------+-----------------------------------------------+ 1 row in set (0.00 sec) C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.11 Source distribution PULL: 2015-DEC-05 Copyright (c) 2000, 2015, 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 > set sql_mode = ''; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql 5.7 > use test Database changed mysql 5.7 > drop table if exists t1,t2; Query OK, 0 rows affected (0.08 sec) mysql 5.7 > create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.03 sec) mysql 5.7 > insert into t1 values(1,1),(2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.7 > create table t2(c1 int, c2 int); Query OK, 0 rows affected (0.05 sec) mysql 5.7 > insert into t2 values(1,1),(2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.7 > select t1.c2, t1.c2 is null, max(t1.c1), (select count(*) from t2), (select count(*) from t2 where t1.c2 is null) from t1 where t1.c1 = 10; +------+---------------+------------+---------------------------+-----------------------------------------------+ | c2 | t1.c2 is null | max(t1.c1) | (select count(*) from t2) | (select count(*) from t2 where t1.c2 is null) | +------+---------------+------------+---------------------------+-----------------------------------------------+ | NULL | 1 | NULL | 2 | NULL | +------+---------------+------------+---------------------------+-----------------------------------------------+ 1 row in set (0.00 sec)
[10 Dec 2015 14:28]
Manyi Lu
This bug is not reproducible in 5.7, please consider upgrade.
[10 Dec 2015 15:17]
Su Dylan
Hi Miguel and Manyi, I am totally confused. After Miguel has provided the output on MySQL 5.7 and marked it as verified, does it become unrepeatable again? Hi Manyi, Can you kindly provide the version and release of MySQL 5.7 you use for recreation and past your result in the comment for our reference? Thank you very much. Quote from previous execution result from Miguel: =========== C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.11 Source distribution PULL: 2015-DEC-05 Copyright (c) 2000, 2015, 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 > set sql_mode = ''; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql 5.7 > use test Database changed mysql 5.7 > drop table if exists t1,t2; Query OK, 0 rows affected (0.08 sec) mysql 5.7 > create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.03 sec) mysql 5.7 > insert into t1 values(1,1),(2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.7 > create table t2(c1 int, c2 int); Query OK, 0 rows affected (0.05 sec) mysql 5.7 > insert into t2 values(1,1),(2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 5.7 > select t1.c2, t1.c2 is null, max(t1.c1), (select count(*) from t2), (select count(*) from t2 where t1.c2 is null) from t1 where t1.c1 = 10; +------+---------------+------------+---------------------------+-----------------------------------------------+ | c2 | t1.c2 is null | max(t1.c1) | (select count(*) from t2) | (select count(*) from t2 where t1.c2 is null) | +------+---------------+------------+---------------------------+-----------------------------------------------+ | NULL | 1 | NULL | 2 | NULL | +------+---------------+------------+---------------------------+-----------------------------------------------+ 1 row in set (0.00 sec) ===========
[11 Dec 2015 8:55]
Manyi Lu
Posted by developer: The bug is reproducible in 5.7 with ONLY_FULL_GROUP_BY turned on.
[11 Dec 2015 8:58]
Manyi Lu
Reproducible in 5.7 with ONLY_FULL_GROUP_BY turned on.
[11 Dec 2015 13:58]
Guilhem Bichot
Hello Su Dylan. Imagine there would be at least two rows matching the WHERE: select t1.c2, t1.c2 is null, max(t1.c1), (select count(*) from t2), (select count(*) from t2 where t1.c2 is null) from t1 where t1.c1 = 10; MAX() imposes that one row only be returned (implicit grouping). Then, what is the value of t1.c2 which should be returned? In SQL2011 terms, t1.c2 is neither aggregated nor functionally dependent on group columns (which here means being constant), so the query is illegal. Enabling only_full_group_by shows it well: ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.t1.c2'; this is incompatible with sql_mode=only_full_group_by Now, if one chooses to not run with this mode (which mode is on by default in 5.7, precisely to guard against such queries). In your testcase there are no rows matching the WHERE. MAX() imposes that one row be returned. What should be returned for t1.c2? We have nothing to return, so we return NULL. Then "t1.c2 is null" returns TRUE. Then (select count(*) from t2): it is a constant subquery (uncorrelated to t1), so we can calculate it and return 2. Finally (select count(*) from t2 where t1.c2 is null) is a correlated subquery: it depends on t1.c2; but t1.c2 is not really NULL: in fact we have no value for it; so we return NULL for the subq (can't calculate the subq). It's not unreasonable behaviour. One could even argue that "t1.c2 IS NULL" should say NULL as we don't know what t1.c2 really is. But, the essence of this is that it's a non-deterministic, non-Standard query, and the default only_full_group_by is there to say it. One can possibly find "surprising behaviours" with only_full_group_by off, but those queries are generally wrong in their design.
