Bug #36632 Select distinct from a simple view on an InnoDB table returns incorrect results
Submitted: 9 May 2008 17:09 Modified: 23 Jul 2008 17:50
Reporter: Galt Johnson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.1.24-rc (64-bit server) OS:Any (Mac OS X 10.5.2, Ubuntu 64bit)
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: distinct, innodb, VIEW

[9 May 2008 17:09] Galt Johnson
Description:
Selecting a distinct column from a simple view on an InnoDB table where the column is the primary key of the underlying table returns incorrect results.

How to repeat:
create table t(pk int not null primary key auto_increment, d varchar(20)) engine=innodb;
insert into t(d) values('row 1'), ('row 2'), ('row 3'), ('row 4'), ('row 5');
create or replace view tv as select pk, d from t where pk < 20;
select distinct pk from tv;

+----+
| pk |
+----+
|  2 | 
|  3 | 
|  4 | 
|  5 | 
|  5 | 
+----+
5 rows in set (0.00 sec)

The result is clearly incorrect. This only occurs for InnoDB tables. Creating the view with "algorithm=temptable" works, but is obviously not acceptable for large tables.
[9 May 2008 17:51] Susanne Ebrecht
Many thanks for writing a bug report.

I could reproduce it as described:

create table t(pk int not null primary key auto_increment, d varchar(20)) engine=innodb;
insert into t(d) values('row 1'), ('row 2'), ('row 3'), ('row 4'), ('row 5');
create or replace view tv as select pk, d from t where pk < 20;
select distinct pk from tv;

+----+
| pk |
+----+
|  2 | 
|  3 | 
|  4 | 
|  5 | 
|  5 | 
+----+

The number '1' is missing here and the number '5' is duplicated.

Test with MyISAM:
select distinct pk from tv;
+----+
| pk |
+----+
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+
5 rows in set (0.00 sec)
[10 May 2008 14:29] Peter Laursen
Same on server 5.0.51b.
6.04 works as expected!

(32 bit servers on Windows)
[10 May 2008 14:31] Peter Laursen
oops .. my mistake.  5.0.51b is ok too.
[9 Jun 2008 13:52] Heikki Tuuri
Michael I,

please look at this deterministic bug.

--Heikki
[9 Jun 2008 18:55] Calvin Sun
6.0 tree with all 5.1 patches merged show the same wrong results:

Server version: 6.0.6-alpha-pro Source distribution

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

mysql> use test;
Database changed
mysql> create table t(pk int not null primary key auto_increment, d varchar(20)
 engine=innodb;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into t(d) values('row 1'), ('row 2'), ('row 3'), ('row 4'), ('row
5');
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create view tv as select pk, d from t where pk < 20;
Query OK, 0 rows affected (0.03 sec)

mysql> select distinct pk from tv;
+----+
| pk |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
|  5 |
+----+
5 rows in set (0.01 sec)

mysql> select * from t;
+----+-------+
| pk | d     |
+----+-------+
|  1 | row 1 |
|  2 | row 2 |
|  3 | row 3 |
|  4 | row 4 |
|  5 | row 5 |
+----+-------+
5 rows in set (0.00 sec)
[11 Jun 2008 15:24] Heikki Tuuri
This urgent bug is now assigned to both Vasil and Michael I. This is an exercise for Michael I.
[11 Jun 2008 15:44] Vasil Dimov
Simpler test case:

create table t(pk int primary key) engine=innodb;

insert into t values (1), (2), (3), (4);

create or replace view tv as select pk from t where pk < 20;

select distinct pk from tv;

result -> 2,3,4,4

Some observations:

* if "where pk < 20" is removed, the result is correct - 1,2,3,4

* if we insert 1,2,3 (no 4) then the result is correct - 1,2,3
[11 Jun 2008 16:47] Vasil Dimov
Calvin just confirmed that 6.0.4 works and 6.0.6 does not work (i.e. wrong result).
[11 Jun 2008 22:41] Calvin Sun
5.1.23 also works fine:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-rc-community MySQL Community Server (GPL)

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

mysql> use test
Database changed
mysql> select distinct pk from tv;
+----+
| pk |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.27 sec)

The regression is very likely caused by changes in the server code between 5.1.23 and 5.1.24, not caused by InnoDB changes.
[11 Jun 2008 23:25] Calvin Sun
I have determined that this is not an InnoDB bug. It works fine with 5.1.23-rc-community server. It is equally fine with 5.1.23-rc-community server plus the latest InnoDB code for 5.1.

Change the category to Server:Views and unassign Vasil as the owner.
[19 Jun 2008 6:55] Bugs System
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/commits/48136

2669 Gleb Shchepa	2008-06-19
      Fixed bug #36632: SELECT DISTINCT from a simple view on an
                        InnoDB table, where all selected columns
                        belong to the same unique index key, returns
                        incorrect results
[27 Jun 2008 20:36] Bugs System
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/commits/48680

2669 Gleb Shchepa	2008-06-27
      Fixed bug #36632: SELECT DISTINCT from a simple view on an
                        InnoDB table, where all selected columns
                        belong to the same unique index key, returns
                        incorrect results
      
      Server executes some queries via QUICK_GROUP_MIN_MAX_SELECT
      (MIN/MAX optimization for queries with GROUP BY or DISTINCT
      clause) and that optimization implies loose index scan, so all
      grouping is done by the QUICK_GROUP_MIN_MAX_SELECT::get_next
      method.
      
      The server does not set the precomputed_group_by flag for some
      QUICK_GROUP_MIN_MAX_SELECT queries and duplicates grouping by
      call to the end_send_group function.
      
      Fix: when the test_if_skip_sort_order function selects loose 
      index scan as a best way to satisfy an ORDER BY/GROUP BY type
      of query, the precomputed_group_by flag has been set to use 
      end_send/end_write functions instead of end_send_group/
      end_write_group functions.
[23 Jul 2008 9:10] Georgi Kodinov
Pushed into 5.1.28 and 6.0.7-alpha
[23 Jul 2008 17:50] Paul DuBois
Noted in 5.1.28, 6.0.7 changelogs.
[28 Jul 2008 16:47] Bugs System
Pushed into 5.1.28  (revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (version source revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (pib:3)