Hello Zhaoqing, Very good question. Kylin is designed for aggregated query. But in SQL2, the subquery will do the detailed query first. In Kylin, the detailed query will get result from based cuboid, not the raw fact table. The result is not accurate. To learn more about this behavior, please check https://issues.apache.org/jira/browse/KYLIN-1792
With Warm regards Billy Liu 2018-02-11 18:44 GMT+08:00 陈兆卿 <[email protected]>: > Hi devs of Kylin: > > I’m a engineer from China and new to Kylin. I came across a problem when > using it. > > I had two queries that I believed should return the same result. But > actually they did not in Kylin. > > I tried them in Hive and they returned the same. > > SQL1 will return the same result as in Hive, but the SQL2. > > It seems that the SQL2 returned a error result. > > I cannot figure it out. > > So I need your help. > > > > The two queries are: > > *SQL1:* > > *SELECT count(*)* > > *FROM search_order_15* > > *LEFT JOIN order_product_15_dim ON > (search_order_15.dt=order_product_15_dim.dt* > > * AND > search_order_15.main_product_id=order_product_15_dim.main_product_id)* > > *WHERE search_order_15.dt=date'2017-01-01**';* > > > > *SQL2:* > > * SELECT count(*)* > > *FROM* > > * (SELECT ** > > * FROM search_order_15* > > * WHERE dt=date'2017-01-01') lt* > > *LEFT JOIN* > > * (SELECT ** > > * FROM order_product_15_dim* > > * WHERE dt=date'2017-01-01') rt ON > (lt.main_product_id=rt.main_product_id);* > > > > > > The model is: > > > > (Kylin 2.2.0) >
