[jira] [Created] (KYLIN-2983) Support for INNER JOINS in any order in the FROM clause

[JIRA]

Roberto Tardío Olmos created KYLIN-2983:
-------------------------------------------

             Summary: Support for INNER JOINS in any order in the FROM clause
                 Key: KYLIN-2983
                 URL: https://issues.apache.org/jira/browse/KYLIN-2983
             Project: Kylin
          Issue Type: Improvement
          Components: Query Engine
    Affects Versions: v2.1.0, v2.0.0
            Reporter: Roberto Tardío Olmos
            Assignee: liyang
            Priority: Normal


Kylin versions after Kylin 1.6 (2.0 and 2.1) do not support queries over 
dimensions with INNER JOINS if you do not put first the fact table in the FROM 
clause. I think this can be related with new snowflake schema support. 

For example. I defined a data model over a hive star schema. Therefore I 
defined INNER JOINS between fact table (F_RENDIMIENTO) and dimension tables 
(D_CURSO_ACADEMICO_VK). I also defined a cube and built it. 
The issue is, the following query goes:

select D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO, sum(CREDITOS)
 from F_RENDIMIENTO JOIN D_CURSO_ACADEMICO_VK ON 
F_RENDIMIENTO.ID_CURSO_ACADEMICO = D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO
 group by D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO

But susprisingly if I change the INNER JOIN order the following query does not 
go

select D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO, sum(CREDITOS)
from D_CURSO_ACADEMICO_VK JOIN F_RENDIMIENTO ON 
F_RENDIMIENTO.ID_CURSO_ACADEMICO = D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO
group by D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO

Error while executing SQL "select D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO, 
sum(CREDITOS) from D_CURSO_ACADEMICO_VK JOIN F_RENDIMIENTO ON 
F_RENDIMIENTO.ID_CURSO_ACADEMICO = D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO 
group by D_CURSO_ACADEMICO_VK.ID_CURSO_ACADEMICO LIMIT 50000": No realization 
found for rel#7393:OLAPTableScan.OLAP.[](table=[DM_ACAD_KYLIN_ORC, 
D_CURSO_ACADEMICO_VK],fields=[0, 1]), JoinDesc [type=INNER, 
primary_key=[ID_CURSO_ACADEMICO], foreign_key=[ID_CURSO_ACADEMICO]]

Since this issue is possible related to snowflake schema support, I suggest to 
differentiate in metadata and then, kylin query engine, between this two types 
of schemas, in order to allow this kind of queries over a Star Schema. Another 
possibility is to check on query engine if there is a INNER JOIN, independently 
of then kind of Hive Source Schema. What do you think about?

This issue could lead to human errors wrinting ANSI-92 SQL queries. Furthermore 
some BI tools, specially old ones, can generate this kind of queries not 
supported by Kylin 2.0 and 2.1.

Thanks!



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)