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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
