github-actions[bot] commented on code in PR #66732: URL: https://github.com/apache/doris/pull/66732#discussion_r3774957294
########## be/src/information_schema/schema_per_db_scanner.cpp: ########## @@ -0,0 +1,167 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +#include "information_schema/schema_per_db_scanner.h" + +#include <utility> + +#include "core/block/block.h" +#include "core/data_type/data_type_factory.hpp" +#include "core/string_ref.h" +#include "information_schema/schema_helper.h" +#include "runtime/cluster_info.h" +#include "runtime/exec_env.h" +#include "runtime/runtime_state.h" + +namespace doris { + +SchemaPerDbScanner::SchemaPerDbScanner(const std::vector<SchemaScanner::ColumnDesc>& columns, + TSchemaTableType::type type, + TSchemaTableName::type request_name, + std::string display_name) + : SchemaScanner(columns, type), + _request_name(request_name), + _display_name(std::move(display_name)) {} + +SchemaPerDbScanner::~SchemaPerDbScanner() = default; + +Status SchemaPerDbScanner::start(RuntimeState* state) { + if (!_is_init) { + return Status::InternalError("used before initialized."); + } + + SCOPED_TIMER(_get_db_timer); + TGetDbsParams db_params; + if (_param->common_param->catalog) { + db_params.__set_catalog(*(_param->common_param->catalog)); + } + if (_param->common_param->current_user_ident) { + db_params.__set_current_user_ident(*(_param->common_param->current_user_ident)); + } + add_extra_db_params(&db_params); + + if (_param->common_param->ip && 0 != _param->common_param->port) { + RETURN_IF_ERROR(SchemaHelper::get_db_names( + *(_param->common_param->ip), _param->common_param->port, db_params, &_db_result)); + } else { + return Status::InternalError("IP or port doesn't exists"); + } + _block_rows_limit = state->batch_size(); + _rpc_timeout_ms = state->execution_timeout() * 1000; + return Status::OK(); +} + +Status SchemaPerDbScanner::get_onedb_info_from_fe(int64_t db_id) { + TNetworkAddress master_addr = ExecEnv::GetInstance()->cluster_info()->master_fe_addr; + + TSchemaTableRequestParams schema_table_request_params; + const std::vector<SchemaScanner::ColumnDesc>& columns = get_column_desc(); + for (const auto& column : columns) { + schema_table_request_params.__isset.columns_name = true; + schema_table_request_params.columns_name.emplace_back(column.name); + } + schema_table_request_params.__set_current_user_ident(*_param->common_param->current_user_ident); + schema_table_request_params.__set_catalog(*_param->common_param->catalog); + schema_table_request_params.__set_dbId(db_id); + add_extra_request_params(&schema_table_request_params); + + TFetchSchemaTableDataRequest request; + request.__set_schema_table_name(_request_name); + request.__set_schema_table_params(schema_table_request_params); + + TFetchSchemaTableDataResult result; + RETURN_IF_ERROR(SchemaHelper::fetch_schema_table_data(master_addr.hostname, master_addr.port, + request, &result, _rpc_timeout_ms)); + return fill_block_from_result(result); +} + +Status SchemaPerDbScanner::fill_block_from_result(TFetchSchemaTableDataResult& result) { + Status status(Status::create(result.status)); + if (!status.ok()) { + LOG(WARNING) << "fetch " << _display_name << " from FE failed, errmsg=" << status; + return status; + } + const std::vector<SchemaScanner::ColumnDesc>& columns = get_column_desc(); + std::vector<TRow> result_data = result.data_batch; + + _fetched_block = Block::create_unique(); + for (const auto& column : columns) { + auto data_type = DataTypeFactory::instance().create_data_type(column.type, true); + _fetched_block->insert( + ColumnWithTypeAndName(data_type->create_column(), data_type, column.name)); + } + _fetched_block->reserve(_block_rows_limit); + if (!result_data.empty() && result_data[0].column_value.size() != columns.size()) { + return Status::InternalError<false>("{} schema is not match for FE and BE", _display_name); + } + + for (auto& row : result_data) { + for (int j = 0; j < (int)columns.size(); j++) { + RETURN_IF_ERROR(insert_block_column(row.column_value[j], j, _fetched_block.get(), + columns[j].type)); + } + } + return Status::OK(); +} + +bool SchemaPerDbScanner::check_and_mark_eos(bool* eos) const { + if (_row_idx == _total_rows) { + *eos = true; + if (_db_index < _db_result.db_ids.size()) { + *eos = false; + } + return true; + } + return false; +} + +Status SchemaPerDbScanner::get_next_block_internal(Block* block, bool* eos) { + if (!_is_init) { + return Status::InternalError("Used before initialized."); + } + + if (nullptr == block || nullptr == eos) { + return Status::InternalError("input pointer is nullptr."); + } + SCOPED_TIMER(_fill_block_timer); + + if ((_fetched_block == nullptr) || (_row_idx == _total_rows)) { + if (_db_index < _db_result.db_ids.size()) { + RETURN_IF_ERROR(get_onedb_info_from_fe(_db_result.db_ids[_db_index])); + _row_idx = 0; // reset row index so that it starts filling the next block. + _total_rows = (int)_fetched_block->rows(); Review Comment: Returning an empty block with `eos=false` schedules the next FE fetch and blocks `_data_dependency`, but the operator's outer `do/while` immediately re-enters and polls that dependency until the RPC completes. Constraint scans routinely hit databases with zero rows, so one query can pin a pipeline worker for each empty database. Please consume empty databases inside the async scanner until rows or final EOS are available. ########## be/src/information_schema/schema_per_db_scanner.cpp: ########## @@ -0,0 +1,167 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +#include "information_schema/schema_per_db_scanner.h" + +#include <utility> + +#include "core/block/block.h" +#include "core/data_type/data_type_factory.hpp" +#include "core/string_ref.h" +#include "information_schema/schema_helper.h" +#include "runtime/cluster_info.h" +#include "runtime/exec_env.h" +#include "runtime/runtime_state.h" + +namespace doris { + +SchemaPerDbScanner::SchemaPerDbScanner(const std::vector<SchemaScanner::ColumnDesc>& columns, + TSchemaTableType::type type, + TSchemaTableName::type request_name, + std::string display_name) + : SchemaScanner(columns, type), + _request_name(request_name), + _display_name(std::move(display_name)) {} + +SchemaPerDbScanner::~SchemaPerDbScanner() = default; + +Status SchemaPerDbScanner::start(RuntimeState* state) { + if (!_is_init) { + return Status::InternalError("used before initialized."); + } + + SCOPED_TIMER(_get_db_timer); + TGetDbsParams db_params; + if (_param->common_param->catalog) { + db_params.__set_catalog(*(_param->common_param->catalog)); + } + if (_param->common_param->current_user_ident) { + db_params.__set_current_user_ident(*(_param->common_param->current_user_ident)); + } + add_extra_db_params(&db_params); Review Comment: The planner already extracted exact `TABLE_SCHEMA`/`TABLE_NAME` filters into `common_param`, but this base path drops both (only the partitions subclass adds the DB pattern, and the per-DB request has no table field). A Connector/J lookup for one table therefore RPCs every visible database and builds metadata for every table before BE residual filtering. Please forward both exact filters and prune on FE before table enumeration. ########## be/src/information_schema/schema_scanner.cpp: ########## @@ -305,6 +308,12 @@ std::unique_ptr<SchemaScanner> SchemaScanner::create(TSchemaTableType::type type return SchemaBackendMsRpcTableThrottlersScanner::create_unique(); case TSchemaTableType::SCH_TSO_STATUS: return SchemaTsoStatusScanner::create_unique(); + case TSchemaTableType::SCH_STATISTICS: Review Comment: Doris upgrades BE before FE, but a new BE now sends schema-table enum values 20–22 to an old FE. The old generated enum maps these unknown values to null, and `fetchSchemaTableData` returns `Fetch schema table name is not set`, so queries that previously used the dummy scanner fail during the supported mixed-version phase. Gate activation on an FE capability/version bit and retain dummy behavior when it is absent. ########## fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/ShowIndexCommand.java: ########## @@ -107,11 +108,54 @@ protected void analyze(ConnectContext ctx) throws AnalysisException { private ShowResultSet handleShowIndex(ConnectContext ctx, StmtExecutor executor) throws Exception { analyze(ctx); - List<List<String>> rows = Lists.newArrayList(); - // in show index, only support internal catalog DatabaseIf db = Env.getCurrentEnv().getCatalogMgr() .getCatalogOrAnalysisException(tableNameInfo.getCtl()) .getDbOrAnalysisException(tableNameInfo.getDb()); + List<List<String>> rows = ctx.getSessionVariable().enableMysqlCompatibleIndexMetadata() + ? mysqlCompatibleRows(db) : legacyRows(db); + return new ShowResultSet(getMetaData(), rows); + } + + /** + * Reports keys and indexes the way MySQL does: one row per indexed column, with the + * unique key of the table named PRIMARY so that ODBC and JDBC clients recognize it. + */ + private List<List<String>> mysqlCompatibleRows(DatabaseIf db) throws Exception { + List<List<String>> rows = Lists.newArrayList(); + TableIf table = db.getTableOrAnalysisException(tableNameInfo.getTbl()); + table.readLock(); + try { + for (TableKeyMeta.KeyRow row : TableKeyMeta.buildKeyRows(table)) { + // A null cell is sent as SQL NULL, which is what MySQL reports for a + // value that does not apply to the index. + rows.add(Lists.newArrayList( + row.getTableName(), + row.isNonUnique() ? "1" : "0", + row.getIndexName(), + String.valueOf(row.getSeqInIndex()), + row.getColumnName(), + row.getCollation(), + row.getCardinality() == null ? null : String.valueOf(row.getCardinality()), + null, + null, + row.isNullable() ? "YES" : "", + row.getIndexType(), + row.getComment(), Review Comment: Compatible mode still labels the final slot `Properties` and writes the declared index description into `Comment`. MySQL reserves `Comment` for status information and exposes the declared description as `Index_comment`, so clients that look up that field still cannot consume this result. Please select compatible result metadata too and emit the documented `Comment`/`Index_comment` layout. ########## fe/fe-core/src/main/java/org/apache/doris/tablefunction/MetadataGenerator.java: ########## @@ -1599,6 +1536,132 @@ private static TFetchSchemaTableDataResult tableOptionsMetadataResult(TSchemaTab return result; } + /** Emits the rows one table contributes to a key metadata schema table. */ + private interface KeyMetadataRowEmitter { + void emit(CatalogIf catalog, DatabaseIf database, TableIf table, List<TRow> dataBatch); + } + + /** + * Walks the tables of one database and lets the caller turn each into rows. Shared by + * STATISTICS, KEY_COLUMN_USAGE and TABLE_CONSTRAINTS so that the three of them agree on + * what they can see and on how they lock. + */ + private static TFetchSchemaTableDataResult keyMetadataResult(TSchemaTableRequestParams params, + KeyMetadataRowEmitter emitter) { + if (!params.isSetCurrentUserIdent()) { + return errorResult("current user ident is not set."); + } + if (!params.isSetDbId()) { + return errorResult("current db id is not set."); + } + if (!params.isSetCatalog()) { + return errorResult("current catalog is not set."); + } + + UserIdentity currentUserIdentity = UserIdentity.fromThrift(params.getCurrentUserIdent()); + TFetchSchemaTableDataResult result = new TFetchSchemaTableDataResult(); + List<TRow> dataBatch = Lists.newArrayList(); + CatalogIf catalog = Env.getCurrentEnv().getCatalogMgr().getCatalog(params.getCatalog()); + // The BE asks for one database at a time from a list it fetched earlier, so a catalog + // or database that has since been dropped is an empty answer, not an error. + DatabaseIf database = catalog == null ? null : catalog.getDbNullable(params.getDbId()); + if (database != null) { + List<TableIf> tables = database.getTables(); + for (TableIf table : tables) { Review Comment: `Database.getTables()` includes every session's temporary tables, but this callback has no session context and does not skip them. A user with global SHOW can therefore receive another session's encoded table name and key/constraint metadata; existing list/describe RPCs explicitly skip temporary tables for this reason. Please apply the same `table.isTemporary()` fence before the privilege check. ########## fe/fe-core/src/main/java/org/apache/doris/catalog/TableKeyMeta.java: ########## @@ -0,0 +1,460 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +package org.apache.doris.catalog; + +import org.apache.doris.catalog.constraint.Constraint; +import org.apache.doris.catalog.constraint.ForeignKeyConstraint; +import org.apache.doris.catalog.constraint.PrimaryKeyConstraint; +import org.apache.doris.catalog.constraint.UniqueConstraint; +import org.apache.doris.catalog.info.TableNameInfo; +import org.apache.doris.info.TableNameInfoUtils; + +import com.google.common.collect.Lists; + +import java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.TreeMap; + +/** + * Describes the keys and indexes of a table the way MySQL does, as one row per + * (index, column) pair. + * + * <p>MySQL clients discover the primary key of a table through {@code SHOW KEYS} and + * {@code information_schema.STATISTICS}, both of which are shaped like this. The MySQL + * ODBC driver, for instance, answers {@code SQLPrimaryKeys} and {@code SQLStatistics} + * by running {@code SHOW KEYS FROM `db`.`tbl`} and looking for rows whose key name is + * exactly {@code PRIMARY}. Producing those rows in one place keeps every such surface + * telling the same story. + * + * <p>What counts as the primary key, in priority order: + * <ol> + * <li>A user declared {@code PRIMARY KEY} constraint, if the table has one.</li> + * <li>The key columns of a UNIQUE KEY or AGGREGATE KEY table. Both models make the + * key columns unique, so they are a faithful primary key.</li> + * </ol> + * The key columns of a DUPLICATE KEY table are only a sort prefix and are <em>not</em> + * unique, so they are reported as a non-unique index instead. Reporting them as a + * primary key would let a client such as Access believe it can address a single row by + * them, which silently corrupts edits. + */ +public class TableKeyMeta { + /** MySQL's reserved name for the primary key. Clients match on this exact string. */ + public static final String PRIMARY_KEY_NAME = "PRIMARY"; + /** Name reported for the sort prefix of a DUPLICATE KEY table. */ + public static final String DUPLICATE_KEY_NAME = "DUPLICATE"; + + private static final String BTREE = "BTREE"; + private static final String ASCENDING = "A"; + + private TableKeyMeta() {} + + /** One (index, column) pair, i.e. one row of SHOW KEYS or information_schema.STATISTICS. */ + public static class KeyRow { + private final String tableName; + private final boolean nonUnique; + private final String indexName; + private final int seqInIndex; + private final String columnName; + private final String collation; + private final Long cardinality; + private final boolean nullable; + private final String indexType; + private final String comment; + private final String properties; + + public KeyRow(String tableName, boolean nonUnique, String indexName, int seqInIndex, String columnName, + String collation, Long cardinality, boolean nullable, String indexType, String comment, + String properties) { + this.tableName = tableName; + this.nonUnique = nonUnique; + this.indexName = indexName; + this.seqInIndex = seqInIndex; + this.columnName = columnName; + this.collation = collation; + this.cardinality = cardinality; + this.nullable = nullable; + this.indexType = indexType; + this.comment = comment; + this.properties = properties; + } + + public String getTableName() { + return tableName; + } + + public boolean isNonUnique() { + return nonUnique; + } + + public String getIndexName() { + return indexName; + } + + public int getSeqInIndex() { + return seqInIndex; + } + + public String getColumnName() { + return columnName; + } + + /** "A" for an ordered index, null when the order is not meaningful. */ + public String getCollation() { + return collation; + } + + /** Estimated distinct values, or null when unknown. */ + public Long getCardinality() { + return cardinality; + } + + public boolean isNullable() { + return nullable; + } + + public String getIndexType() { + return indexType; + } + + public String getComment() { + return comment; + } + + /** Doris specific index properties. Empty for keys derived from the table model. */ + public String getProperties() { + return properties; + } + } + + /** + * Builds every key row of a table, primary key first. + * + * <p>The caller is expected to hold a read lock on the table. + */ + public static List<KeyRow> buildKeyRows(TableIf table) { + List<KeyRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + List<Column> primaryKeyColumns = findPrimaryKeyColumns(table, constraints); + if (!primaryKeyColumns.isEmpty()) { + addRows(rows, table, PRIMARY_KEY_NAME, primaryKeyColumns, false, tableCardinality(table), BTREE, "", ""); + } + + // Unique constraints are declared rather than enforced, but they are the user's own + // statement about the data, so report them as unique indexes. + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (!(entry.getValue() instanceof UniqueConstraint)) { + continue; + } + List<Column> columns = orderBySchema(table, ((UniqueConstraint) entry.getValue()).getUniqueColumnNames()); + if (!columns.isEmpty()) { + addRows(rows, table, entry.getKey(), columns, false, tableCardinality(table), BTREE, "", ""); + } + } + + if (primaryKeyColumns.isEmpty() && table instanceof OlapTable + && ((OlapTable) table).getKeysType() == KeysType.DUP_KEYS) { + // Only a sort prefix, so not unique. Still worth reporting: it tells a client + // that a prefix scan on these columns is cheap. + List<Column> sortKeyColumns = keyColumnsOf(table); + if (!sortKeyColumns.isEmpty()) { + addRows(rows, table, DUPLICATE_KEY_NAME, sortKeyColumns, true, null, BTREE, "", ""); + } + } + + if (table instanceof OlapTable) { + for (Index index : ((OlapTable) table).getIndexes()) { + List<Column> columns = Lists.newArrayList(); + for (String columnName : index.getColumns()) { + Column column = table.getColumn(columnName); + if (column != null) { + columns.add(column); + } + } + if (columns.isEmpty()) { + continue; + } + // A secondary index imposes no order on its columns, so MySQL reports no collation. + addRows(rows, table, index.getIndexName(), columns, true, null, + index.getIndexType().name(), index.getComment(), index.getPropertiesString()); + } + } + return rows; + } + + /** + * The COLUMN_KEY value MySQL reports for each column of a table: PRI for a column of the + * primary key, UNI for the first column of a unique index, MUL for the first column of a + * non-unique one. Columns that are none of these are absent from the map. + * + * <p>Derived from the same rows as SHOW KEYS, so the two always agree. + */ + public static Map<String, String> buildColumnKeys(TableIf table) { + Map<String, String> columnKeys = new HashMap<>(); + for (KeyRow row : buildKeyRows(table)) { + String value; + if (PRIMARY_KEY_NAME.equals(row.getIndexName())) { + value = "PRI"; + } else if (row.getSeqInIndex() != 1) { + // Only the leading column of an index gets a marker. + continue; + } else { + value = row.isNonUnique() ? "MUL" : "UNI"; + } + String current = columnKeys.get(row.getColumnName()); + if (current == null || rank(value) > rank(current)) { + columnKeys.put(row.getColumnName(), value); + } + } + return columnKeys; + } + + private static int rank(String columnKey) { + switch (columnKey) { + case "PRI": + return 3; + case "UNI": + return 2; + default: + return 1; + } + } + + /** One row of information_schema.TABLE_CONSTRAINTS. */ + public static class ConstraintRow { + private final String constraintName; + private final String constraintType; + + public ConstraintRow(String constraintName, String constraintType) { + this.constraintName = constraintName; + this.constraintType = constraintType; + } + + public String getConstraintName() { + return constraintName; + } + + /** One of PRIMARY KEY, UNIQUE, FOREIGN KEY. */ + public String getConstraintType() { + return constraintType; + } + } + + /** One row of information_schema.KEY_COLUMN_USAGE. */ + public static class KeyColumnUsageRow { + private final String constraintName; + private final String columnName; + private final int ordinalPosition; + private final Integer positionInUniqueConstraint; + private final String referencedTableSchema; + private final String referencedTableName; + private final String referencedColumnName; + + public KeyColumnUsageRow(String constraintName, String columnName, int ordinalPosition, + Integer positionInUniqueConstraint, String referencedTableSchema, String referencedTableName, + String referencedColumnName) { + this.constraintName = constraintName; + this.columnName = columnName; + this.ordinalPosition = ordinalPosition; + this.positionInUniqueConstraint = positionInUniqueConstraint; + this.referencedTableSchema = referencedTableSchema; + this.referencedTableName = referencedTableName; + this.referencedColumnName = referencedColumnName; + } + + public String getConstraintName() { + return constraintName; + } + + public String getColumnName() { + return columnName; + } + + public int getOrdinalPosition() { + return ordinalPosition; + } + + /** Null unless this row belongs to a foreign key. */ + public Integer getPositionInUniqueConstraint() { + return positionInUniqueConstraint; + } + + public String getReferencedTableSchema() { + return referencedTableSchema; + } + + public String getReferencedTableName() { + return referencedTableName; + } + + public String getReferencedColumnName() { + return referencedColumnName; + } + } + + /** + * Builds the TABLE_CONSTRAINTS rows of a table. + * + * <p>The primary key is always named PRIMARY here, even when it came from a constraint + * the user named something else, because that is the name clients look for. + */ + public static List<ConstraintRow> buildConstraintRows(TableIf table) { + List<ConstraintRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + if (!findPrimaryKeyColumns(table, constraints).isEmpty()) { + rows.add(new ConstraintRow(PRIMARY_KEY_NAME, Constraint.ConstraintType.PRIMARY_KEY.getName())); + } + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (entry.getValue() instanceof UniqueConstraint) { + rows.add(new ConstraintRow(entry.getKey(), Constraint.ConstraintType.UNIQUE.getName())); + } else if (entry.getValue() instanceof ForeignKeyConstraint) { + rows.add(new ConstraintRow(entry.getKey(), Constraint.ConstraintType.FOREIGN_KEY.getName())); + } + } + return rows; + } + + /** Builds the KEY_COLUMN_USAGE rows of a table. */ + public static List<KeyColumnUsageRow> buildKeyColumnUsageRows(TableIf table) { + List<KeyColumnUsageRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + int position = 1; + for (Column column : findPrimaryKeyColumns(table, constraints)) { + rows.add(new KeyColumnUsageRow(PRIMARY_KEY_NAME, column.getName(), position++, + null, null, null, null)); + } + + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + Constraint constraint = entry.getValue(); + if (constraint instanceof UniqueConstraint) { + position = 1; + for (Column column : orderBySchema(table, ((UniqueConstraint) constraint).getUniqueColumnNames())) { + rows.add(new KeyColumnUsageRow(entry.getKey(), column.getName(), position++, + null, null, null, null)); + } + } else if (constraint instanceof ForeignKeyConstraint) { + ForeignKeyConstraint foreignKey = (ForeignKeyConstraint) constraint; + TableNameInfo referenced = foreignKey.getReferencedTableName(); + position = 1; + // The map keeps the order the foreign key was declared in, so the nth local + // column pairs with the nth column of the key it references. + for (Map.Entry<String, String> pair : foreignKey.getForeignToReference().entrySet()) { + rows.add(new KeyColumnUsageRow(entry.getKey(), pair.getKey(), position, + position, referenced == null ? null : referenced.getDb(), + referenced == null ? null : referenced.getTbl(), pair.getValue())); + position++; + } + } + } + return rows; + } + + private static void addRows(List<KeyRow> rows, TableIf table, String indexName, List<Column> columns, + boolean nonUnique, Long cardinality, String indexType, String comment, String properties) { + String collation = BTREE.equals(indexType) ? ASCENDING : null; + int seq = 1; + for (Column column : columns) { + rows.add(new KeyRow(table.getName(), nonUnique, indexName, seq++, column.getName(), collation, Review Comment: `table.getName()` is the encoded `<session>_#TEMP#_<name>` for a temporary table. Thus enabling the compatible mode changes `SHOW INDEX FROM temp_t` from the SQL-visible name to the internal session-qualified name. Normalize temporary names (or pass the command's requested table name) before building direct SHOW rows. ########## fe/fe-core/src/main/java/org/apache/doris/catalog/TableKeyMeta.java: ########## @@ -0,0 +1,460 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +package org.apache.doris.catalog; + +import org.apache.doris.catalog.constraint.Constraint; +import org.apache.doris.catalog.constraint.ForeignKeyConstraint; +import org.apache.doris.catalog.constraint.PrimaryKeyConstraint; +import org.apache.doris.catalog.constraint.UniqueConstraint; +import org.apache.doris.catalog.info.TableNameInfo; +import org.apache.doris.info.TableNameInfoUtils; + +import com.google.common.collect.Lists; + +import java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.TreeMap; + +/** + * Describes the keys and indexes of a table the way MySQL does, as one row per + * (index, column) pair. + * + * <p>MySQL clients discover the primary key of a table through {@code SHOW KEYS} and + * {@code information_schema.STATISTICS}, both of which are shaped like this. The MySQL + * ODBC driver, for instance, answers {@code SQLPrimaryKeys} and {@code SQLStatistics} + * by running {@code SHOW KEYS FROM `db`.`tbl`} and looking for rows whose key name is + * exactly {@code PRIMARY}. Producing those rows in one place keeps every such surface + * telling the same story. + * + * <p>What counts as the primary key, in priority order: + * <ol> + * <li>A user declared {@code PRIMARY KEY} constraint, if the table has one.</li> + * <li>The key columns of a UNIQUE KEY or AGGREGATE KEY table. Both models make the + * key columns unique, so they are a faithful primary key.</li> + * </ol> + * The key columns of a DUPLICATE KEY table are only a sort prefix and are <em>not</em> + * unique, so they are reported as a non-unique index instead. Reporting them as a + * primary key would let a client such as Access believe it can address a single row by + * them, which silently corrupts edits. + */ +public class TableKeyMeta { + /** MySQL's reserved name for the primary key. Clients match on this exact string. */ + public static final String PRIMARY_KEY_NAME = "PRIMARY"; + /** Name reported for the sort prefix of a DUPLICATE KEY table. */ + public static final String DUPLICATE_KEY_NAME = "DUPLICATE"; + + private static final String BTREE = "BTREE"; + private static final String ASCENDING = "A"; + + private TableKeyMeta() {} + + /** One (index, column) pair, i.e. one row of SHOW KEYS or information_schema.STATISTICS. */ + public static class KeyRow { + private final String tableName; + private final boolean nonUnique; + private final String indexName; + private final int seqInIndex; + private final String columnName; + private final String collation; + private final Long cardinality; + private final boolean nullable; + private final String indexType; + private final String comment; + private final String properties; + + public KeyRow(String tableName, boolean nonUnique, String indexName, int seqInIndex, String columnName, + String collation, Long cardinality, boolean nullable, String indexType, String comment, + String properties) { + this.tableName = tableName; + this.nonUnique = nonUnique; + this.indexName = indexName; + this.seqInIndex = seqInIndex; + this.columnName = columnName; + this.collation = collation; + this.cardinality = cardinality; + this.nullable = nullable; + this.indexType = indexType; + this.comment = comment; + this.properties = properties; + } + + public String getTableName() { + return tableName; + } + + public boolean isNonUnique() { + return nonUnique; + } + + public String getIndexName() { + return indexName; + } + + public int getSeqInIndex() { + return seqInIndex; + } + + public String getColumnName() { + return columnName; + } + + /** "A" for an ordered index, null when the order is not meaningful. */ + public String getCollation() { + return collation; + } + + /** Estimated distinct values, or null when unknown. */ + public Long getCardinality() { + return cardinality; + } + + public boolean isNullable() { + return nullable; + } + + public String getIndexType() { + return indexType; + } + + public String getComment() { + return comment; + } + + /** Doris specific index properties. Empty for keys derived from the table model. */ + public String getProperties() { + return properties; + } + } + + /** + * Builds every key row of a table, primary key first. + * + * <p>The caller is expected to hold a read lock on the table. + */ + public static List<KeyRow> buildKeyRows(TableIf table) { + List<KeyRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + List<Column> primaryKeyColumns = findPrimaryKeyColumns(table, constraints); + if (!primaryKeyColumns.isEmpty()) { + addRows(rows, table, PRIMARY_KEY_NAME, primaryKeyColumns, false, tableCardinality(table), BTREE, "", ""); + } + + // Unique constraints are declared rather than enforced, but they are the user's own + // statement about the data, so report them as unique indexes. + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (!(entry.getValue() instanceof UniqueConstraint)) { + continue; + } + List<Column> columns = orderBySchema(table, ((UniqueConstraint) entry.getValue()).getUniqueColumnNames()); + if (!columns.isEmpty()) { + addRows(rows, table, entry.getKey(), columns, false, tableCardinality(table), BTREE, "", ""); + } + } + + if (primaryKeyColumns.isEmpty() && table instanceof OlapTable + && ((OlapTable) table).getKeysType() == KeysType.DUP_KEYS) { + // Only a sort prefix, so not unique. Still worth reporting: it tells a client + // that a prefix scan on these columns is cheap. + List<Column> sortKeyColumns = keyColumnsOf(table); + if (!sortKeyColumns.isEmpty()) { + addRows(rows, table, DUPLICATE_KEY_NAME, sortKeyColumns, true, null, BTREE, "", ""); + } + } + + if (table instanceof OlapTable) { + for (Index index : ((OlapTable) table).getIndexes()) { + List<Column> columns = Lists.newArrayList(); + for (String columnName : index.getColumns()) { + Column column = table.getColumn(columnName); + if (column != null) { + columns.add(column); + } + } + if (columns.isEmpty()) { + continue; + } + // A secondary index imposes no order on its columns, so MySQL reports no collation. + addRows(rows, table, index.getIndexName(), columns, true, null, + index.getIndexType().name(), index.getComment(), index.getPropertiesString()); + } + } + return rows; + } + + /** + * The COLUMN_KEY value MySQL reports for each column of a table: PRI for a column of the + * primary key, UNI for the first column of a unique index, MUL for the first column of a + * non-unique one. Columns that are none of these are absent from the map. + * + * <p>Derived from the same rows as SHOW KEYS, so the two always agree. + */ + public static Map<String, String> buildColumnKeys(TableIf table) { + Map<String, String> columnKeys = new HashMap<>(); + for (KeyRow row : buildKeyRows(table)) { + String value; + if (PRIMARY_KEY_NAME.equals(row.getIndexName())) { + value = "PRI"; + } else if (row.getSeqInIndex() != 1) { + // Only the leading column of an index gets a marker. + continue; + } else { + value = row.isNonUnique() ? "MUL" : "UNI"; + } + String current = columnKeys.get(row.getColumnName()); + if (current == null || rank(value) > rank(current)) { + columnKeys.put(row.getColumnName(), value); + } + } + return columnKeys; + } + + private static int rank(String columnKey) { + switch (columnKey) { + case "PRI": + return 3; + case "UNI": + return 2; + default: + return 1; + } + } + + /** One row of information_schema.TABLE_CONSTRAINTS. */ + public static class ConstraintRow { + private final String constraintName; + private final String constraintType; + + public ConstraintRow(String constraintName, String constraintType) { + this.constraintName = constraintName; + this.constraintType = constraintType; + } + + public String getConstraintName() { + return constraintName; + } + + /** One of PRIMARY KEY, UNIQUE, FOREIGN KEY. */ + public String getConstraintType() { + return constraintType; + } + } + + /** One row of information_schema.KEY_COLUMN_USAGE. */ + public static class KeyColumnUsageRow { + private final String constraintName; + private final String columnName; + private final int ordinalPosition; + private final Integer positionInUniqueConstraint; + private final String referencedTableSchema; + private final String referencedTableName; + private final String referencedColumnName; + + public KeyColumnUsageRow(String constraintName, String columnName, int ordinalPosition, + Integer positionInUniqueConstraint, String referencedTableSchema, String referencedTableName, + String referencedColumnName) { + this.constraintName = constraintName; + this.columnName = columnName; + this.ordinalPosition = ordinalPosition; + this.positionInUniqueConstraint = positionInUniqueConstraint; + this.referencedTableSchema = referencedTableSchema; + this.referencedTableName = referencedTableName; + this.referencedColumnName = referencedColumnName; + } + + public String getConstraintName() { + return constraintName; + } + + public String getColumnName() { + return columnName; + } + + public int getOrdinalPosition() { + return ordinalPosition; + } + + /** Null unless this row belongs to a foreign key. */ + public Integer getPositionInUniqueConstraint() { + return positionInUniqueConstraint; + } + + public String getReferencedTableSchema() { + return referencedTableSchema; + } + + public String getReferencedTableName() { + return referencedTableName; + } + + public String getReferencedColumnName() { + return referencedColumnName; + } + } + + /** + * Builds the TABLE_CONSTRAINTS rows of a table. + * + * <p>The primary key is always named PRIMARY here, even when it came from a constraint + * the user named something else, because that is the name clients look for. + */ + public static List<ConstraintRow> buildConstraintRows(TableIf table) { + List<ConstraintRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + if (!findPrimaryKeyColumns(table, constraints).isEmpty()) { + rows.add(new ConstraintRow(PRIMARY_KEY_NAME, Constraint.ConstraintType.PRIMARY_KEY.getName())); + } + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (entry.getValue() instanceof UniqueConstraint) { + rows.add(new ConstraintRow(entry.getKey(), Constraint.ConstraintType.UNIQUE.getName())); + } else if (entry.getValue() instanceof ForeignKeyConstraint) { + rows.add(new ConstraintRow(entry.getKey(), Constraint.ConstraintType.FOREIGN_KEY.getName())); + } + } + return rows; + } + + /** Builds the KEY_COLUMN_USAGE rows of a table. */ + public static List<KeyColumnUsageRow> buildKeyColumnUsageRows(TableIf table) { + List<KeyColumnUsageRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + int position = 1; + for (Column column : findPrimaryKeyColumns(table, constraints)) { + rows.add(new KeyColumnUsageRow(PRIMARY_KEY_NAME, column.getName(), position++, + null, null, null, null)); + } + + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + Constraint constraint = entry.getValue(); + if (constraint instanceof UniqueConstraint) { + position = 1; + for (Column column : orderBySchema(table, ((UniqueConstraint) constraint).getUniqueColumnNames())) { + rows.add(new KeyColumnUsageRow(entry.getKey(), column.getName(), position++, + null, null, null, null)); + } + } else if (constraint instanceof ForeignKeyConstraint) { + ForeignKeyConstraint foreignKey = (ForeignKeyConstraint) constraint; + TableNameInfo referenced = foreignKey.getReferencedTableName(); + position = 1; + // The map keeps the order the foreign key was declared in, so the nth local + // column pairs with the nth column of the key it references. + for (Map.Entry<String, String> pair : foreignKey.getForeignToReference().entrySet()) { + rows.add(new KeyColumnUsageRow(entry.getKey(), pair.getKey(), position, + position, referenced == null ? null : referenced.getDb(), Review Comment: `POSITION_IN_UNIQUE_CONSTRAINT` is the referenced column's position in the parent key, not the local FK position. Reordered references are accepted, so `(x,y) REFERENCES parent(b,a)` against parent key `(a,b)` must emit `2,1`; this always emits `1,2`. Resolve each referenced name against the preserved parent-key order. ########## fe/fe-core/src/main/java/org/apache/doris/catalog/TableKeyMeta.java: ########## @@ -0,0 +1,460 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +package org.apache.doris.catalog; + +import org.apache.doris.catalog.constraint.Constraint; +import org.apache.doris.catalog.constraint.ForeignKeyConstraint; +import org.apache.doris.catalog.constraint.PrimaryKeyConstraint; +import org.apache.doris.catalog.constraint.UniqueConstraint; +import org.apache.doris.catalog.info.TableNameInfo; +import org.apache.doris.info.TableNameInfoUtils; + +import com.google.common.collect.Lists; + +import java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.TreeMap; + +/** + * Describes the keys and indexes of a table the way MySQL does, as one row per + * (index, column) pair. + * + * <p>MySQL clients discover the primary key of a table through {@code SHOW KEYS} and + * {@code information_schema.STATISTICS}, both of which are shaped like this. The MySQL + * ODBC driver, for instance, answers {@code SQLPrimaryKeys} and {@code SQLStatistics} + * by running {@code SHOW KEYS FROM `db`.`tbl`} and looking for rows whose key name is + * exactly {@code PRIMARY}. Producing those rows in one place keeps every such surface + * telling the same story. + * + * <p>What counts as the primary key, in priority order: + * <ol> + * <li>A user declared {@code PRIMARY KEY} constraint, if the table has one.</li> + * <li>The key columns of a UNIQUE KEY or AGGREGATE KEY table. Both models make the + * key columns unique, so they are a faithful primary key.</li> + * </ol> + * The key columns of a DUPLICATE KEY table are only a sort prefix and are <em>not</em> + * unique, so they are reported as a non-unique index instead. Reporting them as a + * primary key would let a client such as Access believe it can address a single row by + * them, which silently corrupts edits. + */ +public class TableKeyMeta { + /** MySQL's reserved name for the primary key. Clients match on this exact string. */ + public static final String PRIMARY_KEY_NAME = "PRIMARY"; + /** Name reported for the sort prefix of a DUPLICATE KEY table. */ + public static final String DUPLICATE_KEY_NAME = "DUPLICATE"; + + private static final String BTREE = "BTREE"; + private static final String ASCENDING = "A"; + + private TableKeyMeta() {} + + /** One (index, column) pair, i.e. one row of SHOW KEYS or information_schema.STATISTICS. */ + public static class KeyRow { + private final String tableName; + private final boolean nonUnique; + private final String indexName; + private final int seqInIndex; + private final String columnName; + private final String collation; + private final Long cardinality; + private final boolean nullable; + private final String indexType; + private final String comment; + private final String properties; + + public KeyRow(String tableName, boolean nonUnique, String indexName, int seqInIndex, String columnName, + String collation, Long cardinality, boolean nullable, String indexType, String comment, + String properties) { + this.tableName = tableName; + this.nonUnique = nonUnique; + this.indexName = indexName; + this.seqInIndex = seqInIndex; + this.columnName = columnName; + this.collation = collation; + this.cardinality = cardinality; + this.nullable = nullable; + this.indexType = indexType; + this.comment = comment; + this.properties = properties; + } + + public String getTableName() { + return tableName; + } + + public boolean isNonUnique() { + return nonUnique; + } + + public String getIndexName() { + return indexName; + } + + public int getSeqInIndex() { + return seqInIndex; + } + + public String getColumnName() { + return columnName; + } + + /** "A" for an ordered index, null when the order is not meaningful. */ + public String getCollation() { + return collation; + } + + /** Estimated distinct values, or null when unknown. */ + public Long getCardinality() { + return cardinality; + } + + public boolean isNullable() { + return nullable; + } + + public String getIndexType() { + return indexType; + } + + public String getComment() { + return comment; + } + + /** Doris specific index properties. Empty for keys derived from the table model. */ + public String getProperties() { + return properties; + } + } + + /** + * Builds every key row of a table, primary key first. + * + * <p>The caller is expected to hold a read lock on the table. + */ + public static List<KeyRow> buildKeyRows(TableIf table) { + List<KeyRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + List<Column> primaryKeyColumns = findPrimaryKeyColumns(table, constraints); + if (!primaryKeyColumns.isEmpty()) { + addRows(rows, table, PRIMARY_KEY_NAME, primaryKeyColumns, false, tableCardinality(table), BTREE, "", ""); + } + + // Unique constraints are declared rather than enforced, but they are the user's own + // statement about the data, so report them as unique indexes. + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (!(entry.getValue() instanceof UniqueConstraint)) { + continue; + } + List<Column> columns = orderBySchema(table, ((UniqueConstraint) entry.getValue()).getUniqueColumnNames()); + if (!columns.isEmpty()) { + addRows(rows, table, entry.getKey(), columns, false, tableCardinality(table), BTREE, "", ""); + } + } + + if (primaryKeyColumns.isEmpty() && table instanceof OlapTable + && ((OlapTable) table).getKeysType() == KeysType.DUP_KEYS) { + // Only a sort prefix, so not unique. Still worth reporting: it tells a client + // that a prefix scan on these columns is cheap. + List<Column> sortKeyColumns = keyColumnsOf(table); + if (!sortKeyColumns.isEmpty()) { + addRows(rows, table, DUPLICATE_KEY_NAME, sortKeyColumns, true, null, BTREE, "", ""); + } + } + + if (table instanceof OlapTable) { + for (Index index : ((OlapTable) table).getIndexes()) { + List<Column> columns = Lists.newArrayList(); + for (String columnName : index.getColumns()) { + Column column = table.getColumn(columnName); + if (column != null) { + columns.add(column); + } + } + if (columns.isEmpty()) { + continue; + } + // A secondary index imposes no order on its columns, so MySQL reports no collation. + addRows(rows, table, index.getIndexName(), columns, true, null, + index.getIndexType().name(), index.getComment(), index.getPropertiesString()); + } + } + return rows; + } + + /** + * The COLUMN_KEY value MySQL reports for each column of a table: PRI for a column of the + * primary key, UNI for the first column of a unique index, MUL for the first column of a + * non-unique one. Columns that are none of these are absent from the map. + * + * <p>Derived from the same rows as SHOW KEYS, so the two always agree. + */ + public static Map<String, String> buildColumnKeys(TableIf table) { + Map<String, String> columnKeys = new HashMap<>(); + for (KeyRow row : buildKeyRows(table)) { + String value; + if (PRIMARY_KEY_NAME.equals(row.getIndexName())) { + value = "PRI"; + } else if (row.getSeqInIndex() != 1) { + // Only the leading column of an index gets a marker. + continue; + } else { + value = row.isNonUnique() ? "MUL" : "UNI"; + } + String current = columnKeys.get(row.getColumnName()); + if (current == null || rank(value) > rank(current)) { + columnKeys.put(row.getColumnName(), value); + } + } + return columnKeys; + } + + private static int rank(String columnKey) { + switch (columnKey) { + case "PRI": + return 3; + case "UNI": + return 2; + default: + return 1; + } + } + + /** One row of information_schema.TABLE_CONSTRAINTS. */ + public static class ConstraintRow { + private final String constraintName; + private final String constraintType; + + public ConstraintRow(String constraintName, String constraintType) { + this.constraintName = constraintName; + this.constraintType = constraintType; + } + + public String getConstraintName() { + return constraintName; + } + + /** One of PRIMARY KEY, UNIQUE, FOREIGN KEY. */ + public String getConstraintType() { + return constraintType; + } + } + + /** One row of information_schema.KEY_COLUMN_USAGE. */ + public static class KeyColumnUsageRow { + private final String constraintName; + private final String columnName; + private final int ordinalPosition; + private final Integer positionInUniqueConstraint; + private final String referencedTableSchema; + private final String referencedTableName; + private final String referencedColumnName; + + public KeyColumnUsageRow(String constraintName, String columnName, int ordinalPosition, + Integer positionInUniqueConstraint, String referencedTableSchema, String referencedTableName, + String referencedColumnName) { + this.constraintName = constraintName; + this.columnName = columnName; + this.ordinalPosition = ordinalPosition; + this.positionInUniqueConstraint = positionInUniqueConstraint; + this.referencedTableSchema = referencedTableSchema; + this.referencedTableName = referencedTableName; + this.referencedColumnName = referencedColumnName; + } + + public String getConstraintName() { + return constraintName; + } + + public String getColumnName() { + return columnName; + } + + public int getOrdinalPosition() { + return ordinalPosition; + } + + /** Null unless this row belongs to a foreign key. */ + public Integer getPositionInUniqueConstraint() { + return positionInUniqueConstraint; + } + + public String getReferencedTableSchema() { + return referencedTableSchema; + } + + public String getReferencedTableName() { + return referencedTableName; + } + + public String getReferencedColumnName() { + return referencedColumnName; + } + } + + /** + * Builds the TABLE_CONSTRAINTS rows of a table. + * + * <p>The primary key is always named PRIMARY here, even when it came from a constraint + * the user named something else, because that is the name clients look for. + */ + public static List<ConstraintRow> buildConstraintRows(TableIf table) { + List<ConstraintRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + if (!findPrimaryKeyColumns(table, constraints).isEmpty()) { + rows.add(new ConstraintRow(PRIMARY_KEY_NAME, Constraint.ConstraintType.PRIMARY_KEY.getName())); + } + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (entry.getValue() instanceof UniqueConstraint) { + rows.add(new ConstraintRow(entry.getKey(), Constraint.ConstraintType.UNIQUE.getName())); + } else if (entry.getValue() instanceof ForeignKeyConstraint) { + rows.add(new ConstraintRow(entry.getKey(), Constraint.ConstraintType.FOREIGN_KEY.getName())); + } + } + return rows; + } + + /** Builds the KEY_COLUMN_USAGE rows of a table. */ + public static List<KeyColumnUsageRow> buildKeyColumnUsageRows(TableIf table) { + List<KeyColumnUsageRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + int position = 1; + for (Column column : findPrimaryKeyColumns(table, constraints)) { + rows.add(new KeyColumnUsageRow(PRIMARY_KEY_NAME, column.getName(), position++, + null, null, null, null)); + } + + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + Constraint constraint = entry.getValue(); + if (constraint instanceof UniqueConstraint) { + position = 1; + for (Column column : orderBySchema(table, ((UniqueConstraint) constraint).getUniqueColumnNames())) { + rows.add(new KeyColumnUsageRow(entry.getKey(), column.getName(), position++, + null, null, null, null)); + } + } else if (constraint instanceof ForeignKeyConstraint) { + ForeignKeyConstraint foreignKey = (ForeignKeyConstraint) constraint; + TableNameInfo referenced = foreignKey.getReferencedTableName(); + position = 1; + // The map keeps the order the foreign key was declared in, so the nth local + // column pairs with the nth column of the key it references. + for (Map.Entry<String, String> pair : foreignKey.getForeignToReference().entrySet()) { + rows.add(new KeyColumnUsageRow(entry.getKey(), pair.getKey(), position, + position, referenced == null ? null : referenced.getDb(), + referenced == null ? null : referenced.getTbl(), pair.getValue())); + position++; + } + } + } + return rows; + } + + private static void addRows(List<KeyRow> rows, TableIf table, String indexName, List<Column> columns, + boolean nonUnique, Long cardinality, String indexType, String comment, String properties) { + String collation = BTREE.equals(indexType) ? ASCENDING : null; + int seq = 1; + for (Column column : columns) { + rows.add(new KeyRow(table.getName(), nonUnique, indexName, seq++, column.getName(), collation, + cardinality, column.isAllowNull(), indexType, comment, properties)); + } + } + + /** + * A declared PRIMARY KEY constraint wins over the table model, so that the owner of a + * DUPLICATE KEY table whose data really is unique can make their table usable from + * ODBC and JDBC with a single ALTER TABLE. + */ + private static List<Column> findPrimaryKeyColumns(TableIf table, Map<String, Constraint> constraints) { + for (Constraint constraint : sortedByName(constraints).values()) { + if (constraint instanceof PrimaryKeyConstraint) { + List<Column> columns = orderBySchema(table, ((PrimaryKeyConstraint) constraint).getPrimaryKeyNames()); Review Comment: Declared constraint order is lost here: `ADD CONSTRAINT p PRIMARY KEY (b,a)` is persisted as a set and then emitted in base-schema order `(a,b)`. That makes `SEQ_IN_INDEX` and `KEY_COLUMN_USAGE.ORDINAL_POSITION` disagree with the declared key. Preserve an ordered column list for declared PRIMARY/UNIQUE constraints; schema order is appropriate only for storage-model keys. ########## fe/fe-core/src/main/java/org/apache/doris/service/FrontendServiceImpl.java: ########## @@ -995,6 +996,11 @@ public TDescribeTablesResult describeTables(TDescribeTablesParams params) throws if (table != null && !table.isTemporary()) { table.readLock(); try { + // MySQL marks a column PRI, UNI or MUL depending on the kind of index it + // leads. Doris used to report its table model here instead, which meant + // values such as AGG and DUP that no MySQL client knows what to do with. + Map<String, String> columnKeys = params.isMysqlCompatibleIndexMetadata() Review Comment: This changes `COLUMN_KEY` only for schema-backed column queries. Plain/FULL `SHOW COLUMNS` and the `LIKE` form take `ShowColumnsCommand`'s direct branch and still emit `YES`/`NO`, while adding a `WHERE` routes through this callback and emits `PRI`/`UNI`/`MUL`. Please apply the same compatible key map to the direct SHOW path so one command does not change semantics solely because a predicate was added. ########## fe/fe-core/src/main/java/org/apache/doris/tablefunction/MetadataGenerator.java: ########## @@ -1599,6 +1536,132 @@ private static TFetchSchemaTableDataResult tableOptionsMetadataResult(TSchemaTab return result; } + /** Emits the rows one table contributes to a key metadata schema table. */ + private interface KeyMetadataRowEmitter { + void emit(CatalogIf catalog, DatabaseIf database, TableIf table, List<TRow> dataBatch); + } + + /** + * Walks the tables of one database and lets the caller turn each into rows. Shared by + * STATISTICS, KEY_COLUMN_USAGE and TABLE_CONSTRAINTS so that the three of them agree on + * what they can see and on how they lock. + */ + private static TFetchSchemaTableDataResult keyMetadataResult(TSchemaTableRequestParams params, + KeyMetadataRowEmitter emitter) { + if (!params.isSetCurrentUserIdent()) { + return errorResult("current user ident is not set."); + } + if (!params.isSetDbId()) { + return errorResult("current db id is not set."); + } + if (!params.isSetCatalog()) { + return errorResult("current catalog is not set."); + } + + UserIdentity currentUserIdentity = UserIdentity.fromThrift(params.getCurrentUserIdent()); + TFetchSchemaTableDataResult result = new TFetchSchemaTableDataResult(); + List<TRow> dataBatch = Lists.newArrayList(); + CatalogIf catalog = Env.getCurrentEnv().getCatalogMgr().getCatalog(params.getCatalog()); + // The BE asks for one database at a time from a list it fetched earlier, so a catalog + // or database that has since been dropped is an empty answer, not an error. + DatabaseIf database = catalog == null ? null : catalog.getDbNullable(params.getDbId()); + if (database != null) { + List<TableIf> tables = database.getTables(); + for (TableIf table : tables) { + if (!Env.getCurrentEnv().getAccessManager().checkTblPriv(currentUserIdentity, catalog.getName(), + database.getFullName(), table.getName(), PrivPredicate.SHOW)) { + continue; + } + table.readLock(); + try { + emitter.emit(catalog, database, table, dataBatch); + } finally { + table.readUnlock(); + } + } + } + result.setDataBatch(dataBatch); + result.setStatus(new TStatus(TStatusCode.OK)); + return result; + } + + private static TCell nullCell() { + return new TCell().setIsNull(true); + } + + private static TCell stringOrNull(String value) { + return value == null ? nullCell() : new TCell().setStringVal(value); + } + + private static TCell longOrNull(Long value) { + return value == null ? nullCell() : new TCell().setLongVal(value); + } + + private static TFetchSchemaTableDataResult statisticsMetadataResult(TSchemaTableRequestParams params) { + return keyMetadataResult(params, (catalog, database, table, dataBatch) -> { + for (TableKeyMeta.KeyRow row : TableKeyMeta.buildKeyRows(table)) { + TRow trow = new TRow(); + trow.addToColumnValue(new TCell().setStringVal(catalog.getName())); // TABLE_CATALOG + trow.addToColumnValue(new TCell().setStringVal(database.getFullName())); // TABLE_SCHEMA Review Comment: These new schema fields bypass the existing `show_full_dbname_in_info_schema_db` contract. For an external catalog with that global enabled, `getDbNames()` exposes `catalog.db`, but this emitter writes raw `db`; consequently unfiltered information-schema output is inconsistent and `TABLE_SCHEMA='catalog.db'` filters every generated row away. Reuse the MySQL-visible schema normalization for all emitted schema/index/constraint fields. ########## fe/fe-core/src/main/java/org/apache/doris/catalog/TableKeyMeta.java: ########## @@ -0,0 +1,460 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +package org.apache.doris.catalog; + +import org.apache.doris.catalog.constraint.Constraint; +import org.apache.doris.catalog.constraint.ForeignKeyConstraint; +import org.apache.doris.catalog.constraint.PrimaryKeyConstraint; +import org.apache.doris.catalog.constraint.UniqueConstraint; +import org.apache.doris.catalog.info.TableNameInfo; +import org.apache.doris.info.TableNameInfoUtils; + +import com.google.common.collect.Lists; + +import java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.TreeMap; + +/** + * Describes the keys and indexes of a table the way MySQL does, as one row per + * (index, column) pair. + * + * <p>MySQL clients discover the primary key of a table through {@code SHOW KEYS} and + * {@code information_schema.STATISTICS}, both of which are shaped like this. The MySQL + * ODBC driver, for instance, answers {@code SQLPrimaryKeys} and {@code SQLStatistics} + * by running {@code SHOW KEYS FROM `db`.`tbl`} and looking for rows whose key name is + * exactly {@code PRIMARY}. Producing those rows in one place keeps every such surface + * telling the same story. + * + * <p>What counts as the primary key, in priority order: + * <ol> + * <li>A user declared {@code PRIMARY KEY} constraint, if the table has one.</li> + * <li>The key columns of a UNIQUE KEY or AGGREGATE KEY table. Both models make the + * key columns unique, so they are a faithful primary key.</li> + * </ol> + * The key columns of a DUPLICATE KEY table are only a sort prefix and are <em>not</em> + * unique, so they are reported as a non-unique index instead. Reporting them as a + * primary key would let a client such as Access believe it can address a single row by + * them, which silently corrupts edits. + */ +public class TableKeyMeta { + /** MySQL's reserved name for the primary key. Clients match on this exact string. */ + public static final String PRIMARY_KEY_NAME = "PRIMARY"; + /** Name reported for the sort prefix of a DUPLICATE KEY table. */ + public static final String DUPLICATE_KEY_NAME = "DUPLICATE"; + + private static final String BTREE = "BTREE"; + private static final String ASCENDING = "A"; + + private TableKeyMeta() {} + + /** One (index, column) pair, i.e. one row of SHOW KEYS or information_schema.STATISTICS. */ + public static class KeyRow { + private final String tableName; + private final boolean nonUnique; + private final String indexName; + private final int seqInIndex; + private final String columnName; + private final String collation; + private final Long cardinality; + private final boolean nullable; + private final String indexType; + private final String comment; + private final String properties; + + public KeyRow(String tableName, boolean nonUnique, String indexName, int seqInIndex, String columnName, + String collation, Long cardinality, boolean nullable, String indexType, String comment, + String properties) { + this.tableName = tableName; + this.nonUnique = nonUnique; + this.indexName = indexName; + this.seqInIndex = seqInIndex; + this.columnName = columnName; + this.collation = collation; + this.cardinality = cardinality; + this.nullable = nullable; + this.indexType = indexType; + this.comment = comment; + this.properties = properties; + } + + public String getTableName() { + return tableName; + } + + public boolean isNonUnique() { + return nonUnique; + } + + public String getIndexName() { + return indexName; + } + + public int getSeqInIndex() { + return seqInIndex; + } + + public String getColumnName() { + return columnName; + } + + /** "A" for an ordered index, null when the order is not meaningful. */ + public String getCollation() { + return collation; + } + + /** Estimated distinct values, or null when unknown. */ + public Long getCardinality() { + return cardinality; + } + + public boolean isNullable() { + return nullable; + } + + public String getIndexType() { + return indexType; + } + + public String getComment() { + return comment; + } + + /** Doris specific index properties. Empty for keys derived from the table model. */ + public String getProperties() { + return properties; + } + } + + /** + * Builds every key row of a table, primary key first. + * + * <p>The caller is expected to hold a read lock on the table. + */ + public static List<KeyRow> buildKeyRows(TableIf table) { + List<KeyRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + List<Column> primaryKeyColumns = findPrimaryKeyColumns(table, constraints); + if (!primaryKeyColumns.isEmpty()) { + addRows(rows, table, PRIMARY_KEY_NAME, primaryKeyColumns, false, tableCardinality(table), BTREE, "", ""); Review Comment: Passing one table row count to every key part reports each composite prefix as unique. For unique `(a,b)` with `(1,1),(1,2)`, the first row's cardinality is 1 while the full key's is 2, but both become 2 here. Use prefix NDV when available, or report NULL for prefixes whose cardinality is unknown. ########## fe/fe-core/src/main/java/org/apache/doris/catalog/TableKeyMeta.java: ########## @@ -0,0 +1,460 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +package org.apache.doris.catalog; + +import org.apache.doris.catalog.constraint.Constraint; +import org.apache.doris.catalog.constraint.ForeignKeyConstraint; +import org.apache.doris.catalog.constraint.PrimaryKeyConstraint; +import org.apache.doris.catalog.constraint.UniqueConstraint; +import org.apache.doris.catalog.info.TableNameInfo; +import org.apache.doris.info.TableNameInfoUtils; + +import com.google.common.collect.Lists; + +import java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.TreeMap; + +/** + * Describes the keys and indexes of a table the way MySQL does, as one row per + * (index, column) pair. + * + * <p>MySQL clients discover the primary key of a table through {@code SHOW KEYS} and + * {@code information_schema.STATISTICS}, both of which are shaped like this. The MySQL + * ODBC driver, for instance, answers {@code SQLPrimaryKeys} and {@code SQLStatistics} + * by running {@code SHOW KEYS FROM `db`.`tbl`} and looking for rows whose key name is + * exactly {@code PRIMARY}. Producing those rows in one place keeps every such surface + * telling the same story. + * + * <p>What counts as the primary key, in priority order: + * <ol> + * <li>A user declared {@code PRIMARY KEY} constraint, if the table has one.</li> + * <li>The key columns of a UNIQUE KEY or AGGREGATE KEY table. Both models make the + * key columns unique, so they are a faithful primary key.</li> + * </ol> + * The key columns of a DUPLICATE KEY table are only a sort prefix and are <em>not</em> + * unique, so they are reported as a non-unique index instead. Reporting them as a + * primary key would let a client such as Access believe it can address a single row by + * them, which silently corrupts edits. + */ +public class TableKeyMeta { + /** MySQL's reserved name for the primary key. Clients match on this exact string. */ + public static final String PRIMARY_KEY_NAME = "PRIMARY"; + /** Name reported for the sort prefix of a DUPLICATE KEY table. */ + public static final String DUPLICATE_KEY_NAME = "DUPLICATE"; + + private static final String BTREE = "BTREE"; + private static final String ASCENDING = "A"; + + private TableKeyMeta() {} + + /** One (index, column) pair, i.e. one row of SHOW KEYS or information_schema.STATISTICS. */ + public static class KeyRow { + private final String tableName; + private final boolean nonUnique; + private final String indexName; + private final int seqInIndex; + private final String columnName; + private final String collation; + private final Long cardinality; + private final boolean nullable; + private final String indexType; + private final String comment; + private final String properties; + + public KeyRow(String tableName, boolean nonUnique, String indexName, int seqInIndex, String columnName, + String collation, Long cardinality, boolean nullable, String indexType, String comment, + String properties) { + this.tableName = tableName; + this.nonUnique = nonUnique; + this.indexName = indexName; + this.seqInIndex = seqInIndex; + this.columnName = columnName; + this.collation = collation; + this.cardinality = cardinality; + this.nullable = nullable; + this.indexType = indexType; + this.comment = comment; + this.properties = properties; + } + + public String getTableName() { + return tableName; + } + + public boolean isNonUnique() { + return nonUnique; + } + + public String getIndexName() { + return indexName; + } + + public int getSeqInIndex() { + return seqInIndex; + } + + public String getColumnName() { + return columnName; + } + + /** "A" for an ordered index, null when the order is not meaningful. */ + public String getCollation() { + return collation; + } + + /** Estimated distinct values, or null when unknown. */ + public Long getCardinality() { + return cardinality; + } + + public boolean isNullable() { + return nullable; + } + + public String getIndexType() { + return indexType; + } + + public String getComment() { + return comment; + } + + /** Doris specific index properties. Empty for keys derived from the table model. */ + public String getProperties() { + return properties; + } + } + + /** + * Builds every key row of a table, primary key first. + * + * <p>The caller is expected to hold a read lock on the table. + */ + public static List<KeyRow> buildKeyRows(TableIf table) { + List<KeyRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + List<Column> primaryKeyColumns = findPrimaryKeyColumns(table, constraints); + if (!primaryKeyColumns.isEmpty()) { + addRows(rows, table, PRIMARY_KEY_NAME, primaryKeyColumns, false, tableCardinality(table), BTREE, "", ""); + } + + // Unique constraints are declared rather than enforced, but they are the user's own + // statement about the data, so report them as unique indexes. + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (!(entry.getValue() instanceof UniqueConstraint)) { + continue; + } + List<Column> columns = orderBySchema(table, ((UniqueConstraint) entry.getValue()).getUniqueColumnNames()); + if (!columns.isEmpty()) { + addRows(rows, table, entry.getKey(), columns, false, tableCardinality(table), BTREE, "", ""); Review Comment: These names share the MySQL index namespace, but Doris validates constraint and secondary-index names separately and does not reserve `PRIMARY`. An existing UNIQUE/secondary index named `PRIMARY` is therefore emitted as the exact synthetic primary marker (and `buildColumnKeys()` labels it `PRI` by string alone); equal nonreserved names also merge unrelated definitions for clients. Preserve an explicit key kind and disambiguate or reject cross-namespace collisions. ########## regression-test/suites/query_p0/show/test_show_index_mysql_compatible.groovy: ########## @@ -0,0 +1,269 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +suite("test_show_index_mysql_compatible") { + def dbName = "test_show_index_mysql_compat" + sql "DROP DATABASE IF EXISTS ${dbName} FORCE" + sql "CREATE DATABASE ${dbName}" + sql "USE ${dbName}" + + sql """ + CREATE TABLE uniq_mor ( + `user_id` LARGEINT NOT NULL, + `username` VARCHAR(50) NOT NULL, + `city` VARCHAR(20) + ) + UNIQUE KEY(`user_id`) + DISTRIBUTED BY HASH(`user_id`) BUCKETS 1 + PROPERTIES ("replication_num" = "1", "enable_unique_key_merge_on_write" = "false") + """ + + sql """ + CREATE TABLE uniq_mow ( + `user_id` LARGEINT NOT NULL, + `event_date` DATE NOT NULL, + `city` VARCHAR(20) + ) + UNIQUE KEY(`user_id`, `event_date`) + DISTRIBUTED BY HASH(`user_id`) BUCKETS 1 + PROPERTIES ("replication_num" = "1", "enable_unique_key_merge_on_write" = "true") + """ + + sql """ + CREATE TABLE agg_tbl ( + `user_id` LARGEINT NOT NULL, + `city` VARCHAR(20) NULL, + `cost` BIGINT SUM + ) + AGGREGATE KEY(`user_id`, `city`) + DISTRIBUTED BY HASH(`user_id`) BUCKETS 1 + PROPERTIES ("replication_num" = "1") + """ + + sql """ + CREATE TABLE dup_tbl ( + `user_id` LARGEINT NOT NULL, + `event_date` DATE NOT NULL, + `note` TEXT, + INDEX idx_note (`note`) USING INVERTED COMMENT 'note idx' + ) + DUPLICATE KEY(`user_id`, `event_date`) + DISTRIBUTED BY HASH(`user_id`) BUCKETS 1 + PROPERTIES ("replication_num" = "1") + """ + + // --------------------------------------------------------------- + // Switch off: the output stays exactly what Doris has always shown. + // --------------------------------------------------------------- + sql "SET enable_mysql_compatible_index_metadata = false" + + // A unique key table with no secondary index reports nothing at all. This is the + // behaviour that keeps MySQL ODBC/JDBC clients from finding a primary key, and it + // has to stay put while the switch is off. + assertEquals(0, sql("SHOW KEYS FROM uniq_mor").size()) Review Comment: This deterministic regression uses fixed assertions but no `qt`/`order_qt` cases or generated `.out`, and it drops the database at the end. The repository test contract requires determined results to be generated by the harness and fixtures to be cleaned before—not after—the test so failed state remains inspectable. Please convert the fixed outputs to ordered `qt` cases, generate the `.out`, and remove the final cleanup. ########## fe/fe-core/src/main/java/org/apache/doris/catalog/TableKeyMeta.java: ########## @@ -0,0 +1,460 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +package org.apache.doris.catalog; + +import org.apache.doris.catalog.constraint.Constraint; +import org.apache.doris.catalog.constraint.ForeignKeyConstraint; +import org.apache.doris.catalog.constraint.PrimaryKeyConstraint; +import org.apache.doris.catalog.constraint.UniqueConstraint; +import org.apache.doris.catalog.info.TableNameInfo; +import org.apache.doris.info.TableNameInfoUtils; + +import com.google.common.collect.Lists; + +import java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.TreeMap; + +/** + * Describes the keys and indexes of a table the way MySQL does, as one row per + * (index, column) pair. + * + * <p>MySQL clients discover the primary key of a table through {@code SHOW KEYS} and + * {@code information_schema.STATISTICS}, both of which are shaped like this. The MySQL + * ODBC driver, for instance, answers {@code SQLPrimaryKeys} and {@code SQLStatistics} + * by running {@code SHOW KEYS FROM `db`.`tbl`} and looking for rows whose key name is + * exactly {@code PRIMARY}. Producing those rows in one place keeps every such surface + * telling the same story. + * + * <p>What counts as the primary key, in priority order: + * <ol> + * <li>A user declared {@code PRIMARY KEY} constraint, if the table has one.</li> + * <li>The key columns of a UNIQUE KEY or AGGREGATE KEY table. Both models make the + * key columns unique, so they are a faithful primary key.</li> + * </ol> + * The key columns of a DUPLICATE KEY table are only a sort prefix and are <em>not</em> + * unique, so they are reported as a non-unique index instead. Reporting them as a + * primary key would let a client such as Access believe it can address a single row by + * them, which silently corrupts edits. + */ +public class TableKeyMeta { + /** MySQL's reserved name for the primary key. Clients match on this exact string. */ + public static final String PRIMARY_KEY_NAME = "PRIMARY"; + /** Name reported for the sort prefix of a DUPLICATE KEY table. */ + public static final String DUPLICATE_KEY_NAME = "DUPLICATE"; + + private static final String BTREE = "BTREE"; + private static final String ASCENDING = "A"; + + private TableKeyMeta() {} + + /** One (index, column) pair, i.e. one row of SHOW KEYS or information_schema.STATISTICS. */ + public static class KeyRow { + private final String tableName; + private final boolean nonUnique; + private final String indexName; + private final int seqInIndex; + private final String columnName; + private final String collation; + private final Long cardinality; + private final boolean nullable; + private final String indexType; + private final String comment; + private final String properties; + + public KeyRow(String tableName, boolean nonUnique, String indexName, int seqInIndex, String columnName, + String collation, Long cardinality, boolean nullable, String indexType, String comment, + String properties) { + this.tableName = tableName; + this.nonUnique = nonUnique; + this.indexName = indexName; + this.seqInIndex = seqInIndex; + this.columnName = columnName; + this.collation = collation; + this.cardinality = cardinality; + this.nullable = nullable; + this.indexType = indexType; + this.comment = comment; + this.properties = properties; + } + + public String getTableName() { + return tableName; + } + + public boolean isNonUnique() { + return nonUnique; + } + + public String getIndexName() { + return indexName; + } + + public int getSeqInIndex() { + return seqInIndex; + } + + public String getColumnName() { + return columnName; + } + + /** "A" for an ordered index, null when the order is not meaningful. */ + public String getCollation() { + return collation; + } + + /** Estimated distinct values, or null when unknown. */ + public Long getCardinality() { + return cardinality; + } + + public boolean isNullable() { + return nullable; + } + + public String getIndexType() { + return indexType; + } + + public String getComment() { + return comment; + } + + /** Doris specific index properties. Empty for keys derived from the table model. */ + public String getProperties() { + return properties; + } + } + + /** + * Builds every key row of a table, primary key first. + * + * <p>The caller is expected to hold a read lock on the table. + */ + public static List<KeyRow> buildKeyRows(TableIf table) { + List<KeyRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + List<Column> primaryKeyColumns = findPrimaryKeyColumns(table, constraints); + if (!primaryKeyColumns.isEmpty()) { + addRows(rows, table, PRIMARY_KEY_NAME, primaryKeyColumns, false, tableCardinality(table), BTREE, "", ""); + } + + // Unique constraints are declared rather than enforced, but they are the user's own + // statement about the data, so report them as unique indexes. + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (!(entry.getValue() instanceof UniqueConstraint)) { + continue; + } + List<Column> columns = orderBySchema(table, ((UniqueConstraint) entry.getValue()).getUniqueColumnNames()); + if (!columns.isEmpty()) { + addRows(rows, table, entry.getKey(), columns, false, tableCardinality(table), BTREE, "", ""); + } + } + + if (primaryKeyColumns.isEmpty() && table instanceof OlapTable + && ((OlapTable) table).getKeysType() == KeysType.DUP_KEYS) { + // Only a sort prefix, so not unique. Still worth reporting: it tells a client + // that a prefix scan on these columns is cheap. + List<Column> sortKeyColumns = keyColumnsOf(table); + if (!sortKeyColumns.isEmpty()) { + addRows(rows, table, DUPLICATE_KEY_NAME, sortKeyColumns, true, null, BTREE, "", ""); + } + } + + if (table instanceof OlapTable) { + for (Index index : ((OlapTable) table).getIndexes()) { + List<Column> columns = Lists.newArrayList(); + for (String columnName : index.getColumns()) { + Column column = table.getColumn(columnName); + if (column != null) { + columns.add(column); + } + } + if (columns.isEmpty()) { + continue; + } + // A secondary index imposes no order on its columns, so MySQL reports no collation. + addRows(rows, table, index.getIndexName(), columns, true, null, + index.getIndexType().name(), index.getComment(), index.getPropertiesString()); + } + } + return rows; + } + + /** + * The COLUMN_KEY value MySQL reports for each column of a table: PRI for a column of the + * primary key, UNI for the first column of a unique index, MUL for the first column of a + * non-unique one. Columns that are none of these are absent from the map. + * + * <p>Derived from the same rows as SHOW KEYS, so the two always agree. + */ + public static Map<String, String> buildColumnKeys(TableIf table) { + Map<String, String> columnKeys = new HashMap<>(); + for (KeyRow row : buildKeyRows(table)) { + String value; + if (PRIMARY_KEY_NAME.equals(row.getIndexName())) { + value = "PRI"; + } else if (row.getSeqInIndex() != 1) { + // Only the leading column of an index gets a marker. + continue; + } else { + value = row.isNonUnique() ? "MUL" : "UNI"; + } + String current = columnKeys.get(row.getColumnName()); + if (current == null || rank(value) > rank(current)) { + columnKeys.put(row.getColumnName(), value); + } + } + return columnKeys; + } + + private static int rank(String columnKey) { + switch (columnKey) { + case "PRI": + return 3; + case "UNI": + return 2; + default: + return 1; + } + } + + /** One row of information_schema.TABLE_CONSTRAINTS. */ + public static class ConstraintRow { + private final String constraintName; + private final String constraintType; + + public ConstraintRow(String constraintName, String constraintType) { + this.constraintName = constraintName; + this.constraintType = constraintType; + } + + public String getConstraintName() { + return constraintName; + } + + /** One of PRIMARY KEY, UNIQUE, FOREIGN KEY. */ + public String getConstraintType() { + return constraintType; + } + } + + /** One row of information_schema.KEY_COLUMN_USAGE. */ + public static class KeyColumnUsageRow { + private final String constraintName; + private final String columnName; + private final int ordinalPosition; + private final Integer positionInUniqueConstraint; + private final String referencedTableSchema; + private final String referencedTableName; + private final String referencedColumnName; + + public KeyColumnUsageRow(String constraintName, String columnName, int ordinalPosition, + Integer positionInUniqueConstraint, String referencedTableSchema, String referencedTableName, + String referencedColumnName) { + this.constraintName = constraintName; + this.columnName = columnName; + this.ordinalPosition = ordinalPosition; + this.positionInUniqueConstraint = positionInUniqueConstraint; + this.referencedTableSchema = referencedTableSchema; + this.referencedTableName = referencedTableName; + this.referencedColumnName = referencedColumnName; + } + + public String getConstraintName() { + return constraintName; + } + + public String getColumnName() { + return columnName; + } + + public int getOrdinalPosition() { + return ordinalPosition; + } + + /** Null unless this row belongs to a foreign key. */ + public Integer getPositionInUniqueConstraint() { + return positionInUniqueConstraint; + } + + public String getReferencedTableSchema() { + return referencedTableSchema; + } + + public String getReferencedTableName() { + return referencedTableName; + } + + public String getReferencedColumnName() { + return referencedColumnName; + } + } + + /** + * Builds the TABLE_CONSTRAINTS rows of a table. + * + * <p>The primary key is always named PRIMARY here, even when it came from a constraint + * the user named something else, because that is the name clients look for. + */ + public static List<ConstraintRow> buildConstraintRows(TableIf table) { + List<ConstraintRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + if (!findPrimaryKeyColumns(table, constraints).isEmpty()) { + rows.add(new ConstraintRow(PRIMARY_KEY_NAME, Constraint.ConstraintType.PRIMARY_KEY.getName())); + } + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (entry.getValue() instanceof UniqueConstraint) { + rows.add(new ConstraintRow(entry.getKey(), Constraint.ConstraintType.UNIQUE.getName())); + } else if (entry.getValue() instanceof ForeignKeyConstraint) { + rows.add(new ConstraintRow(entry.getKey(), Constraint.ConstraintType.FOREIGN_KEY.getName())); + } + } + return rows; + } + + /** Builds the KEY_COLUMN_USAGE rows of a table. */ + public static List<KeyColumnUsageRow> buildKeyColumnUsageRows(TableIf table) { + List<KeyColumnUsageRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + int position = 1; + for (Column column : findPrimaryKeyColumns(table, constraints)) { + rows.add(new KeyColumnUsageRow(PRIMARY_KEY_NAME, column.getName(), position++, + null, null, null, null)); + } + + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + Constraint constraint = entry.getValue(); + if (constraint instanceof UniqueConstraint) { + position = 1; + for (Column column : orderBySchema(table, ((UniqueConstraint) constraint).getUniqueColumnNames())) { + rows.add(new KeyColumnUsageRow(entry.getKey(), column.getName(), position++, + null, null, null, null)); + } + } else if (constraint instanceof ForeignKeyConstraint) { + ForeignKeyConstraint foreignKey = (ForeignKeyConstraint) constraint; + TableNameInfo referenced = foreignKey.getReferencedTableName(); + position = 1; + // The map keeps the order the foreign key was declared in, so the nth local + // column pairs with the nth column of the key it references. + for (Map.Entry<String, String> pair : foreignKey.getForeignToReference().entrySet()) { + rows.add(new KeyColumnUsageRow(entry.getKey(), pair.getKey(), position, + position, referenced == null ? null : referenced.getDb(), + referenced == null ? null : referenced.getTbl(), pair.getValue())); + position++; + } + } + } + return rows; + } + + private static void addRows(List<KeyRow> rows, TableIf table, String indexName, List<Column> columns, + boolean nonUnique, Long cardinality, String indexType, String comment, String properties) { + String collation = BTREE.equals(indexType) ? ASCENDING : null; + int seq = 1; + for (Column column : columns) { + rows.add(new KeyRow(table.getName(), nonUnique, indexName, seq++, column.getName(), collation, + cardinality, column.isAllowNull(), indexType, comment, properties)); + } + } + + /** + * A declared PRIMARY KEY constraint wins over the table model, so that the owner of a + * DUPLICATE KEY table whose data really is unique can make their table usable from + * ODBC and JDBC with a single ALTER TABLE. + */ + private static List<Column> findPrimaryKeyColumns(TableIf table, Map<String, Constraint> constraints) { + for (Constraint constraint : sortedByName(constraints).values()) { + if (constraint instanceof PrimaryKeyConstraint) { + List<Column> columns = orderBySchema(table, ((PrimaryKeyConstraint) constraint).getPrimaryKeyNames()); + if (!columns.isEmpty()) { + return columns; + } + } + } + if (table instanceof OlapTable) { + KeysType keysType = ((OlapTable) table).getKeysType(); + if (keysType == KeysType.UNIQUE_KEYS || keysType == KeysType.AGG_KEYS + || keysType == KeysType.PRIMARY_KEYS) { + return keyColumnsOf(table); + } + } + return Lists.newArrayList(); + } + + private static List<Column> keyColumnsOf(TableIf table) { + List<Column> columns = Lists.newArrayList(); + for (Column column : table.getBaseSchema()) { + if (column.isKey()) { + columns.add(column); + } + } + return columns; + } + + /** + * Constraints hold their columns in an unordered set, but the key sequence a client + * reads has to be stable and has to match the storage order, so resolve the order + * from the table schema. + */ + private static List<Column> orderBySchema(TableIf table, Set<String> columnNames) { + List<Column> columns = Lists.newArrayList(); + for (Column column : table.getBaseSchema()) { + for (String columnName : columnNames) { + if (column.getName().equalsIgnoreCase(columnName)) { + columns.add(column); + break; + } + } + } + return columns; + } + + private static Map<String, Constraint> sortedByName(Map<String, Constraint> constraints) { + Map<String, Constraint> sorted = new TreeMap<>(String.CASE_INSENSITIVE_ORDER); + sorted.putAll(constraints); + return sorted; + } + + private static Map<String, Constraint> getConstraints(TableIf table) { + TableNameInfo tableNameInfo = TableNameInfoUtils.fromTableOrNull(table); + if (tableNameInfo == null) { + return Collections.emptyMap(); + } + return Env.getCurrentEnv().getConstraintManager().getConstraints(tableNameInfo); + } + + /** + * A unique key is distinct on every row, so its cardinality is the row count. An + * unreported row count is left unknown rather than reported as zero, which a client + * would read as "this index selects nothing". + */ + private static Long tableCardinality(TableIf table) { + try { + long rowCount = table.getRowCount(); Review Comment: This metadata path can block on remote connector I/O. `ExternalTable.getRowCount()` initializes the table and uses the fill-enabled row-count cache; on this FE RPC thread there is no `ConnectContext`, so it waits on the loader, which may fetch remote statistics or list files. Use the existing nonblocking cached-row-count API (or return NULL) for schema metadata. ########## fe/fe-core/src/main/java/org/apache/doris/catalog/TableKeyMeta.java: ########## @@ -0,0 +1,460 @@ +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. + +package org.apache.doris.catalog; + +import org.apache.doris.catalog.constraint.Constraint; +import org.apache.doris.catalog.constraint.ForeignKeyConstraint; +import org.apache.doris.catalog.constraint.PrimaryKeyConstraint; +import org.apache.doris.catalog.constraint.UniqueConstraint; +import org.apache.doris.catalog.info.TableNameInfo; +import org.apache.doris.info.TableNameInfoUtils; + +import com.google.common.collect.Lists; + +import java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.TreeMap; + +/** + * Describes the keys and indexes of a table the way MySQL does, as one row per + * (index, column) pair. + * + * <p>MySQL clients discover the primary key of a table through {@code SHOW KEYS} and + * {@code information_schema.STATISTICS}, both of which are shaped like this. The MySQL + * ODBC driver, for instance, answers {@code SQLPrimaryKeys} and {@code SQLStatistics} + * by running {@code SHOW KEYS FROM `db`.`tbl`} and looking for rows whose key name is + * exactly {@code PRIMARY}. Producing those rows in one place keeps every such surface + * telling the same story. + * + * <p>What counts as the primary key, in priority order: + * <ol> + * <li>A user declared {@code PRIMARY KEY} constraint, if the table has one.</li> + * <li>The key columns of a UNIQUE KEY or AGGREGATE KEY table. Both models make the + * key columns unique, so they are a faithful primary key.</li> + * </ol> + * The key columns of a DUPLICATE KEY table are only a sort prefix and are <em>not</em> + * unique, so they are reported as a non-unique index instead. Reporting them as a + * primary key would let a client such as Access believe it can address a single row by + * them, which silently corrupts edits. + */ +public class TableKeyMeta { + /** MySQL's reserved name for the primary key. Clients match on this exact string. */ + public static final String PRIMARY_KEY_NAME = "PRIMARY"; + /** Name reported for the sort prefix of a DUPLICATE KEY table. */ + public static final String DUPLICATE_KEY_NAME = "DUPLICATE"; + + private static final String BTREE = "BTREE"; + private static final String ASCENDING = "A"; + + private TableKeyMeta() {} + + /** One (index, column) pair, i.e. one row of SHOW KEYS or information_schema.STATISTICS. */ + public static class KeyRow { + private final String tableName; + private final boolean nonUnique; + private final String indexName; + private final int seqInIndex; + private final String columnName; + private final String collation; + private final Long cardinality; + private final boolean nullable; + private final String indexType; + private final String comment; + private final String properties; + + public KeyRow(String tableName, boolean nonUnique, String indexName, int seqInIndex, String columnName, + String collation, Long cardinality, boolean nullable, String indexType, String comment, + String properties) { + this.tableName = tableName; + this.nonUnique = nonUnique; + this.indexName = indexName; + this.seqInIndex = seqInIndex; + this.columnName = columnName; + this.collation = collation; + this.cardinality = cardinality; + this.nullable = nullable; + this.indexType = indexType; + this.comment = comment; + this.properties = properties; + } + + public String getTableName() { + return tableName; + } + + public boolean isNonUnique() { + return nonUnique; + } + + public String getIndexName() { + return indexName; + } + + public int getSeqInIndex() { + return seqInIndex; + } + + public String getColumnName() { + return columnName; + } + + /** "A" for an ordered index, null when the order is not meaningful. */ + public String getCollation() { + return collation; + } + + /** Estimated distinct values, or null when unknown. */ + public Long getCardinality() { + return cardinality; + } + + public boolean isNullable() { + return nullable; + } + + public String getIndexType() { + return indexType; + } + + public String getComment() { + return comment; + } + + /** Doris specific index properties. Empty for keys derived from the table model. */ + public String getProperties() { + return properties; + } + } + + /** + * Builds every key row of a table, primary key first. + * + * <p>The caller is expected to hold a read lock on the table. + */ + public static List<KeyRow> buildKeyRows(TableIf table) { + List<KeyRow> rows = Lists.newArrayList(); + Map<String, Constraint> constraints = getConstraints(table); + + List<Column> primaryKeyColumns = findPrimaryKeyColumns(table, constraints); + if (!primaryKeyColumns.isEmpty()) { + addRows(rows, table, PRIMARY_KEY_NAME, primaryKeyColumns, false, tableCardinality(table), BTREE, "", ""); + } + + // Unique constraints are declared rather than enforced, but they are the user's own + // statement about the data, so report them as unique indexes. + for (Map.Entry<String, Constraint> entry : sortedByName(constraints).entrySet()) { + if (!(entry.getValue() instanceof UniqueConstraint)) { + continue; + } + List<Column> columns = orderBySchema(table, ((UniqueConstraint) entry.getValue()).getUniqueColumnNames()); + if (!columns.isEmpty()) { + addRows(rows, table, entry.getKey(), columns, false, tableCardinality(table), BTREE, "", ""); + } + } + + if (primaryKeyColumns.isEmpty() && table instanceof OlapTable + && ((OlapTable) table).getKeysType() == KeysType.DUP_KEYS) { + // Only a sort prefix, so not unique. Still worth reporting: it tells a client + // that a prefix scan on these columns is cheap. + List<Column> sortKeyColumns = keyColumnsOf(table); + if (!sortKeyColumns.isEmpty()) { + addRows(rows, table, DUPLICATE_KEY_NAME, sortKeyColumns, true, null, BTREE, "", ""); + } + } + + if (table instanceof OlapTable) { + for (Index index : ((OlapTable) table).getIndexes()) { + List<Column> columns = Lists.newArrayList(); + for (String columnName : index.getColumns()) { + Column column = table.getColumn(columnName); + if (column != null) { + columns.add(column); + } + } + if (columns.isEmpty()) { + continue; + } + // A secondary index imposes no order on its columns, so MySQL reports no collation. + addRows(rows, table, index.getIndexName(), columns, true, null, + index.getIndexType().name(), index.getComment(), index.getPropertiesString()); + } + } + return rows; + } + + /** + * The COLUMN_KEY value MySQL reports for each column of a table: PRI for a column of the + * primary key, UNI for the first column of a unique index, MUL for the first column of a + * non-unique one. Columns that are none of these are absent from the map. + * + * <p>Derived from the same rows as SHOW KEYS, so the two always agree. + */ + public static Map<String, String> buildColumnKeys(TableIf table) { + Map<String, String> columnKeys = new HashMap<>(); + for (KeyRow row : buildKeyRows(table)) { + String value; + if (PRIMARY_KEY_NAME.equals(row.getIndexName())) { + value = "PRI"; + } else if (row.getSeqInIndex() != 1) { + // Only the leading column of an index gets a marker. + continue; + } else { + value = row.isNonUnique() ? "MUL" : "UNI"; Review Comment: A composite UNIQUE key does not make its leading column individually unique, but this branch always marks its first row `UNI`. For `(a,b)` with repeated `a`, MySQL's `COLUMN_KEY` may be `MUL`, and the current value can make clients infer a single-column unique key. Distinguish single-column UNIQUE from composite UNIQUE when building column markers. -- 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]
