drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "XiaHang (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-5807) ambiguous error
Date Wed, 20 Sep 2017 08:45:00 GMT
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)

Mime
View raw message