Re: [PR] Partition information schema content per catalog [datafusion]

["pepijnve (via GitHub)" <gi...@apache.org>]

pepijnve commented on code in PR #25887:
URL: https://github.com/apache/datafusion/pull/25887#discussion_r4300283083


##########
datafusion/sqllogictest/test_files/information_schema_multiple_catalogs.slt:
##########
@@ -76,25 +76,24 @@ set datafusion.catalog.default_schema = my_other_schema;
 statement ok
 create table t3 as values(1);
 
+query TTT rowsort
+SELECT table_catalog, table_schema, table_name
+FROM my_catalog.information_schema.tables
+WHERE table_schema <> 'information_schema';
+----
+my_catalog my_schema t1
+my_catalog my_schema t2
+
+query TTT rowsort
+SELECT table_catalog, table_schema, table_name
+FROM my_other_catalog.information_schema.tables
+WHERE table_schema <> 'information_schema';
+----
+my_other_catalog my_other_schema t3
+
 query TTTT rowsort
 SELECT * from information_schema.tables;

Review Comment:
   I was curious about how this was theoretically supposed to work per the SQL specification. In ISO 9075-11:2023 at least, the information schema views are indeed segregated per catalog. There's a theoretical notion of a 'definition schema' containing base tables which are allowed to contain everything, and then the information schema view are defined in terms of those.
   
   First we have
   
   ```
   CREATE VIEW INFORMATION_SCHEMA_CATALOG_NAME AS
   SELECT CATALOG_NAME
   FROM DEFINITION_SCHEMA.CATALOG_NAME
   WHERE CATALOG_NAME = 'CN';
   ```
   
   where `CN` is the name of the catalog in which this Information Schema resides.
   
   The tables view is defined as (shortened for legibility)
   
   ```
   CREATE VIEW TABLES AS
   SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ...
   FROM DEFINITION_SCHEMA.TABLES
   WHERE ... 
   AND
   TABLE_CATALOG
   = ( SELECT CATALOG_NAME
   FROM INFORMATION_SCHEMA_CATALOG_NAME );
   ```
   
   The last predicate is what restricts the set of tables to only those that exist in the same catalog as the information schema itself. I did not check all of them exhaustively, but the same seems to hold for most of the other object views as well.
   
   This is of course just the standards view on things. Logging this here as FYI.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org