[#MDEV-31241] Crashes in subselects in choose_best_splitting after upgrade to 10.6.13

Comment by Daniel Black [ 2023-05-11 ]

Appears to have the same stack trace as ~~MDEV-31240~~.

Can you show the EXPLAIN query?

Comment by Daniel Black [ 2023-05-11 ]

Since you have a test system with debug symbols installed, can you:

Use gdb to attach to mariadb

sudo -u mysql gdb -p $(pidof mariadbd)

(gdb) continue

Execute the crashing query.

(gdb) set print frame-arguments all

(gdb) bt full

And include that output here please.

Comment by Markus Peter [ 2023-05-11 ]

I cannot show the EXPLAIN as the EXPLAIN crashes, too.

I've run gdb on a core dump earlier and poked around a bit:

[Thread debugging using libthread_db enabled]

Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".

--Type <RET> for more, q to quit, c to continue without paging--

Core was generated by `/usr/sbin/mariadbd'.

Program terminated with signal SIGSEGV, Segmentation fault.

#0  0x00007f2ef9ce396f in __GI___poll (fds=0x55e4f5b322c8, nfds=2, timeout=timeout@entry=-1)

    at ../sysdeps/unix/sysv/linux/poll.c:29

29	../sysdeps/unix/sysv/linux/poll.c: No such file or directory.

[Current thread is 1 (Thread 0x7f2ef98fe080 (LWP 9559))]

(gdb) thread 2

[Switching to thread 2 (Thread 0x7f2ea810c700 (LWP 9698))]

#0  st_join_table::choose_best_splitting (this=this@entry=0x7f292c176888, idx=idx@entry=0,

    remaining_tables=remaining_tables@entry=1, spl_pd_boundary=spl_pd_boundary@entry=0x7f2ea8109cd8)

    at ./sql/opt_split.cc:1047

1047	./sql/opt_split.cc: No such file or directory.

(gdb) bt

#0  st_join_table::choose_best_splitting (this=this@entry=0x7f292c176888, idx=idx@entry=0,

    remaining_tables=remaining_tables@entry=1, spl_pd_boundary=spl_pd_boundary@entry=0x7f2ea8109cd8)

    at ./sql/opt_split.cc:1047

#1  0x000055e4f3386988 in best_access_path (join=0x7f292c1556d8, s=0x7f292c176888, remaining_tables=1,

    join_positions=0x7f292c176e58, idx=0, disable_jbuf=true, record_count=1, pos=0x7f292c176e58,

    loose_scan_pos=0x7f2ea8109e60) at ./sql/sql_select.cc:7898

#2  0x000055e4f338a71f in best_extension_by_limited_search (join=0x7f292c1556d8, remaining_tables=1, idx=0,

    record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=4) at ./sql/sql_select.cc:10334

#3  0x000055e4f338b534 in greedy_search (use_cond_selectivity=4, prune_level=1, search_depth=62, remaining_tables=1,

    join=0x7f292c1556d8) at ./sql/sql_select.cc:9471

#4  choose_plan (join=0x7f292c1556d8, join_tables=<optimized out>) at ./sql/sql_select.cc:9031

#5  0x000055e4f33b57b1 in make_join_statistics (join=0x7f292c1556d8, tables_list=..., keyuse_array=0x7f292c1559f8)

    at ./sql/sql_select.cc:5963

#6  0x000055e4f33ba49c in JOIN::optimize_inner (this=0x7f292c1556d8) at ./sql/sql_select.cc:2509

#7  0x000055e4f33baab0 in JOIN::optimize (this=this@entry=0x7f292c1556d8) at ./sql/sql_select.cc:1848

#8  0x000055e4f332773d in st_select_lex::optimize_unflattened_subqueries (this=0x7f292c0113b0,

    const_only=const_only@entry=false) at ./sql/sql_lex.cc:4925

#9  0x000055e4f34a608e in JOIN::optimize_unflattened_subqueries (this=this@entry=0x7f292c119820)

    at ./sql/opt_subselect.cc:5674

#10 0x000055e4f33b61da in JOIN::optimize_stage2 (this=<optimized out>) at ./sql/sql_select.cc:3334

#11 0x000055e4f33ba5ac in JOIN::optimize_inner (this=0x7f292c119820) at ./sql/sql_select.cc:2535

#12 0x000055e4f33baab0 in JOIN::optimize (this=this@entry=0x7f292c119820) at ./sql/sql_select.cc:1848

#13 0x000055e4f33bab83 in mysql_select (thd=0x7f292c000c58, tables=0x7f292c020218, fields=..., conds=0x7f292c020a30,

    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x7f292c1197f8,

    unit=0x7f292c004e08, select_lex=0x7f292c0113b0) at ./sql/sql_select.cc:5055

#14 0x000055e4f33bb38f in handle_select (thd=thd@entry=0x7f292c000c58, lex=lex@entry=0x7f292c004d40,

    result=result@entry=0x7f292c1197f8, setup_tables_done_option=setup_tables_done_option@entry=0)

    at ./sql/sql_select.cc:559

#15 0x000055e4f33410f1 in execute_sqlcom_select (thd=0x7f292c000c58, all_tables=0x7f292c020218)

    at ./sql/sql_parse.cc:6273

#16 0x000055e4f3350267 in mysql_execute_command (thd=0x7f292c000c58, is_called_from_prepared_stmt=<optimized out>)

    at ./sql/sql_parse.cc:3949

#17 0x000055e4f3351857 in mysql_parse (thd=0x7f292c000c58, rawbuf=<optimized out>, length=<optimized out>,

    parser_state=<optimized out>) at ./sql/sql_parse.cc:8036

#18 0x000055e4f335405d in dispatch_command (command=COM_QUERY, thd=0x7f292c000c58, packet=<optimized out>,

    packet_length=<optimized out>, blocking=<optimized out>) at ./sql/sql_class.h:240

#19 0x000055e4f3355bf8 in do_command (thd=0x7f292c000c58, blocking=blocking@entry=true) at ./sql/sql_parse.cc:1409

#20 0x000055e4f346470f in do_handle_one_connection (connect=<optimized out>, put_in_cache=true)

    at ./sql/sql_connect.cc:1416

#21 0x000055e4f3464a5d in handle_one_connection (arg=arg@entry=0x55e4f5b34db8) at ./sql/sql_connect.cc:1318

#22 0x000055e4f37d14c2 in pfs_spawn_thread (arg=0x55e4f5b34e28) at ./storage/perfschema/pfs.cc:2201

#23 0x00007f2efa0e8ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477

#24 0x00007f2ef9cefa2f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

(gdb) info locals

pos = 0x80712c176d10

last_found = 1

spl_opt_info = 0x7f292c1767e8

join = 0x7f292c155d68

thd = 0x7f292c000c58

tables_usable_for_splitting = 1

keyuse_ext = 0x7f292c1cae80

best_key_keyuse_ext_start = <optimized out>

best_table = <optimized out>

best_rec_per_key = <optimized out>

spl_plan = 0x0

best_key = <optimized out>

best_key_parts = <optimized out>

best_param_tables = <optimized out>

trace_obj = {<Json_writer_struct> = {_vptr.Json_writer_struct = 0x55e4f4171910 <vtable for Json_writer_object+16>,

    my_writer = 0x0, context = {writer = 0x0}, closed = false}, <No data fields>}

trace_arr = {<Json_writer_struct> = {_vptr.Json_writer_struct = 0x55e4f4171930 <vtable for Json_writer_array+16>,

    my_writer = 0x0, context = {writer = 0x0}, closed = true}, <No data fields>}

refills = 1.7976931348623157e+308

excluded_tables = 1

(gdb) p pos

$1 = (POSITION *) 0x80712c176d10

(gdb) p pos->table

Cannot access memory at address 0x80712c176d10

(gdb) info args

this = 0x7f292c176888

idx = 0

remaining_tables = 1

spl_pd_boundary = 0x7f2ea8109cd8

I will attach the backtrace you requested as a file - copy & paste seems to destroy indentation.

Comment by Daniel Black [ 2023-05-11 ]

Thanks. Good addition of the locals.

{noformat} can be used for getting a raw format on sections.

Comment by Daniel Black [ 2023-05-11 ]

When idx =0, accessing POSITION *pos= &this->join->positions[idx - 1] isn't a good move. (opt_split.cc:1045).

Comment by Sergei Petrunia [ 2023-05-11 ]

Pretty-printed crashing query:

SELECT * FROM

  (SELECT

     CONCAT(Nachname, ', ', Vorname) AS Name ,

     CONCAT(RIGHT(mo.month, 2), ', ', LEFT(mo.month, 4)) AS Monat ,

     Bildungsgutscheinnummer AS Maßnahmennummer ,

     CONCAT(DATE_FORMAT(Vertrag_Beginn,'%d.%m.%Y'), ' - ',

     DATE_FORMAT(Vertrag_Ende, '%d.%m.%Y')) AS Maßnahmenzeitraum ,

        SELECT

          SUM(Anzahl)

        FROM

          view_Schüler_Fehlzeit_Monat_Tag vst

        WHERE

          s.Id = vst.Schüler_Id AND

          vst.jahr_monat BETWEEN date_format(Vertrag_Beginn, '%Y-%m') AND mo.month

      ) AS 'Fehltage_gesamt' ,

      f.eins , f.zwei , f.drei , f.vier , f.fünf , f.sechs , f.sieben , f.acht ,

      f.neun , f.zehn , f.elf , f.zwölf , f.dreizehn , f.vierzehn , f.fünfzehn ,

      f.sechzehn , f.siebzehn , f.achtzehn , f.neunzehn , f.zwanzig , f.einundzwanzig ,

      f.zweiundzwanzig , f.dreiundzwanzig , f.vierundzwanzig , f.fünfundzwanzig ,

      f.sechsundzwanzig , f.siebenundzwanzig , f.achtundzwanzig , f.neunundzwanzig ,

      f.dreißig , f.einunddreißig

   FROM

     Akademie_Schüler s JOIN const.month mo ON mo.month BETWEEN date_format(Vertrag_Beginn, '%Y-%m') AND

        date_format(ifnull(Vertrag_Ende, CURRENT_DATE), '%Y-%m')

     LEFT JOIN view_Schüler_Fehlzeit_Monat_Tag f ON f.Schüler_Id = s.Id AND mo.month = jahr_monat

   WHERE 1=0

AS _dummy_table WHERE 1=0 LIMIT 0

Note: we crash when optimizing the join inside the subquery. The subquery uses view_Schüler_Fehlzeit_Monat_Tag. The text of the query we have does not seem to be important for the crash.

Comment by Sergei Petrunia [ 2023-05-11 ]

See ~~MDEV-31240~~ for analysis and suggested patch.

Comment by Markus Peter [ 2023-05-11 ]

Original view definition is:

CREATE TABLE `Fehlzeit_Schüler` (

  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `Datum` date NOT NULL,

  `Schüler_Id` int(11) unsigned NOT NULL,

  `Typ` enum('Ganzer Tag','Kommt Später','Geht Früher','Kommt Später und geht Früher') NOT NULL DEFAULT 'Ganzer Tag',

  `Verpasste_Zeit` int(11) DEFAULT NULL,

  `Entschuldigung` enum('Krank','Termin','Sonstiges(Angekündigt)','Sonstiges(Unangekündigt)','KEINE') DEFAULT NULL,

  `Entschuldigung_Begründung` text DEFAULT NULL,

  `Foto_Krankmeldung` text DEFAULT NULL,

  `nubis_created_by` varchar(80) DEFAULT NULL,

  `nubis_created_at` datetime DEFAULT current_timestamp(),

  `nubis_changed_by` varchar(80) DEFAULT NULL,

  `nubis_changed_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`Id`),

  UNIQUE KEY `key_schuelerid_datum` (`Schüler_Id`,`Datum`)

) ;

CREATE VIEW view_Schüler_Fehlzeit_Monat_Tag AS SELECT

	Schüler_Id,

	DATE_FORMAT(Datum,'%Y-%m') AS 'jahr_monat',

	Datum AS 'Datum',

	COUNT(*) AS Anzahl,

	GROUP_CONCAT(IF (Day(Datum) = 1,map_excuses(Entschuldigung),NULL)) AS eins,

	GROUP_CONCAT(IF (Day(Datum) = 2,map_excuses(Entschuldigung),NULL)) AS zwei,

	GROUP_CONCAT(IF (Day(Datum) = 3,map_excuses(Entschuldigung),NULL)) AS drei,

	GROUP_CONCAT(IF (Day(Datum) = 4,map_excuses(Entschuldigung),NULL)) AS vier,

	GROUP_CONCAT(IF (Day(Datum) = 5,map_excuses(Entschuldigung),NULL)) AS fünf,

	GROUP_CONCAT(IF (Day(Datum) = 6,map_excuses(Entschuldigung),NULL)) AS sechs,

	GROUP_CONCAT(IF (Day(Datum) = 7,map_excuses(Entschuldigung),NULL)) AS sieben,

	GROUP_CONCAT(IF (Day(Datum) = 8,map_excuses(Entschuldigung),NULL)) AS acht,

	GROUP_CONCAT(IF (Day(Datum) = 9,map_excuses(Entschuldigung),NULL)) AS neun,

	GROUP_CONCAT(IF (Day(Datum) = 10,map_excuses(Entschuldigung),NULL)) AS zehn,

	GROUP_CONCAT(IF (Day(Datum) = 11,map_excuses(Entschuldigung),NULL)) AS elf,

	GROUP_CONCAT(IF (Day(Datum) = 12,map_excuses(Entschuldigung),NULL)) AS zwölf,

	GROUP_CONCAT(IF (Day(Datum) = 13,map_excuses(Entschuldigung),NULL)) AS dreizehn,

	GROUP_CONCAT(IF (Day(Datum) = 14,map_excuses(Entschuldigung),NULL)) AS vierzehn,

	GROUP_CONCAT(IF (Day(Datum) = 15,map_excuses(Entschuldigung),NULL)) AS fünfzehn,

	GROUP_CONCAT(IF (Day(Datum) = 16,map_excuses(Entschuldigung),NULL)) AS sechzehn,

	GROUP_CONCAT(IF (Day(Datum) = 17,map_excuses(Entschuldigung),NULL)) AS siebzehn,

	GROUP_CONCAT(IF (Day(Datum) = 18,map_excuses(Entschuldigung),NULL)) AS achtzehn,

	GROUP_CONCAT(IF (Day(Datum) = 19,map_excuses(Entschuldigung),NULL)) AS neunzehn,

	GROUP_CONCAT(IF (Day(Datum) = 20,map_excuses(Entschuldigung),NULL)) AS zwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 21,map_excuses(Entschuldigung),NULL)) AS einundzwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 22,map_excuses(Entschuldigung),NULL)) AS zweiundzwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 23,map_excuses(Entschuldigung),NULL)) AS dreiundzwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 24,map_excuses(Entschuldigung),NULL)) AS vierundzwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 25,map_excuses(Entschuldigung),NULL)) AS fünfundzwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 26,map_excuses(Entschuldigung),NULL)) AS sechsundzwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 27,map_excuses(Entschuldigung),NULL)) AS siebenundzwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 28,map_excuses(Entschuldigung),NULL)) AS achtundzwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 29,map_excuses(Entschuldigung),NULL)) AS neunundzwanzig,

	GROUP_CONCAT(IF (Day(Datum) = 30,map_excuses(Entschuldigung),NULL)) AS dreißig,

	GROUP_CONCAT(IF (Day(Datum) = 31,map_excuses(Entschuldigung),NULL)) AS einunddreißig

FROM

	Fehlzeit_Schüler

GROUP BY

	Schüler_Id,

	DATE_FORMAT(Datum, '%Y-%m')

and for completeness, const.month is

CREATE TABLE `month` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `month` varchar(7) DEFAULT NULL,

  `premonth` varchar(7) DEFAULT NULL,

  `year` smallint(5) unsigned NOT NULL DEFAULT 0,

  PRIMARY KEY (`id`),

  UNIQUE KEY `month` (`month`),

  KEY `year` (`year`)

);

Comment by Markus Peter [ 2023-05-11 ]

I also attached an EXPLAIN screenshot from an older installation which is not crashing.

Comment by Daniel Black [ 2023-05-12 ]

Thanks for the info warp. Apologies for the bug introduction. Leaving resolution to the ~~MDEV-31240~~ issue.

Comment by Sergei Petrunia [ 2023-05-12 ]

Thanks for the view definition.

Putting the view into the query and removing irrelevant parts:

SELECT * FROM

  (SELECT

     (SELECT

...

        FROM

          (SELECT

                 Schüler_Id,

                 ....

              FROM

                 Fehlzeit_Schüler

              GROUP BY

                 Schüler_Id....

vst

        WHERE

          s.Id = vst.Schüler_Id AND

      ) AS 'Fehltage_gesamt',

   FROM

     Akademie_Schüler s JOIN ...

Note

s.Id = vst.Schüler_Id

vst.Schüler_Id is the Split Materialized column.
s.Id is the outside reference.

Indeed this is the same construct as in ~~MDEV-31240~~. The fix for ~~MDEV-31240~~ will fix this.

Comment by Sergei Petrunia [ 2023-05-12 ]

Ok the patch for ~~MDEV-31240~~ is pushed into 10.4. It will be merged to other versions.

[MDEV-31241] Crashes in subselects in choose_best_splitting after upgrade to 10.6.13 Created: 2023-05-11 Updated: 2023-12-18 Resolved: 2023-05-12
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer, Server
Affects Version/s:	10.6.13
Fix Version/s:	N/A

[MDEV-31241] Crashes in subselects in choose_best_splitting after upgrade to 10.6.13 Created: 2023-05-11 Updated: 2023-12-18 Resolved: 2023-05-12