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

Crash in Item_func::fix_fields when prepared statement with subqueries and window function is executed with sql_mode = ONLY_FULL_GROUP_BY

    XMLWordPrintable

Details

    Description

      Consider the following simplified test case:

      MariaDB [test]> select version();
      +-----------------+
      | version()       |
      +-----------------+
      | 10.6.14-MariaDB |
      +-----------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> drop table if exists div_req_sel_w_scores;
      Query OK, 0 rows affected (0.130 sec)
       
      MariaDB [test]> 
      MariaDB [test]>  CREATE TABLE div_req_sel_w_scores (
          ->   request_id      int(11) NOT NULL,
          ->   score_value     int(11) NOT NULL,
          ->   net_names       int(11) not null,
          ->   PRIMARY KEY (request_id, score_value)
          -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
      Query OK, 0 rows affected (0.090 sec)
       
      MariaDB [test]> 
      MariaDB [test]> drop table if exists div_mdl_seg;
      Query OK, 0 rows affected (0.084 sec)
       
      MariaDB [test]> 
      MariaDB [test]> CREATE TABLE div_mdl_seg (
          ->   model_id        int(11) NOT NULL,
          ->   seg_id          smallint(6) NOT NULL,
          ->   low_score       int(11) NOT NULL,
          ->   PRIMARY KEY (model_id, seg_id)
          -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
      Query OK, 0 rows affected (0.074 sec)
       
      MariaDB [test]> 
      MariaDB [test]> drop table if exists div_req_sel;
      Query OK, 0 rows affected (0.088 sec)
       
      MariaDB [test]> 
      MariaDB [test]> CREATE TABLE div_req_sel (
          ->   request_id    int(11) NOT NULL AUTO_INCREMENT,
          ->   subsystem_id  int(11) NOT NULL,
          ->   model_id      int(11) DEFAULT NULL,
          ->   PRIMARY KEY (request_id, subsystem_id) USING BTREE
          -> ) ENGINE=InnoDB AUTO_INCREMENT=1006382 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;
      Query OK, 0 rows affected (0.085 sec)
       
      MariaDB [test]> 
      MariaDB [test]> 
      MariaDB [test]> insert into div_req_sel
          ->   (request_id, subsystem_id, model_id)
          -> values 
          ->   (1462687, 4,  118620);
      Query OK, 1 row affected (0.004 sec)
       
      MariaDB [test]> insert into div_mdl_seg
          ->   (model_id, seg_id, low_score)
          -> values 
          ->   (118620, 1,  50),
          ->   (118620, 2,  30);
      Query OK, 2 rows affected (0.004 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into div_req_sel_w_scores 
          ->   (request_id, score_value, net_names)
          -> values
          ->   (1462687,-30, 351242),
          ->   (1462687,-29, 2090304);
      Query OK, 2 rows affected (0.003 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select @@sql_mode;
      +-------------------------------------------------------------------------------------------+
      | @@sql_mode                                                                                |
      +-------------------------------------------------------------------------------------------+
      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      +-------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> PREPARE stmt FROM "
          "> SELECT a.seg_id, s.score_value, s.net_names
          ">   FROM (SELECT rs.request_id, seg_id, low_score AS low_score_inclusive,
          ">                LEAD(low_score) OVER (PARTITION BY NULL ORDER BY seg_id) AS high_score_exclusive
          ">           FROM div_mdl_seg dms
          ">                JOIN div_req_sel rs USING (model_id)
          ">          WHERE request_id = 1462687
          ">         ) a
          ">         LEFT JOIN div_req_sel_w_scores s 
          ">             ON (    s.request_id = a.request_id
          ">                 AND a.low_score_inclusive <= s.score_value
          ">                 AND IFNULL(a.high_score_exclusive, 9999999) > s.score_value)
          ">  WHERE a.low_score_inclusive >=
          ">         (SELECT MIN(score_value)
          ">            FROM div_req_sel_w_scores
          ">           WHERE request_id = 1462687)
          ">  ORDER BY s.score_value DESC
          "> ";
      Query OK, 0 rows affected (0.001 sec)
      Statement prepared
       
      MariaDB [test]> EXECUTE stmt;
      +--------+-------------+-----------+
      | seg_id | score_value | net_names |
      +--------+-------------+-----------+
      |      1 |        NULL |      NULL |
      |      2 |        NULL |      NULL |
      +--------+-------------+-----------+
      2 rows in set (0.002 sec)
       
      MariaDB [test]> EXECUTE stmt;
      +--------+-------------+-----------+
      | seg_id | score_value | net_names |
      +--------+-------------+-----------+
      |      1 |        NULL |      NULL |
      |      2 |        NULL |      NULL |
      +--------+-------------+-----------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]> set session sql_mode = "ONLY_FULL_GROUP_BY";
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [test]> EXECUTE stmt;
      ERROR 2013 (HY000): Lost connection to server during query
      

      We get a crash with a backtrace similar to the following (this is from macOS, but the bug is repeatable on Linux with official binaries, including ES 10.6.12-7 as well):

      2023-05-17 10:13:31 0 [Note] /Users/Valerii/dbs/maria10.6/bin/mariadbd: ready for connections.
      Version: '10.6.14-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  MariaDB Server
      2023-05-17 10:13:31 0 [Note] InnoDB: Buffer pool(s) load completed at 230517 10:13:31
      Yuliyas-Air:maria10.6 Valerii$ tail -110 data/Yuliyas-Air.err 
      diagnose the problem, but since we have already crashed, 
      something is definitely wrong and this may fail.
       
      Server version: 10.6.14-MariaDB source revision: afe44ef2126d74c11407266cf7d0225ac0392e61
      key_buffer_size=134217728
      read_buffer_size=131072
      max_used_connections=1
      max_threads=153
      thread_count=1
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467973 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x7faf23833818
      Attempting backtrace. You can use the following information to find out
      where mysqld died. If you see no messages after this, something went
      terribly wrong...
      stack_bottom = 0x70000905ee70 thread_stack 0x49000
      Printing to addr2line failed
      0   mariadbd                            0x000000010afb726c my_print_stacktrace + 60
      0   mariadbd                            0x000000010a750094 handle_fatal_signal + 740
      0   libsystem_platform.dylib            0x00007fff64d90f5a _sigtramp + 26
      0   ???                                 0x0000000000000000 0x0 + 0
      0   mariadbd                            0x000000010a7c0acb _ZN9Item_func10fix_fieldsEP3THDPP4Item + 139
      0   mariadbd                            0x000000010a94ae8a _Z13setup_on_exprP3THDP10TABLE_LISTb + 586
      0   mariadbd                            0x000000010a94b0f0 _Z11setup_condsP3THDP10TABLE_LISTR4ListIS1_EPP4Item + 400
      0   mariadbd                            0x000000010a9ff645 _ZN4JOIN7prepareEP10TABLE_LISTP4ItemjP8st_orderbS5_S3_S5_P13st_select_lexP18st_select_lex_unit + 1717
      0   mariadbd                            0x000000010aaa09fc _ZN18st_select_lex_unit12prepare_joinEP3THDP13st_select_lexP13select_resultyb + 396
      0   mariadbd                            0x000000010aa9c75a _ZN18st_select_lex_unit7prepareEP10TABLE_LISTP13select_resulty + 1322
      0   mariadbd                            0x000000010a9771c0 _ZL21mysql_derived_prepareP3THDP3LEXP10TABLE_LIST + 640
      0   mariadbd                            0x000000010a978eeb _Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj + 171
      0   mariadbd                            0x000000010aaca4bc _ZN10TABLE_LIST14handle_derivedEP3LEXj + 140
      0   mariadbd                            0x000000010a99b6cf _ZN13st_select_lex14handle_derivedEP3LEXj + 63
      0   mariadbd                            0x000000010a9ff1f0 _ZN4JOIN7prepareEP10TABLE_LISTP4ItemjP8st_orderbS5_S3_S5_P13st_select_lexP18st_select_lex_unit + 608
      0   mariadbd                            0x000000010a9fb292 _Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex + 546
      0   mariadbd                            0x000000010a9faf4e _Z13handle_selectP3THDP3LEXP13select_resultm + 238
      0   mariadbd                            0x000000010a9c614f _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 1231
      0   mariadbd                            0x000000010a9bf8e9 _Z21mysql_execute_commandP3THDb + 2617
      0   mariadbd                            0x000000010a9ec7c1 _ZN18Prepared_statement7executeEP6Stringb + 993
      0   mariadbd                            0x000000010a9e76c9 _ZN18Prepared_statement12execute_loopEP6StringbPhS2_ + 201
      0   mariadbd                            0x000000010a9e74dd _Z22mysql_sql_stmt_executeP3THD + 413
      0   mariadbd                            0x000000010a9c2199 _Z21mysql_execute_commandP3THDb + 13033
      0   mariadbd                            0x000000010a9bb11b _Z11mysql_parseP3THDPcjP12Parser_state + 731
      0   mariadbd                            0x000000010a9b8ffb _Z16dispatch_command19enum_server_commandP3THDPcjb + 3899
      0   mariadbd                            0x000000010a9bb6d7 _Z10do_commandP3THDb + 1031
      0   mariadbd                            0x000000010ab0828e _Z24do_handle_one_connectionP7CONNECTb + 430
      0   mariadbd                            0x000000010ab07fab handle_one_connection + 91
      0   mariadbd                            0x000000010acaaa89 pfs_spawn_thread + 217
      0   libsystem_pthread.dylib             0x00007fff64d9a661 _pthread_body + 340
      0   libsystem_pthread.dylib             0x00007fff64d9a50d _pthread_body + 0
      0   libsystem_pthread.dylib             0x00007fff64d99bf9 thread_start + 13
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7faf24028418): SELECT a.seg_id, s.score_value, s.net_names
        FROM (SELECT rs.request_id, seg_id, low_score AS low_score_inclusive,
                     LEAD(low_score) OVER (PARTITION BY NULL ORDER BY seg_id) AS high_score_exclusive
                FROM div_mdl_seg dms
                     JOIN div_req_sel rs USING (model_id)
               WHERE request_id = 1462687
              ) a
              LEFT JOIN div_req_sel_w_scores s 
                  ON (    s.request_id = a.request_id
                      AND a.low_score_inclusive <= s.score_value
                      AND IFNULL(a.high_score_exclusive, 9999999) > s.score_value)
       WHERE a.low_score_inclusive >=
              (SELECT MIN(score_value)
                 FROM div_req_sel_w_scores
                WHERE request_id = 1462687)
       ORDER BY s.score_value DESC
       
      Connection ID (thread ID): 3
      Status: NOT_KILLED
      

      Note that crash happens upon the first execution as well, but does NOT happen for normal, non-prepared statement. In a fresh session:

      MariaDB [test]> set session sql_mode = "ONLY_FULL_GROUP_BY";
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> select @@sql_mode, version();
      +--------------------+-----------------+
      | @@sql_mode         | version()       |
      +--------------------+-----------------+
      | ONLY_FULL_GROUP_BY | 10.6.14-MariaDB |
      +--------------------+-----------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> SELECT a.seg_id, s.score_value, s.net_names
          ->   FROM (SELECT rs.request_id, seg_id, low_score AS low_score_inclusive,
          ->                LEAD(low_score) OVER (PARTITION BY NULL ORDER BY seg_id) AS high_score_exclusive
          ->           FROM div_mdl_seg dms
          ->                JOIN div_req_sel rs USING (model_id)
          ->          WHERE request_id = 1462687
          ->         ) a
          ->         LEFT JOIN div_req_sel_w_scores s 
          ->             ON (    s.request_id = a.request_id
          ->                 AND a.low_score_inclusive <= s.score_value
          ->                 AND IFNULL(a.high_score_exclusive, 9999999) > s.score_value)
          ->  WHERE a.low_score_inclusive >=
          ->         (SELECT MIN(score_value)
          ->            FROM div_req_sel_w_scores
          ->           WHERE request_id = 1462687)
          ->  ORDER BY s.score_value DESC;
      +--------+-------------+-----------+
      | seg_id | score_value | net_names |
      +--------+-------------+-----------+
      |      1 |        NULL |      NULL |
      |      2 |        NULL |      NULL |
      +--------+-------------+-----------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]> PREPARE stmt FROM "
          "> SELECT a.seg_id, s.score_value, s.net_names
          ">   FROM (SELECT rs.request_id, seg_id, low_score AS low_score_inclusive,
          ">                LEAD(low_score) OVER (PARTITION BY NULL ORDER BY seg_id) AS high_score_exclusive
          ">           FROM div_mdl_seg dms
          ">                JOIN div_req_sel rs USING (model_id)
          ">          WHERE request_id = 1462687
          ">         ) a
          ">         LEFT JOIN div_req_sel_w_scores s 
          ">             ON (    s.request_id = a.request_id
          ">                 AND a.low_score_inclusive <= s.score_value
          ">                 AND IFNULL(a.high_score_exclusive, 9999999) > s.score_value)
          ">  WHERE a.low_score_inclusive >=
          ">         (SELECT MIN(score_value)
          ">            FROM div_req_sel_w_scores
          ">           WHERE request_id = 1462687)
          ">  ORDER BY s.score_value DESC
          "> ";
      Query OK, 0 rows affected (0.001 sec)
      Statement prepared
       
      MariaDB [test]> EXECUTE stmt;
      ERROR 2013 (HY000): Lost connection to server during query
      

      The same query worked well as a prepared statement in 10.5.15, for example, so it's some kind of a regression in (at least recent) 10.6.

      Attachments

        Issue Links

          Activity

            People

              shulga Dmitry Shulga
              valerii Valerii Kravchuk
              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.