[
https://issues.apache.org/jira/browse/KYLIN-1792?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15333199#comment-15333199
]
hongbin ma commented on KYLIN-1792:
-----------------------------------
[email protected] posted a mail called "Question about query sample cube table
KYLIN_SALES" in kylin user list, the JIRA should also take his question as a
reference:
got a problem about query samle cube table "KYLIN_SALES"
The cube SQL is :
SELECT
KYLIN_SALES.PART_DT
,KYLIN_SALES.LEAF_CATEG_ID
,KYLIN_SALES.LSTG_SITE_ID
,KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME
,KYLIN_CATEGORY_GROUPINGS.CATEG_LVL2_NAME
,KYLIN_CATEGORY_GROUPINGS.CATEG_LVL3_NAME
,KYLIN_SALES.LSTG_FORMAT_NAME
,KYLIN_SALES.PRICE
,KYLIN_SALES.SELLER_ID
FROM DEFAULT.KYLIN_SALES as KYLIN_SALES
INNER JOIN DEFAULT.KYLIN_CAL_DT as KYLIN_CAL_DT
ON KYLIN_SALES.PART_DT = KYLIN_CAL_DT.CAL_DT
INNER JOIN DEFAULT.KYLIN_CATEGORY_GROUPINGS as KYLIN_CATEGORY_GROUPINGS
ON KYLIN_SALES.LEAF_CATEG_ID = KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND
KYLIN_SALES.LSTG_SITE_ID = KYLIN_CATEGORY_GROUPINGS.SITE_ID
My query SQL1 （works OK）:
SELECT
KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME,
SUM(price) as sum__price
FROM KYLIN_SALES as KYLIN_SALES
INNER JOIN KYLIN_CATEGORY_GROUPINGS as KYLIN_CATEGORY_GROUPINGS
ON KYLIN_SALES.LEAF_CATEG_ID = KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND
KYLIN_SALES.LSTG_SITE_ID = KYLIN_CATEGORY_GROUPINGS.SITE_ID
GROUP BY KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME
My query SQL2 (works OK) :
SELECT
KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME,
SUM(price) as sum__price
FROM KYLIN_SALES as KYLIN_SALES
INNER JOIN (SELECT * FROM KYLIN_CATEGORY_GROUPINGS) as KYLIN_CATEGORY_GROUPINGS
ON KYLIN_SALES.LEAF_CATEG_ID = KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND
KYLIN_SALES.LSTG_SITE_ID = KYLIN_CATEGORY_GROUPINGS.SITE_ID
GROUP BY KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME;
My query SQL3 (No data result) :
SELECT
KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME,
SUM(price) as sum__price
FROM KYLIN_SALES as KYLIN_SALES
INNER JOIN (SELECT LEAF_CATEG_ID, SITE_ID,META_CATEG_NAME FROM
KYLIN_CATEGORY_GROUPINGS) as KYLIN_CATEGORY_GROUPINGS
ON KYLIN_SALES.LEAF_CATEG_ID = KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND
KYLIN_SALES.LSTG_SITE_ID = KYLIN_CATEGORY_GROUPINGS.SITE_ID
GROUP BY KYLIN_CATEGORY_GROUPINGS.META_CATEG_NAME
What's different from SQL2 and SQL3?
But in my custom Cube, the query like SQL3 also works OK.
Thanks!
> behaviours for non-aggregated queries
> -------------------------------------
>
> Key: KYLIN-1792
> URL: https://issues.apache.org/jira/browse/KYLIN-1792
> Project: Kylin
> Issue Type: Improvement
> Affects Versions: v1.5.2
> Reporter: hongbin ma
> Assignee: Yiming Liu
>
> Even though kylin is not designed for non-aggregated queries, people do
> frequently use queries like
> {code:sql}
> select * from fact limit
> {code}
> to verify their cubes. Recently many new users reported problems when running
> such queries, we'll use this JIRA as an umbrella to track all related issues.
> for such queries on fact table:
> - select * from fact : should return results from base cuboid (thus not raw
> fact table data) and MIGHT crash query server or region server
> - select * from fact limit x : should return results from base cuboid (thus
> not raw fact table data) and SHOULD NOT crash query server or region server.
> Current there's a known issue KYLIN-1787 fails to push down the limit value
> - select a,b from fact: should return results from base cuboid (thus not raw
> fact table data) and MIGHT crash query server or region server (when a or b
> contains is a measure, the query only works when you defined a sum() measure )
> - select a,b from fact limit x: should return results from base cuboid (thus
> not raw fact table data) and SHOULD NOT crash query server or region server
> (when a or b contains is a measure, the query only works when you defined a
> sum() measure)
> such four kinds of queries on look up tables should return correct results
> from the lookup table snapshot directly, and even without the limit clause,
> they have a low possibility to crash query server or region server because
> lookup table tends to be small. However, it's still better to make sure limit
> is taking effect.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
