Bug #84445 select distinct with constant value for left join mysql 5.7.16
Submitted: 9 Jan 2017 9:29 Modified: 25 Sep 2018 22:51
Reporter: SANGHATI GHOSH Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.7.16, 5.7.17 OS:Linux
Assigned to: CPU Architecture:Any
Tags: distinct, join, left, MySQL

[9 Jan 2017 9:29] SANGHATI GHOSH
Description:
drop table if exists t;
create table  t(id int);
insert into t values(1),(2),(2),(2);

I need to left join the table with itself. It has some constant values as well. The right side is not returning any value. Here is a simplified version of the query: 

select a.id,b.const_field 
from (select id from t where id=2 ) a 
left join (select 'a' as const_field, id from t where 1=2 ) b 
on a.id = b.id; 
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
|    2 | NULL        |
|    2 | NULL        |
+------+-------------+

Now, for MySQL 5.7, when we add a distinct to the clause, the const_field field automatically adds the constant value. Query: 

select distinct a.id,b.const_field 
from (select id from t where id=2 ) a 
left join (select 'a' as const_field, id from t where 1=2 ) b 
on a.id = b.id; 
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | a           |
+------+-------------+

Whereas in In MySQL 5.5, Oracle, SQLServer we got the proper result which is 2, (NULL). Upgrading MySQL version to 5.7 is causing this issue 

The same issue is happening in 5.7 version for group by, order by also.

How to repeat:
drop table if exists t;
create table  t(id int);
insert into t values(1),(2),(2),(2);

select a.id,b.const_field 
from (select id from t where id=2 ) a 
left join (select 'a' as const_field, id from t where 1=2 ) b 
on a.id = b.id; 
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
|    2 | NULL        |
|    2 | NULL        |
+------+-------------+

select distinct a.id,b.const_field 
from (select id from t where id=2 ) a 
left join (select 'a' as const_field, id from t where 1=2 ) b 
on a.id = b.id; 
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | a           |
+------+-------------+
[9 Jan 2017 10:38] Umesh Shastry
Hello SANGHATI,

Thank you for the report and test case.

Thanks,
Umesh
[9 Jan 2017 10:39] Umesh Shastry
-- 5.7.17

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.17: bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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.

root@localhost [(none)]> use test
Database changed
root@localhost [test]> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test]> create table  t(id int);
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> insert into t values(1),(2),(2),(2);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@localhost [test]>
root@localhost [test]> select a.id,b.const_field
    -> from (select id from t where id=2 ) a
    -> left join (select 'a' as const_field, id from t where 1=2 ) b
    -> on a.id = b.id;
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
|    2 | NULL        |
|    2 | NULL        |
+------+-------------+
3 rows in set (0.00 sec)

root@localhost [test]> select distinct a.id,b.const_field
    -> from (select id from t where id=2 ) a
    -> left join (select 'a' as const_field, id from t where 1=2 ) b
    -> on a.id = b.id;
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | a           |
+------+-------------+
1 row in set (0.01 sec)
[9 Jan 2017 10:41] Umesh Shastry
-- 5.6.35
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.35: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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.

root@localhost [(none)]> use test
Database changed
root@localhost [test]> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test]> create table  t(id int);
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> insert into t values(1),(2),(2),(2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@localhost [test]>
root@localhost [test]> select a.id,b.const_field
    -> from (select id from t where id=2 ) a
    -> left join (select 'a' as const_field, id from t where 1=2 ) b
    -> on a.id = b.id;
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
|    2 | NULL        |
|    2 | NULL        |
+------+-------------+
3 rows in set (0.00 sec)

root@localhost [test]> select distinct a.id,b.const_field
    -> from (select id from t where id=2 ) a
    -> left join (select 'a' as const_field, id from t where 1=2 ) b
    -> on a.id = b.id;
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
+------+-------------+
1 row in set (0.00 sec)

-- 5.5.54

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.5.54: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.54-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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.

root@localhost [(none)]> use test
Database changed
root@localhost [test]> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test]> create table  t(id int);
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> insert into t values(1),(2),(2),(2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@localhost [test]>
root@localhost [test]> select a.id,b.const_field
    -> from (select id from t where id=2 ) a
    -> left join (select 'a' as const_field, id from t where 1=2 ) b
    -> on a.id = b.id;
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
|    2 | NULL        |
|    2 | NULL        |
+------+-------------+
3 rows in set (0.02 sec)

root@localhost [test]> select distinct a.id,b.const_field
    -> from (select id from t where id=2 ) a
    -> left join (select 'a' as const_field, id from t where 1=2 ) b
    -> on a.id = b.id;
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
+------+-------------+
1 row in set (0.01 sec)

root@localhost [test]>
[12 Jan 2017 10:22] SANGHATI GHOSH
Please update us ASAP. This is an urgent production issue.
[12 Jan 2017 11:34] Øystein Grøvlen
Posted by developer:
 
Work-around is to turn off optimizer_switch derived_merge:

mysql> set optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0,00 sec)

mysql> select distinct a.id,b.const_field from (select id from t where id=2 ) a left join (select 'a' as const_field, id from t where 1=2 ) b on a.id = b.id;
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
+------+-------------+
1 row in set (0,00 sec)
[1 Aug 2017 3:20] ashe sun
mysql-5.7.18 repeat it

mysql> select @@version;
+------------------+
| @@version        |
+------------------+
| 5.7.18-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table  t(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(1),(2),(2),(2);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select a.id,b.const_field 
    -> from (select id from t where id=2 ) a 
    -> left join (select 'a' as const_field, id from t where 1=2 ) b 
    -> on a.id = b.id; 
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
|    2 | NULL        |
|    2 | NULL        |
+------+-------------+
3 rows in set (0.00 sec)

mysql> select a.id,b.const_field 
    -> from (select id from t where id=2 ) a 
    -> left join (select 'a' as const_field, id from t where 1=2 ) b 
    -> on a.id = b.id; 
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
|    2 | NULL        |
|    2 | NULL        |
+------+-------------+
3 rows in set (0.00 sec)

mysql> select distinct a.id,b.const_field 
    -> from (select id from t where id=2 ) a 
    -> left join (select 'a' as const_field, id from t where 1=2 ) b 
    -> on a.id = b.id; 
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | a           |
+------+-------------+
1 row in set (0.00 sec)

mysql> set optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> select distinct a.id,b.const_field  from (select id from t where id=2 ) a  left join (select 'a' as const_field, id from t where 1=2 ) b  on a.id = b.id;
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
+------+-------------+
1 row in set (0.00 sec)

mysql> select a.id,b.const_field  from (select id from t where id=2 ) a  left join (select 'a' as const_field, id from t where 1=2 ) b  on a.id = b.id;
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
|    2 | NULL        |
|    2 | NULL        |
+------+-------------+
3 rows in set (0.00 sec)
[7 Sep 2018 11:48] Sveta Smirnova
Not repeatable with 5.7.21 and 8.0.12:

sveta@delly:~/build/mysql-8.0/mysql-test$ mysqlmtr -P 13010 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12-debug Source distribution

Copyright (c) 2000, 2018, 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> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table  t(id int);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t values(1),(2),(2),(2);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select a.id,b.const_field 
    -> from (select id from t where id=2 ) a 
    -> left join (select 'a' as const_field, id from t where 1=2 ) b 
    -> on a.id = b.id; 
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
|    2 | NULL        |
|    2 | NULL        |
+------+-------------+
3 rows in set (0.01 sec)

mysql> select distinct a.id,b.const_field 
    -> from (select id from t where id=2 ) a 
    -> left join (select 'a' as const_field, id from t where 1=2 ) b 
    -> on a.id = b.id; 
+------+-------------+
| id   | const_field |
+------+-------------+
|    2 | NULL        |
+------+-------------+
1 row in set (0.00 sec)

mysql>
[25 Sep 2018 22:51] Roy Lyseng
This is probably a duplicate of bug#26627181, which was fixed in 5.7.21 and 8.0.4.