Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
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
|
Attachments
Issue Links
- is caused by
-
MDEV-22597 Add views for periods in information_schema
-
- Closed
-
- is duplicated by
-
-
- Closed
-
Activity
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.
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.
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
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.