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

Item_func::fix_fields: Assertion `used_tables_cache == 0' failed.

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2(EOL), 10.3(EOL)
    • 10.2.17
    • Optimizer
    • None

    Description

      testcase:

       
      		 
      CREATE TABLE t1 (i1 int, v1 varchar(1));
      		 
      INSERT INTO t1 VALUES (7,'x');
      		 
       
      		 
      CREATE TABLE t2 (i1 int);
      		 
      INSERT INTO t2 VALUES (8);
      		 
       
      		 
      CREATE TABLE t3 (i1 int ,v1 varchar(1), v2 varchar(1));
      		 
      INSERT INTO t3 VALUES (4, 'v','v'),(62,'v','k'),(7, 'n', NULL);
      		 
       
      		 
      SELECT 1
      		 
      FROM (t1 AS a1
      		 
            JOIN (((SELECT  DISTINCT t3.*
      		 
                    FROM t3) AS a2
      		 
                   JOIN t1 ON (t1.v1 = a2.v2))) ON (t1.v1 = a2.v1))
      		 
      WHERE (SELECT  BIT_COUNT(t2.i1)
      		 
             FROM (t2 JOIN t3)) IS NULL;
      		 
       
      		 
      DROP TABLE t1, t2, t3;


      Thread 1 (Thread 0x7ff7fa9d9700 (LWP 8684)):
      		 
      #0  __pthread_kill (threadid=<optimized out>, signo=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
      		 
      #1  0x0000557b9a8228b7 in my_write_core (sig=6) at /home/alice/git/10.2/mysys/stacktrace.c:477
      		 
      #2  0x0000557b9a0b2e35 in handle_fatal_signal (sig=6) at /home/alice/git/10.2/sql/signal_handler.cc:305
      		 
      #3  <signal handler called>
      		 
      #4  0x00007ff800706428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
      		 
      #5  0x00007ff80070802a in __GI_abort () at abort.c:89
      		 
      #6  0x00007ff8006febd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x557b9a9b03fe "used_tables_cache == 0", file=file@entry=0x557b9a9b03d8 "/home/alice/git/10.2/sql/item_func.cc", line=line@entry=187, function=function@entry=0x557b9a9b0fa0 <Item_func::fix_fields(THD*, Item**)::__PRETTY_FUNCTION__> "virtual bool Item_func::fix_fields(THD*, Item**)") at assert.c:92
      		 
      #7  0x00007ff8006fec82 in __GI___assert_fail (assertion=0x557b9a9b03fe "used_tables_cache == 0", file=0x557b9a9b03d8 "/home/alice/git/10.2/sql/item_func.cc", line=187, function=0x557b9a9b0fa0 <Item_func::fix_fields(THD*, Item**)::__PRETTY_FUNCTION__> "virtual bool Item_func::fix_fields(THD*, Item**)") at assert.c:101
      		 
      #8  0x0000557b9a12a85d in Item_func::fix_fields (this=0x7ff7e816c800, thd=0x7ff7e8000a98, ref=0x7ff7e81616b8) at /home/alice/git/10.2/sql/item_func.cc:187
      		 
      #9  0x0000557b99e83a4f in JOIN::optimize_inner (this=0x7ff7e8161240) at /home/alice/git/10.2/sql/sql_select.cc:1313
      		 
      #10 0x0000557b99e82f3d in JOIN::optimize (this=0x7ff7e8161240) at /home/alice/git/10.2/sql/sql_select.cc:1101
      		 
      #11 0x0000557b99e1383a in mysql_derived_optimize (thd=0x7ff7e8000a98, lex=0x7ff7e8004570, derived=0x7ff7e8159bf0) at /home/alice/git/10.2/sql/sql_derived.cc:906
      		 
      #12 0x0000557b99e11f7a in mysql_handle_single_derived (lex=0x7ff7e8004570, derived=0x7ff7e8159bf0, phases=4) at /home/alice/git/10.2/sql/sql_derived.cc:197
      		 
      #13 0x0000557b99e83d1c in JOIN::optimize_inner (this=0x7ff7e8160b20) at /home/alice/git/10.2/sql/sql_select.cc:1353
      		 
      #14 0x0000557b99e82f3d in JOIN::optimize (this=0x7ff7e8160b20) at /home/alice/git/10.2/sql/sql_select.cc:1101
      		 
      #15 0x0000557b99e8bfe4 in mysql_select (thd=0x7ff7e8000a98, tables=0x7ff7e8115de0, wild_num=0, fields=..., conds=0x7ff7e8140c50, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ff7e814a7b0, unit=0x7ff7e8004638, select_lex=0x7ff7e8004d90) at /home/alice/git/10.2/sql/sql_select.cc:3719
      		 
      #16 0x0000557b99e807a7 in handle_select (thd=0x7ff7e8000a98, lex=0x7ff7e8004570, result=0x7ff7e814a7b0, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:373
      		 
      #17 0x0000557b99e4c752 in execute_sqlcom_select (thd=0x7ff7e8000a98, all_tables=0x7ff7e8115de0) at /home/alice/git/10.2/sql/sql_parse.cc:6456
      		 
      #18 0x0000557b99e424d0 in mysql_execute_command (thd=0x7ff7e8000a98) at /home/alice/git/10.2/sql/sql_parse.cc:3467
      		 
      #19 0x0000557b99e500c6 in mysql_parse (thd=0x7ff7e8000a98, rawbuf=0x7ff7e806b4c0 "SELECT 1\nFROM (t1 AS a1\nJOIN (((SELECT  DISTINCT t3.*\nFROM t3) AS a2\nJOIN t1 ON (t1.v1 = a2.v2))) ON (t1.v1 = a2.v1))\nWHERE (SELECT  BIT_COUNT(t2.i1)\nFROM (t2 JOIN t3)) IS NULL", length=176, parser_state=0x7ff7fa9d81f0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7898
      		 
      #20 0x0000557b99e3dd6f in dispatch_command (command=COM_QUERY, thd=0x7ff7e8000a98, packet=0x7ff7e8007fe9 "SELECT 1\nFROM (t1 AS a1\nJOIN (((SELECT  DISTINCT t3.*\nFROM t3) AS a2\nJOIN t1 ON (t1.v1 = a2.v2))) ON (t1.v1 = a2.v1))\nWHERE (SELECT  BIT_COUNT(t2.i1)\nFROM (t2 JOIN t3)) IS NULL", packet_length=176, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1806
      		 
      #21 0x0000557b99e3c6cb in do_command (thd=0x7ff7e8000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360
      		 
      #22 0x0000557b99f8a7df in do_handle_one_connection (connect=0x557b9d63ad48) at /home/alice/git/10.2/sql/sql_connect.cc:1335
      		 
      #23 0x0000557b99f8a55f in handle_one_connection (arg=0x557b9d63ad48) at /home/alice/git/10.2/sql/sql_connect.cc:1241
      		 
      #24 0x0000557b9a2e7e9e in pfs_spawn_thread (arg=0x557b9d59a908) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862
      		 
      #25 0x00007ff8013436ba in start_thread (arg=0x7ff7fa9d9700) at pthread_create.c:333
      		 
      #26 0x00007ff8007d841d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25650 Assertion `used_tables_cache == 0' failed in Item_func::fix_fields

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa created issue -
            alice Alice Sherepa made changes -
            Field Original Value New Value
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            Assignee Igor Babaev [ igor ]
            igor Igor Babaev (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            igor Igor Babaev (Inactive) added a comment -

            This bug can be demonstrated with 10.2.14.
            The proper fix of this bug is in the patch for MDEV-15035 applied to 5.5 and merged upstream. This patch moved the call of optimize_constant_subqueries() after the call of simplify_joins(). As a result of this move no subquery can be executed when simplify_join() is performed because any subquery is not optimized yet.
            This is critical for prepared statement and SP: we can't evaluate any subquery when executing simplify_joins()
            as this function is supposed to perform optimizations only at the first execution of the query and these optimizations are saved for all later executions.

            Here's what happens in 10.2.14 when the query from the reported offending test case is executed.
            The call of optimize_constant_subqueries() in JOIN::optimize_inner() optimizes the subquery from the WHERE condition

            SELECT  BIT_COUNT(t2.i1)  FROM (t2 JOIN t3)  [SQ]

            The call of simplify_joins() flattens the nested joins and merges their ON conditions with the WHERE condition obtaining a new WHERE condition for which it calls fix_fields(). Then fix_fields() evaluates the subquery predicate as it is constant and is considered to be not expensive (not optimized subquery is never considered as expensive!). When evaluating the predicate we tries to evaluate the subquery SQ as a single-row subquery. Here we got an error because SQ returns more than 1 row. Still simplify_joins() returns 

            [SQ] is null and t1.v1 = a2.v1 and t1.v1 = a2.v2

            Then optimize_cond () is called for this condition. The call returns multiple equal(t1.v1, a2.v1, a2.v2)
            as [SQ] is null is evaluated to true.
            After this the call of pushdown_cond_for_derived() finds out that the equality a2.v1= a2.v2 extracted
            from multiple equal(t1.v1, a2.v1, a2.v2) can be pushed into the materialized derived a2. After constructing this equality Item_func::fix_fields() is called for it. Just before returning the result this function checks thd->is_error() and returns TRUE leaving 'fixed=0'. Later this equality is transformed into the equality t3.v2 = t3.v1 and cleanup_excluding_const_fields_processor is called for the nodes of the equality item. As the item
            t3.v2 = t3.v1 is not marked as fixed the cleanup() procedure is not called for it and used_tables_cache is not cleaned for this item.
            After this t3.v2 = t3.v1 is pushed into WHERE of a2 and fix_fields() is called for it. Here 

            DBUG_ASSERT(used_tables_cache == 0);

            fails because used_tables_cache was not cleaned.

            igor Igor Babaev (Inactive) added a comment - This bug can be demonstrated with 10.2.14. The proper fix of this bug is in the patch for MDEV-15035 applied to 5.5 and merged upstream. This patch moved the call of optimize_constant_subqueries() after the call of simplify_joins(). As a result of this move no subquery can be executed when simplify_join() is performed because any subquery is not optimized yet. This is critical for prepared statement and SP: we can't evaluate any subquery when executing simplify_joins() as this function is supposed to perform optimizations only at the first execution of the query and these optimizations are saved for all later executions. Here's what happens in 10.2.14 when the query from the reported offending test case is executed. The call of optimize_constant_subqueries() in JOIN::optimize_inner() optimizes the subquery from the WHERE condition SELECT BIT_COUNT(t2.i1) FROM (t2 JOIN t3) [SQ] The call of simplify_joins() flattens the nested joins and merges their ON conditions with the WHERE condition obtaining a new WHERE condition for which it calls fix_fields(). Then fix_fields() evaluates the subquery predicate as it is constant and is considered to be not expensive (not optimized subquery is never considered as expensive!). When evaluating the predicate we tries to evaluate the subquery SQ as a single-row subquery. Here we got an error because SQ returns more than 1 row. Still simplify_joins() returns [SQ] is null and t1.v1 = a2.v1 and t1.v1 = a2.v2 Then optimize_cond () is called for this condition. The call returns multiple equal(t1.v1, a2.v1, a2.v2) as [SQ] is null is evaluated to true. After this the call of pushdown_cond_for_derived() finds out that the equality a2.v1= a2.v2 extracted from multiple equal(t1.v1, a2.v1, a2.v2) can be pushed into the materialized derived a2. After constructing this equality Item_func::fix_fields() is called for it. Just before returning the result this function checks thd->is_error() and returns TRUE leaving 'fixed=0'. Later this equality is transformed into the equality t3.v2 = t3.v1 and cleanup_excluding_const_fields_processor is called for the nodes of the equality item. As the item t3.v2 = t3.v1 is not marked as fixed the cleanup() procedure is not called for it and used_tables_cache is not cleaned for this item. After this t3.v2 = t3.v1 is pushed into WHERE of a2 and fix_fields() is called for it. Here DBUG_ASSERT(used_tables_cache == 0); fails because used_tables_cache was not cleaned.
            igor Igor Babaev (Inactive) added a comment -

            The current 10.2 code (as of commit de85355436e483902e0fff432348bc16f9ec1557) does not fail for the reported test case because the subquery SQ is executed as late as in make_join_select(). It happens because simplify_joins() finds out that this subquery is not optimized yet and so should be considered as expensive.
            Due to the 5.5 bug mdev-16820 SQ is considered as expensive even when optimize_cond() is called.
            After the patch of mdev-16820 has been applied the query still did not fail, now due to patch for MDEV-11071 that removed the call thd->is_error() from Item_func::fix_fields().
            However even with all these fixes it makes sense to move the code

              if (thd->is_error())
            		 
              {
            		 
                error= 1;
            		 
                DBUG_PRINT("error",("Error from optimize_cond"));
            		 
                DBUG_RETURN(1);
            		 
              }

            right after this statement

              conds= optimize_cond(this, conds, join_list, FALSE,
            		 
                                   &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS);

            .
            We saw this before the of condition pushdown for materialized derived/view was added.

            igor Igor Babaev (Inactive) added a comment - The current 10.2 code (as of commit de85355436e483902e0fff432348bc16f9ec1557) does not fail for the reported test case because the subquery SQ is executed as late as in make_join_select(). It happens because simplify_joins() finds out that this subquery is not optimized yet and so should be considered as expensive. Due to the 5.5 bug mdev-16820 SQ is considered as expensive even when optimize_cond() is called. After the patch of mdev-16820 has been applied the query still did not fail, now due to patch for MDEV-11071 that removed the call thd->is_error() from Item_func::fix_fields(). However even with all these fixes it makes sense to move the code if (thd->is_error()) { error= 1; DBUG_PRINT("error",("Error from optimize_cond")); DBUG_RETURN(1); } right after this statement conds= optimize_cond(this, conds, join_list, FALSE, &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS); . We saw this before the of condition pushdown for materialized derived/view was added.
            igor Igor Babaev (Inactive) added a comment -

            A fix for this bug was pushed into 10.2.
            It should be merged upstream as it is.

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.2. It should be merged upstream as it is.
            igor Igor Babaev (Inactive) made changes -
            Fix Version/s 10.2.17 [ 23111 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            Roel Roel Van de Paar made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 85160 ] MariaDB v4 [ 153659 ]

            People

              igor Igor Babaev (Inactive)
              alice Alice Sherepa
              Votes:
              1 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.