morningman opened a new pull request, #66732:
URL: https://github.com/apache/doris/pull/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.
- Does this need documentation?
- [x] Yes. Will follow up with a doc PR for the new session variable and
the newly populated `information_schema` tables.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
--
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]
