Information Schema Shows Tables from Other Catalogs

[tustvold]

Describe the bug

Currently information_schema shows the tables from all catalogs, effectively breaking catalog-level isolation.

To Reproduce

Using the datafusion-cli

❯ create database foo;
0 rows in set. Query took 0.000 seconds.
❯ select * from information_schema.tables;
+---------------+--------------------+-------------+------------+
| table_catalog | table_schema       | table_name  | table_type |
+---------------+--------------------+-------------+------------+
| foo           | information_schema | tables      | VIEW       |
| foo           | information_schema | views       | VIEW       |
| foo           | information_schema | columns     | VIEW       |
| foo           | information_schema | df_settings | VIEW       |
| datafusion    | information_schema | tables      | VIEW       |
| datafusion    | information_schema | views       | VIEW       |
| datafusion    | information_schema | columns     | VIEW       |
| datafusion    | information_schema | df_settings | VIEW       |
+---------------+--------------------+-------------+------------+

Expected behavior

❯ create database foo;
0 rows in set. Query took 0.000 seconds.
❯ select * from information_schema.tables;
+---------------+--------------------+-------------+------------+
| table_catalog | table_schema       | table_name  | table_type |
+---------------+--------------------+-------------+------------+
| datafusion    | information_schema | tables      | VIEW       |
| datafusion    | information_schema | views       | VIEW       |
| datafusion    | information_schema | columns     | VIEW       |
| datafusion    | information_schema | df_settings | VIEW       |
+---------------+--------------------+-------------+------------+

This is consistent with postgres

postgres=# select distinct table_catalog from information_schema.tables;
 table_catalog 
---------------
 postgres
(1 row)

postgres=# create database foo;
CREATE DATABASE

postgres=# select distinct table_catalog from information_schema.tables;
 table_catalog 
---------------
 postgres
(1 row)

postgres=# \c foo
You are now connected to database "foo" as user "postgres".

foo=# select distinct table_catalog from information_schema.tables;
 table_catalog 
---------------
 foo
(1 row)

It is also consistent with mysql

mysql> select distinct table_catalog from information_schema.tables;
+---------------+
| TABLE_CATALOG |
+---------------+
| def           |
+---------------+
1 row in set (0.00 sec)

mysql> create database foo;
Query OK, 1 row affected (0.01 sec)

mysql> select distinct table_catalog from information_schema.tables;
+---------------+
| TABLE_CATALOG |
+---------------+
| def           |
+---------------+
1 row in set (0.00 sec)

mysql> use foo;
Database changed
mysql> select distinct table_catalog from information_schema.tables;
+---------------+
| TABLE_CATALOG |
+---------------+
| def           |
+---------------+
1 row in set (0.01 sec)

Additional context

I was looking into this as part of #3777, and information_schema kept cropping up as a sticking point for asyncification.

[alamb]

I think this was fixed a long time ago

[alamb]

Thanks again to @drauschenbach for highlighting this