MySQL Bugs: #50995: Having clause on subquery result produces incorrect results.

Bug #50995	Having clause on subquery result produces incorrect results.
Submitted:	8 Feb 2010 14:19	Modified:	20 Jun 2010 17:51
Reporter:	Adam Trotter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.1.42, 5.1.43, 5.1.45-bzr	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	regression

Description:
If a query is written with a subquery which references data in the outer query, and the outer query is using an index to resolve the where clause and has an order by and having clause against the results of the subquery, the having clause will produce incorrect results.

The issue was discovered on 5.1.42 running on CentOS, and is repeatable on 5.1.43 running on Windows 7.

If we ignore the index, we get the correct results.  Also, if a group by is added, or the order by is removed, we get correct results.

Finally, if we run the original query again, but, the data types on the temp table are char, we get correct results.  (I have tested, bigint, int, tinyint, and decimal types for the fields, they are all broken).  With a char data type, the issue seems to be fixed only because a temporary table is being used to resolve the query.

How to repeat:
drop temporary table if exists t1;
create temporary table t1
(
 id1 bigint unsigned not null,
 id2 bigint unsigned not null,
 index t1_id2_index(id2)
);

insert into t1 set id1=1, id2=1;
insert into t1 set id1=2, id2=1;
insert into t1 set id1=3, id2=1;

select bug.id1,
       (select 0
        from dual
        where bug.id1=bug.id1
       ) as amount
from t1 as bug
where bug.id2 = 1
having amount > 0
order by bug.id1;

Explain / Query Results

Attachment: mysql_bug_doc.sql (application/octet-stream, text), 7.78 KiB.

Verified just as described with recent 5.1.45 from bzr:

77-52-24-143:5.1 openxs$ 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 2
Server version: 5.1.45-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
    -> (
    ->  id1 bigint unsigned not null,
    ->  id2 bigint unsigned not null,
    ->  index t1_id2_index(id2)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 set id1=1, id2=1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 set id1=2, id2=1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 set id1=3, id2=1;
Query OK, 1 row affected (0.00 sec)

mysql> select bug.id1,
    ->        (select 0
    ->         from dual
    ->         where bug.id1=bug.id1
    ->        ) as amount
    -> from t1 as bug
    -> where bug.id2 = 1
    -> having amount > 0
    -> order by bug.id1;
+-----+--------+
| id1 | amount |
+-----+--------+
|   1 |      0 |
|   2 |      0 |
|   3 |      0 |
+-----+--------+
3 rows in set (0.01 sec)

mysql> explain select bug.id1,
    ->        (select 0
    ->         from dual
    ->         where bug.id1=bug.id1
    ->        ) as amount
    -> from t1 as bug
    -> where bug.id2 = 1
    -> having amount > 0
    -> order by bug.id1;
+----+--------------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+
| id | select_type        | table | type | possible_keys | key          | key_len | ref   | rows | Extra                       |
+----+--------------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+
|  1 | PRIMARY            | bug   | ref  | t1_id2_index  | t1_id2_index | 8       | const |    2 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | NULL  | NULL | NULL          | NULL         | NULL    | NULL  | NULL | No tables used              |
+----+--------------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+
2 rows in set (0.00 sec)

mysql> explain select bug.id1,        (select 0         from dual         where bug.id1=bug.id1        ) as amount from t1 as bug ignore index(t1_id2_index) where bug.id2 = 1 having amount > 0 order by bug.id1;
+----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | PRIMARY            | bug   | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used              |
+----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------+
2 rows in set (0.00 sec)

mysql> select bug.id1,        (select 0         from dual         where bug.id1=bug.id1        ) as amount from t1 as bug ignore index(t1_id2_index) where bug.id2 = 1 having amount > 0 order by bug.id1;
Empty set (0.01 sec)

5.0.91 is NOT affected, so this is a regression comparing to 5.0:

77-52-24-143:5.0 openxs$ 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.0.91-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.00 sec)

mysql> create temporary table t1
    -> (
    ->  id1 bigint unsigned not null,
    ->  id2 bigint unsigned not null,
    ->  index t1_id2_index(id2)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 set id1=1, id2=1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 set id1=2, id2=1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 set id1=3, id2=1;
Query OK, 1 row affected (0.00 sec)

mysql> select bug.id1,
    ->        (select 0
    ->         from dual
    ->         where bug.id1=bug.id1
    ->        ) as amount
    -> from t1 as bug
    -> where bug.id2 = 1
    -> having amount > 0
    -> order by bug.id1;
Empty set (0.00 sec)

5.1.37 is NOT affected also, so this is a recent regression!

77-52-24-143:mysql openxs$ 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.37 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create temporary table t1 (  id1 bigint unsigned not null,  id2 bigint unsigned not null,  index t1_id2_index(id2) );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 set id1=1, id2=1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 set id1=2, id2=1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 set id1=3, id2=1;
Query OK, 1 row affected (0.00 sec)

mysql> select bug.id1,
    ->        (select 0
    ->         from dual
    ->         where bug.id1=bug.id1
    ->        ) as amount
    -> from t1 as bug
    -> where bug.id2 = 1
    -> having amount > 0
    -> order by bug.id1;
Empty set (0.00 sec)

5.1.41 does not have this issue.  I compiled a debug build of 5.1.42 and ultimately determined the problem was a change in sql_select.cc between 5.1.41 and 5.1.42.  I can't find the change documented in a bug fix anywhere.

In 5.1.41, lines 2125 to 2136 of sql_select.cc was:

	else					// This should never happen
	{
	  if (!(curr_table->select->cond=
		new Item_cond_and(curr_table->select->cond,
				  sort_table_cond)))
	    DBUG_VOID_RETURN;
	  /*
	    Item_cond_and do not need fix_fields for execution, its parameters
	    are fixed or do not need fix_fields, too
	  */
	  curr_table->select->cond->quick_fix_field();
	}

In 5.1.42, this was changed to:

	else
	{
	  if (!(curr_table->select->cond=
		new Item_cond_and(curr_table->select->cond,
				  sort_table_cond)))
	    DBUG_VOID_RETURN;
	  curr_table->select->cond->fix_fields(thd, 0);
	}

Reverting 

curr_table->select->cond->fix_fields(thd, 0);

to

curr_table->select->cond->quick_fix_field();

seems to fix the issue in both 5.1.42 and 5.1.43.

This problem is result of Bug#48052 fix.

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/100517

3344 Sergey Glukhov	2010-02-16
      Bug#50995 Having clause on subquery result produces incorrect results.
      This is the result of bug#48052 fix.
      The problem is that we can not use cond->fix_fields(thd, 0) here as
      it will break condition if it's AND condition. In our case fix_fields()
      cuts off 'having' condition.
      The fix:
      reverted fix_fields() to quick_fix_field() and
      updated cond->used_tables_cache with appropriate value.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        reverted fix_fields() to quick_fix_field() and
        updated cond->used_tables_cache with appropriate value.

Review sent by email.

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/101443

3354 Sergey Glukhov	2010-02-25
      Bug#50995 Having clause on subquery result produces incorrect results.
      The problem is that cond->fix_fields(thd, 0) breaks condition(cuts off 'having').
      The bug actually not in fix_fields but in the base_list_iterator::replace(&list) function.
      The base_list::last points to the 'next' member of the last element in list.
      This code in the base_list_iterator::replace:
       if ((list->last == &current->next) && (new_list.elements > 1))
         list->last= new_list.last;
      should extend the list when we actually append on list to another. But because
      the 'next' member is the first in the list_node structure the 'last' is the same
      as &current->next when current is one prior to the last element in list.
      All this leads to stripping off last part of AND/OR expression while optimizing
      AND-OR condition tree.
      Another probelm is fixed here is that we should not add an item to condition if
      this item is 0.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/item_cmpfunc.h
        added ASSERT to make sure that we do not add zero item
     @ sql/sql_list.h
        The base_list::last points to the 'next' member of the last element in list.
        This code in the base_list_iterator::replace:
         if ((list->last == &current->next) && (new_list.elements > 1))
           list->last= new_list.last;
        should extend the list when we actually append on list to another. But because
        the 'next' member is the first in the list_node structure the 'last' is the same
        as &current->next when current is one prior to the last element in list.
        The fix is to move list_node *next from first position in struct.
     @ sql/sql_select.cc
        skip adding an item to condition if
        this item is 0 or empty list.

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/101597

3358 Sergey Glukhov	2010-02-26
      Bug#50995 Having clause on subquery result produces incorrect results.
      The problem is that cond->fix_fields(thd, 0) breaks
      condition(cuts off 'having'). The reason of that is
      that NULL valued Item pointer is present in the
      middle of Item list and it breaks the Item processing
      loop.
     @ mysql-test/r/having.result
        test case
     @ mysql-test/t/having.test
        test case
     @ sql/item_cmpfunc.h
        added ASSERT to make sure that we do not add NULL valued Item pointer
     @ sql/sql_select.cc
        skip adding an item to condition if Item pointer is NULL.
        skip adding a list to condition if this list is empty.

Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:sergey.glukhov@sun.com-20100226113925-mxwn1hfxe3l8khc4) (merge vers: 5.1.45) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100226130631-8czhisohzf6jyo2x) (merge vers: 5.5.3-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100301093944-a4rvrmqqco6c0qao) (pib:16)

Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

Referring to a subquery result in a HAVING clause could produce
incorrect results.

Noted in 5.1.43sp1 changelog.

Pushed into 5.1.47 (revid:build@mysql.com-20100412101220-adgau1r18kwgthps) (version source revid:build@mysql.com-20100412101220-adgau1r18kwgthps) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100429203306-tg0wz4y2xyx8edrl) (merge vers: 5.5.5-m3) (pib:16)

Already fixed.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)