Roel Van de Paar added a comment - 2022-08-08 23:31 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)

Roel Van de Paar added a comment - 2022-08-08 23:33 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>

Sergei Petrunia added a comment - 2023-11-13 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)

Sergei Petrunia added a comment - 2023-11-13 11:23 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

Sergei Petrunia added a comment - 2023-11-13 11:24 - edited 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;

Lena Startseva added a comment - 2023-11-20 18:16 psergei , this problem was fixed with commit 9a0cbd31ce8576468981b14b066dea155cb922d9 ( MDEV-26294 )

Sergei Petrunia added a comment - 2025-04-07 06:51 Closing as Duplicate of MDEV-26294