| Bug #52361 | Ifnull(Subquery) produces incorrect values | ||
|---|---|---|---|
| Submitted: | 25 Mar 2010 12:42 | Modified: | 29 Mar 2010 15:22 |
| Reporter: | Adam Trotter | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.1.45, 5.1.46-bzr | OS: | Linux |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | ifnull, regression, subquery | ||
[25 Mar 2010 12:44]
Adam Trotter
Meant to tag this S2, not S3 :)
[25 Mar 2010 12:47]
Adam Trotter
Results of the query: +----+---------------------+-------+ | id | name | total | +----+---------------------+-------+ | 1 | Test 1 (Has Amount) | 30.70 | | 2 | Test 2 (No Amount) | 30.00 | | 3 | Test 3 (No Amount) | 30.00 | | 4 | Test 4 (Has Amount) | 30.13 | | 5 | Test 5 (No Amount) | 30.00 | | 6 | Test 6 (No Amount) | 30.00 | | 1 | bug | 0.00 | +----+---------------------+-------+ Expected results: +----+---------------------+-------+ | id | name | total | +----+---------------------+-------+ | 1 | Test 1 (Has Amount) | 30.70 | | 2 | Test 2 (No Amount) | 0 | | 3 | Test 3 (No Amount) | 0 | | 4 | Test 4 (Has Amount) | 30.13 | | 5 | Test 5 (No Amount) | 0 | | 6 | Test 6 (No Amount) | 0 | | 1 | bug | 0 | +----+---------------------+-------+
[25 Mar 2010 12:59]
Valeriy Kravchuk
With 5.1.43 I've got correct results:
mysql> (
-> select t1.id,
-> t1.name,
-> ifnull(
-> (select sum(amount)
-> from t3
-> where t3.t1_id=t1.id
-> ),0) as total
-> from t1
-> force index(primary)
-> left join t2 on t2.t1_id=t1.id
-> group by t1.id
-> )
-> union
-> (select 1 as id,
-> 'bug' as name,
-> 0 as ytd_total
-> from dual
-> );
+----+---------------------+-------+
| id | name | total |
+----+---------------------+-------+
| 1 | Test 1 (Has Amount) | 30.70 |
| 2 | Test 2 (No Amount) | 0.00 |
| 3 | Test 3 (No Amount) | 0.00 |
| 4 | Test 4 (Has Amount) | 30.13 |
| 5 | Test 5 (No Amount) | 0.00 |
| 6 | Test 6 (No Amount) | 0.00 |
| 1 | bug | 0.00 |
+----+---------------------+-------+
7 rows in set (0.11 sec)
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.43-community-log |
+----------------------+
1 row in set (0.02 sec)
So, this looks like recent regression.
[25 Mar 2010 13:05]
Valeriy Kravchuk
Verified just as described with recent 5.1.46 from bzr on Linux:
openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.46-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop temporary table if exists t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create temporary table t1
-> (
-> id int unsigned,
-> name varchar(50),
-> primary key(id)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> insert into t1 set id=1, name='Test 1 (Has Amount)';
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 set id=2, name='Test 2 (No Amount)';
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 set id=3, name='Test 3 (No Amount)';
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 set id=4, name='Test 4 (Has Amount)';
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 set id=5, name='Test 5 (No Amount)';
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 set id=6, name='Test 6 (No Amount)';
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> drop temporary table if exists t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create temporary table t2
-> (
-> id int unsigned,
-> t1_id int unsigned,
-> primary key(id),
-> index t1id_index(t1_id)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into t2 set id=1, t1_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 set id=2, t1_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 set id=3, t1_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 set id=4, t1_id=1;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into t2 set id=5, t1_id=4;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 set id=6, t1_id=4;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 set id=7, t1_id=4;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 set id=8, t1_id=4;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> drop temporary table if exists t3;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create temporary table t3
-> (
-> t1_id int unsigned,
-> amount decimal(18,2)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into t3 set t1_id=1, amount=10.15;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 set t1_id=1, amount=10.25;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 set t1_id=1, amount=10.30;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 set t1_id=4, amount=10.01;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 set t1_id=4, amount=10.03;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 set t1_id=4, amount=10.09;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> (
-> select t1.id,
-> t1.name,
-> ifnull(
-> (select sum(amount)
-> from t3
-> where t3.t1_id=t1.id
-> ),0) as total
-> from t1
-> force index(primary)
-> left join t2 on t2.t1_id=t1.id
-> group by t1.id
-> )
-> union
-> (select 1 as id,
-> 'bug' as name,
-> 0 as ytd_total
-> from dual
-> );
+----+---------------------+-------+
| id | name | total |
+----+---------------------+-------+
| 1 | Test 1 (Has Amount) | 30.70 |
| 2 | Test 2 (No Amount) | 30.00 |
| 3 | Test 3 (No Amount) | 30.00 |
| 4 | Test 4 (Has Amount) | 30.13 |
| 5 | Test 5 (No Amount) | 30.00 |
| 6 | Test 6 (No Amount) | 30.00 |
| 1 | bug | 0.00 |
+----+---------------------+-------+
7 rows in set (0.02 sec)
[26 Mar 2010 8:50]
Gleb Shchepa
This bug is a regression of the fix for the bug #50843: Filesort used instead of clustered index led to performance degradation. Note: that fix changes the execution plan in this way: id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +1 PRIMARY t1 index NULL PRIMARY 4 NULL 3 1 PRIMARY t2 ref t1id_index t1id_index 5 test.t1.id 2 Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 6 Using where 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL
Description: I am not certain this is a problem with the query optimizer as this must be reproduced in a very specific way and it seems as if it's both an issue with the query optimizer and handling of particular data types. Given the sample tables and data, records 2, 3, 5, and 6 in t1 should produce a NULL result from the subquery (and do). However, if the subquery is wrapped with an ifnul(subquery,0), it returns the portion of the number before the decimal from the last row that produced a result, and drops the digits after the decimal. This is not an issue in 5.1.44, so it's a new issue to 5.1.45. All of the following must happen for the bug to appear: Unioning the query with the bug to another query (without the union, the query works fine) The query is using an index (in this case the primary key) on the main table. A left join must be present such that for the records that will contain information that is going to be summed, they produce multiple rows. A group by must be present to collapse the duplicated rows produced by the left join. All of the above leads me to believe that this is a problem with the query optimizer, however, doing a simple operation on the ifnull fixes the issue as well. If instead of saying ifnull(subquery,0) we say ifnull(subquery,'0') or the result of the subquery is ran through the cast function first, the problem does not happen. Also, if the decimal(18,2) field is changed to a double, the problem does not happen either. How to repeat: drop temporary table if exists t1; create temporary table t1 ( id int unsigned, name varchar(50), primary key(id) ); insert into t1 set id=1, name='Test 1 (Has Amount)'; insert into t1 set id=2, name='Test 2 (No Amount)'; insert into t1 set id=3, name='Test 3 (No Amount)'; insert into t1 set id=4, name='Test 4 (Has Amount)'; insert into t1 set id=5, name='Test 5 (No Amount)'; insert into t1 set id=6, name='Test 6 (No Amount)'; drop temporary table if exists t2; create temporary table t2 ( id int unsigned, t1_id int unsigned, primary key(id), index t1id_index(t1_id) ); insert into t2 set id=1, t1_id=1; insert into t2 set id=2, t1_id=1; insert into t2 set id=3, t1_id=1; insert into t2 set id=4, t1_id=1; insert into t2 set id=5, t1_id=4; insert into t2 set id=6, t1_id=4; insert into t2 set id=7, t1_id=4; insert into t2 set id=8, t1_id=4; drop temporary table if exists t3; create temporary table t3 ( t1_id int unsigned, amount decimal(18,2) ); insert into t3 set t1_id=1, amount=10.15; insert into t3 set t1_id=1, amount=10.25; insert into t3 set t1_id=1, amount=10.30; insert into t3 set t1_id=4, amount=10.01; insert into t3 set t1_id=4, amount=10.03; insert into t3 set t1_id=4, amount=10.09; ( select t1.id, t1.name, ifnull( (select sum(amount) from t3 where t3.t1_id=t1.id ),0) as total from t1 force index(primary) left join t2 on t2.t1_id=t1.id group by t1.id ) union (select 1 as id, 'bug' as name, 0 as ytd_total from dual );