MDEV-9142 :Adding Constraint with no database reference

results in ERROR 1046 (3D000) at line 13: No database selected.

Use database from create table to foreign key database if
nothing else is given.

5 files changed, 113 insertions, 7 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result
index 6c3306f5c15..7ba300b245e 100644
--- a/mysql-test/suite/innodb/r/innodb-fk.result
+++ b/mysql-test/suite/innodb/r/innodb-fk.result
@@ -68,3 +68,47 @@ Warning	150	Alter  table `test`.`t2` with foreign key constraint failed. Referen
 Error	1005	Can't create table '#sql-temporary' (errno: 150)
 drop table t2;
 drop table t1;
+CREATE DATABASE kg_test1;
+CREATE DATABASE kg_test2;
+CREATE TABLE `kg_test1`.`group` (
+Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE `kg_test1`.`person` (
+`Id` INT(11) NOT NULL AUTO_INCREMENT,
+`Name` VARCHAR(50) NOT NULL,
+PRIMARY KEY (`Id`),
+CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+show create table `kg_test1`.`person`;
+Table	Create Table
+person	CREATE TABLE `person` (
+  `Id` int(11) NOT NULL AUTO_INCREMENT,
+  `Name` varchar(50) NOT NULL,
+  PRIMARY KEY (`Id`),
+  CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+CREATE TABLE `kg_test2`.`person2` (
+`Id` INT(11) NOT NULL AUTO_INCREMENT,
+`Name` VARCHAR(50) NOT NULL,
+PRIMARY KEY (`Id`),
+CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+ERROR HY000: Can't create table 'kg_test2.person2' (errno: 150)
+CREATE TABLE `kg_test2`.`person2` (
+`Id` INT(11) NOT NULL AUTO_INCREMENT,
+`Name` VARCHAR(50) NOT NULL,
+PRIMARY KEY (`Id`),
+CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES  `kg_test1`.`group` (`Id`)
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+show create table `kg_test2`.`person2`;
+Table	Create Table
+person2	CREATE TABLE `person2` (
+  `Id` int(11) NOT NULL AUTO_INCREMENT,
+  `Name` varchar(50) NOT NULL,
+  PRIMARY KEY (`Id`),
+  CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+SHOW WARNINGS;
+Level	Code	Message
+DROP DATABASE kg_test2;
+DROP DATABASE kg_test1;
diff --git a/mysql-test/suite/innodb/t/innodb-fk.test b/mysql-test/suite/innodb/t/innodb-fk.test
index 9bfd16b88e9..fd8dce7594c 100644
--- a/mysql-test/suite/innodb/t/innodb-fk.test
+++ b/mysql-test/suite/innodb/t/innodb-fk.test
@@ -124,3 +124,44 @@ show warnings;
 
 drop table t2;
 drop table t1;
+
+#
+# MDEV-9142 :Adding Constraint with no database reference
+# results in ERROR 1046 (3D000) at line 13: No database selected
+#
+CREATE DATABASE kg_test1;
+CREATE DATABASE kg_test2;
+
+CREATE TABLE `kg_test1`.`group` (
+  Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `kg_test1`.`person` (
+`Id` INT(11) NOT NULL AUTO_INCREMENT,
+`Name` VARCHAR(50) NOT NULL,
+PRIMARY KEY (`Id`),
+CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+
+show create table `kg_test1`.`person`;
+
+--error 1005
+CREATE TABLE `kg_test2`.`person2` (
+`Id` INT(11) NOT NULL AUTO_INCREMENT,
+`Name` VARCHAR(50) NOT NULL,
+PRIMARY KEY (`Id`),
+CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `kg_test2`.`person2` (
+`Id` INT(11) NOT NULL AUTO_INCREMENT,
+`Name` VARCHAR(50) NOT NULL,
+PRIMARY KEY (`Id`),
+CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES  `kg_test1`.`group` (`Id`)
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+
+show create table `kg_test2`.`person2`;
+
+SHOW WARNINGS;
+DROP DATABASE kg_test2;
+DROP DATABASE kg_test1;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index a52d55b47e9..a3114aba7d3 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -5395,6 +5395,7 @@ bool check_global_access(THD *thd, ulong want_access, bool no_errors)
                                 temporary table flag)
   @param alter_info    [in]     Initial list of columns and indexes for the
                                 table to be created
+  @param create_db     [in]     Database of the created table
 
   @retval
    false  ok.
@@ -5403,7 +5404,8 @@ bool check_global_access(THD *thd, ulong want_access, bool no_errors)
 */
 bool check_fk_parent_table_access(THD *thd,
                                   HA_CREATE_INFO *create_info,
-                                  Alter_info *alter_info)
+                                  Alter_info *alter_info,
+                                  const char* create_db)
 {
   Key *key;
   List_iterator<Key> key_iterator(alter_info->key_list);
@@ -5443,10 +5445,28 @@ bool check_fk_parent_table_access(THD *thd,
           return true;
         }
       }
-      else if (thd->lex->copy_db_to(&db_name.str, &db_name.length))
-        return true;
       else
-        is_qualified_table_name= false;
+      {
+        if (!thd->db)
+        {
+          db_name.str= (char *) thd->memdup(create_db, strlen(create_db)+1);
+          db_name.length= strlen(create_db);
+          is_qualified_table_name= true;
+
+          if(create_db && check_db_name(&db_name))
+          {
+            my_error(ER_WRONG_DB_NAME, MYF(0), db_name.str);
+            return true;
+          }
+        }
+        else
+        {
+          if (thd->lex->copy_db_to(&db_name.str, &db_name.length))
+            return true;
+          else
+           is_qualified_table_name= false;
+        }
+      }
 
       // if lower_case_table_names is set then convert tablename to lower case.
       if (lower_case_table_names)
@@ -7462,7 +7482,7 @@ bool create_table_precheck(THD *thd, TABLE_LIST *tables,
       goto err;
   }
 
-  if (check_fk_parent_table_access(thd, &lex->create_info, &lex->alter_info))
+  if (check_fk_parent_table_access(thd, &lex->create_info, &lex->alter_info, create_table->db))
     goto err;
 
   error= FALSE;
diff --git a/sql/sql_parse.h b/sql/sql_parse.h
index 60d5925c573..4c3070d197d 100644
--- a/sql/sql_parse.h
+++ b/sql/sql_parse.h
@@ -47,7 +47,8 @@ bool create_table_precheck(THD *thd, TABLE_LIST *tables,
                            TABLE_LIST *create_table);
 bool check_fk_parent_table_access(THD *thd,
                                   HA_CREATE_INFO *create_info,
-                                  Alter_info *alter_info);
+                                  Alter_info *alter_info,
+                                  const char* create_db);
 
 bool parse_sql(THD *thd,
                Parser_state *parser_state,
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 024ca9b58e1..2cd36ceb4de 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -6305,7 +6305,7 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
    till this point for the alter operation.
   */
   if ((alter_info->flags & ALTER_FOREIGN_KEY) &&
-      check_fk_parent_table_access(thd, create_info, alter_info))
+      check_fk_parent_table_access(thd, create_info, alter_info, new_db))
     goto err;
 
   /*