Details
-
Bug
-
Status: Open
-
Critical
-
Resolution: Unresolved
-
1.11.0
-
None
-
None
-
Linux
Description
if the final plan like below , JdbcFilter is below a JdbcJoin and above another JdbcJoin .
JdbcProject(order_id=[$0], mord_id=[$6], item_id=[$2], div_pay_amt=[$5], item_quantity=[$4], slr_id=[$11]): rowcount = 5625.0, cumulative cost =
{12540.0 rows, 29763.0 cpu, 0.0 io}, id = 327
JdbcJoin(condition=[=($3, $11)], joinType=[left]): rowcount = 5625.0, cumulative cost =
, id = 325
JdbcFilter(condition=[OR(AND(OR(IS NOT NULL($7), >($5, 0)), =($1, 2), OR(AND(=($10, '箱包皮具/热销女包/男包'), >(/($5, $4), 1000)), AND(OR(=($10, '家装主材'), =($10, '大家电')), >(/($5, $4), 1000)), AND(OR(=($10, '珠宝/钻石/翡翠/黄金'), =($10, '饰品/流行首饰/时尚饰品新')), >(/($5, $4), 2000)), AND(>(/($5, $4), 500), <>($10, '箱包皮具/热销女包/男包'), <>($10, '家装主材'), <>($10, '大家电'), <>($10, '珠宝/钻石/翡翠/黄金'), <>($10, '饰品/流行首饰/时尚饰品新'))), <>($10, '成人用品/情趣用品'), <>($10, '鲜花速递/花卉仿真/绿植园艺'), <>($10, '水产肉类/新鲜蔬果/熟食')), AND(<=(-(EXTRACT(FLAG(EPOCH), CURRENT_TIMESTAMP), EXTRACT(FLAG(EPOCH), CAST($8):TIMESTAMP(0))), *(*(*(14, 24), 60), 60)), OR(AND(OR(=($10, '箱包皮具/热销女包/男包'), =($10, '家装主材'), =($10, '大家电'), =($10, '珠宝/钻石/翡翠/黄金'), =($10, '饰品/流行首饰/时尚饰品新')), >(/($5, $4), 2000)), AND(OR(=($10, '男装'), =($10, '女装/女士精品'), =($10, '办公设备/耗材/相关服务')), >(/($5, $4), 1000)), AND(OR(=($10, '流行男鞋'), =($10, '女鞋')), >(/($5, $4), 1500))), IS NOT NULL($8)), AND(>=(-(EXTRACT(FLAG(EPOCH), CURRENT_TIMESTAMP), EXTRACT(FLAG(EPOCH), CAST($8):TIMESTAMP(0))), *(*(*(15, 24), 60), 60)), <=(-(EXTRACT(FLAG(EPOCH), CURRENT_TIMESTAMP), EXTRACT(FLAG(EPOCH), CAST($8):TIMESTAMP(0))), *(*(*(60, 24), 60), 60)), OR(AND(OR(=($10, '箱包皮具/热销女包/男包'), =($10, '珠宝/钻石/翡翠/黄金'), =($10, '饰品/流行首饰/时尚饰品新')), >(/($5, $4), 5000)), AND(OR(=($10, '男装'), =($10, '女装/女士精品')), >(/($5, $4), 3000)), AND(OR(=($10, '流行男鞋'), =($10, '女鞋')), >(/($5, $4), 2500)), AND(=($10, '办公设备/耗材/相关服务'), >(/($5, $4), 2000))), IS NOT NULL($8)))]): rowcount = 375.0, cumulative cost =
, id = 320
JdbcJoin(condition=[=($2, $9)], joinType=[left]): rowcount = 1500.0, cumulative cost =
, id = 318
JdbcProject(order_id=[$0], pay_status=[$2], item_id=[$3], seller_id=[$5], item_quantity=[$7], div_pay_amt=[$20], mord_id=[$1], pay_time=[$19], succ_time=[$52]): rowcount = 100.0, cumulative cost =
, id = 313
JdbcTableScan(table=[[public, dws_tb_crm_u2_ord_base_df]]): rowcount = 100.0, cumulative cost =
JdbcProject(item_id=[$0], cate_level1_name=[$47]): rowcount = 100.0, cumulative cost = {180.0 rows, 261.0 cpu, 0.0 io}, id = 316
JdbcTableScan(table=[[public, dws_tb_crm_u2_itm_base_df]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}
, id = 46
JdbcProject(slr_id=[$3]): rowcount = 100.0, cumulative cost =
, id = 323
JdbcTableScan(table=[[public, dws_tb_crm_u2_slr_base]]): rowcount = 100.0, cumulative cost =
, id = 68
the sql is converted to
SELECT "t1"."order_id", "t1"."mord_id", "t1"."item_id", "t1"."div_pay_amt", "t1"."item_quantity", "t2"."slr_id"
FROM (SELECT *
FROM (SELECT "order_id", "pay_status", "item_id", "seller_id", "item_quantity", "div_pay_amt", "mord_id", "pay_time", "succ_time"
FROM "dws_tb_crm_u2_ord_base_df") AS "t"
LEFT JOIN (SELECT "item_id", "cate_level1_name"
FROM "dws_tb_crm_u2_itm_base_df") AS "t0" ON "t"."item_id" = "t0"."item_id"
WHERE ("t"."pay_time" IS NOT NULL OR "t"."div_pay_amt" > 0) AND "t"."pay_status" = 2 AND ("t0"."cate_level1_name" = '箱包皮具/热销女包/男包' AND "t"."div_pay_amt" / "t"."item_quantity" > 1000 OR ("t0"."cate_level1_name" = '家装主材' OR "t0"."cate_level1_name" = '大家电') AND "t"."div_pay_amt" / "t"."item_quantity" > 1000 OR ("t0"."cate_level1_name" = '珠宝/钻石/翡翠/黄金' OR "t0"."cate_level1_name" = '饰品/流行首饰/时尚饰品新') AND "t"."div_pay_amt" / "t"."item_quantity" > 2000 OR "t"."div_pay_amt" / "t"."item_quantity" > 500 AND "t0"."cate_level1_name" <> '箱包皮具/热销女包/男包' AND "t0"."cate_level1_name" <> '家装主材' AND "t0"."cate_level1_name" <> '大家电' AND "t0"."cate_level1_name" <> '珠宝/钻石/翡翠/黄金' AND "t0"."cate_level1_name" <> '饰品/流行首饰/时尚饰品新') AND "t0"."cate_level1_name" <> '成人用品/情趣用品' AND "t0"."cate_level1_name" <> '鲜花速递/花卉仿真/绿植园艺' AND "t0"."cate_level1_name" <> '水产肉类/新鲜蔬果/熟食' OR EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - EXTRACT(EPOCH FROM CAST("t"."succ_time" AS TIMESTAMP(0))) <= 14 * 24 * 60 * 60 AND (("t0"."cate_level1_name" = '箱包皮具/热销女包/男包' OR "t0"."cate_level1_name" = '家装主材' OR "t0"."cate_level1_name" = '大家电' OR "t0"."cate_level1_name" = '珠宝/钻石/翡翠/黄金' OR "t0"."cate_level1_name" = '饰品/流行首饰/时尚饰品新') AND "t"."div_pay_amt" / "t"."item_quantity" > 2000 OR ("t0"."cate_level1_name" = '男装' OR "t0"."cate_level1_name" = '女装/女士精品' OR "t0"."cate_level1_name" = '办公设备/耗材/相关服务') AND "t"."div_pay_amt" / "t"."item_quantity" > 1000 OR ("t0"."cate_level1_name" = '流行男鞋' OR "t0"."cate_level1_name" = '女鞋') AND "t"."div_pay_amt" / "t"."item_quantity" > 1500) AND "t"."succ_time" IS NOT NULL OR EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - EXTRACT(EPOCH FROM CAST("t"."succ_time" AS TIMESTAMP(0))) >= 15 * 24 * 60 * 60 AND EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - EXTRACT(EPOCH FROM CAST("t"."succ_time" AS TIMESTAMP(0))) <= 60 * 24 * 60 * 60 AND (("t0"."cate_level1_name" = '箱包皮具/热销女包/男包' OR "t0"."cate_level1_name" = '珠宝/钻石/翡翠/黄金' OR "t0"."cate_level1_name" = '饰品/流行首饰/时尚饰品新') AND "t"."div_pay_amt" / "t"."item_quantity" > 5000 OR ("t0"."cate_level1_name" = '男装' OR "t0"."cate_level1_name" = '女装/女士精品') AND "t"."div_pay_amt" / "t"."item_quantity" > 3000 OR ("t0"."cate_level1_name" = '流行男鞋' OR "t0"."cate_level1_name" = '女鞋') AND "t"."div_pay_amt" / "t"."item_quantity" > 2500 OR "t0"."cate_level1_name" = '办公设备/耗材/相关服务' AND "t"."div_pay_amt" / "t"."item_quantity" > 2000) AND "t"."succ_time" IS NOT NULL) AS "t1"
LEFT JOIN (SELECT "slr_id"
FROM "dws_tb_crm_u2_slr_base") AS "t2" ON "t1"."seller_id" = "t2"."slr_id"
it will throw an ERROR: column reference "item_id" is ambiguous