能举一个不能走的例子吗？？？
我举两个可以走的，MySQL和OceanBase社区版都可以走。
MySQL：
--表结构：
create table a(id int,name varchar(100));
create table b(id int,name varchar(100));
alter table b add index(id);
insert into a values (1,'1'),(1,'11'),(2,'2');
insert into b values (1,'1'),(1,'22'),(2,'2');
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30 |
+-----------+
1 row in set (0.00 sec)
--TiDB中不能走，MySQL中可以走：
mysql> explain analyze select /*+ NO_BNL(a,b) */ * from a,b where a.id=b.id and a.name=substr(b.name,1,10);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=2.12 rows=4) (actual time=0.036..0.046 rows=2 loops=1)
-> Filter: (a.id is not null) (cost=0.55 rows=3) (actual time=0.019..0.021 rows=3 loops=1)
-> Table scan on a (cost=0.55 rows=3) (actual time=0.018..0.020 rows=3 loops=1)
-> Filter: (a.`name` = substr(b.`name`,1,10)) (cost=0.42 rows=2) (actual time=0.006..0.008 rows=1 loops=3)
-> Index lookup on b using id (id=a.id) (cost=0.42 rows=2) (actual time=0.005..0.006 rows=2 loops=3)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
OceanBase
--表结构：
create table a(id int,name varchar(100));
create table b(id int,name varchar(100));
alter table b add index(id);
insert into a values (1,'1'),(1,'11'),(2,'2');
insert into b values (1,'1'),(1,'22'),(2,'2');
obclient [test]> select version();
+------------------------------+
| version() |
+------------------------------+
| 5.7.25-OceanBase_CE-v4.2.0.0 |
+------------------------------+
1 row in set (0.001 sec)
--TiDB中不能走，OceanBase中可以走：
obclient [test]> explain extended pretty_color select /*+ USE_NL(a,b) leading(a,b) */ * from a,b where a.id=b.id and a.name=substr(b.name,1,10);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN | |2 |71 | |
| |1 |├─TABLE FULL SCAN |a |3 |4 | |
| |2 |└─DISTRIBUTED TABLE RANGE SCAN|b(id)|1 |22 | |
| =============================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([a.id(0x7f4b8ce40f50)], [a.name(0x7f4b8ce431e0)], [b.id(0x7f4b8ce41230)], [b.name(0x7f4b8ce434c0)]), filter(nil), rowset=256 |
| conds(nil), nl_params_([a.id(0x7f4b8ce40f50)(:0)], [a.name(0x7f4b8ce431e0)(:1)]), use_batch=true |
| 1 - output([a.id(0x7f4b8ce40f50)], [a.name(0x7f4b8ce431e0)]), filter(nil), rowset=256 |
| access([a.id(0x7f4b8ce40f50)], [a.name(0x7f4b8ce431e0)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([a.__pk_increment(0x7f4b8ce44470)]), range(MIN ; MAX)always true |
| 2 - output([b.id(0x7f4b8ce41230)], [b.name(0x7f4b8ce434c0)]), filter([:1 = substr(b.name(0x7f4b8ce434c0), 1, 10)(0x7f4b8ce41a90)(0x7f4b8cec23a0)]), rowset=256 |
| access([GROUP_ID(0x7f4b8cef0150)], [b.__pk_increment(0x7f4b8ce44740)], [b.id(0x7f4b8ce41230)], [b.name(0x7f4b8ce434c0)]), partitions(p0) |
| is_index_back=true, is_global_index=false, filter_before_indexback[false], |
| range_key([b.id(0x7f4b8ce41230)], [b.__pk_increment(0x7f4b8ce44740)]), range(MIN ; MAX), |
| range_cond([:0 = b.id(0x7f4b8ce41230)(0x7f4b8cec18b0)]) |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| LEADING(("a" "b")) |
| USE_NL("b") |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| LEADING(@"SEL$1" ("test"."a"@"SEL$1" "test"."b"@"SEL$1")) |
| USE_NL(@"SEL$1" "test"."b"@"SEL$1") |
| FULL(@"SEL$1" "test"."a"@"SEL$1") |
| INDEX(@"SEL$1" "test"."b"@"SEL$1" "id") |
| USE_DAS(@"SEL$1" "test"."b"@"SEL$1") |
| OPTIMIZER_FEATURES_ENABLE('4.0.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| a: |
| table_rows:3 |
| physical_range_rows:3 |
| logical_range_rows:3 |
| index_back_rows:0 |
| output_rows:3 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[a] |
| stats version:0 |
| dynamic sampling level:1 |
| b: |
| table_rows:3 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:1 |
| output_rows:0 |
| table_dop:1 |
| dop_method:DAS DOP |
| avaiable_index_name:[id, b] |
| unstable_index_name:[b] |
| stats version:0 |
| dynamic sampling level:1 |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
72 rows in set (0.004 sec)