Bug Report: adding a foreign key constraint specifing referenced table's db name gives error

[LuigiMiazzo17]

Overview of the Issue

When trying to add a foreign key constraint on two table's columns fails when the second table is in the form of db_name.table with a Failed to open the referenced table 'table' error. If db_name is removed from the referenced table, then it works as expected.

Reproduction Steps

After creating a keyspace (named db_name), create two tables:

1. create table table1 (col1 integer, col2 VARCHAR(16), primary key (col1));;
2. create table table2 (col1 integer, col2 VARCHAR(16), primary key (col1));.

Try to add the constraint:
alter table table1 add constraint foreign key (col1) references db_name.table2 (col1);.

Observe the error.

Binary Version

vitess/lite:v21.0.0

Operating System and Environment details

Deployed with the vitess operator

Log Fragments

ERROR 1824 (HY000): target: db_name.-.primary: vttablet: rpc error: code = Unknown desc = Failed to open the referenced table 'table2' (errno 1824) (sqlstate HY000) (CallerID: db_name): Sql: "alter table table1 add foreign key (col1) references db_name.table2 (col1)", BindVars: {}

[shlomi-noach]

What happens if you run:

create table db_name.table1 (col1 integer, col2 VARCHAR(16), primary key (col1));

?

What is the output of select database() ; show databases ;?

[LuigiMiazzo17]

Hello, the statement with VARCHAR(16) works as expected and the table is created.

The second requested output is the following:

mysql> select database() ; show databases ;
+------------+
| database() |
+------------+
| keycloak   |
+------------+
1 row in set (0.09 sec)

+--------------------+
| Database           |
+--------------------+
| keycloak           |
| information_schema |
| mysql              |
| sys                |
| performance_schema |
+--------------------+
5 rows in set (0.04 sec)

Now, keycloak is just a random name I used to tryout the compatibility of vitess with keycloak, and this problem (and actually another one for which I opened a separate issue) was ecountered during its table migrations.

[shlomi-noach]

Hello, the statement with VARCHAR(16) works as expected and the table is created.

Seems to be related to #17904 rather than to this issue.