Bug#55222 - RB://517 - Approved by Sunny

InnoDB does not attempt to handle lower_case_table_names == 2 when looking
up foreign table names and referenced table name.  It turned that server
variable into a boolean and ignored the possibility of it being '2'.  

The setting lower_case_table_names == 2 means that it should be stored and
displayed in mixed case as given, but compared internally in lower case.
Normally the server deals with this since it stores table names.  But
InnoDB stores referential constraints for the server, so it needs to keep
track of both lower case and given names.

This solution creates two table name pointers for each foreign and referenced
table name.  One to display the name, and one to look it up.  Both pointers
point to the same allocated string unless this setting is 2.  So the overhead
added is not too much.

Two functions are created in dict0mem.c to populate the ..._lookup versions
of these pointers.  Both dict_mem_foreign_table_name_lookup_set() and
dict_mem_referenced_table_name_lookup_set() are called 5 times each.

1 files changed, 108 insertions, 0 deletions

diff --git a/mysql-test/r/lowercase_table4.result b/mysql-test/r/lowercase_table4.result
index e3f861f8884..f896b9008e3 100755
--- a/mysql-test/r/lowercase_table4.result
+++ b/mysql-test/r/lowercase_table4.result
@@ -5,3 +5,111 @@
 CREATE DATABASE XY;
 USE XY;
 DROP DATABASE XY;
+USE TEST;
+#
+# Bug55222 Mysqldump table names case bug in REFERENCES clause
+# InnoDB did not handle lower_case_table_names=2 for
+# foreign_table_names and referenced_table_names.
+#
+SHOW VARIABLES LIKE 'lower_case_table_names';
+Variable_name	Value
+lower_case_table_names	2
+DROP TABLE IF EXISTS `Table2`;
+DROP TABLE IF EXISTS `Table1`;
+CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB;
+ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1);
+SHOW CREATE TABLE `Table2`;
+Table	Table2
+Create Table	CREATE TABLE `Table2` (
+  `c1` int(11) NOT NULL,
+  `c2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`c1`),
+  KEY `fk1` (`c2`),
+  CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `Table1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
+CONSTRAINT_CATALOG	def
+CONSTRAINT_SCHEMA	test
+CONSTRAINT_NAME	fk1
+UNIQUE_CONSTRAINT_CATALOG	def
+UNIQUE_CONSTRAINT_SCHEMA	test
+UNIQUE_CONSTRAINT_NAME	PRIMARY
+MATCH_OPTION	NONE
+UPDATE_RULE	RESTRICT
+DELETE_RULE	RESTRICT
+TABLE_NAME	Table2
+REFERENCED_TABLE_NAME	Table1
+DROP TABLE `Table2`;
+DROP TABLE `Table1`;
+DROP TABLE IF EXISTS Product_Order;
+DROP TABLE IF EXISTS Product;
+DROP TABLE IF EXISTS Customer;
+CREATE TABLE Product (Category INT NOT NULL, Id INT NOT NULL,
+Price DECIMAL, PRIMARY KEY(Category, Id)) ENGINE=InnoDB;
+CREATE TABLE Customer (Id INT NOT NULL, PRIMARY KEY (Id)) ENGINE=InnoDB;
+CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT,
+Product_Category INT NOT NULL,
+Product_Id INT NOT NULL,
+Customer_Id INT NOT NULL,
+PRIMARY KEY(No),
+INDEX (Product_Category, Product_Id),
+FOREIGN KEY (Product_Category, Product_Id)
+REFERENCES Product(Category, Id) ON UPDATE CASCADE ON DELETE RESTRICT,
+INDEX (Customer_Id),
+FOREIGN KEY (Customer_Id)
+REFERENCES Customer(Id)
+) ENGINE=INNODB;
+SHOW CREATE TABLE Product_Order;
+Table	Product_Order
+Create Table	CREATE TABLE `Product_Order` (
+  `No` int(11) NOT NULL AUTO_INCREMENT,
+  `Product_Category` int(11) NOT NULL,
+  `Product_Id` int(11) NOT NULL,
+  `Customer_Id` int(11) NOT NULL,
+  PRIMARY KEY (`No`),
+  KEY `Product_Category` (`Product_Category`,`Product_Id`),
+  KEY `Customer_Id` (`Customer_Id`),
+  CONSTRAINT `product_order_ibfk_1` FOREIGN KEY (`Product_Category`, `Product_Id`) REFERENCES `Product` (`Category`, `Id`) ON UPDATE CASCADE,
+  CONSTRAINT `product_order_ibfk_2` FOREIGN KEY (`Customer_Id`) REFERENCES `Customer` (`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE Product;
+Table	Product
+Create Table	CREATE TABLE `Product` (
+  `Category` int(11) NOT NULL,
+  `Id` int(11) NOT NULL,
+  `Price` decimal(10,0) DEFAULT NULL,
+  PRIMARY KEY (`Category`,`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE Customer;
+Table	Customer
+Create Table	CREATE TABLE `Customer` (
+  `Id` int(11) NOT NULL,
+  PRIMARY KEY (`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
+CONSTRAINT_CATALOG	def
+CONSTRAINT_SCHEMA	test
+CONSTRAINT_NAME	product_order_ibfk_1
+UNIQUE_CONSTRAINT_CATALOG	def
+UNIQUE_CONSTRAINT_SCHEMA	test
+UNIQUE_CONSTRAINT_NAME	PRIMARY
+MATCH_OPTION	NONE
+UPDATE_RULE	CASCADE
+DELETE_RULE	RESTRICT
+TABLE_NAME	Product_Order
+REFERENCED_TABLE_NAME	Product
+CONSTRAINT_CATALOG	def
+CONSTRAINT_SCHEMA	test
+CONSTRAINT_NAME	product_order_ibfk_2
+UNIQUE_CONSTRAINT_CATALOG	def
+UNIQUE_CONSTRAINT_SCHEMA	test
+UNIQUE_CONSTRAINT_NAME	PRIMARY
+MATCH_OPTION	NONE
+UPDATE_RULE	RESTRICT
+DELETE_RULE	RESTRICT
+TABLE_NAME	Product_Order
+REFERENCED_TABLE_NAME	Customer
+DROP TABLE Product_Order;
+DROP TABLE Product;
+DROP TABLE Customer;