[
https://issues.apache.org/jira/browse/DRILL-5807?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16607474#comment-16607474
]
Cheolgoo Kang commented on DRILL-5807:
--------------------------------------
Pretty-fied:
{code}
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"
{code}
This query will be having {{item_id}} from both {{t}} and {{t0}} since you're
doing {{SELECT *}}:
{code}
...
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"
...
{code}
> ambiguous error
> ---------------
>
> Key: DRILL-5807
> URL: https://issues.apache.org/jira/browse/DRILL-5807
> Project: Apache Drill
> Issue Type: Bug
> Components: Client - JDBC
> Affects Versions: 1.11.0
> Environment: Linux
> Reporter: XiaHang
> Priority: Critical
>
> 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 = {8040.0 rows, 2763.0 cpu, 0.0 io}, 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 = {2235.0 rows, 2582.0 cpu,
> 0.0 io}, id = 320
> JdbcJoin(condition=[=($2, $9)], joinType=[left]): rowcount = 1500.0,
> cumulative cost = {1860.0 rows, 1082.0 cpu, 0.0 io}, 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 = {180.0
> rows, 821.0 cpu, 0.0 io}, id = 313
> JdbcTableScan(table=[[public, dws_tb_crm_u2_ord_base_df]]):
> rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 29
> 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 = {180.0
> rows, 181.0 cpu, 0.0 io}, id = 323
> JdbcTableScan(table=[[public, dws_tb_crm_u2_slr_base]]): rowcount =
> 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, 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
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
