-
Notifications
You must be signed in to change notification settings - Fork 6
/
Copy pathrequest.txt
executable file
·45 lines (34 loc) · 30.6 KB
/
request.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
2022/5/12 下午5:23
java.lang.NullPointerException
====================================================
2022/5/12 下午5:24
----------------------------303953486694152966958109
Content-Disposition: form-data; name="sql"
----------------------------303953486694152966958109--
com.alibaba.fastjson.JSONException: illegal number format : -
====================================================
2022/5/12 下午5:24
{"sql": "select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey);"}
{"data":{"rewritten_sql":"SELECT MAX(\"t24\".\"EXPR$0\")\nFROM (SELECT \"l_orderkey\", MAX(\"l_orderkey\") AS \"EXPR$0\"\nFROM \"lineitem\"\nGROUP BY \"l_orderkey\") AS \"t24\"\nINNER JOIN (SELECT \"c_custkey\" AS \"c_custkey1\"\nFROM \"customer\"\nGROUP BY \"c_custkey\") AS \"t26\" ON \"t24\".\"l_orderkey\" = \"t26\".\"c_custkey1\"","origin_cost":"51106.7700","origin_sql":"select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey)","rewritten_cost":"3850.2700","is_rewritten":true,"origin_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], c_custkey1=[CAST($16):INTEGER], $f1=[CAST($17):BOOLEAN])\n LogicalJoin(condition=[=($0, $16)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","treeJson":{"cost":51106.77,"children":[{"cost":39563.52,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..17=[{inputs}], l_orderkey=[$t0])\n LogicalJoin(condition=[=($0, $16)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..2=[{inputs}], expr#3=[true], c_custkey1=[$t1], $f0=[$t3])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..7=[{inputs}], c_custkey=[$t0], c_custkey1=[$t0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectToCalcRule":2}},{"cost":4053.27,"children":[{"cost":45047.27,"children":[],"name":"rule_join","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalFilter(condition=[=($0, $2)])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$2])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$3], $f0=[true])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey=[$2], c_custkey0=[$2], EXPR$00=[$3])\n LogicalJoin(condition=[true], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"JoinProjectTransposeRule(Other-Project)":2}},{"cost":3850.27,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..1=[{inputs}], c_custkey1=[$t0])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectMergeRule":1}}],"name":"rule_agg","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$0])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalProject(c_custkey=[$0], c_custkey0=[$0], EXPR$0=[$1])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"AggregateProjectMergeRule":2}}],"name":"original query","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], c_custkey1=[CAST($16):INTEGER], $f1=[CAST($17):BOOLEAN])\n LogicalJoin(condition=[=($0, $16)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{}},"rewritten_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(EXPR$0=[$1])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n"},"message":"SUCCESS","status":true}
2022/5/12 下午5:24
====================================================
2022/5/12 下午5:26
{"sql": "select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey);"}
{"data":{"rewritten_sql":"SELECT MAX(\"t157\".\"EXPR$0\")\nFROM (SELECT \"l_orderkey\", MAX(\"l_orderkey\") AS \"EXPR$0\"\nFROM \"lineitem\"\nGROUP BY \"l_orderkey\") AS \"t157\"\nINNER JOIN (SELECT \"c_custkey\", MAX(\"c_custkey\") AS \"EXPR$0\"\nFROM \"customer\"\nGROUP BY \"c_custkey\") AS \"t158\" ON \"t157\".\"l_orderkey\" = \"t158\".\"c_custkey\"","origin_cost":"51256.7700","origin_sql":"select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey)","rewritten_cost":"3747.2700","is_rewritten":true,"origin_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], l_null=[$16], c_custkey1=[CAST($17):INTEGER], $f1=[CAST($18):BOOLEAN])\n LogicalJoin(condition=[=($0, $17)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","treeJson":{"cost":51256.77,"children":[{"cost":4053.27,"children":[{"cost":45047.27,"children":[{"cost":24547.27,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalFilter(condition=[=($0, $2)])\n LogicalCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}])\n LogicalJoin(condition=[true], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectMergeRule":1}},{"cost":8427.27,"children":[{"cost":3747.27,"children":[],"name":"rule_agg","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"AggregateProjectMergeRule":2}},{"cost":5687.52,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectMergeRule":1}}],"name":"rule_filter","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$2])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$3], $f0=[true])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey=[$2], c_custkey0=[$2], EXPR$00=[$3])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"FilterProjectTransposeRule":2}}],"name":"rule_join","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalFilter(condition=[=($0, $2)])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$2])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$3], $f0=[true])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey=[$2], c_custkey0=[$2], EXPR$00=[$3])\n LogicalJoin(condition=[true], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"JoinProjectTransposeRule(Other-Project)":2}},{"cost":3850.27,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..1=[{inputs}], c_custkey1=[$t0])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectMergeRule":1}}],"name":"rule_agg","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$0])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalProject(c_custkey=[$0], c_custkey0=[$0], EXPR$0=[$1])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"AggregateProjectMergeRule":2}},{"cost":39723.52,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..18=[{inputs}], l_orderkey=[$t0])\n LogicalJoin(condition=[=($0, $17)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..2=[{inputs}], expr#3=[true], c_custkey1=[$t1], $f0=[$t3])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..8=[{inputs}], c_custkey=[$t0], c_custkey1=[$t0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectToCalcRule":2}}],"name":"original query","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], l_null=[$16], c_custkey1=[CAST($17):INTEGER], $f1=[CAST($18):BOOLEAN])\n LogicalJoin(condition=[=($0, $17)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{}},"rewritten_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(EXPR$0=[$1])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0])\n LogicalTableScan(table=[[customer]])\n"},"message":"SUCCESS","status":true}
2022/5/12 下午5:26
====================================================
2022/5/12 下午5:28
{"sql": "select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey);"}
{"data":{"rewritten_sql":"SELECT MAX(\"t17\".\"EXPR$0\")\nFROM (SELECT \"l_orderkey\", MAX(\"l_orderkey\") AS \"EXPR$0\"\nFROM \"lineitem\"\nGROUP BY \"l_orderkey\") AS \"t17\"\nINNER JOIN (SELECT \"c_custkey\" AS \"c_custkey1\"\nFROM \"customer\"\nGROUP BY \"c_custkey\") AS \"t19\" ON \"t17\".\"l_orderkey\" = \"t19\".\"c_custkey1\"","origin_cost":"51106.7700","origin_sql":"select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey)","rewritten_cost":"3850.2700","is_rewritten":true,"origin_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], c_custkey1=[CAST($16):INTEGER], $f1=[CAST($17):BOOLEAN])\n LogicalJoin(condition=[=($0, $16)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","treeJson":{"cost":51106.77,"children":[{"cost":39563.52,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..17=[{inputs}], l_orderkey=[$t0])\n LogicalJoin(condition=[=($0, $16)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..2=[{inputs}], expr#3=[true], c_custkey1=[$t1], $f0=[$t3])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..7=[{inputs}], c_custkey=[$t0], c_custkey1=[$t0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectToCalcRule":2}},{"cost":4053.27,"children":[{"cost":3850.27,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..1=[{inputs}], c_custkey1=[$t0])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectMergeRule":1}},{"cost":45047.27,"children":[],"name":"rule_join","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalFilter(condition=[=($0, $2)])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$2])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$3], $f0=[true])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey=[$2], c_custkey0=[$2], EXPR$00=[$3])\n LogicalJoin(condition=[true], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"JoinProjectTransposeRule(Other-Project)":2}}],"name":"rule_agg","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$0])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalProject(c_custkey=[$0], c_custkey0=[$0], EXPR$0=[$1])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"AggregateProjectMergeRule":2}}],"name":"original query","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], c_custkey1=[CAST($16):INTEGER], $f1=[CAST($17):BOOLEAN])\n LogicalJoin(condition=[=($0, $16)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{}},"rewritten_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(EXPR$0=[$1])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n"},"message":"SUCCESS","status":true}
2022/5/12 下午5:28
====================================================
2022/5/12 下午5:29
{"sql": "select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey);"}
{"data":{"rewritten_sql":"SELECT MAX(\"t157\".\"EXPR$0\")\nFROM (SELECT \"l_orderkey\", MAX(\"l_orderkey\") AS \"EXPR$0\"\nFROM \"lineitem\"\nGROUP BY \"l_orderkey\") AS \"t157\"\nINNER JOIN (SELECT \"c_custkey\", MAX(\"c_custkey\") AS \"EXPR$0\"\nFROM \"customer\"\nGROUP BY \"c_custkey\") AS \"t158\" ON \"t157\".\"l_orderkey\" = \"t158\".\"c_custkey\"","origin_cost":"51256.7700","origin_sql":"select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey)","rewritten_cost":"3747.2700","is_rewritten":true,"origin_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], l_null=[$16], c_custkey1=[CAST($17):INTEGER], $f1=[CAST($18):BOOLEAN])\n LogicalJoin(condition=[=($0, $17)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","treeJson":{"cost":51256.77,"children":[{"cost":4053.27,"children":[{"cost":45047.27,"children":[{"cost":24547.27,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalFilter(condition=[=($0, $2)])\n LogicalCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}])\n LogicalJoin(condition=[true], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectMergeRule":1}},{"cost":8427.27,"children":[{"cost":3747.27,"children":[],"name":"rule_agg","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"AggregateProjectMergeRule":2}},{"cost":5687.52,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalCalc(expr#0..3=[{inputs}], proj#0..2=[{exprs}])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectMergeRule":1}}],"name":"rule_filter","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$2])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$3], $f0=[true])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey=[$2], c_custkey0=[$2], EXPR$00=[$3])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"FilterProjectTransposeRule":2}}],"name":"rule_join","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalFilter(condition=[=($0, $2)])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$2])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$3], $f0=[true])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey=[$2], c_custkey0=[$2], EXPR$00=[$3])\n LogicalJoin(condition=[true], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"JoinProjectTransposeRule(Other-Project)":2}},{"cost":3850.27,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..1=[{inputs}], c_custkey1=[$t0])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectMergeRule":1}}],"name":"rule_agg","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$0])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalProject(c_custkey=[$0], c_custkey0=[$0], EXPR$0=[$1])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"AggregateProjectMergeRule":2}},{"cost":39723.52,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..18=[{inputs}], l_orderkey=[$t0])\n LogicalJoin(condition=[=($0, $17)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..2=[{inputs}], expr#3=[true], c_custkey1=[$t1], $f0=[$t3])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..8=[{inputs}], c_custkey=[$t0], c_custkey1=[$t0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectToCalcRule":2}}],"name":"original query","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], l_null=[$16], c_custkey1=[CAST($17):INTEGER], $f1=[CAST($18):BOOLEAN])\n LogicalJoin(condition=[=($0, $17)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{}},"rewritten_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(EXPR$0=[$1])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0])\n LogicalTableScan(table=[[customer]])\n"},"message":"SUCCESS","status":true}
2022/5/12 下午5:29
====================================================
2022/5/12 下午5:43
{"sql": "select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey);"}
{"data":{"rewritten_sql":"SELECT MAX(\"t17\".\"EXPR$0\")\nFROM (SELECT \"l_orderkey\", MAX(\"l_orderkey\") AS \"EXPR$0\"\nFROM \"lineitem\"\nGROUP BY \"l_orderkey\") AS \"t17\"\nINNER JOIN (SELECT \"c_custkey\" AS \"c_custkey1\"\nFROM \"customer\"\nGROUP BY \"c_custkey\") AS \"t19\" ON \"t17\".\"l_orderkey\" = \"t19\".\"c_custkey1\"","origin_cost":"51106.7700","origin_sql":"select max(distinct l_orderkey) from lineitem where exists(select max(c_custkey) from customer where c_custkey = l_orderkey group by c_custkey)","rewritten_cost":"3850.2700","is_rewritten":true,"origin_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], c_custkey1=[CAST($16):INTEGER], $f1=[CAST($17):BOOLEAN])\n LogicalJoin(condition=[=($0, $16)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","treeJson":{"cost":51106.77,"children":[{"cost":39563.52,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..17=[{inputs}], l_orderkey=[$t0])\n LogicalJoin(condition=[=($0, $16)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..2=[{inputs}], expr#3=[true], c_custkey1=[$t1], $f0=[$t3])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalCalc(expr#0..7=[{inputs}], c_custkey=[$t0], c_custkey1=[$t0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectToCalcRule":2}},{"cost":4053.27,"children":[{"cost":3850.27,"children":[],"name":"rule_project","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalCalc(expr#0..1=[{inputs}], c_custkey1=[$t0])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"ProjectMergeRule":1}},{"cost":45047.27,"children":[],"name":"rule_join","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalFilter(condition=[=($0, $2)])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$2])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey1=[$3], $f0=[true])\n LogicalProject(l_orderkey=[$0], EXPR$0=[$1], c_custkey=[$2], c_custkey0=[$2], EXPR$00=[$3])\n LogicalJoin(condition=[true], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"JoinProjectTransposeRule(Other-Project)":2}}],"name":"rule_agg","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($1)])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$0])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalProject(c_custkey=[$0], c_custkey0=[$0], EXPR$0=[$1])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{"AggregateProjectMergeRule":2}}],"name":"original query","plan":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalProject(l_orderkey=[$0], l_partkey=[$1], l_suppkey=[$2], l_linenumber=[$3], l_quantity=[$4], l_extendedprice=[$5], l_discount=[$6], l_tax=[$7], l_returnflag=[$8], l_linestatus=[$9], l_shipdate=[$10], l_commitdate=[$11], l_receiptdate=[$12], l_shipinstruct=[$13], l_shipmode=[$14], l_comment=[$15], c_custkey1=[CAST($16):INTEGER], $f1=[CAST($17):BOOLEAN])\n LogicalJoin(condition=[=($0, $16)], joinType=[inner])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$1], $f0=[true])\n LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($0)])\n LogicalProject(c_custkey=[$0], c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n","activated_rules":{}},"rewritten_sql_node":"LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])\n LogicalProject(EXPR$0=[$1])\n LogicalJoin(condition=[=($0, $2)], joinType=[inner])\n LogicalAggregate(group=[{0}], EXPR$0=[MAX($0)])\n LogicalProject(l_orderkey=[$0])\n LogicalTableScan(table=[[lineitem]])\n LogicalProject(c_custkey1=[$0])\n LogicalTableScan(table=[[customer]])\n"},"message":"SUCCESS","status":true}
2022/5/12 下午5:43
====================================================