XiaHang created DRILL-5807:
------------------------------
Summary: 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
(v6.4.14#64029)
