In some condition use sub-query make doris return wrong value #1097

qdore · 2019-05-06T10:41:39Z

Describe the bug

correct result:
wrong result

To Reproduce
Steps to reproduce the behavior:

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

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;

The text was updated successfully, but these errors were encountered:

imay · 2019-05-06T11:00:26Z

@qdore

can you explain these two queries and paste the results?
can you try remove ORDER BY event_day DESC in query 2 and check the result?

qdore · 2019-05-06T11:07:38Z

explain results:
(1) right result:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                                                                                                                                                                            |
|  OUTPUT EXPRS:<slot 5> | <slot 30> |                                                                                                                                                                                       |
|   PARTITION: UNPARTITIONED                                                                                                                                                                                                 |
|                                                                                                                                                                                                                            |
|   RESULT SINK                                                                                                                                                                                                              |
|                                                                                                                                                                                                                            |
|   20:EXCHANGE                                                                                                                                                                                                              |
|      tuple ids: 1 10N                                                                                                                                                                                                      |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 1                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: HASH_PARTITIONED: <slot 5>                                                                                                                                                                                    |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 20                                                                                                                                                                                                        |
|     UNPARTITIONED                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
|   9:HASH JOIN                                                                                                                                                                                                              |
|   |  join op: LEFT OUTER JOIN (BROADCAST)                                                                                                                                                                                  |
|   |  hash predicates:                                                                                                                                                                                                      |
|   |    <slot 5> = <slot 29>                                                                                                                                                                                                |
|   |  tuple ids: 1 10N                                                                                                                                                                                                      |
|   |                                                                                                                                                                                                                        |
|   |----19:EXCHANGE                                                                                                                                                                                                         |
|   |       tuple ids: 10                                                                                                                                                                                                    |
|   |                                                                                                                                                                                                                        |
|   11:AGGREGATE (merge finalize)                                                                                                                                                                                            |
|   |  group by: <slot 5>                                                                                                                                                                                                    |
|   |  tuple ids: 1                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   10:EXCHANGE                                                                                                                                                                                                              |
|      tuple ids: 1                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 2                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: HASH_PARTITIONED: <slot 27>                                                                                                                                                                                   |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 19                                                                                                                                                                                                        |
|     UNPARTITIONED                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
|   8:AGGREGATE (update finalize)                                                                                                                                                                                            |
|   |  output: count(<slot 28>)                                                                                                                                                                                              |
|   |  group by: <slot 27>                                                                                                                                                                                                   |
|   |  tuple ids: 10                                                                                                                                                                                                         |
|   |                                                                                                                                                                                                                        |
|   18:AGGREGATE (merge serialize)                                                                                                                                                                                           |
|   |  group by: <slot 27>, <slot 28>                                                                                                                                                                                        |
|   |  tuple ids: 9                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   17:EXCHANGE                                                                                                                                                                                                              |
|      tuple ids: 9                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 3                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: HASH_PARTITIONED: <slot 14>, <slot 15>                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 17                                                                                                                                                                                                        |
|     HASH_PARTITIONED: <slot 27>                                                                                                                                                                                            |
|                                                                                                                                                                                                                            |
|   7:AGGREGATE (update serialize)                                                                                                                                                                                           |
|   |  STREAMING                                                                                                                                                                                                             |
|   |  group by: <slot 14>, <slot 15>                                                                                                                                                                                        |
|   |  tuple ids: 9                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   6:HASH JOIN                                                                                                                                                                                                              |
|   |  join op: INNER JOIN (BROADCAST)                                                                                                                                                                                       |
|   |  hash predicates:                                                                                                                                                                                                      |
|   |    <slot 14> = <slot 23>                                                                                                                                                                                               |
|   |    <slot 15> = <slot 24>                                                                                                                                                                                               |
|   |  tuple ids: 4 7                                                                                                                                                                                                        |
|   |                                                                                                                                                                                                                        |
|   |----16:EXCHANGE                                                                                                                                                                                                         |
|   |       tuple ids: 7                                                                                                                                                                                                     |
|   |                                                                                                                                                                                                                        |
|   13:AGGREGATE (merge finalize)                                                                                                                                                                                            |
|   |  group by: <slot 14>, <slot 15>                                                                                                                                                                                        |
|   |  tuple ids: 4                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   12:EXCHANGE                                                                                                                                                                                                              |
|      tuple ids: 4                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 4                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: HASH_PARTITIONED: <slot 23>, <slot 24>                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 16                                                                                                                                                                                                        |
|     UNPARTITIONED                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
|   15:AGGREGATE (merge finalize)                                                                                                                                                                                            |
|   |  group by: <slot 23>, <slot 24>                                                                                                                                                                                        |
|   |  tuple ids: 7                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   14:EXCHANGE                                                                                                                                                                                                              |
|      tuple ids: 7                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 5                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: RANDOM                                                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 14                                                                                                                                                                                                        |
|     HASH_PARTITIONED: <slot 23>, <slot 24>                                                                                                                                                                                 |
|                                                                                                                                                                                                                            |
|   5:AGGREGATE (update serialize)                                                                                                                                                                                           |
|   |  STREAMING                                                                                                                                                                                                             |
|   |  group by: `event_day`, `cuid`                                                                                                                                                                                         |
|   |  tuple ids: 7                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   4:OlapScanNode                                                                                                                                                                                                           |
|      TABLE: visd_dws_topic_novel_wide_table_user                                                                                                                                                                           |
|      PREAGGREGATION: ON                                                                                                                                                                                                    |
|      PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_new` = 1.0      |
|      partitions=25/25                                                                                                                                                                                                      |
|      rollup: visd_dws_topic_novel_wide_table_user                                                                                                                                                                          |
|      buckets=800/800                                                                                                                                                                                                       |
|      cardinality=1060653874                                                                                                                                                                                                |
|      avgRowSize=29.075388                                                                                                                                                                                                  |
|      numNodes=30                                                                                                                                                                                                           |
|      tuple ids: 6                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 6                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: RANDOM                                                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 12                                                                                                                                                                                                        |
|     HASH_PARTITIONED: <slot 14>, <slot 15>                                                                                                                                                                                 |
|                                                                                                                                                                                                                            |
|   3:AGGREGATE (update serialize)                                                                                                                                                                                           |
|   |  STREAMING                                                                                                                                                                                                             |
|   |  group by: `event_day`, `cuid`                                                                                                                                                                                         |
|   |  tuple ids: 4                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   2:OlapScanNode                                                                                                                                                                                                           |
|      TABLE: visd_dws_topic_novel_wide_table_user                                                                                                                                                                           |
|      PREAGGREGATION: ON                                                                                                                                                                                                    |
|      PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_read_dau` = 1.0 |
|      partitions=25/25                                                                                                                                                                                                      |
|      rollup: visd_dws_topic_novel_wide_table_user                                                                                                                                                                          |
|      buckets=800/800                                                                                                                                                                                                       |
|      cardinality=1060653874                                                                                                                                                                                                |
|      avgRowSize=29.079033                                                                                                                                                                                                  |
|      numNodes=30                                                                                                                                                                                                           |
|      tuple ids: 3                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 7                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: RANDOM                                                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 10                                                                                                                                                                                                        |
|     HASH_PARTITIONED: <slot 5>                                                                                                                                                                                             |
|                                                                                                                                                                                                                            |
|   1:AGGREGATE (update serialize)                                                                                                                                                                                           |
|   |  STREAMING                                                                                                                                                                                                             |
|   |  group by: `event_day`                                                                                                                                                                                                 |
|   |  tuple ids: 1                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   0:OlapScanNode                                                                                                                                                                                                           |
|      TABLE: visd_dws_topic_novel_wide_table_user                                                                                                                                                                           |
|      PREAGGREGATION: ON                                                                                                                                                                                                    |
|      PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_dau` = 1.0      |
|      partitions=25/25                                                                                                                                                                                                      |
|      rollup: visd_dws_topic_novel_wide_table_user_no_cuid_userid_rollup                                                                                                                                                    |
|      buckets=800/800                                                                                                                                                                                                       |
|      cardinality=21101622                                                                                                                                                                                                  |
|      avgRowSize=170.06363                                                                                                                                                                                                  |
|      numNodes=30                                                                                                                                                                                                           |
|      tuple ids: 0                                                                                                                                                                                                          |

(2) wrong result:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                                                                                                                                                                            |
|  OUTPUT EXPRS:date(<slot 35>) | <slot 36> |                                                                                                                                                                                |
|   PARTITION: UNPARTITIONED                                                                                                                                                                                                 |
|                                                                                                                                                                                                                            |
|   RESULT SINK                                                                                                                                                                                                              |
|                                                                                                                                                                                                                            |
|   21:MERGING-EXCHANGE                                                                                                                                                                                                      |
|      limit: 1000                                                                                                                                                                                                           |
|      tuple ids: 13                                                                                                                                                                                                         |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 1                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: HASH_PARTITIONED: <slot 5>                                                                                                                                                                                    |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 21                                                                                                                                                                                                        |
|     UNPARTITIONED                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
|   10:TOP-N                                                                                                                                                                                                                 |
|   |  order by: <slot 35> DESC                                                                                                                                                                                              |
|   |  offset: 0                                                                                                                                                                                                             |
|   |  limit: 1000                                                                                                                                                                                                           |
|   |  tuple ids: 13                                                                                                                                                                                                         |
|   |                                                                                                                                                                                                                        |
|   9:HASH JOIN                                                                                                                                                                                                              |
|   |  join op: LEFT OUTER JOIN (BROADCAST)                                                                                                                                                                                  |
|   |  hash predicates:                                                                                                                                                                                                      |
|   |    <slot 5> = <slot 29>                                                                                                                                                                                                |
|   |  tuple ids: 1 10N                                                                                                                                                                                                      |
|   |                                                                                                                                                                                                                        |
|   |----20:EXCHANGE                                                                                                                                                                                                         |
|   |       tuple ids: 10                                                                                                                                                                                                    |
|   |                                                                                                                                                                                                                        |
|   12:AGGREGATE (merge finalize)                                                                                                                                                                                            |
|   |  group by: <slot 5>                                                                                                                                                                                                    |
|   |  tuple ids: 1                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   11:EXCHANGE                                                                                                                                                                                                              |
|      tuple ids: 1                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 2                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: HASH_PARTITIONED: <slot 27>                                                                                                                                                                                   |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 20                                                                                                                                                                                                        |
|     UNPARTITIONED                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
|   8:AGGREGATE (update finalize)                                                                                                                                                                                            |
|   |  output: count(<slot 28>)                                                                                                                                                                                              |
|   |  group by: <slot 27>                                                                                                                                                                                                   |
|   |  tuple ids: 10                                                                                                                                                                                                         |
|   |                                                                                                                                                                                                                        |
|   19:AGGREGATE (merge serialize)                                                                                                                                                                                           |
|   |  group by: <slot 27>, <slot 28>                                                                                                                                                                                        |
|   |  tuple ids: 9                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   18:EXCHANGE                                                                                                                                                                                                              |
|      tuple ids: 9                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 3                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: HASH_PARTITIONED: <slot 14>, <slot 15>                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 18                                                                                                                                                                                                        |
|     HASH_PARTITIONED: <slot 27>                                                                                                                                                                                            |
|                                                                                                                                                                                                                            |
|   7:AGGREGATE (update serialize)                                                                                                                                                                                           |
|   |  STREAMING                                                                                                                                                                                                             |
|   |  group by: <slot 14>, <slot 15>                                                                                                                                                                                        |
|   |  tuple ids: 9                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   6:HASH JOIN                                                                                                                                                                                                              |
|   |  join op: INNER JOIN (BROADCAST)                                                                                                                                                                                       |
|   |  hash predicates:                                                                                                                                                                                                      |
|   |    <slot 14> = <slot 23>                                                                                                                                                                                               |
|   |    <slot 15> = <slot 24>                                                                                                                                                                                               |
|   |  tuple ids: 4 7                                                                                                                                                                                                        |
|   |                                                                                                                                                                                                                        |
|   |----17:EXCHANGE                                                                                                                                                                                                         |
|   |       tuple ids: 7                                                                                                                                                                                                     |
|   |                                                                                                                                                                                                                        |
|   14:AGGREGATE (merge finalize)                                                                                                                                                                                            |
|   |  group by: <slot 14>, <slot 15>                                                                                                                                                                                        |
|   |  tuple ids: 4                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   13:EXCHANGE                                                                                                                                                                                                              |
|      tuple ids: 4                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 4                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: HASH_PARTITIONED: <slot 23>, <slot 24>                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 17                                                                                                                                                                                                        |
|     UNPARTITIONED                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
|   16:AGGREGATE (merge finalize)                                                                                                                                                                                            |
|   |  group by: <slot 23>, <slot 24>                                                                                                                                                                                        |
|   |  tuple ids: 7                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   15:EXCHANGE                                                                                                                                                                                                              |
|      tuple ids: 7                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 5                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: RANDOM                                                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 15                                                                                                                                                                                                        |
|     HASH_PARTITIONED: <slot 23>, <slot 24>                                                                                                                                                                                 |
|                                                                                                                                                                                                                            |
|   5:AGGREGATE (update serialize)                                                                                                                                                                                           |
|   |  STREAMING                                                                                                                                                                                                             |
|   |  group by: `event_day`, `cuid`                                                                                                                                                                                         |
|   |  tuple ids: 7                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   4:OlapScanNode                                                                                                                                                                                                           |
|      TABLE: visd_dws_topic_novel_wide_table_user                                                                                                                                                                           |
|      PREAGGREGATION: ON                                                                                                                                                                                                    |
|      PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_new` = 1.0      |
|      partitions=25/25                                                                                                                                                                                                      |
|      rollup: visd_dws_topic_novel_wide_table_user                                                                                                                                                                          |
|      buckets=800/800                                                                                                                                                                                                       |
|      cardinality=1060653874                                                                                                                                                                                                |
|      avgRowSize=29.077042                                                                                                                                                                                                  |
|      numNodes=30                                                                                                                                                                                                           |
|      tuple ids: 6                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 6                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: RANDOM                                                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 13                                                                                                                                                                                                        |
|     HASH_PARTITIONED: <slot 14>, <slot 15>                                                                                                                                                                                 |
|                                                                                                                                                                                                                            |
|   3:AGGREGATE (update serialize)                                                                                                                                                                                           |
|   |  STREAMING                                                                                                                                                                                                             |
|   |  group by: `event_day`, `cuid`                                                                                                                                                                                         |
|   |  tuple ids: 4                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   2:OlapScanNode                                                                                                                                                                                                           |
|      TABLE: visd_dws_topic_novel_wide_table_user                                                                                                                                                                           |
|      PREAGGREGATION: ON                                                                                                                                                                                                    |
|      PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_read_dau` = 1.0 |
|      partitions=25/25                                                                                                                                                                                                      |
|      rollup: visd_dws_topic_novel_wide_table_user                                                                                                                                                                          |
|      buckets=800/800                                                                                                                                                                                                       |
|      cardinality=1060653874                                                                                                                                                                                                |
|      avgRowSize=29.078333                                                                                                                                                                                                  |
|      numNodes=30                                                                                                                                                                                                           |
|      tuple ids: 3                                                                                                                                                                                                          |
|                                                                                                                                                                                                                            |
| PLAN FRAGMENT 7                                                                                                                                                                                                            |
|  OUTPUT EXPRS:                                                                                                                                                                                                             |
|   PARTITION: RANDOM                                                                                                                                                                                                        |
|                                                                                                                                                                                                                            |
|   STREAM DATA SINK                                                                                                                                                                                                         |
|     EXCHANGE ID: 11                                                                                                                                                                                                        |
|     HASH_PARTITIONED: <slot 5>                                                                                                                                                                                             |
|                                                                                                                                                                                                                            |
|   1:AGGREGATE (update serialize)                                                                                                                                                                                           |
|   |  STREAMING                                                                                                                                                                                                             |
|   |  group by: `event_day`                                                                                                                                                                                                 |
|   |  tuple ids: 1                                                                                                                                                                                                          |
|   |                                                                                                                                                                                                                        |
|   0:OlapScanNode                                                                                                                                                                                                           |
|      TABLE: visd_dws_topic_novel_wide_table_user                                                                                                                                                                           |
|      PREAGGREGATION: ON                                                                                                                                                                                                    |
|      PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_dau` = 1.0      |
|      partitions=25/25                                                                                                                                                                                                      |
|      rollup: visd_dws_topic_novel_wide_table_user_no_cuid_userid_rollup                                                                                                                                                    |
|      buckets=800/800                                                                                                                                                                                                       |
|      cardinality=21090527                                                                                                                                                                                                  |
|      avgRowSize=170.1286                                                                                                                                                                                                   |
|      numNodes=30                                                                                                                                                                                                           |
|      tuple ids: 0                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

remove order by event_day is ok:

imay · 2019-05-06T11:12:31Z

@qdore

It seems that the problem is located in our order by implementation. We will look into it and try to reproduce this problem.

And if you are convenient, you can help us to simplify this query and to reproduce this problem.

qdore · 2019-05-06T11:18:23Z

I'm very glad to help location this problem, I will try to simplify this query. ☺

chenhao7253886 · 2019-05-06T12:07:04Z

Did you have upgraded to our lastest version? This seems to be solved before.

qdore · 2019-05-06T14:11:31Z

The version I tried is 0.9.0, I will try the newest master branch. And it will be nice if you paste me the patch commit log~

chenhao7253886 · 2019-05-07T01:30:48Z

OK, i will conform whether it has been fixed.

qdore · 2019-05-08T11:40:51Z

no sub-query, no order by, still get wrong result:

qdore · 2019-05-08T11:41:55Z

The special thing is that I use count(distinct cuid) instead of HLL_UNION_AGG(cuid_uv_set), after that the result is very weird.

qdore · 2019-05-08T11:52:18Z

Seems when I use count distinct as the denominator will lead to wrong result.

qdore · 2019-05-08T11:56:06Z

When I use (cast(sum(read_duration) as bigint))/count(distinct cuid), the result become correct. I guess The function is overflowed

imay · 2019-05-08T11:57:45Z

When I use (cast(sum(read_duration) as bigint))/count(distinct cuid), the result become corrent. I guess The function is overflowed when I use "count distinct"

what is column read_duration type? and what's your Doris's version

qdore · 2019-05-08T12:07:11Z

read_duration ： DECIMAL(20,2)
doris version: 0.9

lide-reed · 2019-05-08T13:09:26Z

OK, it seems there is a bug in DECIMAL, thank you, I will track it ASAP.

lide-reed · 2019-05-09T03:03:11Z

Can you add my wechat "leanderleereed"? I want to talk more detail about this issue?

qdore · 2019-05-09T09:19:37Z

Sure~

lide-reed · 2019-05-09T10:49:14Z

This bug has been fixed in the tag of Doris 0.9.0, and please upgrade your Doris.

qdore · 2019-05-13T03:45:09Z

Seems version 0.9.0-rc02 has solved this bug, Thx!

…o fix memtracker (apache#1172) * [Fix](memory-leak) Fix boost::stacktrace memory leak (apache#1097) * [Fix](selectdb-cloud) Several picks to fix memtracker (apache#1087) * [enhancement](memtracker) Add independent and unique scanner mem tracker for each query (apache#13262) * [enhancement](memory) Print memory usage log when memory allocation fails (apache#13301) * [enhancement](memtracker) Print query memory usage log every second when `memory_verbose_track` is enabled (apache#13302) * [fix](memory) Fix USE_JEMALLOC=true UBSAN compilation error apache#13398 * [enhancement](memtracker) Fix brpc causing query mem tracker to be inaccurate apache#13401 * [fix](memtracker) Fix transmit_tracker null pointer because phamp is not thread safe apache#13528 * [enhancement](memtracker) Fix Brpc mem count and refactored thread context macro (apache#13469) * [enhancement](memtracker) Refactor load channel + memtable mem tracker (apache#13795) * [fix](load) Fix load channel mgr lock (apache#13960) hot fix load channel mgr lock * [fix](memtracker) Fix DCHECK !std::count(_consumer_tracker_stack.begin(), _consumer_tracker_stack.end(), tracker) * [tempfix][memtracker] wait pick 0b945fe Co-authored-by: Xinyi Zou <zouxinyi02@gmail.com>

chenhao7253886 self-assigned this May 6, 2019

qdore closed this as completed May 13, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

In some condition use sub-query make doris return wrong value #1097

In some condition use sub-query make doris return wrong value #1097

qdore commented May 6, 2019 •

edited

Loading

imay commented May 6, 2019

qdore commented May 6, 2019 •

edited

Loading

imay commented May 6, 2019

qdore commented May 6, 2019

chenhao7253886 commented May 6, 2019

qdore commented May 6, 2019

chenhao7253886 commented May 7, 2019 •

edited

Loading

qdore commented May 8, 2019 •

edited

Loading

qdore commented May 8, 2019 •

edited

Loading

qdore commented May 8, 2019

qdore commented May 8, 2019 •

edited

Loading

imay commented May 8, 2019 •

edited

Loading

qdore commented May 8, 2019

lide-reed commented May 8, 2019

lide-reed commented May 9, 2019 •

edited

Loading

qdore commented May 9, 2019

lide-reed commented May 9, 2019

qdore commented May 13, 2019

In some condition use sub-query make doris return wrong value #1097

In some condition use sub-query make doris return wrong value #1097

Comments

qdore commented May 6, 2019 • edited Loading

imay commented May 6, 2019

qdore commented May 6, 2019 • edited Loading

imay commented May 6, 2019

qdore commented May 6, 2019

chenhao7253886 commented May 6, 2019

qdore commented May 6, 2019

chenhao7253886 commented May 7, 2019 • edited Loading

qdore commented May 8, 2019 • edited Loading

qdore commented May 8, 2019 • edited Loading

qdore commented May 8, 2019

qdore commented May 8, 2019 • edited Loading

imay commented May 8, 2019 • edited Loading

qdore commented May 8, 2019

lide-reed commented May 8, 2019

lide-reed commented May 9, 2019 • edited Loading

qdore commented May 9, 2019

lide-reed commented May 9, 2019

qdore commented May 13, 2019

qdore commented May 6, 2019 •

edited

Loading

qdore commented May 6, 2019 •

edited

Loading

chenhao7253886 commented May 7, 2019 •

edited

Loading

qdore commented May 8, 2019 •

edited

Loading

qdore commented May 8, 2019 •

edited

Loading

qdore commented May 8, 2019 •

edited

Loading

imay commented May 8, 2019 •

edited

Loading

lide-reed commented May 9, 2019 •

edited

Loading