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

Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' failed in compare_order_elements

Details

    Description

      CREATE TABLE t1 ( a char(25), b text);
      		 
      INSERT INTO t1 VALUES ('foo','bar');
      		 
       
      		 
      SELECT SUM(b) OVER (PARTITION BY a), ROW_NUMBER() OVER (PARTITION BY b) 
      FROM t1
      		 
      GROUP BY LEFT((SYSDATE()), 'foo') WITH ROLLUP;

      10.2 54d0a55adf6fbfc92c5473b

      		 
      mysqld: /10.2/sql/sql_window.cc:322: int compare_order_elements(ORDER*, ORDER*): Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' failed.
      		 
      190506 10:48:00 [ERROR] mysqld got signal 6 ;
      		 
       
      		 
      linux/raise.c:54(__GI_raise)[0x7f70751d2428]
      		 
      stdlib/abort.c:91(__GI_abort)[0x7f70751d402a]
      		 
      assert/assert.c:92(__assert_fail_base)[0x7f70751cabd7]
      		 
      /lib/x86_64-linux-gnu/libc.so.6(+0x2dc82)[0x7f70751cac82]
      		 
      sql/sql_window.cc:323(compare_order_elements(st_order*, st_order*))[0x558ae3b19362]
      		 
      sql/sql_window.cc:361(compare_order_lists(SQL_I_List<st_order>*, SQL_I_List<st_order>*))[0x558ae3b19924]
      		 
      sql/sql_window.cc:474(compare_window_funcs_by_window_specs(Item_window_func*, Item_window_func*, void*))[0x558ae3b1a0b5]
      		 
      sql/sql_list.h:626(void bubble_sort<Item_window_func>(List<Item_window_func>*, int (*)(Item_window_func*, Item_window_func*, void*), void*))[0x558ae3b2719b]
      		 
      sql/sql_window.cc:561(order_window_funcs_by_window_specs(List<Item_window_func>*))[0x558ae3b1a830]
      		 
      sql/sql_window.cc:2897(Window_funcs_computation::setup(THD*, List<Item_window_func>*, st_join_table*))[0x558ae3b1ed34]
      		 
      sql/sql_select.cc:2914(JOIN::make_aggr_tables_info())[0x558ae379498c]
      		 
      sql/sql_select.cc:2233(JOIN::optimize_inner())[0x558ae378dd41]
      		 
      sql/sql_select.cc:1116(JOIN::optimize())[0x558ae3783103]
      		 
      sql/sql_select.cc:3806(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x558ae379c66b]
      		 
      sql/sql_select.cc:376(handle_select(THD*, LEX*, select_result*, unsigned long))[0x558ae377bf4b]
      		 
      sql/sql_parse.cc:6486(execute_sqlcom_select(THD*, TABLE_LIST*))[0x558ae37039e7]
      		 
      sql/sql_parse.cc:3534(mysql_execute_command(THD*))[0x558ae36f0ddc]
      		 
      sql/sql_parse.cc:8020(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x558ae370c14b]
      		 
      sql/sql_parse.cc:1835(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x558ae36e77d5]
      		 
      sql/sql_parse.cc:1387(do_command(THD*))[0x558ae36e4952]
      		 
      sql/sql_connect.cc:1335(do_handle_one_connection(CONNECT*))[0x558ae3a0e146]
      		 
      sql/sql_connect.cc:1242(handle_one_connection)[0x558ae3a0db4e]
      		 
      perfschema/pfs.cc:1864(pfs_spawn_thread)[0x558ae4bbc1c8]
      		 
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f7075e0f6ba]
      		 
      x86_64/clone.S:111(clone)[0x7f70752a441d]

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19659 [ERROR] mysqld got signal 11 (parent issue MDEV-19583)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28081 MariaDB SEGV issue

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15837 Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' failed in compare_order_elements function

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26614 Crash in CTE for complex query

          • Major - Major loss of function.
          • Closed

          Activity

            Descending order - Click to sort in ascending order
            psergei Sergei Petrunia added a comment -

            Ok, the new fix is here https://github.com/MariaDB/server/commit/85cc56875e9ddb7f5f56013bac22e36805288c16
            and it fixes MDEV-26614 , too.

            psergei Sergei Petrunia added a comment - Ok, the new fix is here https://github.com/MariaDB/server/commit/85cc56875e9ddb7f5f56013bac22e36805288c16 and it fixes MDEV-26614 , too.
            psergei Sergei Petrunia added a comment -

            ... yes, implementing the above will have an issue that window_functions_computation.sorts will list the sorts in arbitrary order:

            {
            		 
              "query_block": {
            		 
                "select_id": 1,
            		 
                "filesort": {
            		 
                  "sort_key": "nullif(t1.`id`,15532)",
            		 
                  "window_functions_computation": {
            		 
                    "sorts": {
            		 
                      "filesort": {
            		 
                        "sort_key": "avg(1) + 3"
            		 
                      },
            		 
                      "filesort": {
            		 
                        "sort_key": "avg(1)"
            		 
                      }
            		 
                    },
            		 
                    "temporary_table": {
            		 
                      "table": {
            		 
                        "table_name": "t1",
            		 
                        "access_type": "ALL",
            		 
                        "rows": 3,
            		 
                        "filtered": 100
            		 
                      }
            		 
                    }
            		 
                  }
            		 
                }
            		 
              }

            psergei Sergei Petrunia added a comment - ... yes, implementing the above will have an issue that window_functions_computation.sorts will list the sorts in arbitrary order: { "query_block": { "select_id": 1, "filesort": { "sort_key": "nullif(t1.`id`,15532)", "window_functions_computation": { "sorts": { "filesort": { "sort_key": "avg(1) + 3" }, "filesort": { "sort_key": "avg(1)" } }, "temporary_table": { "table": { "table_name": "t1", "access_type": "ALL", "rows": 3, "filtered": 100 } } } } }
            psergei Sergei Petrunia added a comment -

            Suggestion: can we get rid of the crash by adjusting the comparison function to

            • compare Item_field objects like it does now
            • any item that's not an Item_field is greater than any Item_field
            • two non-Item-field items are compared as their pointers.

            This is "compatible" with the current ordering for Item_field objects.
            It may miss some optimization opportunities when multiple window functions use the same PARTITION/ORDER BY expressions.
            Comparing Item* pointers means the ordering will vary across executions. (What is the effect of this? Window Function step will run sorts in different order?)

            psergei Sergei Petrunia added a comment - Suggestion: can we get rid of the crash by adjusting the comparison function to compare Item_field objects like it does now any item that's not an Item_field is greater than any Item_field two non-Item-field items are compared as their pointers. This is "compatible" with the current ordering for Item_field objects. It may miss some optimization opportunities when multiple window functions use the same PARTITION/ORDER BY expressions. Comparing Item* pointers means the ordering will vary across executions. (What is the effect of this? Window Function step will run sorts in different order?)
            psergei Sergei Petrunia added a comment -

            In the above case (avg(1) + 3 is not stored as a field in any temporary table.

            psergei Sergei Petrunia added a comment - In the above case (avg(1) + 3 is not stored as a field in any temporary table.
            psergei Sergei Petrunia added a comment - - edited

            Note that this is not limited to Item_copy or Item_sum_field-derived items.

            Slightly modified Alice's testcase:

            CREATE TABLE t1 ( id varchar(10));
            		 
            INSERT INTO t1 values (1),(2),(3);
            		 
             
            		 
            SELECT 
              dense_rank() over (ORDER BY avg(1)+3), 
              rank()       over (ORDER BY avg(1)) 
            FROM t4 
            GROUP BY 
              nullif(id, 15532);

            This crashes compare_order_elements, too, and we have:

            (gdb) p item1
            		 
            $8 = (Item_func_plus *) 0x7fff74013ad8
            		 
            (gdb)  p item2 
            $9 = (Item_avg_field_decimal *) 0x7fff740178d0

            ... so the ORDER BY list can have an arbitrary item.
            I don't see this causing any problems in window function computation code btw. Only compare_order_elements has an issue.

            psergei Sergei Petrunia added a comment - - edited Note that this is not limited to Item_copy or Item_sum_field-derived items. Slightly modified Alice's testcase: CREATE TABLE t1 ( id varchar (10)); INSERT INTO t1 values (1),(2),(3);   SELECT dense_rank() over ( ORDER BY avg (1)+3), rank() over ( ORDER BY avg (1)) FROM t4 GROUP BY nullif (id, 15532); This crashes compare_order_elements, too, and we have: (gdb) p item1 $8 = (Item_func_plus *) 0x7fff74013ad8 (gdb) p item2 $9 = (Item_avg_field_decimal *) 0x7fff740178d0 ... so the ORDER BY list can have an arbitrary item. I don't see this causing any problems in window function computation code btw. Only compare_order_elements has an issue.
            View 7 older comments

            People

              psergei Sergei Petrunia
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.