Bug #13056 "SELECT DISTINCT" query with constant field
Submitted: 8 Sep 2005 8:53 Modified: 22 Sep 2005 10:00
Reporter: Eugene Andrewhin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:MySQL 4.1.14-nt OS:Windows (Windows XP SP2)
Assigned to: CPU Architecture:Any

[8 Sep 2005 8:53] Eugene Andrewhin
Description:
"SELECT DISTINCT const, field" query with constant field first and some field second (contains differents values) in field list return single row.

How to repeat:
Query:
  SELECT DISTINCT 1, b FROM (select 2 b union select 3) x;
in MySQL version 4.1.12 and lowest return:
+------+------+
| 1    | b    |
+------+------+
|    1 |    2 |
+------+------+
|    1 |    3 |
+------+------+
but in version 4.1.14 return single row:
+------+------+
| 1    | b    |
+------+------+
|    1 |    2 |
+------+------+
[8 Sep 2005 9:19] Valeriy Kravchuk
It is a bug, really:

mysql> SELECT 1, b FROM (select 2 b union select 3 b) x;
+---+---+
| 1 | b |
+---+---+
| 1 | 2 |
| 1 | 3 |
+---+---+
2 rows in set (0.01 sec)

mysql> SELECT distinct b FROM (select 2 b union select 3 b) x;
+---+
| b |
+---+
| 2 |
| 3 |
+---+
2 rows in set (0.00 sec)

mysql> SELECT distinct 1,b FROM (select 2 b union select 3 b) x;
+---+---+
| 1 | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

mysql> SELECT distinct b,1 FROM (select 2 b union select 3 b) x;
+---+---+
| b | 1 |
+---+---+
| 2 | 1 |
| 3 | 1 |
+---+---+
2 rows in set (0.00 sec)

It depends on the order of elements. Plans are different:

mysql> explain SELECT distinct b,1 FROM (select 2 b union select 3 b) x;
+----+--------------+------------+------+---------------+------+---------+------
+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref
| rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------
+------+-----------------+
|  1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL |    NULL | NULL
|    2 | Using temporary |
|  2 | DERIVED      | NULL       | NULL | NULL          | NULL |    NULL | NULL
| NULL | No tables used  |
|  3 | UNION        | NULL       | NULL | NULL          | NULL |    NULL | NULL
| NULL | No tables used  |
|NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL |    NULL | NULL
 | NULL |                 |
+----+--------------+------------+------+---------------+------+---------+------
+------+-----------------+
4 rows in set (0.02 sec)

mysql> explain SELECT distinct 1,b FROM (select 2 b union select 3 b) x;
+----+--------------+------------+------+---------------+------+---------+------
+------+----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref
| rows | Extra          |
+----+--------------+------------+------+---------------+------+---------+------
+------+----------------+
|  1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL |    NULL | NULL
|    2 |                |
|  2 | DERIVED      | NULL       | NULL | NULL          | NULL |    NULL | NULL
| NULL | No tables used |
|  3 | UNION        | NULL       | NULL | NULL          | NULL |    NULL | NULL
| NULL | No tables used |
|NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL |    NULL | NULL
 | NULL |                |
+----+--------------+------------+------+---------------+------+---------+------
+------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.00 sec)
[8 Sep 2005 12:02] MySQL Verification Team
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT DISTINCT 1, b FROM (select 2 b union select 3) x;
+---+---+
| 1 | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.14 sec)

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT DISTINCT 1, b FROM (select 2 b union select 3) x;
+---+---+
| 1 | b |
+---+---+
| 1 | 2 |
| 1 | 3 |
+---+---+
2 rows in set (0.05 sec)

mysql>