MySQL Bugs: #12625: SELECT DISTINCT with leading constant returns only one row

Bug #12625	SELECT DISTINCT with leading constant returns only one row
Submitted:	17 Aug 2005 16:19	Modified:	25 Aug 2005 11:13
Reporter:	Anders Karlsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.13	OS:
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
A SELECT statement where there is a leading constant in the row, only a single row is returned, even though the resultset contains several distinct rows.

First seen on 5.0.10 on Linux, but also reproduces on 5.0.11 on Windows.

How to repeat:
From the mysql prompt:
mysql> create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values(1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(1, 3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(1, 4);
Query OK, 1 row affected (0.00 sec)

// The following is the correct result.
mysql> select distinct c1, c2 from t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
+------+------+
4 rows in set (0.00 sec)

// The following is the obviously incorrect result.
mysql> select distinct 1, c2 from t1;
+---+------+
| 1 | c2   |
+---+------+
| 1 |    1 |
+---+------+
1 row in set (0.00 sec)

mysql>

Verified against 4.1.14 and 5.0.12 BK snapshots.  Does not affect 4.0.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
SELECT DISTINCT 1, a FROM t1;
SELECT DISTINCT a, 1 FROM t1;

The bug manifests itself with non-leading constant in the DISTINCT list as well.
It can bee seen from the following example:

mysql> CREATE TABLE t2 (a int, b int);
Query OK, 0 rows affected (0.27 sec)

mysql> INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT a, b, 2 FROM t2;
+------+------+---+
| a    | b    | 2 |
+------+------+---+
|    1 |    1 | 2 |
|    2 |    2 | 2 |
|    2 |    3 | 2 |
|    2 |    4 | 2 |
|    3 |    5 | 2 |
+------+------+---+
5 rows in set (0.00 sec)

mysql> SELECT DISTINCT 2, a, b FROM t2;
+---+------+------+
| 2 | a    | b    |
+---+------+------+
| 2 |    1 |    1 |
| 2 |    2 |    2 |
| 2 |    3 |    5 |
+---+------+------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT a, 2, b FROM t2;
+------+---+------+
| a    | 2 | b    |
+------+---+------+
|    1 | 2 |    1 |
|    2 | 2 |    2 |
|    3 | 2 |    5 |
+------+---+------+
3 rows in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28516

Approved by email.

ChangeSet
  1.2382 05/08/19 01:57:22 igor@rurik.mysql.com +3 -0
  distinct.test, distinct.result:
    Added test cases for bug #12625.
  sql_select.cc:
    Fixed bug #12625.
    Fixed invalid removal of constant items from the DISTINCT
    list in the function create_distinct_group.

The bug will appear in 4.1.15 and 5.0.12

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:

Documented bugfix in 4.1.15 and 5.0.13 changelogs. Closed.

Correction: Fix was documented for 5.0.12, not 5.0.13 as previously stated.

The bug still exists:

mysql> select distinct w1.location_type t1 from locations w1 where w1.name in ("Philadelphia") and w1.location_type = 'city';
Empty set (0.00 sec)

mysql> select  w1.location_type t1 from locations w1 where w1.name in ("Philadelphia") and w1.location_type = 'city';
+------+
| t1   |
+------+
| city |
| city |
| city |
| city |
| city |
+------+
5 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.18-log |
+------------+
1 row in set (0.02 sec)