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

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

            alice Alice Sherepa added a comment -

            Thanks! I repeated as described on 10.6-10.11 with InnoDB/Myisam, not a recent regression (I tried 10.6.11)

            It seems to be the same bug as MDEV-28206, and it was not fixed for sql_mode = "ONLY_FULL_GROUP_BY", so below I added test from there also:

             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;
             
            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;
             
            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;
             
            insert into div_req_sel
            (request_id, subsystem_id, model_id)
             values 
            (1462687, 4,  118620);
             
            insert into div_mdl_seg
            (model_id, seg_id, low_score)
             values 
            (118620, 1,  50),
            (118620, 2,  30);
             
            insert into div_req_sel_w_scores 
            (request_id, score_value, net_names)
             values
            (1462687,-30, 351242),
            (1462687,-29, 2090304);
             
             
            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
             ";
             
            set session sql_mode = "ONLY_FULL_GROUP_BY";
             
            EXECUTE stmt;
            
            

            set session sql_mode = "ONLY_FULL_GROUP_BY";
            CREATE TABLE t1 ( a int, i int) ;
             
            CREATE TABLE t2 ( a int);
            INSERT INTO t2 VALUES (4000);
             
            execute immediate  "SELECT sum(i) over (ORDER BY i) FROM t1 natural join t2;";
            

            230517 15:55:24 [ERROR] mysqld got signal 11 ;
             
            Server version: 10.6.14-MariaDB-debug-log source revision: a3e5b5c4db47cd29dc090d27db1d7f915cf774f6
             
            sql/signal_handler.cc:241(handle_fatal_signal)[0x5574eb1ab0e6]
            sigaction.c:0(__restore_rt)[0x7f9ecc9fd420]
            sql/item.cc:6236(Item_field::fix_fields(THD*, Item**))[0x5574eb238256]
            sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x5574ea664ff3]
            sql/item_func.cc:350(Item_func::fix_fields(THD*, Item**))[0x5574eb306c46]
            sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x5574ea664ff3]
            sql/item.h:1156(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5574ea66502d]
            sql/item.h:1161(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x5574ea7a1ef5]
            sql/item_cmpfunc.cc:4946(Item_cond::fix_fields(THD*, Item**))[0x5574eb2ab57c]
            sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x5574ea664ff3]
            sql/item.h:1156(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5574ea66502d]
            sql/item.h:1161(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x5574ea7a1ef5]
            sql/sql_base.cc:8563(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x5574ea799357]
            sql/sql_select.cc:862(setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<TABLE_LIST>&, List<Item>&, List<Item>&, Item**, st_order*, st_order*, List<Window_spec>&, List<Item_window_func>&, bool*, unsigned int*))[0x5574ea9cfda2]
            sql/sql_select.cc:1442(JOIN::prepare(TABLE_LIST*, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5574ea9d758a]
            sql/sql_union.cc:1105(st_select_lex_unit::prepare_join(THD*, st_select_lex*, select_result*, unsigned long long, bool))[0x5574eac1e301]
            sql/sql_union.cc:1498(st_select_lex_unit::prepare(TABLE_LIST*, select_result*, unsigned long long))[0x5574eac21e9e]
            sql/sql_derived.cc:840(mysql_derived_prepare(THD*, LEX*, TABLE_LIST*))[0x5574ea836854]
            sql/sql_derived.cc:200(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x5574ea832aba]
            sql/table.cc:9386(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x5574eaccd249]
            sql/sql_lex.h:4491(LEX::handle_list_of_derived(TABLE_LIST*, unsigned int))[0x5574ea87e3b2]
            sql/sql_lex.cc:4999(st_select_lex::handle_derived(LEX*, unsigned int))[0x5574ea8a4831]
            sql/sql_select.cc:1341(JOIN::prepare(TABLE_LIST*, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5574ea9d5ff4]
            sql/sql_select.cc:5045(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5574ea9fddd7]
            sql/sql_select.cc:559(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5574ea9cdd61]
            sql/sql_parse.cc:6273(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5574ea92ee5a]
            sql/sql_parse.cc:3949(mysql_execute_command(THD*, bool))[0x5574ea91d7c7]
            sql/sql_prepare.cc:5205(Prepared_statement::execute(String*, bool))[0x5574ea9921aa]
            sql/sql_prepare.cc:4633(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x5574ea98d368]
            sql/sql_prepare.cc:3687(mysql_sql_stmt_execute(THD*))[0x5574ea98692a]
            sql/sql_parse.cc:3966(mysql_execute_command(THD*, bool))[0x5574ea91d80c]
            sql/sql_parse.cc:8036(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5574ea93a185]
            sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5574ea910005]
            sql/sql_parse.cc:1409(do_command(THD*, bool))[0x5574ea90cd50]
            sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5574ead8a6a9]
            sql/sql_connect.cc:1320(handle_one_connection)[0x5574ead8a006]
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5574eba24f6e]
            nptl/pthread_create.c:478(start_thread)[0x7f9ecc9f1609]
             
             
            Trying to get some variables.
            Some pointers may be invalid and cause the dump to abort.
            Query (0x62b0000d2340): 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
            

            230517 15:58:35 [ERROR] mysqld got signal 11 ;
             
            Server version: 10.6.14-MariaDB-debug-log source revision: a3e5b5c4db47cd29dc090d27db1d7f915cf774f6
             
            sql/signal_handler.cc:241(handle_fatal_signal)[0x55c8043cd0e6]
            sigaction.c:0(__restore_rt)[0x7fa0bc2fb420]
            sql/item.cc:6236(Item_field::fix_fields(THD*, Item**))[0x55c80445a256]
            sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x55c803886ff3]
            sql/item_func.cc:350(Item_func::fix_fields(THD*, Item**))[0x55c804528c46]
            sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x55c803886ff3]
            sql/item.h:1156(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x55c80388702d]
            sql/item.h:1161(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x55c8039c3ef5]
            sql/sql_base.cc:8452(setup_on_expr(THD*, TABLE_LIST*, bool))[0x55c8039ba727]
            sql/sql_base.cc:8571(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x55c8039bb37f]
            sql/sql_select.cc:862(setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<TABLE_LIST>&, List<Item>&, List<Item>&, Item**, st_order*, st_order*, List<Window_spec>&, List<Item_window_func>&, bool*, unsigned int*))[0x55c803bf1da2]
            sql/sql_select.cc:1442(JOIN::prepare(TABLE_LIST*, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x55c803bf958a]
            sql/sql_select.cc:5045(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55c803c1fdd7]
            sql/sql_select.cc:559(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55c803befd61]
            sql/sql_parse.cc:6273(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55c803b50e5a]
            sql/sql_parse.cc:3949(mysql_execute_command(THD*, bool))[0x55c803b3f7c7]
            sql/sql_prepare.cc:5205(Prepared_statement::execute(String*, bool))[0x55c803bb41aa]
            sql/sql_prepare.cc:4633(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55c803baf368]
            sql/sql_prepare.cc:5357(Prepared_statement::execute_immediate(char const*, unsigned int))[0x55c803bb542e]
            sql/sql_prepare.cc:3096(mysql_sql_stmt_execute_immediate(THD*))[0x55c803ba55dd]
            sql/sql_parse.cc:3956(mysql_execute_command(THD*, bool))[0x55c803b3f7e4]
            sql/sql_parse.cc:8036(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55c803b5c185]
            sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55c803b32005]
            sql/sql_parse.cc:1409(do_command(THD*, bool))[0x55c803b2ed50]
            sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55c803fac6a9]
            sql/sql_connect.cc:1320(handle_one_connection)[0x55c803fac006]
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55c804c46f6e]
            nptl/pthread_create.c:478(start_thread)[0x7fa0bc2ef609]
             
            Query (0x62b0000d2c78): SELECT sum(i) over (ORDER BY i) FROM t1 natural join t2
            
            

            alice Alice Sherepa added a comment - Thanks! I repeated as described on 10.6-10.11 with InnoDB/Myisam, not a recent regression (I tried 10.6.11) It seems to be the same bug as MDEV-28206 , and it was not fixed for sql_mode = "ONLY_FULL_GROUP_BY", so below I added test from there also: 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;   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;   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 ;   insert into div_req_sel (request_id, subsystem_id, model_id) values (1462687, 4, 118620); insert into div_mdl_seg (model_id, seg_id, low_score) values (118620, 1, 50), (118620, 2, 30); insert into div_req_sel_w_scores (request_id, score_value, net_names) values (1462687,-30, 351242), (1462687,-29, 2090304);   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 " ;   set session sql_mode = "ONLY_FULL_GROUP_BY" ;   EXECUTE stmt; set session sql_mode = "ONLY_FULL_GROUP_BY" ; CREATE TABLE t1 ( a int , i int ) ; CREATE TABLE t2 ( a int ); INSERT INTO t2 VALUES (4000); execute immediate "SELECT sum(i) over (ORDER BY i) FROM t1 natural join t2;" ; 230517 15:55:24 [ERROR] mysqld got signal 11 ;   Server version: 10.6.14-MariaDB-debug-log source revision: a3e5b5c4db47cd29dc090d27db1d7f915cf774f6   sql/signal_handler.cc:241(handle_fatal_signal)[0x5574eb1ab0e6] sigaction.c:0(__restore_rt)[0x7f9ecc9fd420] sql/item.cc:6236(Item_field::fix_fields(THD*, Item**))[0x5574eb238256] sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x5574ea664ff3] sql/item_func.cc:350(Item_func::fix_fields(THD*, Item**))[0x5574eb306c46] sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x5574ea664ff3] sql/item.h:1156(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5574ea66502d] sql/item.h:1161(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x5574ea7a1ef5] sql/item_cmpfunc.cc:4946(Item_cond::fix_fields(THD*, Item**))[0x5574eb2ab57c] sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x5574ea664ff3] sql/item.h:1156(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x5574ea66502d] sql/item.h:1161(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x5574ea7a1ef5] sql/sql_base.cc:8563(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x5574ea799357] sql/sql_select.cc:862(setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<TABLE_LIST>&, List<Item>&, List<Item>&, Item**, st_order*, st_order*, List<Window_spec>&, List<Item_window_func>&, bool*, unsigned int*))[0x5574ea9cfda2] sql/sql_select.cc:1442(JOIN::prepare(TABLE_LIST*, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5574ea9d758a] sql/sql_union.cc:1105(st_select_lex_unit::prepare_join(THD*, st_select_lex*, select_result*, unsigned long long, bool))[0x5574eac1e301] sql/sql_union.cc:1498(st_select_lex_unit::prepare(TABLE_LIST*, select_result*, unsigned long long))[0x5574eac21e9e] sql/sql_derived.cc:840(mysql_derived_prepare(THD*, LEX*, TABLE_LIST*))[0x5574ea836854] sql/sql_derived.cc:200(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x5574ea832aba] sql/table.cc:9386(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x5574eaccd249] sql/sql_lex.h:4491(LEX::handle_list_of_derived(TABLE_LIST*, unsigned int))[0x5574ea87e3b2] sql/sql_lex.cc:4999(st_select_lex::handle_derived(LEX*, unsigned int))[0x5574ea8a4831] sql/sql_select.cc:1341(JOIN::prepare(TABLE_LIST*, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5574ea9d5ff4] sql/sql_select.cc:5045(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5574ea9fddd7] sql/sql_select.cc:559(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5574ea9cdd61] sql/sql_parse.cc:6273(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5574ea92ee5a] sql/sql_parse.cc:3949(mysql_execute_command(THD*, bool))[0x5574ea91d7c7] sql/sql_prepare.cc:5205(Prepared_statement::execute(String*, bool))[0x5574ea9921aa] sql/sql_prepare.cc:4633(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x5574ea98d368] sql/sql_prepare.cc:3687(mysql_sql_stmt_execute(THD*))[0x5574ea98692a] sql/sql_parse.cc:3966(mysql_execute_command(THD*, bool))[0x5574ea91d80c] sql/sql_parse.cc:8036(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5574ea93a185] sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5574ea910005] sql/sql_parse.cc:1409(do_command(THD*, bool))[0x5574ea90cd50] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5574ead8a6a9] sql/sql_connect.cc:1320(handle_one_connection)[0x5574ead8a006] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5574eba24f6e] nptl/pthread_create.c:478(start_thread)[0x7f9ecc9f1609]     Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x62b0000d2340): 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 230517 15:58:35 [ERROR] mysqld got signal 11 ;   Server version: 10.6.14-MariaDB-debug-log source revision: a3e5b5c4db47cd29dc090d27db1d7f915cf774f6   sql/signal_handler.cc:241(handle_fatal_signal)[0x55c8043cd0e6] sigaction.c:0(__restore_rt)[0x7fa0bc2fb420] sql/item.cc:6236(Item_field::fix_fields(THD*, Item**))[0x55c80445a256] sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x55c803886ff3] sql/item_func.cc:350(Item_func::fix_fields(THD*, Item**))[0x55c804528c46] sql/item.h:1147(Item::fix_fields_if_needed(THD*, Item**))[0x55c803886ff3] sql/item.h:1156(Item::fix_fields_if_needed_for_scalar(THD*, Item**))[0x55c80388702d] sql/item.h:1161(Item::fix_fields_if_needed_for_bool(THD*, Item**))[0x55c8039c3ef5] sql/sql_base.cc:8452(setup_on_expr(THD*, TABLE_LIST*, bool))[0x55c8039ba727] sql/sql_base.cc:8571(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x55c8039bb37f] sql/sql_select.cc:862(setup_without_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<TABLE_LIST>&, List<Item>&, List<Item>&, Item**, st_order*, st_order*, List<Window_spec>&, List<Item_window_func>&, bool*, unsigned int*))[0x55c803bf1da2] sql/sql_select.cc:1442(JOIN::prepare(TABLE_LIST*, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x55c803bf958a] sql/sql_select.cc:5045(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55c803c1fdd7] sql/sql_select.cc:559(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55c803befd61] sql/sql_parse.cc:6273(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55c803b50e5a] sql/sql_parse.cc:3949(mysql_execute_command(THD*, bool))[0x55c803b3f7c7] sql/sql_prepare.cc:5205(Prepared_statement::execute(String*, bool))[0x55c803bb41aa] sql/sql_prepare.cc:4633(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55c803baf368] sql/sql_prepare.cc:5357(Prepared_statement::execute_immediate(char const*, unsigned int))[0x55c803bb542e] sql/sql_prepare.cc:3096(mysql_sql_stmt_execute_immediate(THD*))[0x55c803ba55dd] sql/sql_parse.cc:3956(mysql_execute_command(THD*, bool))[0x55c803b3f7e4] sql/sql_parse.cc:8036(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55c803b5c185] sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55c803b32005] sql/sql_parse.cc:1409(do_command(THD*, bool))[0x55c803b2ed50] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55c803fac6a9] sql/sql_connect.cc:1320(handle_one_connection)[0x55c803fac006] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55c804c46f6e] nptl/pthread_create.c:478(start_thread)[0x7fa0bc2ef609]   Query (0x62b0000d2c78): SELECT sum(i) over (ORDER BY i) FROM t1 natural join t2
            shulga Dmitry Shulga added a comment -

            The branch for review is bb-10.6-MDEV-31296

            shulga Dmitry Shulga added a comment - The branch for review is bb-10.6- MDEV-31296

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push

            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.