[I] JdbcCatalog: getColumns/getTables with null catalog causes cross-schema false positive, leading to "Unknown column 'iceberg_type'" error [iceberg]

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

xhmz opened a new issue, #17300:
URL: https://github.com/apache/iceberg/issues/17300

   ### Describe the bug
   
   When multiple schemas/databases on the same MySQL instance each have an `iceberg_tables` table with different schema versions (V0 vs V1), `JdbcCatalog` incorrectly detects the `iceberg_type` column from a **different schema**, causing `schemaVersion` to be wrongly set to V1 while the actual table in the current connection's schema is V0.
   
   This results in `SQLSyntaxErrorException: Unknown column 'iceberg_type' in 'where clause'` when creating or loading tables.
   
   ### Root Cause
   
   In `JdbcCatalog.java`, both `updateSchemaIfRequired()` and `atomicCreateTable()` call `DatabaseMetaData.getColumns()` / `getTables()` with `null` as the catalog parameter:
   
   ```java
   // updateSchemaIfRequired() — line 234
   dbMeta.getColumns(
       null,                              // ← scans ALL schemas on the server
       null,
       JdbcUtil.CATALOG_TABLE_VIEW_NAME,  // "iceberg_tables" — '_' is a LIKE wildcard
       JdbcUtil.RECORD_TYPE);             // "iceberg_type"
   
   // atomicCreateTable() — line 171
   dbMeta.getTables(
       null,   // ← scans ALL schemas on the server
       null,
       name,
       null);
   ```
   
   Per JDBC spec, `null` for the catalog parameter means "no limitation" — the driver scans **all** schemas/databases on the server. When another schema on the same MySQL instance has an `iceberg_tables` table **with** the `iceberg_type` column (V1 schema), `getColumns()` returns that column even though the current connection's schema has a V0 table (without `iceberg_type`). This causes `schemaVersion` to be incorrectly set to `V1`, and subsequent V1 SQL queries fail with `Unknown column 'iceberg_type'`.
   
   Additionally, `iceberg_tables` contains underscores (`_`), which are single-character wildcards in SQL `LIKE` matching, potentially causing further false matches.
   
   ### To Reproduce
   
   **Environment:** A single MySQL instance with two schemas:
   - `metaservice_db` — has `iceberg_tables` **with** `iceberg_type` column (V1 schema)
   - `iceberg_catalog_db` — has `iceberg_tables` **without** `iceberg_type` column (V0 schema)
   
   **Flink SQL:**
   ```sql
   create catalog custom_iceberg_jdbc with (
     'type' = 'iceberg',
     'catalog-impl' = 'org.apache.iceberg.jdbc.JdbcCatalog',
     'catalog-name' = 'custom_iceberg_jdbc',
     'uri' = 'jdbc:mysql://host:port/iceberg_catalog_db',
     'jdbc.user' = 'root',
     'jdbc.password' = '***',
     'jdbc.schema-version' = 'V0',
     'warehouse' = 'hdfs://...'
   );
   
   create table if not exists custom_iceberg_jdbc.my_db.my_table (id int, name string);
   ```
   
   Even though `jdbc.schema-version=V0` is set, the error still occurs:
   ```
   Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'iceberg_type' in 'where clause'
       at org.apache.iceberg.jdbc.JdbcUtil.lambda$tableOrView$2(JdbcUtil.java:614)
       at org.apache.iceberg.jdbc.JdbcUtil.tableOrView(JdbcUtil.java:602)
       at org.apache.iceberg.jdbc.JdbcUtil.loadTable(JdbcUtil.java:641)
       at org.apache.iceberg.jdbc.JdbcTableOperations.doRefresh(JdbcTableOperations.java:74)
   ```
   
   ### Verification on MySQL
   
   ```sql
   -- Simulating getColumns(null, null, 'iceberg_tables', 'iceberg_type') — scans ALL schemas
   SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
   FROM information_schema.COLUMNS
   WHERE TABLE_NAME LIKE 'iceberg\_tables' AND COLUMN_NAME LIKE 'iceberg_type';
   -- Result: metaservice_db | iceberg_tables | iceberg_type  ← false positive from another schema!
   
   -- Simulating getColumns('iceberg_catalog_db', null, 'iceberg_tables', 'iceberg_type') — limited to current schema
   SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
   FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA = 'iceberg_catalog_db' AND TABLE_NAME LIKE 'iceberg\_tables' AND COLUMN_NAME LIKE 'iceberg_type';
   -- Result: (empty)  ← correct, no iceberg_type column in this schema
   
   -- Current schema's iceberg_tables table structure:
   DESCRIBE iceberg_catalog_db.iceberg_tables;
   -- Only 5 columns: catalog_name, table_namespace, table_name, metadata_location, previous_metadata_location
   -- No iceberg_type column (V0 schema)
   ```
   
   ### Expected behavior
   
   `JdbcCatalog` should only check for the `iceberg_type` column within the schema/database specified in the JDBC connection URL, not across all schemas on the server. The `jdbc.schema-version=V0` setting should be respected, and `schemaVersion` should remain V0 when the current schema's `iceberg_tables` table lacks the `iceberg_type` column.
   
   ### Proposed Fix
   
   Use `conn.getCatalog()` to limit the `getColumns` / `getTables` search scope to the current schema, and escape underscores in the table name pattern to prevent LIKE wildcard matching:
   
   ```java
   // updateSchemaIfRequired()
   String catalog = conn.getCatalog();
   String escapedTableName = JdbcUtil.CATALOG_TABLE_VIEW_NAME.replace("_", "\\_");
   ResultSet typeColumn = dbMeta.getColumns(catalog, null, escapedTableName, JdbcUtil.RECORD_TYPE);
   
   // atomicCreateTable()
   String catalog = conn.getCatalog();
   String escapedName = name.replace("_", "\\_");
   ResultSet result = dbMeta.getTables(catalog, null, escapedName, null);
   ```
   
   ### Why existing tests didn't catch this
   
   All existing `JdbcCatalog` tests use **SQLite** (in-memory or file-based). SQLite does not have the concept of multiple schemas/databases on the same server in the same way MySQL does, so `getColumns(null, null, ...)` in SQLite only sees the current database. The bug only manifests with multi-schema databases like MySQL or PostgreSQL when multiple schemas coexist on the same instance.
   
   ### Impact
   
   This bug affects any deployment where multiple schemas on the same database instance have `iceberg_tables` tables with different schema versions. This is common in:
   - Shared database instances (e.g., a metaservice schema and user catalog schemas coexisting on the same MySQL instance)
   - Multi-tenant environments where different tenants use different schemas on the same database server
   
   The same class of bug has been reported in other Iceberg language implementations:
   - iceberg-go: #1188
   - iceberg-rust: #2068
   
   ### Environment
   
   - Iceberg version: 1.10.x (also confirmed present on `main` branch as of July 2026)
   - Database: MySQL 8.0
   - Compute engine: Flink 1.20
   


-- 
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: issues-unsubscribe@iceberg.apache.org.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org

Re: [I] JdbcCatalog: getColumns/getTables with null catalog causes cross-schema false positive, leading to "Unknown column 'iceberg_type'" error [iceberg]

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

harshitaajoshi commented on issue #17300:
URL: https://github.com/apache/iceberg/issues/17300#issuecomment-5112197595

   Hi, I'd like to take a crack at this.
   
   The fix seems straightforward: use `conn.getCatalog()` instead of `null` in the `getColumns()` and `getTables()` calls so they're scoped to the current database. For the underscore wildcard issue in `iceberg_tables`, I'd use `dbMeta.getSearchStringEscape()` to stay portable across drivers.
   
   One question before I start: is `conn.getCatalog()` the right approach here, or would you prefer pulling the scope from a catalog property instead? I know MySQL returns the DB name from `getCatalog()` while PostgreSQL handles catalogs/schemas differently, so I want to make sure the fix works for both.
   
   Happy to include a regression test using Derby with two schemas to validate the cross-schema scenario that SQLite can't reproduce.


-- 
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: issues-unsubscribe@iceberg.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org

Re: [I] JdbcCatalog: getColumns/getTables with null catalog causes cross-schema false positive, leading to "Unknown column 'iceberg_type'" error [iceberg]

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

harshitaajoshi commented on issue #17300:
URL: https://github.com/apache/iceberg/issues/17300#issuecomment-5187737143

   Looked into this, and there is a second form of the same bug that reproduces without needing multiple schemas.
   
   Both lookups pass `iceberg_tables` as a JDBC metadata pattern, where `_` is a single character wildcard, so an unrelated table like `iceberg1tables` matches on any database. Two things then go wrong:
   
   1. `atomicCreateTable` decides `iceberg_tables` already exists and never creates it, which leaves the catalog permanently broken with "no such table: iceberg_tables".
   2. `updateSchemaIfRequired` reads that table's columns and sets the schema version to V1 against a V0 catalog table.
   
   I have a fix that scopes both lookups to `conn.getCatalog()` and escapes the patterns with `dbMeta.getSearchStringEscape()`. It falls back to the current unrestricted behavior when the driver reports no catalog, which is what SQLite and Derby do. Two regression tests in `TestJdbcCatalog` reproduce the above on plain SQLite and fail without the change.
   
   One open question: I did not also narrow by `conn.getSchema()`. That would cover two schemas inside a single Postgres database, but it can produce a false negative when the catalog table is reachable through `search_path` from another schema. Happy to add it if you would rather cover that case now.
   
   Opening a PR shortly.


-- 
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: issues-unsubscribe@iceberg.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org