MySQL Bugs: #36632: Select distinct from a simple view on an InnoDB table returns incorrect results

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

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.

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)

Same on server 5.0.51b.
6.04 works as expected!

(32 bit servers on Windows)

oops .. my mistake.  5.0.51b is ok too.

Michael I,

please look at this deterministic bug.

--Heikki

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)

This urgent bug is now assigned to both Vasil and Michael I. This is an exercise for Michael I.

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

Calvin just confirmed that 6.0.4 works and 6.0.6 does not work (i.e. wrong result).

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.

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.

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

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.

Pushed into 5.1.28 and 6.0.7-alpha

Noted in 5.1.28, 6.0.7 changelogs.

Pushed into 5.1.28  (revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (version source revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (pib:3)