[MDEV-28663] Wrong data from Virtual Column by ordering on a SET Created: 2022-05-25  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Virtual Columns
Affects Version/s: 10.6.7, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.6, 10.11

Type: Bug Priority: Major
Reporter: Sebastian Stamm Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 0
Labels: None

Attachments: PNG File image-2022-05-25-10-11-49-686.png     PNG File image-2022-05-25-10-14-16-380.png     PNG File image-2022-05-25-10-16-12-143.png    
Issue Links:
Relates
relates to MDEV-25056 Server crash in Protocol::net_store_d... Open
relates to MDEV-25120 MariaDB 10.5.6/10.5.8 crash (sig 11) ... Open

 Description    

SELECT * FROM device ORDER BY moduleTypes ASC LIMIT 1000;


Without ordering:

It looks right.

CREATE TABLE `device` (
 
	`deviceId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 
	`atmType` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
 
	`country` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
 
	`customer` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
 
	`flightRecordDir` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
 
	`ignoreSnr` BIT(1) AS (`system` like '%!IGSNR%' or `country` like 'EGT' or `country` like 'PRD%') stored,
 
	`isInternal` BIT(1) AS (octet_length(`country`) = 3 or `country` like 'PRD%' or `system` like '%!LAB%' or `system` like '%!CAT%' or `system` like '%!INT%' or `customer` like '%LAB') stored,
 
	`moduleTypes` SET('UNDEFINED','POS','CRS','ATS','ATM','CCDM2','RDS','RM4H','RM4V','CMDV6C','CK1','CMDV6A','FIB','AFD','CM4','ICASH40','ICASH45','ICASH60','ICASH65','OTHER_RETAIL','PROBASE_TRACE','OTHER') NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
 
	`path` VARCHAR(255) AS (concat(`country`,'\\',`customer`,'\\',`atmType`,'\\',`system`,'\\',`flightRecordDir`)) virtual,
 
	`system` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_general_ci',
 
	`systemHash` VARCHAR(32) AS (md5(concat(`country`,`customer`,`atmType`,`system`))) stored,
 
	`traceType` ENUM('UNDEFINED','POS','CRS','ATS','ATM','CCDM2','RDS','RM4H','RM4V','CMDV6C','CK1','CMDV6A','FIB','AFD','CM4','ICASH40','ICASH45','ICASH60','ICASH65','OTHER_RETAIL','PROBASE_TRACE','OTHER') NOT NULL COLLATE 'utf8mb3_general_ci',
 
	PRIMARY KEY (`deviceId`) USING BTREE,
 
	INDEX `country_index` (`country`) USING BTREE,
 
	INDEX `customer_index` (`customer`) USING BTREE,
 
	INDEX `atmType_index` (`atmType`) USING BTREE,
 
	INDEX `traceType_index` (`traceType`) USING BTREE,
 
	INDEX `hash_index` (`systemHash`) USING BTREE
 
)
 
COLLATE='utf8mb3_general_ci'
 
ENGINE=InnoDB
 
AUTO_INCREMENT=13777
 
;



 Comments   
Comment by Alice Sherepa [ 2022-05-30 ]

Thank you!
I repeated this behaviour on 10.6-10.9, 10.5 seemed ok, but it might be a matter of luck.

--source include/have_sequence.inc
 
--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (
 
  t1Id INT,
 
  atmType VARCHAR(255),
 
  country VARCHAR(255),
 
  customer VARCHAR(255),
 
  flightRecordDir VARCHAR(255),
 
  moduleTypes SET('UNDEFINED','POS','CRS','ATS','ATM','CCDM2','RDS','RM4H','RM4V','CMDV6C','CK1','CMDV6A','FIB','AFD','CM4','ICASH40','ICASH45','ICASH60','ICASH65','OTHER_RETAIL','PROBASE_TRACE','OTHER') NULL DEFAULT NULL,
 
  path VARCHAR(255) AS (concat(country,'\\',customer,'\\',atmType,'\\',system,'\\',flightRecordDir)) virtual,
 
  system VARCHAR(255)
 
)
 
COLLATE='utf8mb3_general_ci'
 
ENGINE=InnoDB;
 
 
 
insert into t1 (t1Id, atmType, country, customer,flightRecordDir,moduleTypes, system) 
  select seq, seq, seq, seq,seq,
 
  ELT(0.5 + RAND() * 22, 'UNDEFINED','POS','CRS','ATS','ATM','CCDM2','RDS','RM4H','RM4V','CMDV6C','CK1','CMDV6A','FIB','AFD','CM4','ICASH40','ICASH45','ICASH60','ICASH65','OTHER_RETAIL','PROBASE_TRACE','OTHER'),
 
  seq from seq_1_to_10000;
 
 
 
  SELECT * FROM t1 ORDER BY moduleTypes ASC LIMIT 1000;

10.6 debug returns incorrect results, but ASAN reports unknown-crash.

10.6 05d049bdbe6814aee8f011fb

 
Version: '10.6.9-MariaDB-debug-log'
 
=================================================================
 
==2768==ERROR: AddressSanitizer: unknown-crash on address 0x62500018c40e at pc 0x563dd9c9bb13 bp 0x7f5636b5a260 sp 0x7f5636b5a258
 
READ of size 1 at 0x62500018c40e thread T11
 
    #0 0x563dd9c9bb12 in my_mb_wc_utf8mb3_quick /git/10.6/strings/ctype-utf8.h:102
 
    #1 0x563dd9c9e18f in my_utf8mb3_uni /git/10.6/strings/ctype-utf8.c:4825
 
    #2 0x563dd9cb54f6 in my_convert_using_func /git/10.6/strings/ctype.c:1161
 
    #3 0x563dd9cb5b4b in my_convert /git/10.6/strings/ctype.c:1269
 
    #4 0x563dd7bca47e in copy_and_convert(char*, unsigned long, charset_info_st const*, char const*, unsigned long, charset_info_st const*, unsigned int*) /git/10.6/sql/sql_string.h:53
 
    #5 0x563dd80ec489 in String::copy(char const*, unsigned long, charset_info_st const*, charset_info_st const*, unsigned int*) /git/10.6/sql/sql_string.cc:465
 
    #6 0x563dd7bbc04b in Protocol::net_store_data_cs(unsigned char const*, unsigned long, charset_info_st const*, charset_info_st const*) /git/10.6/sql/protocol.cc:104
 
    #7 0x563dd7bc499d in Protocol::store_string_aux(char const*, unsigned long, charset_info_st const*, charset_info_st const*) /git/10.6/sql/protocol.cc:1429
 
    #8 0x563dd7bc52f0 in Protocol_text::store_str(char const*, unsigned long, charset_info_st const*, charset_info_st const*) /git/10.6/sql/protocol.cc:1468
 
    #9 0x563dd7bcd256 in Protocol::store(char const*, unsigned long, charset_info_st const*) /git/10.6/sql/protocol.h:150
 
    #10 0x563dd8683878 in Field_varstring::send(Protocol*) /git/10.6/sql/field.cc:7908
 
    #11 0x563dd7bc695b in Protocol_text::store(Field*) /git/10.6/sql/protocol.cc:1584
 
    #12 0x563dd8767779 in Item_field::send(Protocol*, st_value*) /git/10.6/sql/item.cc:7384
 
    #13 0x563dd7bc4111 in Protocol::send_result_set_row(List<Item>*) /git/10.6/sql/protocol.cc:1328
 
    #14 0x563dd7d69fc3 in select_send::send_data(List<Item>&) /git/10.6/sql/sql_class.cc:3116
 
    #15 0x563dd802cf00 in select_result_sink::send_data_with_check(List<Item>&, st_select_lex_unit*, unsigned long long) /git/10.6/sql/sql_class.h:5680
 
    #16 0x563dd7fe798e in end_send /git/10.6/sql/sql_select.cc:22405
 
    #17 0x563dd7fdfac5 in evaluate_join_record /git/10.6/sql/sql_select.cc:21399
 
    #18 0x563dd7fde3c3 in sub_select(JOIN*, st_join_table*, bool) /git/10.6/sql/sql_select.cc:21169
 
    #19 0x563dd7fdc471 in do_select /git/10.6/sql/sql_select.cc:20714
 
    #20 0x563dd7f69b33 in JOIN::exec_inner() /git/10.6/sql/sql_select.cc:4763
 
    #21 0x563dd7f67075 in JOIN::exec() /git/10.6/sql/sql_select.cc:4541
 
    #22 0x563dd7f6b44e in 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*) /git/10.6/sql/sql_select.cc:5020
 
    #23 0x563dd7f3be2c in handle_select(THD*, LEX*, select_result*, unsigned long) /git/10.6/sql/sql_select.cc:553
 
    #24 0x563dd7ea4468 in execute_sqlcom_select /git/10.6/sql/sql_parse.cc:6255
 
    #25 0x563dd7e92fd8 in mysql_execute_command(THD*, bool) /git/10.6/sql/sql_parse.cc:3945
 
    #26 0x563dd7eaf23d in mysql_parse(THD*, char*, unsigned int, Parser_state*) /git/10.6/sql/sql_parse.cc:8029
 
    #27 0x563dd7e85b10 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /git/10.6/sql/sql_parse.cc:1896
 
    #28 0x563dd7e82846 in do_command(THD*, bool) /git/10.6/sql/sql_parse.cc:1409
 
    #29 0x563dd82d7a26 in do_handle_one_connection(CONNECT*, bool) /git/10.6/sql/sql_connect.cc:1418
 
    #30 0x563dd82d72ab in handle_one_connection /git/10.6/sql/sql_connect.cc:1312
 
    #31 0x563dd8fbb680 in pfs_spawn_thread /git/10.6/storage/perfschema/pfs.cc:2201
 
    #32 0x7f564519ffa2 in start_thread /build/glibc-fWwxX8/glibc-2.28/nptl/pthread_create.c:486
 
    #33 0x7f5644da8efe in clone (/lib/x86_64-linux-gnu/libc.so.6+0xf8efe)
 
 
 
0x62500018c40e is located 4878 bytes inside of 9596-byte region [0x62500018b100,0x62500018d67c)
 
allocated by thread T11 here:
 
    #0 0x7f5645b39330 in __interceptor_malloc (/lib/x86_64-linux-gnu/libasan.so.5+0xe9330)
 
    #1 0x563dd9bc741d in sf_malloc /git/10.6/mysys/safemalloc.c:126
 
    #2 0x563dd9b95e1d in my_malloc /git/10.6/mysys/my_malloc.c:90
 
    #3 0x563dd9b72a55 in alloc_root /git/10.6/mysys/my_alloc.c:244
 
    #4 0x563dd81f96b8 in open_table_from_share(THD*, TABLE_SHARE*, st_mysql_const_lex_string const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, List<String>*) /git/10.6/sql/table.cc:4163
 
    #5 0x563dd7cf300b in open_table(THD*, TABLE_LIST*, Open_table_context*) /git/10.6/sql/sql_base.cc:2016
 
    #6 0x563dd7cfc966 in open_and_process_table /git/10.6/sql/sql_base.cc:3826
 
    #7 0x563dd7cff583 in open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) /git/10.6/sql/sql_base.cc:4309
 
    #8 0x563dd7d0466b in open_and_lock_tables(THD*, DDL_options_st const&, TABLE_LIST*, bool, unsigned int, Prelocking_strategy*) /git/10.6/sql/sql_base.cc:5282
 
    #9 0x563dd7c5f4ca in open_and_lock_tables(THD*, TABLE_LIST*, bool, unsigned int) /git/10.6/sql/sql_base.h:509
 
    #10 0x563dd7e98011 in mysql_execute_command(THD*, bool) /git/10.6/sql/sql_parse.cc:4648
 
    #11 0x563dd7eaf23d in mysql_parse(THD*, char*, unsigned int, Parser_state*) /git/10.6/sql/sql_parse.cc:8029
 
    #12 0x563dd7e85b10 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /git/10.6/sql/sql_parse.cc:1896
 
    #13 0x563dd7e82846 in do_command(THD*, bool) /git/10.6/sql/sql_parse.cc:1409
 
    #14 0x563dd82d7a26 in do_handle_one_connection(CONNECT*, bool) /git/10.6/sql/sql_connect.cc:1418
 
    #15 0x563dd82d72ab in handle_one_connection /git/10.6/sql/sql_connect.cc:1312
 
    #16 0x563dd8fbb680 in pfs_spawn_thread /git/10.6/storage/perfschema/pfs.cc:2201
 
    #17 0x7f564519ffa2 in start_thread /build/glibc-fWwxX8/glibc-2.28/nptl/pthread_create.c:486
 
 
 
Thread T11 created by T0 here:
 
    #0 0x7f5645aa0db0 in __interceptor_pthread_create (/lib/x86_64-linux-gnu/libasan.so.5+0x50db0)
 
    #1 0x563dd8fb71a0 in my_thread_create /git/10.6/storage/perfschema/my_thread.h:52
 
    #2 0x563dd8fbba6f in pfs_spawn_thread_v1 /git/10.6/storage/perfschema/pfs.cc:2252
 
    #3 0x563dd7b7f596 in inline_mysql_thread_create /git/10.6/include/mysql/psi/mysql_thread.h:1139
 
    #4 0x563dd7b96828 in create_thread_to_handle_connection(CONNECT*) /git/10.6/sql/mysqld.cc:5972
 
    #5 0x563dd7b96e93 in create_new_thread(CONNECT*) /git/10.6/sql/mysqld.cc:6031
 
    #6 0x563dd7b97205 in handle_accepted_socket(st_mysql_socket, st_mysql_socket) /git/10.6/sql/mysqld.cc:6093
 
    #7 0x563dd7b97c04 in handle_connections_sockets() /git/10.6/sql/mysqld.cc:6217
 
    #8 0x563dd7b9608f in mysqld_main(int, char**) /git/10.6/sql/mysqld.cc:5867
 
    #9 0x563dd7b7e7e4 in main /git/10.6/sql/main.cc:34
 
    #10 0x7f5644cd409a in __libc_start_main ../csu/libc-start.c:308
 
 
 
SUMMARY: AddressSanitizer: unknown-crash /git/10.6/strings/ctype-utf8.h:102 in my_mb_wc_utf8mb3_quick
 
Shadow bytes around the buggy address:
 
  0x0c4a80029830: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4a80029840: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4a80029850: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4a80029860: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4a80029870: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
=>0x0c4a80029880: 00[06]00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4a80029890: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4a800298a0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4a800298b0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4a800298c0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c4a800298d0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
Shadow byte legend (one shadow byte represents 8 application bytes):
 
  Addressable:           00
 
  Partially addressable: 01 02 03 04 05 06 07 
  Heap left redzone:       fa
 
  Freed heap region:       fd
 
  Stack left redzone:      f1
 
  Stack mid redzone:       f2
 
  Stack right redzone:     f3
 
  Stack after return:      f5
 
  Stack use after scope:   f8
 
  Global redzone:          f9
 
  Global init order:       f6
 
  Poisoned by user:        f7
 
  Container overflow:      fc
 
  Array cookie:            ac
 
  Intra object redzone:    bb
 
  ASan internal:           fe
 
  Left alloca redzone:     ca
 
  Right alloca redzone:    cb
 
==2768==ABORTING

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