[MCOL-4144] Error when using upper case database names Created: 2020-07-02  Updated: 2021-04-26  Resolved: 2020-09-28

Status: Closed
Project: MariaDB ColumnStore
Component/s: N/A
Affects Version/s: 1.5.3
Fix Version/s: 5.4.1

Type: Bug Priority: Critical
Reporter: Anders Karlsson Assignee: Gagan Goel (Inactive)
Resolution: Fixed Votes: 2
Labels: None
Environment:

Linux CentOS 7.7

Issue Links:
Duplicate
duplicates MCOL-4290 Join query errors when using upper ca... Closed
is duplicated by MCOL-4287 Database name is upper cased, SIMPLE ... Closed

 Description   

When using a database names with uppercase letters, you get an error about tables not being joined when selecting from a table in said database:

$ sudo mariadb -u root
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 9
 
Server version: 10.5.4-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> create database bug1;
 
Query OK, 1 row affected (0.000 sec)
 
 
 
MariaDB [(none)]> use bug1;
 
Database changed
 
MariaDB [bug1]> create table t1(c1 int) engine=columnstore;
 
Query OK, 0 rows affected (0.208 sec)
 
 
 
MariaDB [bug1]> insert into t1 values(1);
 
Query OK, 1 row affected (0.075 sec)
 
 
 
MariaDB [bug1]> select * from t1;
 
+------+
 
| c1   |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.030 sec)
 
 
 
MariaDB [bug1]> create database BUG2;
 
Query OK, 1 row affected (0.000 sec)
 
 
 
MariaDB [bug1]> use BUG2;
 
Database changed
 
MariaDB [BUG2]> create table t2(c1 int) engine=columnstore;
 
Query OK, 0 rows affected (0.203 sec)
 
 
 
MariaDB [BUG2]> insert into t2 values(1);
 
Query OK, 1 row affected (0.079 sec)
 
 
 
MariaDB [BUG2]> select * from t2;
 
ERROR 1815 (HY000): Internal error: IDB-1000: 't2' and 't2' are not joined.



 Comments   
Comment by David Hall (Inactive) [ 2020-07-30 ]

Columnstore has always assumed lower_case_table_names = 0. This had been the default for MDS at some point in the past. This is not the case today.
A workaround is to set lower_case_table_names = 0 in columnstore.cnf. This setting is not available from SQL.

Comment by David Hall (Inactive) [ 2020-09-08 ]

lower_case_table_names=1 has been added to the columnstore.cnf for 1.5. This Jira remains open for fixing the code to handle code sensitive table names.

Comment by David Hall (Inactive) [ 2020-09-21 ]

Enabled lower_case_table_names such that works pretty much as Server does for values of 1 and 0. lower_case_table_names=2 will be treated as lower_case_table_names=1 in the Engine.

Removed lower_case_table_names=1 from the default columnstore.cnf.

QA:
This should be tested for lower_case_table_names=0 (Case sensitive) and lower_case_table_names=1 (case insensitive – all schema and table names are stored lower case).
If a table is created Case sensitive with upper case letters in the name, then lower_case_table_names is set to 1, that table will become unreachable with table not found errors. This is in line with the behavior of InnoDB.

There are a lot of variations that could occur. Alter Table, sub-queries, joins, Update, Delete. Each exercises a different part of the code, and the lower_case_table_names flag is used there.

Comment by David Hall (Inactive) [ 2020-09-21 ]

As regards our nightly regression tests. There are a number of tests that assume lower_case_table_names=1. Case is used indiscriminately. We must either fix the tests or always ensure that the flag is set before running the tests.

Comment by Gagan Goel (Inactive) [ 2020-09-25 ]

One correction to David.Hall's comment above: lower_case_table_names=2 will be treated as lower_case_table_names=0 on case-sensitive file systems. This will be true for linux platforms.

Comment by Gagan Goel (Inactive) [ 2020-09-25 ]

Following tests are executed to demonstrate how case-sensitivity is handled with this fix:

drop database if exists bug1;
 
drop database if exists BUG1;
 
drop database if exists BUG2;
 
drop database if exists bug2;
 
 
 
create database bug1;
 
use BUG1;
 
use bug1;
 
 
 
create table t1(c1 int) engine=columnstore;
 
insert into t1 values(1), (2);
 
insert into T1 values(3), (4);
 
update t1 set c1=5 where c1=1;
 
update T1 set c1=6 where c1=2;
 
select * from t1;
 
select * from T1;
 
delete from t1 where c1=5;
 
select * from t1;
 
select * from T1;
 
delete from T1;
 
 
 
create table T1(c1 int) engine=columnstore;
 
insert into T1 values(7), (8);
 
insert into t1 values(9), (10);
 
update T1 set c1=11 where c1=7;
 
update t1 set c1=12 where c1=8;
 
select * from T1;
 
select * from t1;
 
delete from T1 where c1=11;
 
select * from T1;
 
select * from t1;
 
delete from t1;
 
 
 
 
 
 
 
create database BUG2;
 
use BUG2;
 
use bug2;
 
 
 
create table t1(c1 int) engine=columnstore;
 
insert into t1 values(1), (2);
 
insert into T1 values(3), (4);
 
update t1 set c1=5 where c1=1;
 
update T1 set c1=6 where c1=2;
 
select * from t1;
 
select * from T1;
 
delete from t1 where c1=5;
 
select * from t1;
 
select * from T1;
 
delete from T1;
 
 
 
create table T1(c1 int) engine=columnstore;
 
insert into T1 values(7), (8);
 
insert into t1 values(9), (10);
 
update T1 set c1=11 where c1=7;
 
update t1 set c1=12 where c1=8;
 
select * from T1;
 
select * from t1;
 
delete from T1 where c1=11;
 
select * from T1;
 
select * from t1;
 
delete from t1;

Output with the default value (0) of lower_case_table_names. This is also the output for lower_case_table_names=2 (which behaves similar to =0 on linux):

ERROR 1049 (42000) at line 7: Unknown database 'BUG1'
 
ERROR 1146 (42S02) at line 12: Table 'bug1.T1' doesn't exist
 
ERROR 1146 (42S02) at line 14: Table 'bug1.T1' doesn't exist
 
ERROR 1146 (42S02) at line 16: Table 'bug1.T1' doesn't exist
 
ERROR 1146 (42S02) at line 19: Table 'bug1.T1' doesn't exist
 
ERROR 1146 (42S02) at line 20: Table 'bug1.T1' doesn't exist
 
ERROR 1049 (42000) at line 38: Unknown database 'bug2'
 
ERROR 1146 (42S02) at line 42: Table 'BUG2.T1' doesn't exist
 
ERROR 1146 (42S02) at line 44: Table 'BUG2.T1' doesn't exist
 
ERROR 1146 (42S02) at line 46: Table 'BUG2.T1' doesn't exist
 
ERROR 1146 (42S02) at line 49: Table 'BUG2.T1' doesn't exist
 
ERROR 1146 (42S02) at line 50: Table 'BUG2.T1' doesn't exist
 
c1
 
5
 
2
 
c1
 
2
 
c1
 
11
 
8
 
c1
 
9
 
2
 
10
 
c1
 
8
 
c1
 
9
 
2
 
10
 
c1
 
5
 
2
 
c1
 
2
 
c1
 
11
 
8
 
c1
 
9
 
2
 
10
 
c1
 
8
 
c1
 
9
 
2
 
10

Output with lower_case_table_names=1:

ERROR 1050 (42S01) at line 22: Table 't1' already exists
 
ERROR 1050 (42S01) at line 52: Table 't1' already exists
 
c1
 
5
 
6
 
3
 
4
 
c1
 
5
 
6
 
3
 
4
 
c1
 
6
 
3
 
4
 
c1
 
6
 
3
 
4
 
c1
 
11
 
12
 
9
 
10
 
c1
 
11
 
12
 
9
 
10
 
c1
 
12
 
9
 
10
 
c1
 
12
 
9
 
10
 
c1
 
5
 
6
 
3
 
4
 
c1
 
5
 
6
 
3
 
4
 
c1
 
6
 
3
 
4
 
c1
 
6
 
3
 
4
 
c1
 
11
 
12
 
9
 
10
 
c1
 
11
 
12
 
9
 
10
 
c1
 
12
 
9
 
10
 
c1
 
12
 
9
 
10

These results match outputs from InnoDB.

Also verified that when lower_case_table_names=0 and tables are created in upper case, and the server is restarted with lower_case_table_names=1, then the tables are not reachable. This is expected behaviour in InnoDB.

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