[MDEV-27601]  Assertion `is_last_prefix <= 0' failed in QUICK_GROUP_MIN_MAX_SELECT::get_next() Created: 2022-01-24  Updated: 2023-11-22

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-15656 Assertion `is_last_prefix <= 0' faile... Closed

 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a varchar(10) CHARACTER SET utf8mb4, key(a)) engine=innodb;
 
INSERT INTO t1 VALUES (0xEFBFBF),(0xF0908080),(0xF48FBFBF);
 
ALTER TABLE t1 MODIFY a varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
 
 
 
SELECT count(DISTINCT a) FROM t1;

10.4 49e3bd2cbcb12ed45908

 
Version: '10.4.23-MariaDB-debug-log'
 
mysqld: /10.4/sql/opt_range.cc:14994: virtual int QUICK_GROUP_MIN_MAX_SELECT::get_next(): Assertion `is_last_prefix <= 0' failed.
 
220124 15:44:34 [ERROR] mysqld got signal 6 ;
 
 
 
 
 
/lib/x86_64-linux-gnu/libc.so.6(+0x30102)[0x7f9bc173f102]
 
sql/opt_range.cc:14998(QUICK_GROUP_MIN_MAX_SELECT::get_next())[0x555f3caed9b2]
 
sql/records.cc:369(rr_quick(READ_RECORD*))[0x555f3cb0f526]
 
sql/records.h:70(READ_RECORD::read_record())[0x555f3bdc3c52]
 
sql/sql_select.cc:21552(join_init_read_record(st_join_table*))[0x555f3c0b480e]
 
sql/sql_select.cc:20600(sub_select(JOIN*, st_join_table*, bool))[0x555f3c0ada62]
 
sql/sql_select.cc:20139(do_select(JOIN*, Procedure*))[0x555f3c0abc67]
 
sql/sql_select.cc:4534(JOIN::exec_inner())[0x555f3c03bf1e]
 
sql/sql_select.cc:4317(JOIN::exec())[0x555f3c03956a]
 
sql/sql_select.cc:4757(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x555f3c03d5c9]
 
sql/sql_select.cc:436(handle_select(THD*, LEX*, select_result*, unsigned long))[0x555f3c00ee98]
 
sql/sql_parse.cc:6449(execute_sqlcom_select(THD*, TABLE_LIST*))[0x555f3bf80dd6]
 
sql/sql_parse.cc:3963(mysql_execute_command(THD*))[0x555f3bf6e697]
 
sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x555f3bf89f21]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x555f3bf60d06]
 
sql/sql_parse.cc:1373(do_command(THD*))[0x555f3bf5d777]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x555f3c345f5d]
 
sql/sql_connect.cc:1317(handle_one_connection)[0x555f3c3456ba]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x555f3cffd7ef]
 
nptl/pthread_create.c:487(start_thread)[0x7f9bc1bfdfa3]
 
x86_64/clone.S:97(clone)[0x7f9bc18084cf]
 
 
 
Query (0x62b0000a1290): SELECT count(DISTINCT a) FROM t1



 Comments   
Comment by Roel Van de Paar [ 2022-08-08 ]

Currently this bug only reproduces on 10.7, 10.8, 10.9

CREATE TABLE t1 (a varchar(10) CHARACTER SET utf8mb4, key(a)) engine=innodb;
 
INSERT INTO t1 VALUES (0xEFBFBF),(0xF0908080),(0xF48FBFBF);
 
ALTER TABLE t1 MODIFY a varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
 
SELECT count(DISTINCT a) FROM t1;

Leads to:

10.7.5 c3ddffe29c1d8ed4465820636748eb3aab145c90 (Debug)

 
mysqld: /test/10.7_dbg/sql/opt_range.cc:15361: virtual int QUICK_GROUP_MIN_MAX_SELECT::get_next(): Assertion `is_last_prefix <= 0' failed.

10.7.5 c3ddffe29c1d8ed4465820636748eb3aab145c90 (Debug)

 
Core was generated by `/test/MD120722-mariadb-10.7.5-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x1491893f7700 (LWP 2610210))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x00001491a1966859 in __GI_abort () at abort.c:79
 
#2  0x00001491a1966729 in __assert_fail_base (fmt=0x1491a1afc588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x559d9dee849e "is_last_prefix <= 0", file=0x559d9dee8510 "/test/10.7_dbg/sql/opt_range.cc", line=15361, function=<optimized out>) at assert.c:92
 
#3  0x00001491a1977fd6 in __GI___assert_fail (assertion=assertion@entry=0x559d9dee849e "is_last_prefix <= 0", file=file@entry=0x559d9dee8510 "/test/10.7_dbg/sql/opt_range.cc", line=line@entry=15361, function=function@entry=0x559d9deea4e8 "virtual int QUICK_GROUP_MIN_MAX_SELECT::get_next()") at assert.c:101
 
#4  0x0000559d9d24272e in QUICK_GROUP_MIN_MAX_SELECT::get_next (this=0x14910407cef0) at /test/10.7_dbg/sql/opt_range.cc:15361
 
#5  0x0000559d9d25332e in rr_quick (info=0x1491040169f0) at /test/10.7_dbg/sql/records.cc:403
 
#6  0x0000559d9d3b66c0 in READ_RECORD::read_record (this=0x1491040169f0) at /test/10.7_dbg/sql/records.h:81
 
#7  join_init_read_record (tab=0x149104016928) at /test/10.7_dbg/sql/sql_select.cc:22248
 
#8  0x0000559d9d39f5bd in sub_select (join=0x149104015448, join_tab=0x149104016928, end_of_records=false) at /test/10.7_dbg/sql/sql_select.cc:21251
 
#9  0x0000559d9d3d2ee5 in do_select (procedure=<optimized out>, join=0x149104015448) at /test/10.7_dbg/sql/sql_select.cc:20799
 
#10 JOIN::exec_inner (this=this@entry=0x149104015448) at /test/10.7_dbg/sql/sql_select.cc:4762
 
#11 0x0000559d9d3d347e in JOIN::exec (this=this@entry=0x149104015448) at /test/10.7_dbg/sql/sql_select.cc:4540
 
#12 0x0000559d9d3d1202 in mysql_select (thd=thd@entry=0x149104000db8, tables=0x1491040143e8, fields=@0x149104013f20: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x149104014368, last = 0x149104014368, elements = 1}, <No data fields>}, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x149104015420, unit=0x149104004fc0, select_lex=0x149104013c80) at /test/10.7_dbg/sql/sql_select.cc:5020
 
#13 0x0000559d9d3d19f8 in handle_select (thd=thd@entry=0x149104000db8, lex=lex@entry=0x149104004ee8, result=result@entry=0x149104015420, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.7_dbg/sql/sql_select.cc:554
 
#14 0x0000559d9d33d979 in execute_sqlcom_select (thd=thd@entry=0x149104000db8, all_tables=0x1491040143e8) at /test/10.7_dbg/sql/sql_parse.cc:6252
 
#15 0x0000559d9d349c70 in mysql_execute_command (thd=thd@entry=0x149104000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.7_dbg/sql/sql_parse.cc:3943
 
#16 0x0000559d9d337b77 in mysql_parse (thd=thd@entry=0x149104000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1491893f6330) at /test/10.7_dbg/sql/sql_parse.cc:8027
 
#17 0x0000559d9d34521f in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x149104000db8, packet=packet@entry=0x14910400b689 "", packet_length=packet_length@entry=32, blocking=blocking@entry=true) at /test/10.7_dbg/sql/sql_class.h:1360
 
#18 0x0000559d9d34792c in do_command (thd=0x149104000db8, blocking=blocking@entry=true) at /test/10.7_dbg/sql/sql_parse.cc:1407
 
#19 0x0000559d9d4a3df6 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x559da086cbb8, put_in_cache=put_in_cache@entry=true) at /test/10.7_dbg/sql/sql_connect.cc:1418
 
#20 0x0000559d9d4a42ff in handle_one_connection (arg=0x559da086cbb8) at /test/10.7_dbg/sql/sql_connect.cc:1312
 
#21 0x00001491a1e77609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#22 0x00001491a1a63133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.7.5 (dbg), 10.8.4 (dbg), 10.9.2 (dbg)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.3.36 (dbg), 10.3.36 (opt), 10.4.26 (dbg), 10.4.26 (opt), 10.5.17 (dbg), 10.5.17 (opt), 10.6.9 (dbg), 10.6.9 (opt), 10.7.5 (opt), 10.8.4 (opt), 10.9.2 (opt), 10.10.0 (dbg), 10.10.0 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)

Comment by Roel Van de Paar [ 2022-08-08 ] 

Server version: 10.4.26-MariaDB-debug MariaDB Server
 
 
 
10.4.26-dbg>CREATE TABLE t1 (a varchar(10) CHARACTER SET utf8mb4, key(a)) engine=innodb;
 
Query OK, 0 rows affected (0.019 sec)
 
 
 
10.4.26-dbg>INSERT INTO t1 VALUES (0xEFBFBF),(0xF0908080),(0xF48FBFBF);
 
Query OK, 3 rows affected (0.007 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
10.4.26-dbg>ALTER TABLE t1 MODIFY a varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
 
Query OK, 3 rows affected (0.121 sec)              
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
10.4.26-dbg>SELECT count(DISTINCT a) FROM t1;
 
+-------------------+
 
| count(DISTINCT a) |
 
+-------------------+
 
|                 3 |
 
+-------------------+
 
1 row in set (0.002 sec)
 
 
 
10.4.26-dbg>

Comment by Sergei Petrunia [ 2023-11-13 ]

Debugged the QUICK_GROUP_MIN_MAX code and I don't see anything wrong there.
It seems the idea about character ordering is different between the storage engine and the key_cmp() function ...

We compare these two characters:
a 4-byte character:

(gdb) x/4xb a
 
  0x7fffb801d5ca:       0xf0    0x90    0x80    0x80
 
(gdb) print a_weight
 
  $40 = 65536

and a 3-byte character:

(gdb) x/3xb b
 
  0x7fffb8069cc3:       0xef    0xbf    0xbf   
(gdb) print b_weight
 
  $41 = 65535

sorting according to the storage engine:

MariaDB [test]> select a, hex(a) from t1 order by a ;
 
+------+----------+
 
| a    | hex(a)   |
 
+------+----------+
 
| ?    | F0908080 |
 
| ?    | F48FBFBF |
 
| ￿    | EFBFBF   |
 
+------+----------+
 
3 rows in set (0.001 sec)

Sorting according to sql layer:

MariaDB [test]> select a, hex(a) from t1 use index() order by a ;
 
+------+----------+
 
| a    | hex(a)   |
 
+------+----------+
 
| ￿    | EFBFBF   |
 
| ?    | F0908080 |
 
| ?    | F48FBFBF |
 
+------+----------+
 
3 rows in set (0.001 sec)

Comment by Sergei Petrunia [ 2023-11-13 ]

That is, we have the above on cset c3ddffe29c1d8ed4465820636748eb3aab145c90 (The one Roel tested with)

but we don't have it on the latest 10.7: bc656c4fa54c12ceabd857e8ae134f8979d82944 (Yes I know it was discontinued)

select a, hex(a) from t1 force index(a) order by a ;
 
a       hex(a)
 
?       EFBFBF
 
?       F0908080
 
?       F48FBFBF
 
select a, hex(a) from t1 use index() order by a ;
 
a       hex(a)
 
?       EFBFBF
 
?       F0908080
 
?       F48FBFBF

Comment by Sergei Petrunia [ 2023-11-13 ]

lstartseva, could you please check which commit fixed this?

cat main/a1.test 
--source include/have_innodb.inc
 
 
CREATE TABLE t1 (a varchar(10) CHARACTER SET utf8mb4, key(a)) engine=innodb;
 
INSERT INTO t1 VALUES (0xEFBFBF),(0xF0908080),(0xF48FBFBF);
 
ALTER TABLE t1 MODIFY a varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
 
select a, hex(a) from t1 force index(a) order by a ;
 
select a, hex(a) from t1 use index() order by a ;
 
drop table t1;

Comment by Lena Startseva [ 2023-11-20 ]

psergei, this problem was fixed with commit 9a0cbd31ce8576468981b14b066dea155cb922d9 (MDEV-26294)

Generated at Thu Feb 08 09:54:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.