[
https://issues.apache.org/jira/browse/KYLIN-2983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16755106#comment-16755106
]
Marko Ćurlin commented on KYLIN-2983:
-------------------------------------
I wouldn't consider this an improvement, but a bug fix. There is no reason for
the order of tables in INNER JOIN to affect the result, or the lack of it.
> 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.0.0, v2.1.0
> Reporter: Roberto Tardío Olmos
> Assignee: liyang
> Priority: Major
> Labels: features, scope
>
> 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_
> {color:red}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]]{color}
> 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 the kind of Hive Source Schema. What do you think about?
> This issue could lead to human errors writing 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
(v7.6.3#76005)
