mariadb/mysql-test/main/lowercase_table5.test

#
# Tests that requires lower_case_table_names to be 0
# (Normal case on Unix)
#
--source include/have_lowercase0.inc
--source include/have_case_sensitive_file_system.inc

#
# Check caching of database options
#

CREATE DATABASE mysql_TEST CHARACTER SET utf8;
SHOW CREATE DATABASE mysql_TEST;
CREATE DATABASE mysql_test CHARACTER SET latin2;
SHOW CREATE DATABASE mysql_test;
SHOW CREATE DATABASE mysql_TEST;
DROP DATABASE mysql_test;
DROP DATABASE mysql_TEST;

# End of 10.0 tests

--echo #
--echo # Start of 10.4 tests
--echo #

--echo #
--echo # MDEV-33019 The database part is not case sensitive in SP names
--echo #

CREATE DATABASE DB1;
CREATE DATABASE db1;
CREATE PROCEDURE DB1.sp() SELECT 'This is DB1.sp' AS ret;
CREATE PROCEDURE db1.sp() SELECT 'This is db1.sp' AS ret;
CALL DB1.sp();
CALL db1.sp();
DROP DATABASE DB1;
--error ER_SP_DOES_NOT_EXIST
CALL DB1.sp();
CALL db1.sp();
DROP DATABASE db1;

CREATE PROCEDURE SP() SELECT 'This is SP' AS ret;
--error ER_SP_ALREADY_EXISTS
CREATE PROCEDURE sp() SELECT 'This is sp' AS ret;
CALL SP();
CALL sp();
DROP PROCEDURE SP;

--echo #
--echo # End of 10.4 tests
--echo #

--echo #
--echo # Start of 10.5 tests
--echo #

--echo #
--echo # MDEV-33084 LASTVAL(t1) and LASTVAL(T1) do not work well with lower-case-table-names=0
--echo #

CREATE SEQUENCE t1;
CREATE SEQUENCE T1;
--disable_ps2_protocol
SELECT nextval(t1), lastval(t1);
SELECT nextval(T1), lastval(T1);
SELECT lastval(t1), lastval(T1) l2;
--enable_ps2_protocol
DROP SEQUENCE t1, T1;

--echo #
--echo # MDEV-33086 SHOW OPEN TABLES IN DB1 -- is case insensitive with lower-case-table-names=0
--echo #

CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT);
SELECT * FROM db1.t1;
SHOW OPEN TABLES IN DB1;
SHOW OPEN TABLES IN db1;
DROP DATABASE db1;

--echo #
--echo # MDEV-33088 Cannot create triggers in the database `MYSQL`
--echo #

CREATE DATABASE MYSQL;
CREATE TABLE MYSQL.t1 (a INT);
CREATE TABLE MYSQL.t2 (a INT);
CREATE TRIGGER MYSQL.tr1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (new.a);
INSERT INTO MYSQL.t1 VALUES (10);
SELECT * FROM MYSQL.t1;
SELECT * FROM MYSQL.t2;
DROP DATABASE MYSQL;


--echo #
--echo # MDEV-33103 LOCK TABLE t1 AS t2 -- alias is not case sensitive with lower-case-table-names=0
--echo #

--disable_view_protocol
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
LOCK TABLE t1 AS t2 READ;
SELECT * FROM t1 AS t2;
UNLOCK TABLES;
LOCK TABLE t1 AS t2 READ;
--error ER_TABLE_NOT_LOCKED
SELECT * FROM t1 AS T2;
UNLOCK TABLES;
DROP TABLE t1;
--enable_view_protocol


--echo #
--echo # MDEV-33108 TABLE_STATISTICS and INDEX_STATISTICS are case insensitive with lower-case-table-names=0
--echo #

SET GLOBAL userstat=1;
CREATE TABLE t1 (a INT, KEY(a));
INSERT INTO t1 VALUES (1),(2),(3),(4);
--disable_ps2_protocol
SELECT * FROM t1 ORDER BY a;
CREATE TABLE T1 (a INT, KEY(a));
INSERT INTO T1 VALUES (1),(2),(3),(4);
SELECT * FROM T1 ORDER BY a;
--enable_ps2_protocol
SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS ORDER BY BINARY TABLE_NAME;
SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS ORDER BY BINARY TABLE_NAME;
DROP TABLE t1;
DROP TABLE T1;
SET GLOBAL userstat=DEFAULT;


--echo #
--echo # MDEV-33109 DROP DATABASE MYSQL -- does not drop SP with lower-case-table-names=0
--echo #

CREATE DATABASE MYSQL;
CREATE FUNCTION MYSQL.f1() RETURNS INT RETURN 1;
DROP DATABASE MYSQL;
SELECT db, name, body FROM mysql.proc WHERE db=BINARY 'MYSQL' AND name='f1';


--echo #
--echo # MDEV-33110 HANDLER commands are case insensitive with lower-case-table-names=0
--echo #

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
HANDLER t1 OPEN;
HANDLER t1 READ FIRST;
CREATE OR REPLACE TABLE T1 (a INT);
DROP TABLE T1;
HANDLER t1 READ NEXT;
HANDLER t1 CLOSE;
DROP TABLE t1;

--echo #
--echo # MDEV-33120 System log table names are case insensitive with lower-cast-table-names=0
--echo #

CREATE TABLE mysql.GENERAL_log (a INT);
INSERT INTO mysql.GENERAL_log VALUES (1),(2);
DROP TABLE mysql.GENERAL_log;

CREATE TABLE mysql.SLOW_log (a INT);
INSERT INTO mysql.SLOW_log VALUES (1),(2);
DROP TABLE mysql.SLOW_log;

CREATE TABLE mysql.TRANSACTION_registry (a INT);
INSERT INTO mysql.TRANSACTION_registry VALUES (1),(2);
DROP TABLE mysql.TRANSACTION_registry;

CREATE DATABASE MYSQL;
CREATE TABLE MYSQL.general_log (a INT);
INSERT INTO MYSQL.general_log VALUES (1),(2);
DROP TABLE MYSQL.general_log;

CREATE TABLE MYSQL.slow_log (a INT);
INSERT INTO MYSQL.slow_log VALUES (1),(2);
DROP TABLE MYSQL.slow_log;

CREATE TABLE MYSQL.transaction_registry (a INT);
INSERT INTO MYSQL.transaction_registry VALUES (1),(2);
DROP TABLE MYSQL.transaction_registry;
DROP DATABASE MYSQL;

--echo #
--echo # End of 10.5 tests
--echo #