You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit 8f53149
Browse filesBrowse the repository at this point in the historyBrowse files
[opt](compatibility) report MySQL-compatible key metadata so clients can find the primary key (#66732)
### What problem does this PR solve?
Issue Number: close#31206
Problem Summary:
A Doris `UNIQUE KEY` or `AGGREGATE KEY` table makes its key columns
unique, but that fact never reached any of the places a MySQL client
looks for it, so no driver could discover a primary key:
- `SHOW KEYS` / `SHOW INDEX` only listed secondary indexes, so a unique
key table with no inverted index returned **nothing at all**. The output
was also not the MySQL shape: one row per index with the column names
joined by commas, and `Non_unique` / `Seq_in_index` / `Collation` /
`Cardinality` / `Null` all empty strings.
- `information_schema.STATISTICS`, `KEY_COLUMN_USAGE` and
`TABLE_CONSTRAINTS` are declared on the FE but have no BE scanner, so
they fall through to `SchemaDummyScanner` and are always empty.
- `information_schema.COLUMNS.COLUMN_KEY` reported the table model
(`UNI` / `AGG` / `DUP`) rather than MySQL's `PRI` / `UNI` / `MUL`. `AGG`
and `DUP` do not exist in MySQL at all.
#31206 reported this for JDBC. It also explains a report from a user
linking a Doris table into Microsoft Access over the MySQL ODBC driver:
without a primary key Access falls back to a read-only snapshot and
downloads the whole table instead of using a keyset cursor.
Connector/ODBC answers `SQLPrimaryKeys`, `SQLStatistics` and
`SQLSpecialColumns` by running ``SHOW KEYS FROM `db`.`tbl` `` and
reading the rows whose key name is exactly `PRIMARY`
(`driver/catalog_no_i_s.cc`, `server_list_dbkeys()`); the `*_i_s()`
variants delegate to the same code. Connector/J with
`useInformationSchema=true` instead queries
`INFORMATION_SCHEMA.STATISTICS ... WHERE INDEX_NAME='PRIMARY'`. Both
paths are covered here.
**How it works**
`TableKeyMeta` becomes the single producer of these rows, so `SHOW KEYS`
and the `information_schema` tables cannot drift apart. What counts as
the primary key, in priority order:
1. a declared `PRIMARY KEY` constraint, if the table has one;
2. the key columns of a `UNIQUE KEY` or `AGGREGATE KEY` table — both
models enforce uniqueness on the key.
The key of a `DUPLICATE KEY` table is only a sort prefix and is **not**
unique, so it is reported as a non-unique index named `DUPLICATE`
instead. Reporting it as a primary key would let a client such as Access
believe it can address a single row by it, which corrupts edits
silently. An owner of a duplicate key table whose data really is unique
can opt in with `ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY (id)`.
Also lets the generic schema table channel send SQL NULL.
`SchemaScanner::insert_block_column` ignored `TCell.isNull` and
unconditionally pushed `false` to the null map, so a column that does
not apply to an index (`SUB_PART`, `PACKED`, `EXPRESSION`) had to report
a stand-in value — and a `CARDINALITY` of 0 on an empty table reads to a
client as "this index selects nothing".
### Release note
Added session variable `enable_mysql_compatible_index_metadata` (default
`false`). When enabled, `SHOW KEYS` / `SHOW INDEX` returns one row per
indexed column in the MySQL layout and exposes the `UNIQUE KEY` /
`AGGREGATE KEY` of a table as an index named `PRIMARY`, and
`information_schema.columns.COLUMN_KEY` reports `PRI` / `UNI` / `MUL`
instead of `UNI` / `AGG` / `DUP`. Set it globally to let MySQL ODBC/JDBC
drivers and BI tools discover primary keys for every client of a
cluster.
`information_schema.STATISTICS`, `KEY_COLUMN_USAGE` and
`TABLE_CONSTRAINTS` are now populated. They were always empty before, so
this needs no switch.
### Check List (For Author)
- Test
- [x] Regression test
- [ ] Unit Test
- [x] Manual test (add detailed scripts or steps below)
New suite
`regression-test/suites/query_p0/show/test_show_index_mysql_compatible.groovy`
covers UNIQUE(MOR) / UNIQUE(MOW) / AGGREGATE / DUPLICATE models,
composite keys, nullable key columns, secondary indexes, declared
PRIMARY KEY / UNIQUE / FOREIGN KEY constraints, the four spellings of
the statement, and both states of the switch — including asserting that
with the switch off the legacy output is unchanged.
`test_query_sys_tables.groovy` gains scoped assertions on the three
`information_schema` tables.
Manual test against the table from #31206:
```sql
CREATE TABLE example_tbl_unique2 (
`user_id` LARGEINT NOT NULL,
`username` VARCHAR(50) NOT NULL,
`city` VARCHAR(20)
) UNIQUE KEY(`user_id`) DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;
-- switch off: 0 rows, the behaviour reported in the issue
SHOW KEYS FROM `cc_test`.`example_tbl_unique2`;
SET enable_mysql_compatible_index_metadata = true;
SHOW KEYS FROM `cc_test`.`example_tbl_unique2`;
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| example_tbl_unique2 | 0 | PRIMARY | 1 | user_id | A | NULL | NULL | NULL | | BTREE |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
-- Connector/J getPrimaryKeys(), verbatim, not behind the switch
SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME,
SEQ_IN_INDEX AS KEY_SEQ, 'PRIMARY' AS PK_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'cc_test' AND TABLE_NAME = 'example_tbl_unique2' AND INDEX_NAME = 'PRIMARY';
| internal | cc_test | example_tbl_unique2 | user_id | 1 | PRIMARY |
```
- Behavior changed:
- [x] Yes.
`SHOW KEYS` / `SHOW INDEX` and `information_schema.columns.COLUMN_KEY`
change shape and values, but only when
`enable_mysql_compatible_index_metadata` is turned on. With the default
`false` their output is byte for byte what it is today, and the
regression test asserts that. The three `information_schema` tables
start returning rows unconditionally; they were always empty before, so
nothing could have depended on their contents.
0 commit comments