Details

    Description

      One Libre.Chat, "Albright" got a crash on this query on MariaDB 10.3.31

      WITH cte AS (
      SELECT
      DENSE_RANK() OVER (ORDER BY thread DESC) AS order_rank,
      DENSE_RANK() OVER (PARTITION BY thread ORDER BY c.id ASC) AS thread_rank,
      c.id,
      c.parent,
      IF(c.parent = 0, c.id, c.parent) AS thread,
      c.user_id,
      c.created,
      CONVERT_TZ(c.created, @@session.time_zone, "+00:00") AS created_utc,
      COALESCE(un.username, un.user_id) AS username,
      COALESCE(un.username_slug, un.user_id) AS username_slug,
      un.profile_pic,
      IF(si.user_id IS NULL, 0, 1) AS is_staff,
      IF(sn.sub_id IS NULL, 0, 1) AS is_subscriber,
      – SUM() returns NULL instead of zero when there are no values
      – (votes) to sum
      COALESCE(SUM(cv.value), 0) AS score,
      IF(c.parent = 0, COALESCE(SUM(cv.value), 0), -2147483648) AS thread_score,
      – We have to put this DENSE_RANK() here. If we put it up top with the
      – others, MariaDB 10.3 crashes. Maybe it's because we need to have it
      – after the field it uses.
      DENSE_RANK() OVER(ORDER BY thread_score DESC, thread DESC) AS score_rank,
      COALESCE(c2.count, 0) AS user_comment_count,
      COALESCE(c3.count, 0) AS reply_count,
      cv2.value AS user_score,
      br.html
      FROM comment c
      INNER JOIN users_new un ON c.user_id = un.user_id
      INNER JOIN body_revision br USING (body_revision_id)
      LEFT JOIN (
      SELECT user_id, count AS count
      FROM comment c2
      WHERE c2.status = 1
      GROUP BY user_id
      ) AS c2 ON c.user_id = c2.user_id
      LEFT JOIN (
      SELECT parent, count AS count
      FROM comment c3
      WHERE c3.status = 1
      GROUP BY parent
      ) AS c3 ON c3.parent = c.id
      LEFT JOIN staff_info si ON si.user_id = un.user_id
      LEFT JOIN subscriptions_new sn ON c.user_id = sn.user_id AND sn.status = 1
      LEFT JOIN comment_vote cv ON c.id = cv.id
      LEFT JOIN comment_vote cv2 ON cv2.id = c.id AND cv2.user_id = '108509'
      WHERE c.entity_type = 5
      AND c.entity_id = '22415'
      AND c.status = 1
      GROUP BY c.id
      ORDER BY created DESC
      )
      SELECT * FROM cte WHERE (order_rank <= 5 OR score_rank <= 5) AND thread_rank <= 6

      Here is the stack trace:
      0 libsystem_platform.dylib 0x000000018a4e9c44 _sigtramp + 56
      0 mysqld 0x00000001024a2a94 ZL19compare_order_listsP10SQL_I_ListI8st_orderES2 + 260
      0 mysqld 0x000000010249f944 _ZN24Window_funcs_computation5setupEP3THDP4ListI16Item_window_funcEP13st_join_table + 316
      0 mysqld 0x00000001023a0afc _ZN4JOIN21make_aggr_tables_infoEv + 2948
      0 mysqld 0x000000010239a374 _ZN4JOIN15optimize_stage2Ev + 10340
      0 mysqld 0x000000010239b50c _ZN4JOIN14optimize_innerEv + 2044
      0 mysqld 0x0000000102397acc _ZN4JOIN8optimizeEv + 76
      0 mysqld 0x0000000102342b8c _Z22mysql_derived_optimizeP3THDP3LEXP10TABLE_LIST + 264
      0 mysqld 0x00000001023437e8 _Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj + 168
      0 mysqld 0x000000010239b4b4 _ZN4JOIN14optimize_innerEv + 1956
      0 mysqld 0x0000000102397acc _ZN4JOIN8optimizeEv + 76
      0 mysqld 0x00000001023949a4 _Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex + 100
      0 mysqld 0x00000001023948d4 _Z13handle_selectP3THDP3LEXP13select_resultm + 332
      0 mysqld 0x0000000102371fa8 _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 952
      0 mysqld 0x000000010236c8d4 _Z21mysql_execute_commandP3THD + 1860
      0 mysqld 0x000000010236a4e4 _Z11mysql_parseP3THDPcjP12Parser_statebb + 436
      0 mysqld 0x00000001023679ac _Z16dispatch_command19enum_server_commandP3THDPcjbb + 2208
      0 mysqld 0x00000001023690ec _Z10do_commandP3THD + 292
      0 mysqld 0x00000001024332a8 _Z24do_handle_one_connectionP7CONNECT + 444
      0 mysqld 0x00000001024330d8 handle_one_connection + 68
      0 libsystem_pthread.dylib 0x000000018a49efd4 _pthread_start + 320
      0 libsystem_pthread.dylib 0x000000018a499d3c thread_start + 8

      Attachments

        Issue Links

          Activity

            Running this query creates GROUP-BY temporary table with this call:

            create_tmp_table(
              fields= List<Item>{
                 "c.`id`"
                 "sum(cv.`value`)"
                 "c.parent = 0"
                 "dense_rank() over ( order by if(c.parent = 0,c.`id`,c.parent) desc)"
                 "if(c.parent = 0,c.`id`,c.parent)"
                 "if(c.parent = 0,coalesce(sum(cv.`value`),0),-2147483648)"
                 "dense_rank() over ( order by if(c.parent = 0,coalesce(sum(cv.`value`),0),-2147483648) desc)"
              }
              group = { "c.`id`" }
              ...
            )
            

            Note this Item is in the list of fields:

            if(c.parent = 0,coalesce(sum(cv.`value`),0),-2147483648)"
            

            ... but the window function's ORDER BY clause is apparently not using it?

            psergei Sergei Petrunia added a comment - Running this query creates GROUP-BY temporary table with this call: create_tmp_table( fields= List<Item>{ "c.`id`" "sum(cv.`value`)" "c.parent = 0" "dense_rank() over ( order by if(c.parent = 0,c.`id`,c.parent) desc)" "if(c.parent = 0,c.`id`,c.parent)" "if(c.parent = 0,coalesce(sum(cv.`value`),0),-2147483648)" "dense_rank() over ( order by if(c.parent = 0,coalesce(sum(cv.`value`),0),-2147483648) desc)" } group = { "c.`id`" } ... ) Note this Item is in the list of fields: if(c.parent = 0,coalesce(sum(cv.`value`),0),-2147483648)" ... but the window function's ORDER BY clause is apparently not using it?

            Comparing ORDER BY references for both window functions:

            (gdb) p ord1->item
              $100 = (Item **) 0x7fff1c07c1f8
            (gdb) p ord2->item
              $102 = (Item **) 0x7fff1c07c200
            (gdb) p ord2->item - ord1->item
              $104 = 1
            

            They both refer to the "ref_pointer_array". The elements of that array should be switched to point to temp.table fields... Which was done for one window function but not for the other...

            The switch is done in change_to_use_tmp_fields (Thanks sanja_byelkin) ...

            The lines that prevent the switch are:

                if ((item->with_sum_func() && item->type() != Item::SUM_FUNC_ITEM) ||
                    item->with_window_func())
            

            makes sense...

            A guess: is this really correct that this query is using one temp. table and not two?

            psergei Sergei Petrunia added a comment - Comparing ORDER BY references for both window functions: (gdb) p ord1->item $100 = (Item **) 0x7fff1c07c1f8 (gdb) p ord2->item $102 = (Item **) 0x7fff1c07c200 (gdb) p ord2->item - ord1->item $104 = 1 They both refer to the "ref_pointer_array". The elements of that array should be switched to point to temp.table fields... Which was done for one window function but not for the other... The switch is done in change_to_use_tmp_fields (Thanks sanja_byelkin ) ... The lines that prevent the switch are: if ((item->with_sum_func() && item->type() != Item::SUM_FUNC_ITEM) || item->with_window_func()) makes sense... A guess: is this really correct that this query is using one temp. table and not two?

            ... Well, a "naive" attempt to get this run with two temp tables wasn't successful.

            The other route is: why does window function's ORDER BY criteria need to be a temp table Field (and not a function of it)?

            The expression (quoting from above):

            (gdb) p dbug_print_item(win_func2)
              $23 = 0x555557a43ca0 <dbug_item_print_buf> 
              "dense_rank() over ( order by if(tmp_field,coalesce(tmp_field,0),-2147483648) desc,thread desc)"
            

            can be computed at window function computation phase...

            psergei Sergei Petrunia added a comment - ... Well, a "naive" attempt to get this run with two temp tables wasn't successful. The other route is: why does window function's ORDER BY criteria need to be a temp table Field (and not a function of it)? The expression (quoting from above): (gdb) p dbug_print_item(win_func2) $23 = 0x555557a43ca0 <dbug_item_print_buf> "dense_rank() over ( order by if(tmp_field,coalesce(tmp_field,0),-2147483648) desc,thread desc)" can be computed at window function computation phase...

            Idea of the fix

            diff --git a/sql/sql_window.cc b/sql/sql_window.cc
            index 17920519b41..2d26dd13fd4 100644
            --- a/sql/sql_window.cc
            +++ b/sql/sql_window.cc
            @@ -430,10 +430,7 @@ int compare_order_elements(ORDER *ord1, ORDER *ord2)
                 return CMP_EQ;
               Item *item1= (*ord1->item)->real_item();
               Item *item2= (*ord2->item)->real_item();
            -  DBUG_ASSERT(item1->type() == Item::FIELD_ITEM &&
            -              item2->type() == Item::FIELD_ITEM); 
            -  int cmp= ((Item_field *) item1)->field->field_index -
            -           ((Item_field *) item2)->field->field_index;
            +  int cmp= (char*)item1 - (char*) item2;
               if (cmp == 0)
               {
                 if (ord1->direction == ord2->direction)
            

            needs to be polished.

            psergei Sergei Petrunia added a comment - Idea of the fix diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 17920519b41..2d26dd13fd4 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -430,10 +430,7 @@ int compare_order_elements(ORDER *ord1, ORDER *ord2) return CMP_EQ; Item *item1= (*ord1->item)->real_item(); Item *item2= (*ord2->item)->real_item(); - DBUG_ASSERT(item1->type() == Item::FIELD_ITEM && - item2->type() == Item::FIELD_ITEM); - int cmp= ((Item_field *) item1)->field->field_index - - ((Item_field *) item2)->field->field_index; + int cmp= (char*)item1 - (char*) item2; if (cmp == 0) { if (ord1->direction == ord2->direction) needs to be polished.

            Closed by fix for MDEV-19398

            psergei Sergei Petrunia added a comment - Closed by fix for MDEV-19398

            People

              psergei Sergei Petrunia
              monty Michael Widenius
              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.