[MDEV-12356] Wrong query result when window function is used in a subquery with HAVING Created: 2017-03-24  Updated: 2019-05-02  Resolved: 2019-05-02

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2.5, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Varun Gupta (Inactive)
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-16064 Wrong result set from query with in s... Confirmed
Relates
relates to MDEV-16064 Wrong result set from query with in s... Confirmed

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



 Comments   
Comment by Sergei Petrunia [ 2017-03-24 ]

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
 
              }
 
            }
 
          }
 
        }
 
      }
 
    ]
 
  }
 
}

Comment by Sergei Petrunia [ 2017-03-24 ]

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.

Comment by Sergei Petrunia [ 2017-03-24 ]

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

Comment by Alice Sherepa [ 2019-03-28 ]

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)

Generated at Thu Feb 08 07:57:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.