[MDEV-32503] Queries from I_S.KEY_PERIOD_USAGE do not obey case-sensitivity Created: 2023-10-17  Updated: 2024-01-15

Status: In Testing
Project: MariaDB Server
Component/s: Character Sets, Information Schema
Affects Version/s: N/A
Fix Version/s: 11.4

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-32504 Search by I_S.KEY_PERIOD_USAGE.CONSTR... Closed
Problem/Incident
is caused by MDEV-22597 Add views for periods in information_... In Testing

 Description   

INFORMATION_SCHEMA.PERIODS (and at least some older I_S views, e.g. TABLES) work as expected with case-sensitive collations, but KEY_PERIOD_USAGE does not.

--source include/have_innodb.inc
 
 
 
create table t (a int, b date, c date, period for app(b,c), primary key(a, app without overlaps));
 
 
 
set names latin1 collate latin1_bin;
 
 
 
select table_schema, table_name, period from information_schema.periods where table_schema = 'TEST';
 
select table_schema, table_name, period_name from information_schema.key_period_usage where table_schema = 'TEST';
 
 
 
set names latin1 collate latin1_general_cs;
 
 
 
select table_schema, table_name, period from information_schema.periods where table_schema = 'TEST';
 
select table_schema, table_name, period_name from information_schema.key_period_usage where table_schema = 'TEST';
 
 
 
# Cleanup
 
drop table t;

bb-11.3-periods-schema 24018c74fae6e5a93b93c2efefd60bde4ad1488e

 
set names latin1 collate latin1_bin;
 
select table_schema, table_name, period from information_schema.periods where table_schema = 'TEST';
 
table_schema	table_name	period
 
select table_schema, table_name, period_name from information_schema.key_period_usage where table_schema = 'TEST';
 
table_schema	table_name	period_name
 
test	t	app
 
set names latin1 collate latin1_general_cs;
 
select table_schema, table_name, period from information_schema.periods where table_schema = 'TEST';
 
table_schema	table_name	period
 
select table_schema, table_name, period_name from information_schema.key_period_usage where table_schema = 'TEST';
 
table_schema	table_name	period_name
 
test	t	app



 Comments   
Comment by Elena Stepanova [ 2023-10-17 ]

Looking further, there is much more inconsistency, even among old I_S views.
Still, it is odd that two new views behave differently between themselves in this respect.

Comment by Nikita Malyavin [ 2023-12-30 ]

I suppose that "it" tries to enumerate tables under the "TEST" directory, in case of PERIODS or KEY_COLUMN_USAGE.

In case of KEY_PERIOD_USAGE, it's "test".

(gdb) bt
 
#0  find_files (thd=0x62c0001f0218, files=0x7f7d26072990, db=0x7f7d25f5a3f0, path=0x7f7d25f5a020 "./TEST/", wild=0x6290002b8ac8) at /home/nik/mariadb/sql/sql_show.cc:966
 
#1  0x0000558d6efbf054 in make_table_name_list (thd=0x62c0001f0218, table_names=0x7f7d26072990, lex=0x62c0001f45b8, lookup_field_vals=0x6290002b8ab8, db_name=0x6290002b8ab8) at /home/nik/mariadb/sql/sql_show.cc:4613
 
#2  0x0000558d6efbba0e in get_all_tables (thd=0x62c0001f0218, tables=0x629000109cb8, cond=0x62900010a5d0) at /home/nik/mariadb/sql/sql_show.cc:5437
 
#3  0x0000558d6efe9ece in get_schema_tables_result (join=0x62900010b500, executed_place=PROCESSED_BY_JOIN_EXEC) at /home/nik/mariadb/sql/sql_show.cc:9374
 
#4  0x0000558d6eeb3c83 in JOIN::exec_inner (this=0x62900010b500) at /home/nik/mariadb/sql/sql_select.cc:4952
 
#5  0x0000558d6eeb0db8 in JOIN::exec (this=0x62900010b500) at /home/nik/mariadb/sql/sql_select.cc:4774
 
#6  0x0000558d6ee2b5a9 in mysql_select (thd=0x62c0001f0218, tables=0x629000109cb8, fields=..., conds=0x62900010a5d0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2701396736, result=0x62900010b4d0, unit=0x62c0001f4698, select_lex=0x629000109370) at /home/nik/mariadb/sql/sql_select.cc:5304
 
#7  0x0000558d6ee2a1bb in handle_select (thd=0x62c0001f0218, lex=0x62c0001f45b8, result=0x62900010b4d0, setup_tables_done_option=0) at /home/nik/mariadb/sql/sql_select.cc:630
 
#8  0x0000558d6ed01a47 in execute_sqlcom_select (thd=0x62c0001f0218, all_tables=0x629000109cb8) at /home/nik/mariadb/sql/sql_parse.cc:6077
 
#9  0x0000558d6ece9061 in mysql_execute_command (thd=0x62c0001f0218, is_called_from_prepared_stmt=false) at /home/nik/mariadb/sql/sql_parse.cc:3926
 
#10 0x0000558d6ecd042b in mysql_parse (thd=0x62c0001f0218, rawbuf=0x629000109238 "select table_schema, table_name, period from information_schema.periods where table_schema = 'TEST'", length=99, parser_state=0x7f7d261be7f0) at /home/nik/mariadb/sql/sql_parse.cc:7798
 
#11 0x0000558d6ecc9466 in dispatch_command (command=COM_QUERY, thd=0x62c0001f0218, packet=0x629000285219 "select table_schema, table_name, period from information_schema.periods where table_schema = 'TEST'", packet_length=99, blocking=true) at /home/nik/mariadb/sql/sql_parse.cc:1893
 
#12 0x0000558d6ecd3186 in do_command (thd=0x62c0001f0218, blocking=true) at /home/nik/mariadb/sql/sql_parse.cc:1406
 
#13 0x0000558d6f33e316 in do_handle_one_connection (connect=0x6080000033b8, put_in_cache=true) at /home/nik/mariadb/sql/sql_connect.cc:1417
 
#14 0x0000558d6f33daaf in handle_one_connection (arg=0x608000003338) at /home/nik/mariadb/sql/sql_connect.cc:1319
 
#15 0x0000558d6fc25f89 in pfs_spawn_thread (arg=0x617000007098) at /home/nik/mariadb/storage/perfschema/pfs.cc:2201
 
#16 0x00007f7d3e0889eb in ?? () from target:/usr/lib/libc.so.6
 
#17 0x00007f7d3e10c654 in clone () from target:/usr/lib/libc.so.6
 
(gdb) f 2
 
#2  0x0000558d6efbba0e in get_all_tables (thd=0x62c0001f0218, tables=0x629000109cb8, cond=0x62900010a5d0) at /home/nik/mariadb/sql/sql_show.cc:5437
 
5437	      int res= make_table_name_list(thd, &table_names, lex,
 
(gdb) p schema_table->table_name
 
$3 = 0x558d6d4ea120 <str> "PERIODS"
 
(gdb) p db_name.str
 
$4 = 0x6290002b8af0 "TEST"
 
(gdb) f 0
 
#0  find_files (thd=0x62c0001f0218, files=0x7f7d26072990, db=0x7f7d25f5a3f0, path=0x7f7d25f5a020 "./TEST/", wild=0x6290002b8ac8) at /home/nik/mariadb/sql/sql_show.cc:966
 
966	    DBUG_RETURN(FIND_FILES_DIR);
 
(gdb) p dirp
 
$5 = (MY_DIR *) 0x0
 
(gdb) p path
 
$6 = 0x7f7d25f5a020 "./TEST/"

In case of KEY_PERIOD_USAGE, there is no lookup_field_vals->db_value, so it's not compared, but instead fetched by the root '.' directory enumeration.

Comment by Nikita Malyavin [ 2023-12-30 ]

The reason for the different results was that for KEY_PERIOD_USAGE i didn't setup the correct fields for ST_SCHEMA_TABLE::idx_field1 the field purpose is completely vanished behind its super creative name. And I just copied values 1, 2 from other places, not giving a big reason to it.

So normally, none should work case-sensitively, but that's a different problem.

Comment by Nikita Malyavin [ 2023-12-30 ]

elenst it seems that the behavior is controlled by lower_case_table_names rather than collation. I don't know whether this one is a bug or not

Comment by Nikita Malyavin [ 2023-12-30 ]

please review commit e117ab01

Comment by Sergei Golubchik [ 2024-01-04 ]

e117ab01 is ok to push

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