explain tidb_mdl_view contains CARTESIAN

[together-wang]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

tiup playground v6.5.4
mysql --comments --host 127.0.0.1 --port 4000 -u root

	CreateMDLView = `CREATE OR REPLACE VIEW mysql.tidb_mdl_view as (
		SELECT job_id,
			db_name,
			table_name,
			query,
			session_id,
			txnstart,
			tidb_decode_sql_digests(all_sql_digests, 4096) AS SQL_DIGESTS
		FROM information_schema.ddl_jobs,
			information_schema.cluster_tidb_trx,
			information_schema.cluster_processlist
		WHERE (ddl_jobs.state != 'synced' and ddl_jobs.state != 'cancelled')
			AND Find_in_set(ddl_jobs.table_id, cluster_tidb_trx.related_table_ids)
			AND cluster_tidb_trx.session_id = cluster_processlist.id
	);`

tidb/session/bootstrap.go

Line 460 in a8bd506

CreateMDLView = `CREATE OR REPLACE VIEW mysql.tidb_mdl_view as (

mysql> explain select * from mysql.tidb_mdl_view;
+-------------------------------------+-------------+-----------+---------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                  | estRows     | task      | access object             | operator info                                                                                                                                                                                                                           |
+-------------------------------------+-------------+-----------+---------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_14                       | 99900000.00 | root      |                           | Column#1, Column#2, Column#3, Column#13, information_schema.cluster_tidb_trx.session_id, information_schema.cluster_processlist.txnstart, tidb_decode_sql_digests(information_schema.cluster_tidb_trx.all_sql_digests, 4096)->Column#43 |
| └─Projection_15                     | 99900000.00 | root      |                           | Column#1, Column#2, Column#3, Column#13, information_schema.cluster_tidb_trx.session_id, information_schema.cluster_tidb_trx.all_sql_digests, information_schema.cluster_processlist.txnstart                                           |
|   └─HashJoin_17                     | 99900000.00 | root      |                           | CARTESIAN inner join, other cond:find_in_set(cast(Column#7, var_string(20)), information_schema.cluster_tidb_trx.related_table_ids)                                                                                                     |
|     ├─HashJoin_18(Build)            | 12487.50    | root      |                           | inner join, equal:[eq(information_schema.cluster_tidb_trx.session_id, information_schema.cluster_processlist.id)]                                                                                                                       |
|     │ ├─TableReader_24(Build)       | 10000.00    | root      |                           | data:TableFullScan_23                                                                                                                                                                                                                   |
|     │ │ └─TableFullScan_23          | 10000.00    | cop[tidb] | table:CLUSTER_PROCESSLIST | keep order:false, stats:pseudo                                                                                                                                                                                                          |
|     │ └─TableReader_22(Probe)       | 9990.00     | root      |                           | data:Selection_21                                                                                                                                                                                                                       |
|     │   └─Selection_21              | 9990.00     | cop[tidb] |                           | not(isnull(information_schema.cluster_tidb_trx.session_id))                                                                                                                                                                             |
|     │     └─TableFullScan_20        | 10000.00    | cop[tidb] | table:CLUSTER_TIDB_TRX    | keep order:false, stats:pseudo                                                                                                                                                                                                          |
|     └─Selection_25(Probe)           | 8000.00     | root      |                           | eq(Column#12, "running")                                                                                                                                                                                                                |
|       └─MemTableScan_26             | 10000.00    | root      | table:DDL_JOBS            |                                                                                                                                                                                                                                         |
+-------------------------------------+-------------+-----------+---------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.01 sec)

2. What did you expect to see? (Required)

The execution plan contains CARTESIAN, and as the production ddl increases, the view query becomes slower and slower. Optimization is recommended.

3. What did you see instead (Required)

mysql> explain select * from mysql.tidb_mdl_view;
+-------------------------------------+-------------+-----------+---------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                  | estRows     | task      | access object             | operator info                                                                                                                                                                                                                           |
+-------------------------------------+-------------+-----------+---------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_14                       | 99900000.00 | root      |                           | Column#1, Column#2, Column#3, Column#13, information_schema.cluster_tidb_trx.session_id, information_schema.cluster_processlist.txnstart, tidb_decode_sql_digests(information_schema.cluster_tidb_trx.all_sql_digests, 4096)->Column#43 |
| └─Projection_15                     | 99900000.00 | root      |                           | Column#1, Column#2, Column#3, Column#13, information_schema.cluster_tidb_trx.session_id, information_schema.cluster_tidb_trx.all_sql_digests, information_schema.cluster_processlist.txnstart                                           |
|   └─HashJoin_17                     | 99900000.00 | root      |                           | CARTESIAN inner join, other cond:find_in_set(cast(Column#7, var_string(20)), information_schema.cluster_tidb_trx.related_table_ids)                                                                                                     |
|     ├─HashJoin_18(Build)            | 12487.50    | root      |                           | inner join, equal:[eq(information_schema.cluster_tidb_trx.session_id, information_schema.cluster_processlist.id)]                                                                                                                       |
|     │ ├─TableReader_24(Build)       | 10000.00    | root      |                           | data:TableFullScan_23                                                                                                                                                                                                                   |
|     │ │ └─TableFullScan_23          | 10000.00    | cop[tidb] | table:CLUSTER_PROCESSLIST | keep order:false, stats:pseudo                                                                                                                                                                                                          |
|     │ └─TableReader_22(Probe)       | 9990.00     | root      |                           | data:Selection_21                                                                                                                                                                                                                       |
|     │   └─Selection_21              | 9990.00     | cop[tidb] |                           | not(isnull(information_schema.cluster_tidb_trx.session_id))                                                                                                                                                                             |
|     │     └─TableFullScan_20        | 10000.00    | cop[tidb] | table:CLUSTER_TIDB_TRX    | keep order:false, stats:pseudo                                                                                                                                                                                                          |
|     └─Selection_25(Probe)           | 8000.00     | root      |                           | eq(Column#12, "running")                                                                                                                                                                                                                |
|       └─MemTableScan_26             | 10000.00    | root      | table:DDL_JOBS            |                                                                                                                                                                                                                                         |
+-------------------------------------+-------------+-----------+---------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

v6.5.4
ALL

[winoros]

find_in_set can not perform as join key.
So there's a CARTESIAN product.
It's correct for the optimizer.
But maybe this SQL can be rewritten.

[lcwangchao]

@wjhuang2016 PTAL

[seiya-annie]

/found gs

[jebter]

/label affects-6.5

[D3Hunter]

workaround if querying tidb_mdl_view is slow, create a new view using this:

limition: only works for DDL with only 1 table, more than 1 won't work, such as exchange partition, rename multiple tables using rename table. (tidb_mdl_view might not working for this case too, see #47743)

CREATE OR REPLACE VIEW new_tidb_mdl_view as (
    SELECT job_id,
        json_extract(cast(cast(job_meta as char) as json), '$.schema_name') as db_name,
        json_extract(cast(cast(job_meta as char) as json), '$.table_name') as table_name,
        json_extract(cast(cast(job_meta as char) as json), '$.query') as query,
        session_id,
        txnstart,
        tidb_decode_sql_digests(all_sql_digests, 4096) AS SQL_DIGESTS
    FROM mysql.tidb_ddl_job,
        information_schema.cluster_tidb_trx,
        information_schema.cluster_processlist
    WHERE tidb_ddl_job.processing = 1
        AND Find_in_set(json_extract(cast(cast(job_meta as char) as json), '$.table_id'), cluster_tidb_trx.related_table_ids)
        AND cluster_tidb_trx.session_id = cluster_processlist.id
);

[wjhuang2016]

It's not a bug. So I remove the bug label.

[kennedy8312]

candidate_1m