Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6859

scalar subqueries in a comparison produced unexpected result

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12, 5.5.40, 10.0.14
    • 5.5.48
    • Optimizer
    • None
    • 5.5.44, 5.5.45, 5.5.47-1, 5.5.48-0

    Description

      A query with scalar subquery in a comparison returns no result when using "ORDER BY" and "LIMIT" in the scalar subquery. (https://mariadb.com/kb/en/sql-99-complete-really/31-searching-with-subqueries/scalar-subqueries/#comment_1325)

      Sample SQL statement:

      SELECT SQL_NO_CACHE a.project_number FROM projects a WHERE ( SELECT z.country FROM projects_history z WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' ORDER BY z.id DESC LIMIT 1 ) IN ( SELECT r.country FROM region r WHERE r.region = 'eame' );

      The query was executed in MariaDB (10.0.3), MySQL(5.6.17) and MySQL(5.0.96). The MySQL(5.0.96) returns the expected result.

      See attached files for details (db dump, explain, actual result and expected result)

      Attachments

        1. database_dump_test_mdb.sql
          2 kB
          Kenneth Cruz
        2. test_results.txt
          4 kB
          Kenneth Cruz

        Issue Links

          links to

          Web Link Initial KB comment

          Activity

            Ascending order - Click to sort in descending order
            View 5 older comments
            psergei Sergei Petrunia added a comment - - edited

            This is where we get the lookup value from:

            (gdb) wher
            		 
              #0  Item_field::str_result (this=0x7fff94008ae8, str=0x7fff9400db50) at /home/psergey/dev2/5.5/sql/item.cc:2602
            		 
              #1  0x00000000007f6808 in Item_cache_str::cache_value (this=0x7fff9400da50) at /home/psergey/dev2/5.5/sql/item.cc:9154
            		 
              #2  0x00000000007fc1d8 in Item_cache::has_value (this=0x7fff9400da50) at /home/psergey/dev2/5.5/sql/item.h:4124
            		 
              #3  0x00000000007f6bb9 in Item_cache_str::save_in_field (this=0x7fff9400da50, field=0x7fff94029988, no_conversions=true) at /home/psergey/dev2/5.5/sql/item.cc:9225
            		 
              #4  0x000000000068241c in store_key_item::copy_inner (this=0x7fff94029950) at /home/psergey/dev2/5.5/sql/sql_select.h:1664
            		 
              #5  0x000000000068200b in store_key::copy (this=0x7fff94029950) at /home/psergey/dev2/5.5/sql/sql_select.h:1554
            		 
              #6  0x000000000067421c in cp_buffer_from_ref (thd=0x2f5bcb0, table=0x7fff9402e268, ref=0x7fff940284f8) at /home/psergey/dev2/5.5/sql/sql_select.cc:20357
            		 
              #7  0x0000000000674155 in cmp_buffer_with_ref (thd=0x2f5bcb0, table=0x7fff9402e268, tab_ref=0x7fff940284f8) at /home/psergey/dev2/5.5/sql/sql_select.cc:20339
            		 
              #8  0x000000000066d22d in join_read_key2 (thd=0x2f5bcb0, tab=0x7fff940282e8, table=0x7fff9402e268, table_ref=0x7fff940284f8) at /home/psergey/dev2/5.5/sql/sql_select.cc:17522
            		 
              #9  0x000000000066d18e in join_read_key (tab=0x7fff940282e8) at /home/psergey/dev2/5.5/sql/sql_select.cc:17500
            		 
              #10 0x000000000066bbae in sub_select (join=0x7fff9400c610, join_tab=0x7fff940282e8, end_of_records=false) at /home/psergey/dev2/5.5/sql/sql_select.cc:16874
            		 
              #11 0x000000000066c2d7 in evaluate_join_record (join=0x7fff9400c610, join_tab=0x7fff94027fc8, error=0) at /home/psergey/dev2/5.5/sql/sql_select.cc:17096
            		 
              #12 0x000000000066bc10 in sub_select (join=0x7fff9400c610, join_tab=0x7fff94027fc8, end_of_records=false) at /home/psergey/dev2/5.5/sql/sql_select.cc:16877
            		 
              #13 0x000000000066b477 in do_select (join=0x7fff9400c610, fields=0x2f5f7c0, table=0x0, procedure=0x0) at /home/psergey/dev2/5.5/sql/sql_select.cc:16539
            		 
              #14 0x0000000000649dff in JOIN::exec (this=0x7fff9400c610) at /home/psergey/dev2/5.5/sql/sql_select.cc:2873
            		 
              #15 0x000000000064a60f in mysql_select (thd=0x2f5bcb0, rref_pointer_array=0x2f5f918, tables=0x7fff94007ab0, wild_num=0, fields=..., conds=0x7fff9400c368, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff9400c5f0, unit=0x2f5efd0, select_lex=0x2f5f6a8) at /home/psergey/dev2/5.5/sql/sql_select.cc:3094

            We copy the data from "z.country" field:

            (gdb) p this->field_name
            		 
              $125 = 0x7fff94016d20 "country"
            		 
            (gdb) p this
            		 
              $126 = (Item_field *) 0x7fff94008ae8
            		 
            (gdb) p this->field->table->alias.Ptr
            		 
              $127 = 0x7fff94012440 "z"

            the problem is, we never executed the subquery. A breakpoint in JOIN::exec() was only hit for select_number==1

            psergei Sergei Petrunia added a comment - - edited This is where we get the lookup value from: (gdb) wher #0 Item_field::str_result (this=0x7fff94008ae8, str=0x7fff9400db50) at /home/psergey/dev2/5.5/sql/item.cc:2602 #1 0x00000000007f6808 in Item_cache_str::cache_value (this=0x7fff9400da50) at /home/psergey/dev2/5.5/sql/item.cc:9154 #2 0x00000000007fc1d8 in Item_cache::has_value (this=0x7fff9400da50) at /home/psergey/dev2/5.5/sql/item.h:4124 #3 0x00000000007f6bb9 in Item_cache_str::save_in_field (this=0x7fff9400da50, field=0x7fff94029988, no_conversions=true) at /home/psergey/dev2/5.5/sql/item.cc:9225 #4 0x000000000068241c in store_key_item::copy_inner (this=0x7fff94029950) at /home/psergey/dev2/5.5/sql/sql_select.h:1664 #5 0x000000000068200b in store_key::copy (this=0x7fff94029950) at /home/psergey/dev2/5.5/sql/sql_select.h:1554 #6 0x000000000067421c in cp_buffer_from_ref (thd=0x2f5bcb0, table=0x7fff9402e268, ref=0x7fff940284f8) at /home/psergey/dev2/5.5/sql/sql_select.cc:20357 #7 0x0000000000674155 in cmp_buffer_with_ref (thd=0x2f5bcb0, table=0x7fff9402e268, tab_ref=0x7fff940284f8) at /home/psergey/dev2/5.5/sql/sql_select.cc:20339 #8 0x000000000066d22d in join_read_key2 (thd=0x2f5bcb0, tab=0x7fff940282e8, table=0x7fff9402e268, table_ref=0x7fff940284f8) at /home/psergey/dev2/5.5/sql/sql_select.cc:17522 #9 0x000000000066d18e in join_read_key (tab=0x7fff940282e8) at /home/psergey/dev2/5.5/sql/sql_select.cc:17500 #10 0x000000000066bbae in sub_select (join=0x7fff9400c610, join_tab=0x7fff940282e8, end_of_records=false) at /home/psergey/dev2/5.5/sql/sql_select.cc:16874 #11 0x000000000066c2d7 in evaluate_join_record (join=0x7fff9400c610, join_tab=0x7fff94027fc8, error=0) at /home/psergey/dev2/5.5/sql/sql_select.cc:17096 #12 0x000000000066bc10 in sub_select (join=0x7fff9400c610, join_tab=0x7fff94027fc8, end_of_records=false) at /home/psergey/dev2/5.5/sql/sql_select.cc:16877 #13 0x000000000066b477 in do_select (join=0x7fff9400c610, fields=0x2f5f7c0, table=0x0, procedure=0x0) at /home/psergey/dev2/5.5/sql/sql_select.cc:16539 #14 0x0000000000649dff in JOIN::exec (this=0x7fff9400c610) at /home/psergey/dev2/5.5/sql/sql_select.cc:2873 #15 0x000000000064a60f in mysql_select (thd=0x2f5bcb0, rref_pointer_array=0x2f5f918, tables=0x7fff94007ab0, wild_num=0, fields=..., conds=0x7fff9400c368, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff9400c5f0, unit=0x2f5efd0, select_lex=0x2f5f6a8) at /home/psergey/dev2/5.5/sql/sql_select.cc:3094 We copy the data from "z.country" field: (gdb) p this->field_name $125 = 0x7fff94016d20 "country" (gdb) p this $126 = (Item_field *) 0x7fff94008ae8 (gdb) p this->field->table->alias.Ptr $127 = 0x7fff94012440 "z" the problem is, we never executed the subquery. A breakpoint in JOIN::exec() was only hit for select_number==1
            psergei Sergei Petrunia added a comment -

            The store_key_item object was created in setup_sj_materialization_part2: 

                for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++)
            		 
                {
            		 
                  tab_ref->items[i]= emb_sj_nest->sj_subq_pred->left_expr->element_index(i);
            		 
                  int null_count= test(cur_key_part->field->real_maybe_null());
            		 
                  *ref_key= new store_key_item(thd, cur_key_part->field,
            		 

            (gdb) p tab_ref->items[0]
            		 
              $142 = (Item_cache_str *) 0x7fff9400db00

            psergei Sergei Petrunia added a comment - The store_key_item object was created in setup_sj_materialization_part2: for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) { tab_ref->items[i]= emb_sj_nest->sj_subq_pred->left_expr->element_index(i); int null_count= test(cur_key_part->field->real_maybe_null()); *ref_key= new store_key_item(thd, cur_key_part->field, (gdb) p tab_ref->items[0] $142 = (Item_cache_str *) 0x7fff9400db00
            psergei Sergei Petrunia added a comment -

            Can't really understand the code in bool Item_cache_str::cache_value()

            Trying this query

            MariaDB [test]> SELECT SQL_NO_CACHE a.project_number FROM projects a  WHERE a.project_number > (SELECT z.country FROM projects_history z    WHERE z.project_number = a.project_number      AND z.history_date <= '2014-09-01'    ORDER BY z.id DESC LIMIT 1);

            and seeing:

            (gdb) print example
            		 
              $158 = (Item_singlerow_subselect *) 0x7fff98008838

            while in the bug's testcase it is:

            (gdb) print example
            		 
              $162 = (Item_field *) 0x7fff94008ae8

            psergei Sergei Petrunia added a comment - Can't really understand the code in bool Item_cache_str::cache_value() Trying this query MariaDB [test]> SELECT SQL_NO_CACHE a.project_number FROM projects a WHERE a.project_number > (SELECT z.country FROM projects_history z WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' ORDER BY z.id DESC LIMIT 1); and seeing: (gdb) print example $158 = (Item_singlerow_subselect *) 0x7fff98008838 while in the bug's testcase it is: (gdb) print example $162 = (Item_field *) 0x7fff94008ae8
            psergei Sergei Petrunia added a comment -

            Discussed with sanja. The interesting part is this code in setup_sj_materialization_part2:

            emb_sj_nest->sj_subq_pred->left_expr->element_index(i);

            Here :

            (gdb) p emb_sj_nest->sj_subq_pred->left_expr
            		 
              $332 = (Item_singlerow_subselect *) 0x7fff94042810
            		 
            (gdb) p emb_sj_nest->sj_subq_pred->left_expr->element_index(0)
            		 
              $333 = (Item_cache_str *) 0x7fff94045750
            		 
            (gdb) p emb_sj_nest->sj_subq_pred->left_expr->element_index(0)->example
            		 
              $334 = (Item_field *) 0x7fff94008ad8

            That is, left_expr is an Item_singlerow_subselect. When one calls left_expr->val_XXX() (provided the subquery returns a single value, not tuple) , the subquery gets re-executed. However, left_expr->element_index() is an Item_cache(Item_field). So, if you just call left_expr->element_index(0)->val_int(), subquery is not executed, and you don't get the new value.

            According to Sanja, this is a common problem for "row-like" items, and the convention is to call row_item->bring_value() before calling elements of the row_item.

            We didn't have this problem with ref access before, because row items are not used for ref access. (Except for sj-materialization)

            psergei Sergei Petrunia added a comment - Discussed with sanja . The interesting part is this code in setup_sj_materialization_part2: emb_sj_nest->sj_subq_pred->left_expr->element_index(i); Here : (gdb) p emb_sj_nest->sj_subq_pred->left_expr $332 = (Item_singlerow_subselect *) 0x7fff94042810 (gdb) p emb_sj_nest->sj_subq_pred->left_expr->element_index(0) $333 = (Item_cache_str *) 0x7fff94045750 (gdb) p emb_sj_nest->sj_subq_pred->left_expr->element_index(0)->example $334 = (Item_field *) 0x7fff94008ad8 That is, left_expr is an Item_singlerow_subselect. When one calls left_expr->val_XXX() (provided the subquery returns a single value, not tuple) , the subquery gets re-executed. However, left_expr->element_index() is an Item_cache(Item_field). So, if you just call left_expr->element_index(0)->val_int(), subquery is not executed, and you don't get the new value. According to Sanja, this is a common problem for "row-like" items, and the convention is to call row_item->bring_value() before calling elements of the row_item. We didn't have this problem with ref access before, because row items are not used for ref access. (Except for sj-materialization)
            psergei Sergei Petrunia added a comment -

            This patch fixes the problem and passes the testsuite. It's not very elegant, though: subquery code in the middle of eq_ref access code:

            === modified file 'sql/sql_select.cc'
            		 
            --- sql/sql_select.cc   2014-10-14 11:11:06 +0000
            		 
            +++ sql/sql_select.cc   2014-10-14 22:20:05 +0000
            		 
            @@ -17517,8 +17517,18 @@ int join_read_key2(THD *thd, JOIN_TAB *t
            		 
                   return 1;
            		 
                 }
            		 
               }
            		 
            -
            		 
            +  
            +  /*
            		 
            +    The following is needed when one makes ref (or eq_ref) access from row
            		 
            +    comparisons: one must call row->bring_value() to get the new values.
            		 
            +  */
            		 
            +  if (tab && tab->bush_children)
            		 
            +  {
            		 
            +    TABLE_LIST *emb_sj_nest= tab->bush_children->start->emb_sj_nest;
            		 
            +    emb_sj_nest->sj_subq_pred->left_expr->bring_value();
            		 
            +  }
            		 
               /* TODO: Why don't we do "Late NULLs Filtering" here? */
            		 
            +
            		 
               if (cmp_buffer_with_ref(thd, table, table_ref) ||
            		 
                   (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW)))
            		 
               {

            psergei Sergei Petrunia added a comment - This patch fixes the problem and passes the testsuite. It's not very elegant, though: subquery code in the middle of eq_ref access code: === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2014-10-14 11:11:06 +0000 +++ sql/sql_select.cc 2014-10-14 22:20:05 +0000 @@ -17517,8 +17517,18 @@ int join_read_key2(THD *thd, JOIN_TAB *t return 1; } } - + + /* + The following is needed when one makes ref (or eq_ref) access from row + comparisons: one must call row->bring_value() to get the new values. + */ + if (tab && tab->bush_children) + { + TABLE_LIST *emb_sj_nest= tab->bush_children->start->emb_sj_nest; + emb_sj_nest->sj_subq_pred->left_expr->bring_value(); + } /* TODO: Why don't we do "Late NULLs Filtering" here? */ + if (cmp_buffer_with_ref(thd, table, table_ref) || (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) {

            People

              psergei Sergei Petrunia
              kennethcruz Kenneth Cruz
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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