kylin-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From 翟娜 (JIRA) <j...@apache.org>
Subject [jira] [Commented] (KYLIN-2777) Should throw error when sql contains non-equi left join
Date Fri, 13 Jul 2018 02:45:00 GMT

    [ https://issues.apache.org/jira/browse/KYLIN-2777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16542436#comment-16542436
] 

翟娜 commented on KYLIN-2777:
---------------------------

SQL like:

select sum(v_revenue) 
from ssb.p_lineorder left join ssb.dates on lo_orderdate = d_datekey
and d_year = 1993

with the sql statements above, I got the following error message.

*version 2.2.X:* Error while applying rule OLAPJoinRule, args [rel#85:LogicalJoin.NONE.[](left=rel#83:Subset#0.OLAP.[],right=rel#84:Subset#1.OLAP.[],condition=AND(=($2,
$5), =($6, 1993)),joinType=left)] while executing SQL: "select sum(v_revenue) from ssb.p_lineorder
left join ssb.dates on lo_orderdate = d_datekey and d_year = 1993 LIMIT 50000"

*version 2.3.X:* 2 while executing SQL: "select sum(v_revenue) from ssb.p_lineorder left
join ssb.dates on lo_orderdate = d_datekey and d_year = 1993"

*version 2.4.X:* Node [rel#418:Subset#4.ENUMERABLE.[]] could not be implemented; planner
state: Root: rel#418:Subset#4.ENUMERABLE.[] Original rel: LogicalAggregate(subset=[rel#418:Subset#4.ENUMERABLE.[]],
group=[{}], EXPR$0=[SUM($0)]): rowcount = 22.5, cumulative cost = \{25.593751072883606 rows,
0.0 cpu, 0.0 io}, id = 413 LogicalProject(subset=[rel#412:Subset#3.NONE.[]], V_REVENUE=[$3]):
rowcount = 225.0, cumulative cost = \{225.0 rows, 225.0 cpu, 0.0 io}, id = 411 LogicalJoin(subset=[rel#410:Subset#2.NONE.[]],
condition=[AND(=($2, $6), =($7, 1993))], joinType=[left]): rowcount = 225.0, cumulative cost
= \{225.0 rows, 0.0 cpu, 0.0 io}, id = 409 OLAPTableScan(subset=[rel#407:Subset#0.OLAP.[]],
table=[[SSB, P_LINEORDER]], ctx=[], fields=[[0, 1, 2, 3, 4, 5]]): rowcount = 100.0, cumulative
cost = \{5.0 rows, 5.050000000000001 cpu, 0.0 io}, id = 391 OLAPTableScan(subset=[rel#408:Subset#1.OLAP.[]],
table=[[SSB, DATES]], ctx=[], fields=[[0, 1]]): rowcount = 100.0, cumulative cost = \{5.0
rows, 5.050000000000001 cpu, 0.0 io}, id = 392 Sets: Set#0, type: RecordType(BIGINT LO_ORDERKEY,
INTEGER LO_CUSTKEY, INTEGER LO_ORDERDATE, BIGINT V_REVENUE, BIGINT _KY_COUNT__, BIGINT _KY_SUM_P_LINEORDER_V_REVENUE_)
rel#407:Subset#0.OLAP.[], best=rel#391, importance=0.6561 rel#391:OLAPTableScan.OLAP.[](table=[SSB,
P_LINEORDER],ctx=,fields=[0, 1, 2, 3, 4, 5]), rowcount=100.0, cumulative cost=\{5.0 rows,
5.050000000000001 cpu, 0.0 io} rel#446:Subset#0.ENUMERABLE.[], best=rel#445, importance=0.32805
rel#445:OLAPToEnumerableConverter.ENUMERABLE.[](input=rel#407:Subset#0.OLAP.[]), rowcount=100.0,
cumulative cost=\{10.0 rows, 10.05 cpu, 0.0 io} Set#1, type: RecordType(INTEGER D_DATEKEY,
INTEGER D_YEAR) rel#408:Subset#1.OLAP.[], best=rel#392, importance=0.6561 rel#392:OLAPTableScan.OLAP.[](table=[SSB,
DATES],ctx=,fields=[0, 1]), rowcount=100.0, cumulative cost=\{5.0 rows, 5.050000000000001
cpu, 0.0 io} rel#444:Subset#1.ENUMERABLE.[], best=rel#443, importance=0.32805 rel#443:OLAPToEnumerableConverter.ENUMERABLE.[](input=rel#408:Subset#1.OLAP.[]),
rowcount=100.0, cumulative cost=\{10.0 rows, 10.05 cpu, 0.0 io} Set#2, type: RecordType(BIGINT
LO_ORDERKEY, INTEGER LO_CUSTKEY, INTEGER LO_ORDERDATE, BIGINT V_REVENUE, BIGINT _KY_COUNT__,
BIGINT _KY_SUM_P_LINEORDER_V_REVENUE_, INTEGER D_DATEKEY, INTEGER D_YEAR) rel#410:Subset#2.NONE.[],
best=null, importance=0.7290000000000001 rel#409:LogicalJoin.NONE.[](left=rel#407:Subset#0.OLAP.[],right=rel#408:Subset#1.OLAP.[],condition=AND(=($2,
$6), =($7, 1993)),joinType=left), rowcount=225.0, cumulative cost=\{inf} rel#434:Subset#2.OLAP.[],
best=null, importance=0.7290000000000001 Set#3, type: RecordType(BIGINT V_REVENUE) rel#412:Subset#3.NONE.[],
best=null, importance=0.81 rel#411:LogicalProject.NONE.[](input=rel#410:Subset#2.NONE.[],V_REVENUE=$3),
rowcount=225.0, cumulative cost=\{inf} rel#420:Subset#3.OLAP.[], best=null, importance=0.81
rel#435:OLAPProjectRel.OLAP.[](input=rel#434:Subset#2.OLAP.[],V_REVENUE=$3,ctx=), rowcount=225.0,
cumulative cost=\{inf} rel#439:Subset#3.ENUMERABLE.[], best=null, importance=0.405 rel#438:OLAPToEnumerableConverter.ENUMERABLE.[](input=rel#420:Subset#3.OLAP.[]),
rowcount=225.0, cumulative cost=\{inf} Set#4, type: RecordType(BIGINT EXPR$0) rel#414:Subset#4.NONE.[],
best=null, importance=0.9 rel#413:LogicalAggregate.NONE.[](input=rel#412:Subset#3.NONE.[],group={},EXPR$0=SUM($0)),
rowcount=22.5, cumulative cost=\{inf} rel#429:LogicalProject.NONE.[](input=rel#428:Subset#5.NONE.[],EXPR$0=CASE(=($1,
0), null, $0)), rowcount=22.5, cumulative cost=\{inf} rel#418:Subset#4.ENUMERABLE.[], best=null,
importance=1.0 rel#419:AbstractConverter.ENUMERABLE.[](input=rel#414:Subset#4.NONE.[],convention=ENUMERABLE,sort=[]),
rowcount=22.5, cumulative cost=\{inf} rel#423:OLAPToEnumerableConverter.ENUMERABLE.[](input=rel#422:Subset#4.OLAP.[]),
rowcount=22.5, cumulative cost=\{inf} rel#422:Subset#4.OLAP.[], best=null, importance=0.9
rel#421:OLAPAggregateRel.OLAP.[](input=rel#420:Subset#3.OLAP.[],group={},EXPR$0=SUM($0),ctx=),
rowcount=22.5, cumulative cost=\{inf} rel#431:OLAPProjectRel.OLAP.[](input=rel#430:Subset#5.OLAP.[],EXPR$0=CASE(=($1,
0), null, $0),ctx=), rowcount=22.5, cumulative cost=\{inf} Set#5, type: RecordType(BIGINT
EXPR$0, BIGINT $f1) rel#428:Subset#5.NONE.[], best=null, importance=0.81 rel#426:LogicalAggregate.NONE.[](input=rel#412:Subset#3.NONE.[],group={},EXPR$0=$SUM0($0),agg#1=COUNT($0)),
rowcount=22.5, cumulative cost=\{inf} rel#430:Subset#5.OLAP.[], best=null, importance=0.81
rel#436:OLAPAggregateRel.OLAP.[](input=rel#420:Subset#3.OLAP.[],group={},EXPR$0=$SUM0($0),agg#1=COUNT($0),ctx=),
rowcount=22.5, cumulative cost=\{inf} rel#442:Subset#5.ENUMERABLE.[], best=null, importance=0.405
rel#441:OLAPToEnumerableConverter.ENUMERABLE.[](input=rel#430:Subset#5.OLAP.[]), rowcount=22.5,
cumulative cost=\{inf} while executing SQL: "select sum(v_revenue) from ssb.p_lineorder left
join ssb.dates on lo_orderdate = d_datekey and d_year = 1993"

 

 

> Should throw error when sql contains non-equi left join
> -------------------------------------------------------
>
>                 Key: KYLIN-2777
>                 URL: https://issues.apache.org/jira/browse/KYLIN-2777
>             Project: Kylin
>          Issue Type: Bug
>            Reporter: Roger Shi
>            Priority: Major
>
> SQL like:
> select fact_table.A, lookup_table.B
> from fact_table fa left join lookup_table lo
> on fa.C = lo.D and fa.E = 'asdf'
> The join is non-equi left join. It's not support in Kylin data model, should throw error
instead of ignore it. Current implementation leads to wrong result instead of clear error
message.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message