# WL#13425 Transform IN and EXISTS predicates for RAPID
SET SESSION autocommit = 1;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES(1,10),(2,20),(3,30);
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES(1,10),(2,20),(3,30),(1,110),(2,120),(3,130);
ANALYZE TABLE t1,t2;
EXPLAIN format='brief' SELECT * FROM t1 ot
WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
id	estRows	task	access object	operator info
Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─Selection	2.40	root		or(lt(subquery_table_to_derived.t1.b, 0), Column#8)
  └─HashJoin	3.00	root		CARTESIAN left outer semi join, other cond:eq(subquery_table_to_derived.t1.a, Column#7)
    ├─Projection(Build)	6.00	root		plus(subquery_table_to_derived.t2.a, 1)->Column#7
    │ └─TableReader	6.00	root		data:TableFullScan
    │   └─TableFullScan	6.00	cop[tikv]	table:it	keep order:false
    └─TableReader(Probe)	3.00	root		data:TableFullScan
      └─TableFullScan	3.00	cop[tikv]	table:ot	keep order:false
SELECT * FROM t1 ot
WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
a	b
2	20
3	30
EXPLAIN format='brief' SELECT * FROM t1 ot
WHERE ot.b<0 OR (ot.a,ot.a+1,ot.a+2)
IN (SELECT it.a+1 AS myname,it.a+2 AS myname,it.a+3 FROM t2 it
WHERE it.a+3=ot.a+2);
id	estRows	task	access object	operator info
Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─Selection	2.40	root		or(lt(subquery_table_to_derived.t1.b, 0), Column#10)
  └─HashJoin	3.00	root		left outer semi join, equal:[eq(Column#14, Column#15)], other cond:eq(plus(subquery_table_to_derived.t1.a, 1), plus(subquery_table_to_derived.t2.a, 2)), eq(plus(subquery_table_to_derived.t1.a, 2), plus(subquery_table_to_derived.t2.a, 3)), eq(subquery_table_to_derived.t1.a, plus(subquery_table_to_derived.t2.a, 1))
    ├─Projection(Build)	6.00	root		subquery_table_to_derived.t2.a, plus(subquery_table_to_derived.t2.a, 3)->Column#15
    │ └─TableReader	6.00	root		data:TableFullScan
    │   └─TableFullScan	6.00	cop[tikv]	table:it	keep order:false
    └─Projection(Probe)	3.00	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b, plus(subquery_table_to_derived.t1.a, 2)->Column#14
      └─TableReader	3.00	root		data:TableFullScan
        └─TableFullScan	3.00	cop[tikv]	table:ot	keep order:false
SELECT * FROM t1 ot
WHERE ot.b<0 OR (ot.a,ot.a+1,ot.a+2)
IN (SELECT it.a+1 AS myname,it.a+2 AS myname,it.a+3 FROM t2 it
WHERE it.a+3=ot.a+2);
a	b
2	20
3	30
EXPLAIN format='brief' SELECT * FROM t1 ot
WHERE ot.b<0 OR EXISTS(SELECT * FROM t2 it WHERE ot.a=it.a+1);
id	estRows	task	access object	operator info
Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─Selection	2.40	root		or(lt(subquery_table_to_derived.t1.b, 0), Column#7)
  └─HashJoin	3.00	root		left outer semi join, equal:[eq(subquery_table_to_derived.t1.a, Column#8)]
    ├─Projection(Build)	6.00	root		plus(subquery_table_to_derived.t2.a, 1)->Column#8
    │ └─TableReader	6.00	root		data:TableFullScan
    │   └─TableFullScan	6.00	cop[tikv]	table:it	keep order:false
    └─TableReader(Probe)	3.00	root		data:TableFullScan
      └─TableFullScan	3.00	cop[tikv]	table:ot	keep order:false
SELECT * FROM t1 ot
WHERE ot.b<0 OR EXISTS(SELECT * FROM t2 it WHERE ot.a=it.a+1);
a	b
2	20
3	30
EXPLAIN format='brief' SELECT * FROM t1 ot
WHERE ot.b<0 OR EXISTS(SELECT it.b FROM t2 it WHERE ot.a=it.a+1);
id	estRows	task	access object	operator info
Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─Selection	2.40	root		or(lt(subquery_table_to_derived.t1.b, 0), Column#7)
  └─HashJoin	3.00	root		left outer semi join, equal:[eq(subquery_table_to_derived.t1.a, Column#8)]
    ├─Projection(Build)	6.00	root		plus(subquery_table_to_derived.t2.a, 1)->Column#8
    │ └─TableReader	6.00	root		data:TableFullScan
    │   └─TableFullScan	6.00	cop[tikv]	table:it	keep order:false
    └─TableReader(Probe)	3.00	root		data:TableFullScan
      └─TableFullScan	3.00	cop[tikv]	table:ot	keep order:false
SELECT * FROM t1 ot
WHERE ot.b<0 OR EXISTS(SELECT it.b FROM t2 it WHERE ot.a=it.a+1);
a	b
2	20
3	30
EXPLAIN format='brief' SELECT * FROM t1 ot
WHERE ot.b<0 OR EXISTS(SELECT it.b,ot.b FROM t2 it WHERE ot.a=it.a+1);
id	estRows	task	access object	operator info
Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─Selection	2.40	root		or(lt(subquery_table_to_derived.t1.b, 0), Column#8)
  └─HashJoin	3.00	root		left outer semi join, equal:[eq(subquery_table_to_derived.t1.a, Column#9)]
    ├─Projection(Build)	6.00	root		plus(subquery_table_to_derived.t2.a, 1)->Column#9
    │ └─TableReader	6.00	root		data:TableFullScan
    │   └─TableFullScan	6.00	cop[tikv]	table:it	keep order:false
    └─TableReader(Probe)	3.00	root		data:TableFullScan
      └─TableFullScan	3.00	cop[tikv]	table:ot	keep order:false
SELECT * FROM t1 ot
WHERE ot.b<0 OR EXISTS(SELECT it.b,ot.b FROM t2 it WHERE ot.a=it.a+1);
a	b
2	20
3	30
CREATE view v2 AS SELECT * FROM t2;
PREPARE s FROM
"SELECT * FROM t1 ot
WHERE ot.b<0 OR EXISTS(SELECT it.b FROM v2 it WHERE ot.a=it.a+1)";
EXECUTE s;
a	b
2	20
3	30
EXPLAIN format='brief' SELECT * FROM t1 ot
WHERE ot.b<0 OR (ot.b<0 AND (ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it)));
id	estRows	task	access object	operator info
Projection	0.00	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─HashJoin	0.00	root		CARTESIAN left outer semi join, other cond:eq(subquery_table_to_derived.t1.a, Column#7)
  ├─Projection(Build)	6.00	root		plus(subquery_table_to_derived.t2.a, 1)->Column#7
  │ └─TableReader	6.00	root		data:TableFullScan
  │   └─TableFullScan	6.00	cop[tikv]	table:it	keep order:false
  └─TableReader(Probe)	0.00	root		data:Selection
    └─Selection	0.00	cop[tikv]		lt(subquery_table_to_derived.t1.b, 0)
      └─TableFullScan	3.00	cop[tikv]	table:ot	keep order:false
SELECT * FROM t1 ot
WHERE ot.b<0 OR (ot.b<0 AND (ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it)));
a	b
EXPLAIN format='brief' SELECT * FROM t1 ot
WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM (SELECT * FROM t2 LIMIT 1) it
WHERE it.a+3=ot.a+1);
id	estRows	task	access object	operator info
Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─Selection	2.40	root		or(lt(subquery_table_to_derived.t1.b, 0), Column#8)
  └─HashJoin	3.00	root		left outer semi join, equal:[eq(Column#10, Column#11)], other cond:eq(subquery_table_to_derived.t1.a, plus(subquery_table_to_derived.t2.a, 1))
    ├─Projection(Build)	1.00	root		subquery_table_to_derived.t2.a, plus(subquery_table_to_derived.t2.a, 3)->Column#11
    │ └─Limit	1.00	root		offset:0, count:1
    │   └─TableReader	1.00	root		data:Limit
    │     └─Limit	1.00	cop[tikv]		offset:0, count:1
    │       └─TableFullScan	1.00	cop[tikv]	table:t2	keep order:false
    └─Projection(Probe)	3.00	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b, plus(subquery_table_to_derived.t1.a, 1)->Column#10
      └─TableReader	3.00	root		data:TableFullScan
        └─TableFullScan	3.00	cop[tikv]	table:ot	keep order:false
BEGIN;
UPDATE t1 ot SET a=a*100
WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1;
a	b
1	10
200	20
300	30
ROLLBACK;
BEGIN;
EXPLAIN format='brief' UPDATE t1 ot, (SELECT 1) AS dummy
SET a=a*100
WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
id	estRows	task	access object	operator info
Update	N/A	root		N/A
└─Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b, subquery_table_to_derived.t1._tidb_rowid, Column#4
  └─Selection	2.40	root		or(lt(subquery_table_to_derived.t1.b, 0), Column#9)
    └─HashJoin	3.00	root		CARTESIAN left outer semi join, other cond:eq(subquery_table_to_derived.t1.a, Column#8)
      ├─Projection(Build)	6.00	root		plus(subquery_table_to_derived.t2.a, 1)->Column#8
      │ └─TableReader	6.00	root		data:TableFullScan
      │   └─TableFullScan	6.00	cop[tikv]	table:it	keep order:false
      └─Projection(Probe)	3.00	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b, subquery_table_to_derived.t1._tidb_rowid, Column#4
        └─HashJoin	3.00	root		CARTESIAN inner join
          ├─Projection(Build)	1.00	root		1->Column#4
          │ └─TableDual	1.00	root		rows:1
          └─TableReader(Probe)	3.00	root		data:TableFullScan
            └─TableFullScan	3.00	cop[tikv]	table:ot	keep order:false
UPDATE t1 ot, (SELECT 1) AS dummy
SET a=a*100
WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1;
a	b
1	10
200	20
300	30
ROLLBACK;
BEGIN;
EXPLAIN format='brief' DELETE ot.* FROM t1 ot, (SELECT 1) AS dummy
WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
id	estRows	task	access object	operator info
Delete	N/A	root		N/A
└─Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b, subquery_table_to_derived.t1._tidb_rowid, Column#4
  └─Selection	2.40	root		or(lt(subquery_table_to_derived.t1.b, 0), Column#9)
    └─HashJoin	3.00	root		CARTESIAN left outer semi join, other cond:eq(subquery_table_to_derived.t1.a, Column#8)
      ├─Projection(Build)	6.00	root		plus(subquery_table_to_derived.t2.a, 1)->Column#8
      │ └─TableReader	6.00	root		data:TableFullScan
      │   └─TableFullScan	6.00	cop[tikv]	table:it	keep order:false
      └─Projection(Probe)	3.00	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b, subquery_table_to_derived.t1._tidb_rowid, Column#4
        └─HashJoin	3.00	root		CARTESIAN inner join
          ├─Projection(Build)	1.00	root		1->Column#4
          │ └─TableDual	1.00	root		rows:1
          └─TableReader(Probe)	3.00	root		data:TableFullScan
            └─TableFullScan	3.00	cop[tikv]	table:ot	keep order:false
DELETE ot.* FROM t1 ot, (SELECT 1) AS dummy
WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1;
a	b
1	10
ROLLBACK;
# Testing WL#12885 together with WL#13425
# WL#12885 with semijoin:
EXPLAIN format='brief' SELECT * FROM t1 WHERE (SELECT SUM(a) FROM t1) IN (SELECT b FROM t1);
id	estRows	task	access object	operator info
HashJoin	3.00	root		CARTESIAN inner join
├─HashAgg(Build)	1.00	root		group by:subquery_table_to_derived.t1.b, funcs:firstrow(1)->Column#13
│ └─TableReader	0.00	root		data:Selection
│   └─Selection	0.00	cop[tikv]		eq(6, subquery_table_to_derived.t1.b)
│     └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
└─TableReader(Probe)	3.00	root		data:TableFullScan
  └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
# WL#12885 with WL#13425:
# Scalar subquery as left expr
EXPLAIN format='brief' SELECT * FROM t1 WHERE (SELECT SUM(a) FROM t1) IN (SELECT b FROM t1) OR a>3;
id	estRows	task	access object	operator info
Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─Selection	2.40	root		or(Column#13, gt(subquery_table_to_derived.t1.a, 3))
  └─HashJoin	3.00	root		CARTESIAN left outer semi join, other cond:eq(6, subquery_table_to_derived.t1.b)
    ├─TableReader(Build)	3.00	root		data:TableFullScan
    │ └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
    └─TableReader(Probe)	3.00	root		data:TableFullScan
      └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
# Scalar subquery as right expr
EXPLAIN format='brief'SELECT * FROM t1 WHERE 36 IN (SELECT (SELECT SUM(a) FROM t1)+b FROM t1) OR a>3;
id	estRows	task	access object	operator info
Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─Selection	2.40	root		or(Column#17, gt(subquery_table_to_derived.t1.a, 3))
  └─HashJoin	3.00	root		CARTESIAN left outer semi join, other cond:eq(36, Column#16)
    ├─Projection(Build)	3.00	root		plus(6, cast(subquery_table_to_derived.t1.b, decimal(10,0) BINARY))->Column#16
    │ └─TableReader	3.00	root		data:TableFullScan
    │   └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
    └─TableReader(Probe)	3.00	root		data:TableFullScan
      └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
# Scalar subquery in EXISTS
EXPLAIN format='brief' SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE (SELECT SUM(a) FROM t1)=b) OR a>3;
id	estRows	task	access object	operator info
TableReader	0.00	root		data:Selection
└─Selection	0.00	cop[tikv]		or(0, gt(subquery_table_to_derived.t1.a, 3))
  └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
# Scalar subquery in NOT EXISTS
EXPLAIN format='brief' SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t1 WHERE (SELECT SUM(a) FROM t1)>b) OR a>3;
id	estRows	task	access object	operator info
TableReader	3.00	root		data:Selection
└─Selection	3.00	cop[tikv]		or(1, gt(subquery_table_to_derived.t1.a, 3))
  └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
# Scalar subquery in NOT IN and non-nullables (antijoin)
EXPLAIN format='brief' SELECT * FROM t1 WHERE 36 NOT IN (SELECT COALESCE((SELECT SUM(a) FROM t1),b,55) FROM t1) OR a>3;
id	estRows	task	access object	operator info
Projection	2.40	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b
└─Selection	2.40	root		or(Column#17, gt(subquery_table_to_derived.t1.a, 3))
  └─HashJoin	3.00	root		Null-aware anti left outer semi join, equal:[eq(Column#19, Column#16)]
    ├─Projection(Build)	3.00	root		coalesce(6, cast(subquery_table_to_derived.t1.b, decimal(10,0) BINARY), 55)->Column#16
    │ └─TableReader	3.00	root		data:TableFullScan
    │   └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
    └─Projection(Probe)	3.00	root		subquery_table_to_derived.t1.a, subquery_table_to_derived.t1.b, 36->Column#19
      └─TableReader	3.00	root		data:TableFullScan
        └─TableFullScan	3.00	cop[tikv]	table:t1	keep order:false
DROP TABLE t1,t2;
DROP view v2;
# Bug#30697743 WL#13425: ASSERT: !THD->IS_ERROR()' IN SELECT_LEX::PREPARE() AT SQL/SQL_RESOLVE
CREATE TABLE C (col_varchar_key VARCHAR(1));
CREATE TABLE insert_select_140
SELECT 1 FROM C WHERE
col_varchar_key IN ( SELECT  1 FROM C
WHERE ( 'f' , 'f' ) IN ( SELECT 1 , COUNT(  1 ) FROM C ));
Error 1105: 'CREATE TABLE ... SELECT' is not implemented yet
DROP TABLE C;
# Bug#30709889 ASSERT: &SUBS_SELECT->FIELDS_LIST == &SUBS_SELECT->ITEM_LIST && SUBS_SELECT->IT
CREATE TABLE t1 (
field2 VARCHAR(2),
field3 BIGINT
);
CREATE TABLE t2 (
col_int INT,
pk INT
);
SELECT 1 FROM t1
WHERE (field2 ,field3) IN
(
SELECT STRAIGHT_JOIN
1 AS field2 ,
( SELECT 1 AS SQ1_field1 FROM t2 AS SQ1_alias1
WHERE SQ1_alias1.col_int != alias1.pk) AS field3
FROM t2 AS alias1 GROUP BY field2,field3
);
1
DROP TABLE t2,t1;
# Bug#31018642 RESULT MISMATCHES BETWEEN TRUNK & WORKLOG
# Verify that <> is decorrelated inside an AND-ed negated predicate
CREATE TABLE t1(a INT);
CREATE TABLE t2(b INT);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2),(3);
ANALYZE TABLE t1,t2;
EXPLAIN format='brief' SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b);
id	estRows	task	access object	operator info
HashJoin	0.80	root		CARTESIAN anti semi join, other cond:ne(subquery_table_to_derived.t1.a, subquery_table_to_derived.t2.b)
├─TableReader(Build)	2.00	root		data:TableFullScan
│ └─TableFullScan	2.00	cop[tikv]	table:t2	keep order:false
└─TableReader(Probe)	1.00	root		data:TableFullScan
  └─TableFullScan	1.00	cop[tikv]	table:t1	keep order:false
SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b);
a
EXPLAIN format='brief' SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b) OR t1.a>0;
id	estRows	task	access object	operator info
Projection	0.80	root		subquery_table_to_derived.t1.a
└─Selection	0.80	root		or(Column#5, gt(subquery_table_to_derived.t1.a, 0))
  └─HashJoin	1.00	root		CARTESIAN anti left outer semi join, other cond:ne(subquery_table_to_derived.t1.a, subquery_table_to_derived.t2.b)
    ├─TableReader(Build)	2.00	root		data:TableFullScan
    │ └─TableFullScan	2.00	cop[tikv]	table:t2	keep order:false
    └─TableReader(Probe)	1.00	root		data:TableFullScan
      └─TableFullScan	1.00	cop[tikv]	table:t1	keep order:false
EXPLAIN format='brief' SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b) AND t1.a>0;
id	estRows	task	access object	operator info
HashJoin	0.80	root		CARTESIAN anti semi join, other cond:ne(subquery_table_to_derived.t1.a, subquery_table_to_derived.t2.b)
├─TableReader(Build)	2.00	root		data:TableFullScan
│ └─TableFullScan	2.00	cop[tikv]	table:t2	keep order:false
└─TableReader(Probe)	1.00	root		data:Selection
  └─Selection	1.00	cop[tikv]		gt(subquery_table_to_derived.t1.a, 0)
    └─TableFullScan	1.00	cop[tikv]	table:t1	keep order:false
SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b) AND t1.a>0;
a
EXPLAIN format='brief'
SELECT * FROM t1
WHERE EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b) AND t1.a>0;
id	estRows	task	access object	operator info
HashJoin	0.80	root		CARTESIAN semi join, other cond:ne(subquery_table_to_derived.t1.a, subquery_table_to_derived.t2.b)
├─TableReader(Build)	2.00	root		data:Selection
│ └─Selection	2.00	cop[tikv]		not(isnull(subquery_table_to_derived.t2.b))
│   └─TableFullScan	2.00	cop[tikv]	table:t2	keep order:false
└─TableReader(Probe)	1.00	root		data:Selection
  └─Selection	1.00	cop[tikv]		gt(subquery_table_to_derived.t1.a, 0), not(isnull(subquery_table_to_derived.t1.a))
    └─TableFullScan	1.00	cop[tikv]	table:t1	keep order:false
# Verify that >=, <=, >, < are also decorrelated.
EXPLAIN format='brief'
SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a>=t2.b) AND t1.a>0;
id	estRows	task	access object	operator info
HashJoin	0.80	root		CARTESIAN anti semi join, other cond:ge(subquery_table_to_derived.t1.a, subquery_table_to_derived.t2.b)
├─TableReader(Build)	2.00	root		data:TableFullScan
│ └─TableFullScan	2.00	cop[tikv]	table:t2	keep order:false
└─TableReader(Probe)	1.00	root		data:Selection
  └─Selection	1.00	cop[tikv]		gt(subquery_table_to_derived.t1.a, 0)
    └─TableFullScan	1.00	cop[tikv]	table:t1	keep order:false
EXPLAIN format='brief'
SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a>t2.b) AND t1.a>0;
id	estRows	task	access object	operator info
HashJoin	0.80	root		CARTESIAN anti semi join, other cond:gt(subquery_table_to_derived.t1.a, subquery_table_to_derived.t2.b)
├─TableReader(Build)	2.00	root		data:TableFullScan
│ └─TableFullScan	2.00	cop[tikv]	table:t2	keep order:false
└─TableReader(Probe)	1.00	root		data:Selection
  └─Selection	1.00	cop[tikv]		gt(subquery_table_to_derived.t1.a, 0)
    └─TableFullScan	1.00	cop[tikv]	table:t1	keep order:false
EXPLAIN format='brief'
SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<=t2.b) AND t1.a>0;
id	estRows	task	access object	operator info
HashJoin	0.80	root		CARTESIAN anti semi join, other cond:le(subquery_table_to_derived.t1.a, subquery_table_to_derived.t2.b)
├─TableReader(Build)	2.00	root		data:TableFullScan
│ └─TableFullScan	2.00	cop[tikv]	table:t2	keep order:false
└─TableReader(Probe)	1.00	root		data:Selection
  └─Selection	1.00	cop[tikv]		gt(subquery_table_to_derived.t1.a, 0)
    └─TableFullScan	1.00	cop[tikv]	table:t1	keep order:false
EXPLAIN format='brief'
SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<t2.b) AND t1.a>0;
id	estRows	task	access object	operator info
HashJoin	0.80	root		CARTESIAN anti semi join, other cond:lt(subquery_table_to_derived.t1.a, subquery_table_to_derived.t2.b)
├─TableReader(Build)	2.00	root		data:TableFullScan
│ └─TableFullScan	2.00	cop[tikv]	table:t2	keep order:false
└─TableReader(Probe)	1.00	root		data:Selection
  └─Selection	1.00	cop[tikv]		gt(subquery_table_to_derived.t1.a, 0)
    └─TableFullScan	1.00	cop[tikv]	table:t1	keep order:false
# Reverse the order of arguments:
EXPLAIN format='brief'
SELECT * FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t2.b<t1.a) AND t1.a>0;
id	estRows	task	access object	operator info
HashJoin	0.80	root		CARTESIAN anti semi join, other cond:lt(subquery_table_to_derived.t2.b, subquery_table_to_derived.t1.a)
├─TableReader(Build)	2.00	root		data:TableFullScan
│ └─TableFullScan	2.00	cop[tikv]	table:t2	keep order:false
└─TableReader(Probe)	1.00	root		data:Selection
  └─Selection	1.00	cop[tikv]		gt(subquery_table_to_derived.t1.a, 0)
    └─TableFullScan	1.00	cop[tikv]	table:t1	keep order:false
DROP TABLE t1,t2;