Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2.5, 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
The testcase comes from David.Hall.
Load the dataset: source mysql-test/include/dbt3_s001.inc
Then run the two queries:
select *
|
from region
|
where (r_regionkey+1) in (select min(n_regionkey) over() + 1
|
from nation
|
where r_regionkey=n_regionkey);
|
select *
|
from region
|
where (r_regionkey+1) in (select min(n_regionkey) + 1
|
from nation
|
where r_regionkey=n_regionkey);
|
and see that they produce different results:
MariaDB [test]> select *
|
-> from region
|
-> where (r_regionkey+1) in (select min(n_regionkey) over() + 1
|
-> from nation
|
-> where r_regionkey=n_regionkey);
|
+-------------+--------+-------------------------------------------------------------+
|
| r_regionkey | r_name | r_comment |
|
+-------------+--------+-------------------------------------------------------------+
|
| 0 | AFRICA | special Tiresias about the furiously even dolphins are furi |
|
+-------------+--------+-------------------------------------------------------------+
|
MariaDB [test]> select *
|
-> from region
|
-> where (r_regionkey+1) in (select min(n_regionkey) + 1
|
-> from nation
|
-> where r_regionkey=n_regionkey);
|
+-------------+-------------+-----------------------------------------------------------------------------------------------------------------+
|
| r_regionkey | r_name | r_comment |
|
+-------------+-------------+-----------------------------------------------------------------------------------------------------------------+
|
| 0 | AFRICA | special Tiresias about the furiously even dolphins are furi |
|
| 1 | AMERICA | even, ironic theodolites according to the bold platelets wa |
|
| 2 | ASIA | silent, bold requests sleep slyly across the quickly sly dependencies. furiously silent instructions alongside |
|
| 3 | EUROPE | special, bold deposits haggle foxes. platelet |
|
| 4 | MIDDLE EAST | furiously unusual packages use carefully above the unusual, exp |
|
+-------------+-------------+-----------------------------------------------------------------------------------------------------------------+
|
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-
- relates to
-
-
- Confirmed
-
Activity
Debugging, I get here into Item_window_func::val_int:
(gdb) wher
|
#0 Item_window_func::val_int (this=0x7ffff40969f0) at /home/psergey/dev-git/10.2-r2/sql/item_windowfunc.h:877
|
#1 0x0000555555d8bd89 in Item_direct_ref::val_int (this=0x7ffff4098ea8) at /home/psergey/dev-git/10.2-r2/sql/item.cc:8010
|
#2 0x0000555555ddc03b in Item_func_plus::int_op (this=0x7ffff4096b68) at /home/psergey/dev-git/10.2-r2/sql/item_func.cc:1297
|
#3 0x0000555555dda9bc in Item_func_hybrid_field_type::val_int (this=0x7ffff4096b68) at /home/psergey/dev-git/10.2-r2/sql/item_func.cc:937
|
#4 0x0000555555a35bc1 in Item::val_int_result (this=0x7ffff4096b68) at /home/psergey/dev-git/10.2-r2/sql/item.h:1183
|
#5 0x0000555555d822ac in Item_ref_null_helper::val_int (this=0x7ffff409aed0) at /home/psergey/dev-git/10.2-r2/sql/item.cc:4626
|
#6 0x0000555555da0a93 in Arg_comparator::compare_int_signed (this=0x7ffff4161160) at /home/psergey/dev-git/10.2-r2/sql/item_cmpfunc.cc:958
|
#7 0x0000555555db36fc in Arg_comparator::compare (this=0x7ffff4161160) at /home/psergey/dev-git/10.2-r2/sql/item_cmpfunc.h:87
|
#8 0x0000555555da2ce5 in Item_func_eq::val_int (this=0x7ffff41610a0) at /home/psergey/dev-git/10.2-r2/sql/item_cmpfunc.cc:1728
|
#9 0x0000555555b69fea in end_write (join=0x7ffff40984a8, join_tab=0x7ffff41615b8, end_of_records=false) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:19817
|
#10 0x0000555555b7af2d in AGGR_OP::put_record (this=0x7ffff41624a0, end_of_records=false) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:26235
|
#11 0x0000555555b7f7ef in AGGR_OP::put_record (this=0x7ffff41624a0) at /home/psergey/dev-git/10.2-r2/sql/sql_select.h:971
|
#12 0x0000555555b66422 in sub_select_postjoin_aggr (join=0x7ffff40984a8, join_tab=0x7ffff41615b8, end_of_records=false) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:18138
|
#13 0x0000555555b67019 in evaluate_join_record (join=0x7ffff40984a8, join_tab=0x7ffff4161208, error=0) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:18639
|
#14 0x0000555555b66935 in sub_select (join=0x7ffff40984a8, join_tab=0x7ffff4161208, end_of_records=false) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:18417
|
#15 0x0000555555b65ecd in do_select (join=0x7ffff40984a8, procedure=0x0) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:17961
|
#16 0x0000555555b40ee1 in JOIN::exec_inner (this=0x7ffff40984a8) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:3463
|
#17 0x0000555555b403e6 in JOIN::exec (this=0x7ffff40984a8) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:3266
|
#18 0x0000555555e304b5 in subselect_single_select_engine::exec (this=0x7ffff4097868) at /home/psergey/dev-git/10.2-r2/sql/item_subselect.cc:3811
|
#19 0x0000555555e257b5 in Item_subselect::exec (this=0x7ffff4097670) at /home/psergey/dev-git/10.2-r2/sql/item_subselect.cc:714
|
#20 0x0000555555e25dca in Item_in_subselect::exec (this=0x7ffff4097670) at /home/psergey/dev-git/10.2-r2/sql/item_subselect.cc:894
|
#21 0x0000555555e288ad in Item_in_subselect::val_bool (this=0x7ffff4097670) at /home/psergey/dev-git/10.2-r2/sql/item_subselect.cc:1740
|
#22 0x0000555555a35c4d in Item::val_bool_result (this=0x7ffff4097670) at /home/psergey/dev-git/10.2-r2/sql/item.h:1187
|
#23 0x0000555555da26be in Item_in_optimizer::val_int (this=0x7ffff4098fd8) at /home/psergey/dev-git/10.2-r2/sql/item_cmpfunc.cc:1605
|
#24 0x0000555555a35bc1 in Item::val_int_result (this=0x7ffff4098fd8) at /home/psergey/dev-git/10.2-r2/sql/item.h:1183
|
#25 0x0000555555d905b1 in Item_cache_int::cache_value (this=0x7ffff4163258) at /home/psergey/dev-git/10.2-r2/sql/item.cc:9468
|
#26 0x0000555555d9a572 in Item_cache_wrapper::cache (this=0x7ffff41631a0) at /home/psergey/dev-git/10.2-r2/sql/item.cc:8225
|
#27 0x0000555555d8ca5a in Item_cache_wrapper::val_int (this=0x7ffff41631a0) at /home/psergey/dev-git/10.2-r2/sql/item.cc:8279
|
#28 0x0000555555b66cfb in evaluate_join_record (join=0x7ffff4097990, join_tab=0x7ffff409a2d8, error=0) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:18512
|
#29 0x0000555555b66935 in sub_select (join=0x7ffff4097990, join_tab=0x7ffff409a2d8, end_of_records=false) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:18417
|
#30 0x0000555555b65ecd in do_select (join=0x7ffff4097990, procedure=0x0) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:17961
|
#31 0x0000555555b40ee1 in JOIN::exec_inner (this=0x7ffff4097990) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:3463
|
#32 0x0000555555b403e6 in JOIN::exec (this=0x7ffff4097990) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:3266
|
#33 0x0000555555b41559 in mysql_select (thd=0x7ffff4047070, tables=0x7ffff40953a8, wild_num=1, fields=..., conds=0x7ffff4097670, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ffff4097970, unit=0x7ffff404ab50, select_lex=0x7ffff404b280) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:3658
|
#34 0x0000555555b3603a in handle_select (thd=0x7ffff4047070, lex=0x7ffff404aa88, result=0x7ffff4097970, setup_tables_done_option=0) at /home/psergey/dev-git/10.2-r2/sql/sql_select.cc:373
|
#35 0x0000555555b0242b in execute_sqlcom_select (thd=0x7ffff4047070, all_tables=0x7ffff40953a8) at /home/psergey/dev-git/10.2-r2/sql/sql_parse.cc:6415
|
#36 0x0000555555af8458 in mysql_execute_command (thd=0x7ffff4047070) at /home/psergey/dev-git/10.2-r2/sql/sql_parse.cc:3448
|
#37 0x0000555555b05dfe in mysql_parse (thd=0x7ffff4047070, rawbuf=0x7ffff4095088 "select * from region where (r_regionkey+1) in (select min(n_regionkey) over() + 1", ' ' <repeats 28 times>, "from nation", ' ' <repeats 28 times>, "where r_regionkey=n_regionkey)", length=180, parser_state=0x7ffff7ed4e20, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2-r2/sql/sql_parse.cc:7858
|
and I see force_return_blank=true.
That is,
- we are executing the subquery.
- we've read a record from table 'nation' (frames #13-#14)
- we are about to write a join output row into the temporary table (frames #9-#11)
- we are computing the HAVING clause. in end_write we're here:
if (!join_tab->having || join_tab->having->val_int())
|
The HAVING clause is:
gdb) p dbug_print_item(join_tab->having)
|
$99 = 0x5555570c1040 <dbug_item_print_buf> "1 = <ref_null_helper>(min(nation.n_regionkey) over () + 1)"
|
This agrees with the EXPLAIN output:
"having_condition": "<cache>(region.r_regionkey + 1) = <ref_null_helper>(min(nation.n_regionkey) over () + 1)",
|
and this is the error: we are getting the value of the window function before we've computed it.
We do so, because IN->EXISTS transformation has injected this equality into HAVING. It has injected it too far. One can't push into HAVING when there are window functions.
But IN->EXISTS is the default subquery strategy, so it needs to work. It needs a 'post-window-function-HAVING' where it could push things to...
Probably the same as MDEV-16064:
MariaDB [test]> SELECT *
|
-> FROM region
|
-> WHERE (r_regionkey+1) IN
|
-> (SELECT min(n_regionkey) over() + 1
|
-> FROM nation
|
-> WHERE r_regionkey=n_regionkey);
|
Empty set (0.005 sec)
|
|
|
MariaDB [test]> SELECT * FROM region
|
-> WHERE (r_regionkey+1) IN
|
-> (SELECT min(n_regionkey) + 1
|
-> FROM nation
|
-> WHERE r_regionkey=n_regionkey);
|
+-------------+-------------+-----------------------------------------------------------------------------------------------------------------+
|
| r_regionkey | r_name | r_comment |
|
+-------------+-------------+-----------------------------------------------------------------------------------------------------------------+
|
| 0 | AFRICA | special Tiresias about the furiously even dolphins are furi |
|
| 1 | AMERICA | even, ironic theodolites according to the bold platelets wa |
|
| 2 | ASIA | silent, bold requests sleep slyly across the quickly sly dependencies. furiously silent instructions alongside |
|
| 3 | EUROPE | special, bold deposits haggle foxes. platelet |
|
| 4 | MIDDLE EAST | furiously unusual packages use carefully above the unusual, exp |
|
+-------------+-------------+-----------------------------------------------------------------------------------------------------------------+
|
5 rows in set (0.004 sec)
|
EXPLAIN for the query with window function:
*************************** 1. row ***************************EXPLAIN: {"query_block": {"select_id": 1,"table": {"table_name": "region","access_type": "ALL","rows": 5,"filtered": 100,"attached_condition": "<in_optimizer>(region.r_regionkey + 1,<exists>(subquery#2))"},"subqueries": [{"query_block": {"select_id": 2,"having_condition": "<cache>(region.r_regionkey + 1) = <ref_null_helper>(min(nation.n_regionkey) over () + 1)","window_functions_computation": {"sorts": {"filesort": {"sort_key": "tmp_field"}},"temporary_table": {"table": {"table_name": "nation","access_type": "ref","possible_keys": ["i_n_regionkey"],"key": "i_n_regionkey","key_length": "5","used_key_parts": ["n_regionkey"],"ref": ["test.region.r_regionkey"],"rows": 1,"filtered": 100,"using_index": true}}}}}]}}