Still reproducible on 5.5 and 10.0, as of 5.5.48+ and 10.0.24+.
Not reproducible on current 10.1 tree, apparently it was fixed in 10.1 by this commit:
commit b05158cc10a75196b5c0bf8dad9360608a2dd5b9
|
Author: Sergei Petrunia <psergey@askmonty.org>
|
Date: Wed Feb 24 17:14:38 2016 +0300
|
|
|
MDEV-8988: Apparently valid SQL query gives wrong result (nested WHERE)
|
|
- "Early NULLs filtering" optimization used to "peel off" Item_ref and
|
Item_direct_ref wrappers from an outside column reference before
|
adding "outer_table_col IS NOT NULL" into JOIN::outer_ref_cond.
|
- When this happened in a subquery that was evaluated in a post-GROUP-BY
|
context, attempt to evaluate JOIN::outer_ref_cond would fetch an
|
incorrect value of outer_table_col.
|
It's still worth fixing in previous versions, unless of course it's a totally harmless debug assertion failure (in which case it might make sense to get rid of it).
Adding a dummy clause that uses the column in the subquery does not fail the above ASSERT
|
|
SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c AND NOT (c LIKE '%booooom!%') ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field; |
field COUNT( DISTINCT c ) |
0 1
|
Running subquery with Ignore Index
EXPLAIN EXTENDED
|
SELECT ( SELECT COUNT(*) FROM t1 IGNORE INDEX(a) where a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t2 range b b 11 NULL 1 100.00 Using index condition; Using filesort |
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where |
|
|
SELECT ( SELECT COUNT(*) FROM t1 IGNORE INDEX(a) where a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field; |
field COUNT( DISTINCT c ) |
0 1
|
|
When adding ORDER BY NULL to the above query, we still se the ASSERT failing
EXPLAIN EXTENDED
|
SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field ORDER BY NULL; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t2 range b b 11 NULL 1 100.00 Using index condition; Using filesort |
2 DEPENDENT SUBQUERY t1 ref a a 11 test.t2.c 2 100.00 Using where; Using index |
Warnings:
|
Note 1276 Field or reference 'test.t2.c' of SELECT #2 was resolved in SELECT #1 |
Note 1003 select <expr_cache><`test`.`t2`.`c`>((select count(0) from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`c`))) AS `field`,count(distinct `test`.`t2`.`c`) AS `COUNT( DISTINCT c )` from `test`.`t2` where (`test`.`t2`.`b` <= 'quux') group by <expr_cache><`test`.`t2`.`c`>((select count(0) from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`c`))) order by NULL |
SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT( DISTINCT c ) FROM t2 WHERE b <= 'quux' GROUP BY field ORDER BY NULL; |
main.bug-6486 [ fail ]
|
|
frame #0: 0x000000010003e574 mysqld`find_all_keys(param=0x0000700000115468, select=0x000000010961c438, fs_info=0x0000700000115388, buffpek_pointers=0x0000700000115568, tempfile=0x0000700000115738, pq=0x0000000000000000, found_rows=0x0000700000115a10) + 292 at filesort.cc:689
|
(lldb) p sort_form
|
(TABLE *) $1 = 0x000000010969d070
|
(lldb) p sort_form->alias
|
(String) $2 = {
|
Ptr = 0x0000000109633470 "t2" |
str_length = 2
|
Alloced_length = 8
|
extra_alloc = 0
|
alloced = true |
thread_specific = false |
str_charset = 0x000000010103b6e0
|
}
|
(lldb) p sort_form->read_set
|
(MY_BITMAP *) $3 = 0x000000010969d180
|
(lldb) p sort_form->read_set->bitmap[0]
|
(my_bitmap_map) $4 = 3
|
The value of sort_form->read_set->bitmap[0], shows that the columns with field index 0 and 1 are set for read( which in our case our column 'b' and column 'c')
Continuing debugging ....
|
|
719 sort_form->read_set= &sort_form->tmp_set;
|
(lldb) p sort_form->read_set
|
(MY_BITMAP *) $7 = 0x000000010969d1e0
|
(lldb) p sort_form->read_set->bitmap[0]
|
(my_bitmap_map) $8 = 0
|
Here the bitmap is unset for the columns 'b' and 'c'. Also keep track of the address of
sort_form->read_set.
Going further:
721 if (quick_select) |
722 select->quick->add_used_key_part_to_set(sort_form->read_set);
|
(lldb) p sort_form->read_set->bitmap[0]
|
(my_bitmap_map) $9 = 1
|
(lldb) p sort_form->read_set
|
(MY_BITMAP *) $10 = 0x000000010969d1e0
|
Now we come to the function Field_blob::var_str and then bitmap_is_set
(lldb) bt
|
* thread #4: tid = 0x7290, 0x00000001000126ff mysqld`bitmap_is_set(map=0x000000010969d180, bit=1) + 15 at my_bitmap.h:130, stop reason = step in |
* frame #0: 0x00000001000126ff mysqld`bitmap_is_set(map=0x000000010969d180, bit=1) + 15 at my_bitmap.h:130
|
frame #1: 0x000000010002a142 mysqld`Field_blob::val_str(this=0x00000001096216a8, val_buffer=0x0000700000114168, val_ptr=0x0000700000114168) + 98 at field.cc:7484 |
|
|
(lldb) p map
|
(const MY_BITMAP *) $13 = 0x000000010969d1e0 |
(lldb) p map->bitmap[0]
|
(my_bitmap_map) $14 = 1
|
|
So here we see that we have the new table->read_set for which column 'c' is not set to be read, so the ASSERT fails.
Debugged the difference.
So, find_all_keys does call Item_subselect::walk, which calls walk() for t1.a=t2.c.
The issue is that t2.c is represented by Item_outer_ref(Item_direct_ref(Item_copy_string( ...))).
Item_copy_string does have a pointer to an Item_field(t2.c) in Item_copy::item but not implement Item::walk method.
Adding a walk() method makes the testcase pass:
~/dev-git/10.0$ git diff
|
diff --git a/sql/item.h b/sql/item.h
|
index 2f8607d..f7f308c 100644
|
--- a/sql/item.h
|
+++ b/sql/item.h
|
@@ -4116,6 +4116,13 @@ class Item_copy :public Item
|
virtual double val_real() = 0;
|
virtual longlong val_int() = 0;
|
virtual int save_in_field(Field *field, bool no_conversions) = 0;
|
+
|
+ //psergey-added:
|
+ bool walk(Item_processor processor, bool walk_subquery, uchar *args)
|
+ {
|
+ return (item->walk(processor, walk_subquery, args)) ||
|
+ (this->*processor)(args);
|
+ }
|
};
|
It would be nicer to have a better understanding of the issue, though 
After adding the above patch the entire main suite passes.
Note that 10.2 does not use Item_copy_string object (although 10.2 codebase has it).
The reference to t2.c in subquery's WHERE is represented by Item_outer_ref( Item_direct_ref ( Item_field ( t2.c)))
10.1 still uses Item_outer_ref(Item_direct_ref(Item_copy_string( ...))).
Item_copy_string is created in setup_copy_fields.
setup_copy_fields is the only function that creates Item_copy_XXX objects, they have one use case.
varun, please make a patch with the above 'walk' method and the testcase.
Ok to push.