| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.1.14 | OS: | Windows (Windows, Linux) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[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.
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.