[GitHub] [incubator-doris] qdore opened a new issue #1097: In some condition use sub-query make doris return wrong value

Mon, 06 May 2019 03:41:53 -0700 

qdore opened a new issue #1097: In some condition use sub-query make doris 
return wrong value
URL: https://github.com/apache/incubator-doris/issues/1097
 
 
   **Describe the bug**
   1. correct result:
   
![image](https://user-images.githubusercontent.com/6831829/57220531-3f0af500-702e-11e9-973d-e8c99543d3a9.png)
   2. wrong result
   
![image](https://user-images.githubusercontent.com/6831829/57220547-4fbb6b00-702e-11e9-8d13-f497659019a7.png)
   
   
   
   
   **To Reproduce**
   Steps to reproduce the behavior:
   1. Use a normal query sql: 
   
   ```
   SELECT a.event_day,
             e.new_read_num as new_read_num
      FROM
        (SELECT event_day,
                HLL_UNION_AGG(cuid_uv_set) AS 'all_user_num'
         FROM visd_dws_topic_novel_wide_table_user
         WHERE sid IN ('all')
           AND value IN ('all')
           AND event_day >= STR_TO_DATE('2019-04-29 00:00:00', '%Y-%m-%d 
%H:%i:%s')
           AND event_day <= STR_TO_DATE('2019-05-06 00:00:00', '%Y-%m-%d 
%H:%i:%s')
           AND is_dau=1
         GROUP BY event_day) a
        
        LEFT OUTER JOIN
        
        (SELECT t1.event_day,
                count(distinct t1.cuid) AS 'new_read_num'
         FROM
           ( SELECT distinct event_day,
                             cuid
            FROM visd_dws_topic_novel_wide_table_user
            WHERE sid IN ('all')
              AND value IN ('all')
              AND event_day >= STR_TO_DATE('2019-04-29 00:00:00', '%Y-%m-%d 
%H:%i:%s')
              AND event_day <= STR_TO_DATE('2019-05-06 00:00:00', '%Y-%m-%d 
%H:%i:%s')
              AND is_read_dau=1 ) t1
         JOIN
           ( SELECT distinct event_day,
                             cuid
            FROM visd_dws_topic_novel_wide_table_user
            WHERE sid IN ('all')
              AND value IN ('all')
              AND event_day >= STR_TO_DATE('2019-04-29 00:00:00', '%Y-%m-%d 
%H:%i:%s')
              AND event_day <= STR_TO_DATE('2019-05-06 00:00:00', '%Y-%m-%d 
%H:%i:%s')
              AND is_new=1 ) t2 on t1.event_day = t2.event_day
         and t1.cuid = t2.cuid
         GROUP BY event_day) e ON a.event_day=e.event_day
   ```
   
   2. move this sql to a sub query expr_qry
   
   ```
   SELECT DATE(event_day) AS __timestamp,
          expr_qry.new_read_num AS new_read_num
   FROM
     (SELECT a.event_day,
             e.new_read_num as new_read_num
      FROM
        (SELECT event_day,
                HLL_UNION_AGG(cuid_uv_set) AS 'all_user_num'
         FROM visd_dws_topic_novel_wide_table_user
         WHERE sid IN ('all')
           AND value IN ('all')
           AND event_day >= STR_TO_DATE('2019-04-29 00:00:00', '%Y-%m-%d 
%H:%i:%s')
           AND event_day <= STR_TO_DATE('2019-05-06 00:00:00', '%Y-%m-%d 
%H:%i:%s')
           AND is_dau=1
         GROUP BY event_day) a
        
        LEFT OUTER JOIN
        
        (SELECT t1.event_day,
                count(distinct t1.cuid) AS 'new_read_num'
         FROM
           ( SELECT distinct event_day,
                             cuid
            FROM visd_dws_topic_novel_wide_table_user
            WHERE sid IN ('all')
              AND value IN ('all')
              AND event_day >= STR_TO_DATE('2019-04-29 00:00:00', '%Y-%m-%d 
%H:%i:%s')
              AND event_day <= STR_TO_DATE('2019-05-06 00:00:00', '%Y-%m-%d 
%H:%i:%s')
              AND is_read_dau=1 ) t1
         JOIN
           ( SELECT distinct event_day,
                             cuid
            FROM visd_dws_topic_novel_wide_table_user
            WHERE sid IN ('all')
              AND value IN ('all')
              AND event_day >= STR_TO_DATE('2019-04-29 00:00:00', '%Y-%m-%d 
%H:%i:%s')
              AND event_day <= STR_TO_DATE('2019-05-06 00:00:00', '%Y-%m-%d 
%H:%i:%s')
              AND is_new=1 ) t2 on t1.event_day = t2.event_day
         and t1.cuid = t2.cuid
         GROUP BY event_day) e ON a.event_day=e.event_day) expr_qry
   ORDER BY event_day DESC
   LIMIT 1000;
   
   ```
   
   

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@doris.apache.org
For additional commands, e-mail: dev-h...@doris.apache.org

Reply via email to