[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 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Fix Version/s: 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2, 11.4.1
Component/s: Optimizer
Labels:
- regression
- regression-10.6

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

relates to

MDEV-28206 SIGSEGV in Item_field::fix_fields when using LEAD...OVER

Closed

Activity

Ascending order - Click to sort in descending order

Valerii Kravchuk created issue - 2023-05-17 07:26

Alice Sherepa made changes - 2023-05-17 13:22

Field	Original Value	New Value
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]
Affects Version/s		10.11 [ 27614 ]
Affects Version/s		11.0 [ 28320 ]

Alice Sherepa made changes - 2023-05-17 13:22

Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]
Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.0 [ 28320 ]
Fix Version/s	10.6.12 [ 28513 ]
Fix Version/s	10.6.13 [ 28514 ]
Fix Version/s	10.6.14 [ 28914 ]

Alice Sherepa made changes - 2023-05-17 13:48

Link

This issue relates to ~~MDEV-28206~~ [ ~~MDEV-28206~~ ]

Alice Sherepa added a comment - 2023-05-17 14:01

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 Sherepa added a comment - 2023-05-17 14:01 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 Sherepa made changes - 2023-05-17 14:03

Assignee

Dmitry Shulga [ JIRAUSER47315 ]

Alice Sherepa made changes - 2023-05-17 14:03

Status

Open [ 1 ]

Confirmed [ 10101 ]

Julien Fritsch made changes - 2023-10-16 08:04

Priority

Major [ 3 ]

Critical [ 2 ]

Dmitry Shulga made changes - 2023-11-14 10:43

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Dmitry Shulga added a comment - 2023-11-20 08:05

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

Dmitry Shulga added a comment - 2023-11-20 08:05 The branch for review is bb-10.6- MDEV-31296

Dmitry Shulga made changes - 2023-11-20 08:05

Assignee	Dmitry Shulga [ JIRAUSER47315 ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Julien Fritsch made changes - 2023-11-28 10:22

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2023-11-28 10:31

Fix Version/s

10.10 [ 27530 ]

Oleksandr Byelkin added a comment - 2023-12-11 07:40

OK to push

Oleksandr Byelkin added a comment - 2023-12-11 07:40 OK to push

Oleksandr Byelkin made changes - 2023-12-11 07:40

Assignee	Oleksandr Byelkin [ sanja ]	Dmitry Shulga [ JIRAUSER47315 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Dmitry Shulga made changes - 2023-12-11 09:06

Fix Version/s		10.6.17 [ 29518 ]
Fix Version/s		10.11.7 [ 29519 ]
Fix Version/s		11.0.5 [ 29520 ]
Fix Version/s		11.1.4 [ 29024 ]
Fix Version/s		11.2.3 [ 29521 ]
Fix Version/s		11.3.2 [ 29522 ]
Fix Version/s		11.4.1 [ 29523 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.11 [ 27614 ]
Fix Version/s	11.0 [ 28320 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Jira Automation (IT) made changes - 2024-07-04 00:58

Zendesk Related Tickets

133498

People

Assignee:: Dmitry Shulga

Reporter:: Valerii Kravchuk

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2023-05-17 07:26

Updated:: 2024-07-07 19:36

Resolved:: 2023-12-11 09:06

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.