hint联表操作操作不能跨数据库

db_user · 2022 年4 月 27 日 09:32

【 TiDB 使用环境】生产环境 or 测试环境 or POC
生产环境
【 TiDB 版本】
4.0.14
【遇到的问题】
联表操作不能跨库
【复现路径】做过哪些操作出现的问题
use test_db1;
create table t1(a int, index(a));
create table t2(a int, index(a));

use test_db2;
create table t2(a int, index(a));

explain analyze select /*+ inl_join(t1,t2) */ * from test_db1.t1 join test_db1.t2 on t1.a = t2.a;
结果正常

explain analyze select /*+ inl_join(t1,t2) */ * from test_db1.t1 join test_db2.t2 on t1.a = t2.a;
结果异常

【问题现象及影响】

不能正常hint

相似的issue有

github.com/pingcap/tidb

inl_hash_join does not work as expected

已打开 06:29AM - 12 Mar 20 UTC

已关闭 02:20PM - 17 Mar 20 UTC

XuHuaiyu

type/bug sig/planner severity/major

## Bug Report Please answer these questions before submitting your issue. Tha…nks! 1. What did you do? ``` go tidb> use test tidb> create table t1(a int, index(a)); Query OK, 0 rows affected (0.01 sec) tidb> create table t2(a int, index(a)); Query OK, 0 rows affected (0.01 sec) tidb> use test2 tidb> desc select /*+ inl_hash_join(test.t1) */ * from test.t1 join test.t2 on test.t1.a = test.t2.a; +-----------------------------+----------+-----------+-----------------------------------------------------+ | id | estRows | task | operator info | +-----------------------------+----------+-----------+-----------------------------------------------------+ | MergeJoin_7 | 12487.50 | root | inner join, left key:test.t1.a, right key:test.t2.a | | ├─IndexReader_35(Build) | 9990.00 | root | index:IndexFullScan_34 | | │ └─IndexFullScan_34 | 9990.00 | cop[tikv] | table:t2, index:a, keep order:true, stats:pseudo | | └─IndexReader_33(Probe) | 9990.00 | root | index:IndexFullScan_32 | | └─IndexFullScan_32 | 9990.00 | cop[tikv] | table:t1, index:a, keep order:true, stats:pseudo | +-----------------------------+----------+-----------+-----------------------------------------------------+ 5 rows in set, 1 warning (0.00 sec) tidb> show warnings; +---------+------+-------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1815 | There are no matching table names for (t1) in optimizer hint /*+ INL_HASH_JOIN(t1) */. Maybe you can use the table alias name | +---------+------+-------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` 2. What did you expect to see? indexHashJoin can be chosen forcibly 4. What version of TiDB are you using (`tidb-server -V` or run `select tidb_version();` on TiDB)? ``` sql Release Version: v4.0.0-beta-345-gf51a7fc6e Git Commit Hash: f51a7fc6e9b24fc0c0eb1ecb6da2e67ad8a34be4 Git Branch: master UTC Build Time: 2020-03-12 06:26:29 GoVersion: go1.13 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false ```

github.com/pingcap/tidb

planner: hints cannot work for apply

已打开 06:03AM - 30 Aug 21 UTC

qw4990

type/bug sig/planner severity/minor

## Bug Report Please answer these questions before submitting your issue. Tha…nks! ### 1. Minimal reproduce step (Required) ``` create table t1 (a int, key(a)); create table t2 (a int, key(a)); mysql> explain select /*+ SRTEAM_AGG(@q2) */ (select/*+ QB_NAME(q2) */ sum(t2.a) from t2 where t2.a=t1.a) as s, t1.a from t1; +----------------------------------+----------+-----------+----------------------+------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+----------+-----------+----------------------+------------------------------------------------------------------------------------------+ | Projection_10 | 10000.00 | root | | Column#7, test.t1.a | | └─HashJoin_12 | 10000.00 | root | | left outer join, equal:[eq(test.t1.a, test.t2.a)] | | ├─HashAgg_29(Build) | 7992.00 | root | | group by:Column#16, funcs:sum(Column#14)->Column#7, funcs:firstrow(Column#15)->test.t2.a | | │ └─Projection_39 | 9990.00 | root | | cast(test.t2.a, decimal(32,0) BINARY)->Column#14, test.t2.a, test.t2.a | | │ └─IndexReader_36 | 9990.00 | root | | index:IndexFullScan_35 | | │ └─IndexFullScan_35 | 9990.00 | cop[tikv] | table:t2, index:a(a) | keep order:false, stats:pseudo | | └─TableReader_24(Probe) | 10000.00 | root | | data:TableFullScan_23 | | └─TableFullScan_23 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo | +----------------------------------+----------+-----------+----------------------+------------------------------------------------------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) mysql> mysql> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use [parser:8064]Optimizer hint syntax error at line 1 column 29 near "SRTEAM_AGG(@q2) */" | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select /*+ INL_JOIN(t2@q2) */ (select /*+ QB_NAME(q2) */ sum(t2.a) from t2 where t2.a=t1.a) as s, t1.a from t1; +----------------------------------+----------+-----------+----------------------+------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+----------+-----------+----------------------+------------------------------------------------------------------------------------------+ | Projection_10 | 10000.00 | root | | Column#7, test.t1.a | | └─HashJoin_12 | 10000.00 | root | | left outer join, equal:[eq(test.t1.a, test.t2.a)] | | ├─HashAgg_29(Build) | 7992.00 | root | | group by:Column#16, funcs:sum(Column#14)->Column#7, funcs:firstrow(Column#15)->test.t2.a | | │ └─Projection_39 | 9990.00 | root | | cast(test.t2.a, decimal(32,0) BINARY)->Column#14, test.t2.a, test.t2.a | | │ └─IndexReader_36 | 9990.00 | root | | index:IndexFullScan_35 | | │ └─IndexFullScan_35 | 9990.00 | cop[tikv] | table:t2, index:a(a) | keep order:false, stats:pseudo | | └─TableReader_24(Probe) | 10000.00 | root | | data:TableFullScan_23 | | └─TableFullScan_23 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo | +----------------------------------+----------+-----------+----------------------+------------------------------------------------------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1815 | There are no matching table names for (t2) in optimizer hint /*+ INL_JOIN(t2) */ or /*+ TIDB_INLJ(t2) */. Maybe you can use the table alias name | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` If the subquery is not in select field, both can work: ``` mysql> explain select /*+ STREAM_AGG(@q2) */ * from t1 where (select /*+ QB_NAME(q2) */ count(*) from t2 where t2.a=t1.a)>1; +----------------------------------+---------+-----------+----------------------+-------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+----------------------+-------------------------------------------------------------------------------------------+ | MergeJoin_14 | 7992.00 | root | | inner join, left key:test.t1.a, right key:test.t2.a | | ├─Selection_30(Build) | 6393.60 | root | | gt(ifnull(Column#5, 0), 1) | | │ └─StreamAgg_37 | 7992.00 | root | | group by:test.t2.a, funcs:count(Column#6)->Column#5, funcs:firstrow(test.t2.a)->test.t2.a | | │ └─IndexReader_38 | 7992.00 | root | | index:StreamAgg_31 | | │ └─StreamAgg_31 | 7992.00 | cop[tikv] | | group by:test.t2.a, funcs:count(1)->Column#6 | | │ └─IndexFullScan_36 | 9990.00 | cop[tikv] | table:t2, index:a(a) | keep order:true, stats:pseudo | | └─IndexReader_29(Probe) | 9990.00 | root | | index:IndexFullScan_28 | | └─IndexFullScan_28 | 9990.00 | cop[tikv] | table:t1, index:a(a) | keep order:true, stats:pseudo | +----------------------------------+---------+-----------+----------------------+-------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> explain select /*+ INL_JOIN(t2@q2) */ * from t1 where exists (select /*+ QB_NAME(q2) */ 1 from t2 where t2.a=t1.a); +-----------------------------+---------+-----------+----------------------+----------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-----------------------------+---------+-----------+----------------------+----------------------------------------------------------------------------------------------------------------+ | IndexJoin_13 | 7992.00 | root | | semi join, inner:IndexReader_12, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a) | | ├─IndexReader_22(Build) | 9990.00 | root | | index:IndexFullScan_21 | | │ └─IndexFullScan_21 | 9990.00 | cop[tikv] | table:t1, index:a(a) | keep order:false, stats:pseudo | | └─IndexReader_12(Probe) | 1.25 | root | | index:Selection_11 | | └─Selection_11 | 1.25 | cop[tikv] | | not(isnull(test.t2.a)) | | └─IndexRangeScan_10 | 1.25 | cop[tikv] | table:t2, index:a(a) | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false, stats:pseudo | +-----------------------------+---------+-----------+----------------------+----------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) ``` ### 2. What did you expect to see? (Required) ### 3. What did you see instead (Required) ### 4. What is your TiDB version? (Required) ``` +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Release Version: v5.2.0-alpha-763-g04e144aeb-dirty Edition: Community Git Commit Hash: 04e144aeb58a33c0338c6a391f794b2f4c349edd Git Branch: master UTC Build Time: 2021-08-30 04:32:37 GoVersion: go1.16.3 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ ```

github.com/pingcap/tidb

INL_JOIN hint is not used correctly

已打开 07:26AM - 26 Mar 21 UTC

wjhuang2016

type/bug sig/planner severity/moderate component/tablepartition

## Bug Report Please answer these questions before submitting your issue. Tha…nks! ### 1. Minimal reproduce step (Required) ``` sql CREATE TABLE `tbl_1` ( `col_5` decimal(47,21) NOT NULL DEFAULT '5308.88', `col_6` enum('Alice','Bob','Charlie','David') DEFAULT NULL, `col_7` float NOT NULL, `col_8` bigint(20) NOT NULL DEFAULT '-688199144806783096', `col_9` varchar(248) NOT NULL, PRIMARY KEY (`col_5`,`col_7`,`col_9`,`col_8`) /*T![clustered_index] CLUSTERED */, UNIQUE KEY `idx_3` (`col_9`(3),`col_8`), UNIQUE KEY `idx_4` (`col_8`), KEY `idx_5` (`col_7`), KEY `idx_6` (`col_7`), UNIQUE KEY `idx_7` (`col_5`,`col_7`,`col_8`), UNIQUE KEY `idx_8` (`col_7`,`col_6`,`col_8`,`col_5`), UNIQUE KEY `idx_9` (`col_9`,`col_8`), KEY `idx_10` (`col_9`,`col_5`), KEY `idx_11` (`col_5`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin PARTITION BY HASH( `col_8` ) PARTITIONS 4; CREATE TABLE `tbl_2` ( `col_10` decimal(10,0) DEFAULT '142', `col_11` enum('Alice','Bob','Charlie','David') NOT NULL, `col_12` datetime NOT NULL DEFAULT '2017-11-14 00:00:00', `col_13` double NOT NULL, `col_14` mediumint(9) NOT NULL DEFAULT '-2341051', PRIMARY KEY (`col_13`,`col_12`,`col_14`) /*T![clustered_index] CLUSTERED */, KEY `idx_13` (`col_11`,`col_14`,`col_10`), UNIQUE KEY `idx_14` (`col_14`,`col_11`,`col_12`), KEY `idx_15` (`col_12`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; desc select /*+ INL_JOIN(tbl_1) */ tbl_1.col_8 from tbl_1 where col_7 in ( select col_13 from tbl_2 where tbl_2.col_12 < '2018-05-28' ); desc select /*+ INL_JOIN(tbl_1) */ tbl_1.col_8 from tbl_1 where col_7 in ( select col_11 from tbl_2 where tbl_2.col_12 < '2018-05-28' ); ``` ### 2. What did you expect to see? (Required) ``` use the index hash join ``` ### 3. What did you see instead (Required) ``` sql mysql> desc select /*+ INL_JOIN(tbl_1) */ tbl_1.col_8 from tbl_1 where col_7 in ( select col_13 from tbl_2 where tbl_2.col_12 < '2018-05-28' ); +---------------------------------+----------+-----------+---------------------------+----------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+----------+-----------+---------------------------+----------------------------------------------------------------------------------+ | HashJoin_16 | 2.50 | root | | inner join, equal:[eq(test.tbl_1.col_7, test.tbl_2.col_13)] | | ├─StreamAgg_103(Build) | 2.00 | root | | group by:test.tbl_2.col_13, funcs:firstrow(test.tbl_2.col_13)->test.tbl_2.col_13 | | │ └─TableReader_115 | 2.00 | root | | data:Selection_114 | | │ └─Selection_114 | 2.00 | cop[tikv] | | lt(test.tbl_2.col_12, 2018-05-28 00:00:00.000000) | | │ └─TableFullScan_113 | 2.00 | cop[tikv] | table:tbl_2 | keep order:true | | └─PartitionUnion_18(Probe) | 40000.00 | root | | | | ├─TableReader_20 | 10000.00 | root | | data:TableFullScan_19 | | │ └─TableFullScan_19 | 10000.00 | cop[tikv] | table:tbl_1, partition:p0 | keep order:false, stats:pseudo | | ├─TableReader_40 | 10000.00 | root | | data:TableFullScan_39 | | │ └─TableFullScan_39 | 10000.00 | cop[tikv] | table:tbl_1, partition:p1 | keep order:false, stats:pseudo | | ├─TableReader_60 | 10000.00 | root | | data:TableFullScan_59 | | │ └─TableFullScan_59 | 10000.00 | cop[tikv] | table:tbl_1, partition:p2 | keep order:false, stats:pseudo | | └─TableReader_80 | 10000.00 | root | | data:TableFullScan_79 | | └─TableFullScan_79 | 10000.00 | cop[tikv] | table:tbl_1, partition:p3 | keep order:false, stats:pseudo | +---------------------------------+----------+-----------+---------------------------+----------------------------------------------------------------------------------+ 14 rows in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------+ | Warning | 1815 | Optimizer Hint /*+ INL_JOIN(tbl_1) */ or /*+ TIDB_INLJ(tbl_1) */ is inapplicable | +---------+------+----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> desc select /*+ INL_JOIN(tbl_1) */ tbl_1.col_8 from tbl_1 where col_7 in ( select col_11 from tbl_2 where tbl_2.col_12 < '2018-05-28' ); +--------------------------------+----------+-----------+---------------------------+----------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+----------+-----------+---------------------------+----------------------------------------------------+ | HashJoin_16 | 32000.00 | root | | semi join, equal:[eq(test.tbl_1.col_7, Column#11)] | | ├─Projection_98(Build) | 2.00 | root | | cast(test.tbl_2.col_11, double BINARY)->Column#11 | | │ └─TableReader_101 | 2.00 | root | | data:Selection_100 | | │ └─Selection_100 | 2.00 | cop[tikv] | | lt(test.tbl_2.col_12, 2018-05-28 00:00:00.000000) | | │ └─TableFullScan_99 | 2.00 | cop[tikv] | table:tbl_2 | keep order:false | | └─PartitionUnion_17(Probe) | 40000.00 | root | | | | ├─TableReader_19 | 10000.00 | root | | data:TableFullScan_18 | | │ └─TableFullScan_18 | 10000.00 | cop[tikv] | table:tbl_1, partition:p0 | keep order:false, stats:pseudo | | ├─TableReader_39 | 10000.00 | root | | data:TableFullScan_38 | | │ └─TableFullScan_38 | 10000.00 | cop[tikv] | table:tbl_1, partition:p1 | keep order:false, stats:pseudo | | ├─TableReader_59 | 10000.00 | root | | data:TableFullScan_58 | | │ └─TableFullScan_58 | 10000.00 | cop[tikv] | table:tbl_1, partition:p2 | keep order:false, stats:pseudo | | └─TableReader_79 | 10000.00 | root | | data:TableFullScan_78 | | └─TableFullScan_78 | 10000.00 | cop[tikv] | table:tbl_1, partition:p3 | keep order:false, stats:pseudo | +--------------------------------+----------+-----------+---------------------------+----------------------------------------------------+ 14 rows in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1815 | There are no matching table names for (tbl_1) in optimizer hint /*+ INL_JOIN(tbl_1) */ or /*+ TIDB_INLJ(tbl_1) */. Maybe you can use the table alias name | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ### 4. What is your TiDB version? (Required) master

但是没看到解决方案

【附件】

请提供各个组件的 version 信息，如 cdc/tikv，可通过执行 cdc version/tikv-server --version 获取。

tidb狂热爱好者 · 2022 年7 月 1 日 04:53

那就只能先放在同一个数据库里面查询