MySQL Bugs: #84445: select distinct with constant value for left join mysql 5.7.16

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

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           |
+------+-------------+

Hello SANGHATI,

Thank you for the report and test case.

Thanks,
Umesh

-- 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)

-- 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]>

Please update us ASAP. This is an urgent production issue.

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)

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)

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>

This is probably a duplicate of bug#26627181, which was fixed in 5.7.21 and 8.0.4.

I have a similar problem on Windows, MySQL 8.0.21

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 + 1 as id from t) b
on a.id = b.id;

This select prints 3 rows with const_field = "a"
id	const_field
"2"	"a"
"2"	"a"
"2"	"a"

But with distinct I get an empty const_field:
SELECT distinct a.id,b.const_field
from (select id from t where id=2 ) a
left join (select 'a' as const_field, id + 1 as id from t) b
on a.id = b.id;

id	const_field
"2"	(NULL)