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

Crash in CTE for complex query

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Progress (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 10.3
    • Component/s: Optimizer - CTE
    • Labels:
      None

      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

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              monty Michael Widenius
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration