Bug #13646 DISTINCT returns not all expected results
Submitted: 30 Sep 2005 10:28 Modified: 24 Mar 2006 12:24
Reporter: x y Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.14 OS:Microsoft Windows (Windows, Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[30 Sep 2005 10:28] x y
Description:
SELECT DISTINCT
'Hund' AS Hund,
`test`.`Field1`
FROM
`test`

returns only one row.

How to repeat:
1. Create the following table:
CREATE TABLE `test` (
  `Field1` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`Field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`Field1`) VALUES 
  ('a'),
  ('b'),
  ('c');

2. Run the following statement:
SELECT DISTINCT
'Hund' AS Hund,
`test`.`Field1`
FROM
`test`

-> Result is: 1 Row instead of tree

SELECT
'Hund' AS Hund,
`test`.`Field1`
FROM
`test`
GROUP BY
`test`.`Field1`

returns the expected result.
[30 Sep 2005 10:56] Valeriy Kravchuk
Thank you for a bug report. Yes, 4.1.14 works just as you described:

mysql> CREATE TABLE `test` (
    ->   `Field1` varchar(255) NOT NULL default '',
    ->   PRIMARY KEY  (`Field1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO `test` (`Field1`) VALUES
    ->   ('a'),
    ->   ('b'),
    ->   ('c');
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT
    -> 'Hund' AS Hund,
    -> `test`.`Field1`
    -> FROM
    -> `test`;
+------+--------+
| Hund | Field1 |
+------+--------+
| Hund | a      |
+------+--------+
1 row in set (0.05 sec)

mysql> SELECT
    -> 'Hund' AS Hund,
    -> `test`.`Field1`
    -> FROM
    -> `test`
    -> GROUP BY
    -> `test`.`Field1`;
+------+--------+
| Hund | Field1 |
+------+--------+
| Hund | a      |
| Hund | b      |
| Hund | c      |
+------+--------+
3 rows in set (0.00 sec)

mysql> select distinct field1, 'hund' from test;
+--------+------+
| field1 | hund |
+--------+------+
| a      | hund |
| b      | hund |
| c      | hund |
+--------+------+
3 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.06 sec)

And this bhaviour looks like a bug for me.

On 5.0.13-rc it works as we expect:

mysql> use test;
Database changed
mysql> drop table test;
Query OK, 0 rows affected (0.43 sec)

mysql> CREATE TABLE `test` (
    ->   `Field1` varchar(255) NOT NULL default '',
    ->   PRIMARY KEY  (`Field1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.19 sec)

mysql> INSERT INTO `test` (`Field1`) VALUES
    ->   ('a'),
    ->   ('b'),
    ->   ('c');
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT
    -> 'Hund' AS Hund,
    -> `test`.`Field1`
    -> FROM
    -> `test`;
+------+--------+
| Hund | Field1 |
+------+--------+
| Hund | a      |
| Hund | b      |
| Hund | c      |
+------+--------+
3 rows in set (0.02 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)
[24 Mar 2006 12:24] Valeriy Kravchuk
Can not repeat with 4.1.19-BK (ChangeSet@1.2472, 2006-03-20 14:43:02+04:00):

mysql> CREATE TABLE `test` (
    ->   `Field1` varchar(255) NOT NULL default '',
    ->   PRIMARY KEY  (`Field1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `test` (`Field1`) VALUES
    ->   ('a'),
    ->   ('b'),
    ->   ('c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT
    -> 'Hund' AS Hund,
    -> `test`.`Field1`
    -> FROM
    -> `test`;
+------+--------+
| Hund | Field1 |
+------+--------+
| Hund | a      |
| Hund | b      |
| Hund | c      |
+------+--------+
3 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.19    |
+-----------+
1 row in set (0.00 sec)

Bug is fixed somehow, maybe, by backport from 5.0-BK.