[MDEV-12356] Wrong query result when window function is used in a subquery with HAVING - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.2.5, 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: N/A
Component/s: Optimizer - Window functions
Labels:
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

MDEV-16064 Wrong result set from query with in subquery that uses window function

Confirmed

relates to

MDEV-16064 Wrong result set from query with in subquery that uses window function

Confirmed

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia created issue - 2017-03-24 11:32

Sergei Petrunia made changes - 2017-03-24 11:34

Field	Original Value	New Value
Description	The testcase comes from [~David.Hall]. Load the dataset: {{source mysql-test/include/dbt3_s001.inc}} Then run the 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);	The testcase comes from [~David.Hall]. Load the dataset: {{source mysql-test/include/dbt3_s001.inc}} Then run the two queries: {noformat} select * from region where (r_regionkey+1) in (select min(n_regionkey) over() + 1 from nation where r_regionkey=n_regionkey); {noformat} {noformat} select * from region where (r_regionkey+1) in (select min(n_regionkey) + 1 from nation where r_regionkey=n_regionkey); {noformat} and see that they produce different results: {noformat} 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 \| +-------------+--------+-------------------------------------------------------------+ {noformat} {noformat} 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 \| +-------------+-------------+-----------------------------------------------------------------------------------------------------------------+ {noformat}

Sergei Petrunia added a comment - 2017-03-24 11:40

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

Sergei Petrunia added a comment - 2017-03-24 11:40 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 } } } } } ] } }

Sergei Petrunia added a comment - 2017-03-24 12:15

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.

Sergei Petrunia added a comment - 2017-03-24 12:15 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.

Sergei Petrunia added a comment - 2017-03-24 12:26

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...

Sergei Petrunia added a comment - 2017-03-24 12:26 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...

Sergei Golubchik made changes - 2017-05-24 13:59

Issue Type

Task [ 3 ]

Bug [ 1 ]

Sergei Golubchik made changes - 2017-05-24 13:59

Affects Version/s

10.2.5 [ 22117 ]

Sergei Golubchik made changes - 2017-05-24 13:59

Fix Version/s

10.2 [ 14601 ]

Elena Stepanova made changes - 2017-06-01 17:21

Assignee

Vicentiu Ciorbaru [ cvicentiu ]

Alice Sherepa made changes - 2019-03-28 10:51

Link

This issue relates to MDEV-16064 [ MDEV-16064 ]

Alice Sherepa added a comment - 2019-03-28 10:53

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)

Alice Sherepa added a comment - 2019-03-28 10:53 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)

Alice Sherepa made changes - 2019-03-28 10:54

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-03-28 10:54

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-03-28 10:54

Assignee

Vicentiu Ciorbaru [ cvicentiu ]

Varun Gupta [ varun ]

Varun Gupta (Inactive) made changes - 2019-05-02 18:54

Link

This issue duplicates MDEV-16064 [ MDEV-16064 ]

Varun Gupta (Inactive) made changes - 2019-05-02 18:54

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Duplicate [ 3 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 80112 ]

MariaDB v4 [ 151854 ]

People

Assignee:: Varun Gupta (Inactive)

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-03-24 11:32

Updated:: 2019-05-02 18:54

Resolved:: 2019-05-02 18:54

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration