#At file:///home/gluh/MySQL/mysql-5.1-bug-47669/ based on revid:sergey.glukhov@stripped
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.
modified:
mysql-test/r/having.result
mysql-test/t/having.test
sql/sql_select.cc
=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result 2010-02-16 11:14:07 +0000
+++ b/mysql-test/r/having.result 2010-02-16 15:04:37 +0000
@@ -431,3 +431,21 @@ b COUNT(DISTINCT a)
NULL 1
DROP TABLE t1;
End of 5.0 tests
+CREATE TABLE t1
+(
+id1 BIGINT UNSIGNED NOT NULL,
+id2 BIGINT UNSIGNED NOT NULL,
+INDEX id1(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 t1.id1,
+(SELECT 0 FROM DUAL
+WHERE t1.id1=t1.id1) AS amount FROM t1
+WHERE t1.id2 = 1
+HAVING amount > 0
+ORDER BY t1.id1;
+id1 amount
+DROP TABLE t1;
+End of 5.1 tests
=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test 2009-01-16 15:38:38 +0000
+++ b/mysql-test/t/having.test 2010-02-16 15:04:37 +0000
@@ -443,3 +443,28 @@ SELECT b, COUNT(DISTINCT a) FROM t1 GROU
DROP TABLE t1;
--echo End of 5.0 tests
+
+#
+# Bug#50995 Having clause on subquery result produces incorrect results.
+#
+CREATE TABLE t1
+(
+ id1 BIGINT UNSIGNED NOT NULL,
+ id2 BIGINT UNSIGNED NOT NULL,
+ INDEX id1(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 t1.id1,
+(SELECT 0 FROM DUAL
+ WHERE t1.id1=t1.id1) AS amount FROM t1
+WHERE t1.id2 = 1
+HAVING amount > 0
+ORDER BY t1.id1;
+
+DROP TABLE t1;
+
+--echo End of 5.1 tests
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-02-16 09:13:49 +0000
+++ b/sql/sql_select.cc 2010-02-16 15:04:37 +0000
@@ -2172,11 +2172,15 @@ JOIN::exec()
curr_table->select->cond= sort_table_cond;
else
{
+ table_map new_cond_used_tables=
+ curr_table->select->cond->used_tables();
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);
+ curr_table->select->cond->quick_fix_field();
+ ((Item_cond_and*) curr_table->select->cond)->used_tables_cache=
+ (new_cond_used_tables | sort_table_cond->used_tables());
}
curr_table->select_cond= curr_table->select->cond;
curr_table->select_cond->top_level_item();
Attachment: [text/bzr-bundle] bzr/sergey.glukhov@sun.com-20100216150437-0tsknza8oxda8bym.bundle
