MySQL Bugs: #35002: Wrong results returned when using derived table and group by

Bug #35002	Wrong results returned when using derived table and group by
Submitted:	3 Mar 2008 14:17	Modified:	4 Mar 2008 16:20
Reporter:	Laurent Monnoye	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.23-rc/5.1BK/5.0/6.0	OS:	Linux (2.6.22.9-61.fc6)
Assigned to:		CPU Architecture:	Any
Tags:	derived table, GROUP BY, wrong results

Description:
Same query returns different value after adding NOT NULL constraint

[daxtest01]> create table lmo (         timestamp       datetime         ,price          DOUBLE         ,bar_date       DATE         ,bar_id         INT         ,bar_ts         INT );
Query OK, 0 rows affected (0.00 sec)

[daxtest01]> LOAD DATA          INFILE '/tmp/lmo.txt'          INTO TABLE lmo         FIELDS                  TERMINATED BY ','         LINES                 TERMINATED BY '\n';
Query OK, 1529121 rows affected (4.33 sec)
Records: 1529121  Deleted: 0  Skipped: 0  Warnings: 0

[daxtest01]> create index ix1 on lmo(bar_date, bar_id, bar_ts) ;
Query OK, 1529121 rows affected (13.98 sec)
Records: 1529121  Duplicates: 0  Warnings: 0

[daxtest01]> select count(1) from (SELECT                         bar_date                         ,bar_id, max(bar_ts)                                          FROM                         lmo where bar_date='2003-03-25' GROUP BY                         bar_date                         ,bar_id) I1 ;
+----------+
| count(1) |
+----------+
|       50 | 
+----------+
1 row in set (0.01 sec)

[daxtest01]> alter table lmo modify bar_ts int not null ;
Query OK, 1529121 rows affected (11.90 sec)
Records: 1529121  Duplicates: 0  Warnings: 0

[daxtest01]> select count(1) from (SELECT                         bar_date                         ,bar_id, max(bar_ts)                                          FROM                         lmo where bar_date='2003-03-25' GROUP BY                         bar_date                         ,bar_id) I1 ;
+----------+
| count(1) |
+----------+
|       51 | 
+----------+
1 row in set (0.00 sec)

How to repeat:
create table, load data from file, create index, run query (wrong result returned: 50), add NOT NULL constraint, rerun query (right result returned: 51).

Suggested fix:
Adding NOT NULL constraint to a field shouldn't change the returned data set.

Thank you for the bug report. Could you please provide the dump file at
ftp://ftp.mysql.com/pub/mysql/upload using a name like bug35002.tar.gz
and comment here when done. Thanks in advance.

Sorry, I can't use ftp from here (firewall). Any other alternative?

It is possible for me to download from your server if you provide the
URL for. If you wish print the URL in private comment. Thanks in advance.

File can be downloaded from http://www.xotrading.com/data
Please confirm when you have downloaded it, so I can remove the file from our webserver.

I downloaded it. Thank you for the feedback.

Thank you for the bug report.

create table lmo (timestamp datetime,price  DOUBLE ,bar_date  DATE ,
bar_id  INT,bar_ts INT );
LOAD DATA INFILE '/home/miguel/dbs/lmo.txt' INTO TABLE lmo        
FIELDS  TERMINATED BY ',' LINES TERMINATED BY '\n';
create index ix1 on lmo(bar_date, bar_id, bar_ts) ;

select count(1) from (SELECT bar_date ,bar_id, max(bar_ts) FROM                
lmo where bar_date='2003-03-25' GROUP BY bar_date ,bar_id) I1 ;

alter table lmo modify bar_ts int not null ;

select count(1) from (SELECT bar_date ,bar_id, max(bar_ts) FROM                
lmo where bar_date='2003-03-25' GROUP BY bar_date ,bar_id) I1 ;

[miguel@mira dbs]$ 5.1/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.24-rc-debug Source distribution

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

mysql> create table lmo (timestamp datetime,price  DOUBLE ,bar_date  DATE ,
    -> bar_id  INT,bar_ts INT );
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD DATA INFILE '/home/miguel/dbs/lmo.txt' INTO TABLE lmo        
    -> FIELDS  TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 1529121 rows affected (10.86 sec)
Records: 1529121  Deleted: 0  Skipped: 0  Warnings: 0

mysql> create index ix1 on lmo(bar_date, bar_id, bar_ts) ;
Query OK, 1529121 rows affected (13.19 sec)
Records: 1529121  Duplicates: 0  Warnings: 0

mysql> 
mysql> select count(1) from (SELECT bar_date ,bar_id, max(bar_ts) FROM                
    -> lmo where bar_date='2003-03-25' GROUP BY bar_date ,bar_id) I1 ;
+----------+
| count(1) |
+----------+
|       50 | 
+----------+
1 row in set (0.00 sec)

mysql> 
mysql> alter table lmo modify bar_ts int not null ;
Query OK, 1529121 rows affected (16.33 sec)
Records: 1529121  Duplicates: 0  Warnings: 0

mysql> 
mysql> select count(1) from (SELECT bar_date ,bar_id, max(bar_ts) FROM                
    -> lmo where bar_date='2003-03-25' GROUP BY bar_date ,bar_id) I1 ;
+----------+
| count(1) |
+----------+
|       51 | 
+----------+
1 row in set (0.01 sec)

mysql>