search

StarRocks / starrocks

The world's fastest open query engine for sub-second analytics both on and off the data lakehouse. With the flexibility to support nearly any scenario, StarRocks provides best-in-class performance for multi-dimensional analytics, real-time analytics, and ad-hoc queries. A Linux Foundation project.
https://starrocks.io
Apache License 2.0
8.89k stars 1.78k forks source link

low cardinality string with dict produces incorrect result when left join+order by+limit #47108

Closed kaijianding closed 4 months ago

kaijianding commented 4 months ago

Steps to reproduce the behavior (Required)

  1. use ssb data for test https://docs.starrocks.io/zh/docs/benchmarking/SSB_Benchmarking/

    CREATE TABLE `lineorder` (
`lo_orderkey` int(11) NOT NULL COMMENT "",
`lo_linenumber` int(11) NOT NULL COMMENT "",
`lo_custkey` int(11) NOT NULL COMMENT "",
`lo_partkey` int(11) NOT NULL COMMENT "",
`lo_suppkey` int(11) NOT NULL COMMENT "",
`lo_orderdate` int(11) NOT NULL COMMENT "",
`lo_orderpriority` varchar(16) NOT NULL COMMENT "",
`lo_shippriority` int(11) NOT NULL COMMENT "",
`lo_quantity` int(11) NOT NULL COMMENT "",
`lo_extendedprice` int(11) NOT NULL COMMENT "",
`lo_ordtotalprice` int(11) NOT NULL COMMENT "",
`lo_discount` int(11) NOT NULL COMMENT "",
`lo_revenue` int(11) NOT NULL COMMENT "",
`lo_supplycost` int(11) NOT NULL COMMENT "",
`lo_tax` int(11) NOT NULL COMMENT "",
`lo_commitdate` int(11) NOT NULL COMMENT "",
`lo_shipmode` varchar(11) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`lo_orderkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 3
PROPERTIES (
"compression" = "LZ4",
"datacache.enable" = "true",
"enable_async_write_back" = "false",
"replication_num" = "1",
"storage_volume" = "builtin_storage_volume"
);

  2. sh bin/stream_load.sh data_dir to populate data for lineorder

  3. verify dict is used : explain select count(*), lo_shipmode from lineorder group by lo_shipmode;, Decode can be found from output

  4. query

    SELECT
cur.lo_custkey AS lo_custkey,
hb.lo_custkey AS hb_lo_custkey
FROM
(
    SELECT
        lo_custkey,
        lo_shipmode
    FROM
        lineorder
    GROUP BY
        lo_custkey,
        lo_shipmode
) cur
LEFT JOIN (
    SELECT
        lo_custkey,
        lo_shipmode
    FROM
        lineorder
    GROUP BY
        lo_custkey,
        lo_shipmode
) hb ON hb.lo_custkey = cur.lo_custkey
AND hb.lo_shipmode = cur.lo_shipmode
ORDER BY
lo_custkey
LIMIT
1;

Expected behavior (Required)

+------------+---------------+
| lo_custkey | hb_lo_custkey |
+------------+---------------+
|          1 |             1 |
+------------+---------------+
1 row in set (0.15 sec)

Real behavior (Required)

+------------+---------------+
| lo_custkey | hb_lo_custkey |
+------------+---------------+
|          1 |          NULL |
+------------+---------------+
1 row in set (0.07 sec)

+----------------------------------------------------------------+
| Explain String                                                 |
+----------------------------------------------------------------+
| PLAN FRAGMENT 0                                                |
|  OUTPUT EXPRS:3: lo_custkey | 20: lo_custkey                   |
|   PARTITION: UNPARTITIONED                                     |
|                                                                |
|   RESULT SINK                                                  |
|                                                                |
|   16:MERGING-EXCHANGE                                          |
|      limit: 1                                                  |
|                                                                |
| PLAN FRAGMENT 1                                                |
|  OUTPUT EXPRS:                                                 |
|   PARTITION: HASH_PARTITIONED: 20: lo_custkey, 35: lo_shipmode |
|                                                                |
|   STREAM DATA SINK                                             |
|     EXCHANGE ID: 16                                            |
|     UNPARTITIONED                                              |
|                                                                |
|   15:TOP-N                                                     |
|   |  order by: <slot 3> 3: lo_custkey ASC                      |
|   |  offset: 0                                                 |
|   |  limit: 1                                                  |
|   |                                                            |
|   14:Project                                                   |
|   |  <slot 3> : 3: lo_custkey                                  |
|   |  <slot 20> : 20: lo_custkey                                |
|   |                                                            |
|   13:HASH JOIN                                                 |
|   |  join op: RIGHT OUTER JOIN (BUCKET_SHUFFLE(S))             |
|   |  colocate: false, reason:                                  |
|   |  equal join conjunct: 20: lo_custkey = 3: lo_custkey       |
|   |  equal join conjunct: 34: lo_shipmode = 17: lo_shipmode    |
|   |                                                            |
|   |----12:EXCHANGE                                             |
|   |                                                            |
|   4:Decode                                                     |
|   |  <dict id 35> : <string id 34>                             |
|   |                                                            |
|   3:AGGREGATE (merge finalize)                                 |
|   |  group by: 20: lo_custkey, 35: lo_shipmode                 |
|   |                                                            |
|   2:EXCHANGE                                                   |
|                                                                |
| PLAN FRAGMENT 2                                                |
|  OUTPUT EXPRS:                                                 |
|   PARTITION: UNPARTITIONED                                     |
|                                                                |
|   STREAM DATA SINK                                             |
|     EXCHANGE ID: 12                                            |
|     HASH_PARTITIONED: 3: lo_custkey, 17: lo_shipmode           |
|                                                                |
|   11:Decode                                                    |
|   |  <dict id 36> : <string id 17>                             |
|   |                                                            |
|   10:MERGING-EXCHANGE                                          |
|      limit: 1                                                  |
|                                                                |
| PLAN FRAGMENT 3                                                |
|  OUTPUT EXPRS:                                                 |
|   PARTITION: HASH_PARTITIONED: 3: lo_custkey, 36: lo_shipmode  |
|                                                                |
|   STREAM DATA SINK                                             |
|     EXCHANGE ID: 10                                            |
|     UNPARTITIONED                                              |
|                                                                |
|   9:TOP-N                                                      |
|   |  order by: <slot 3> 3: lo_custkey ASC                      |
|   |  offset: 0                                                 |
|   |  limit: 1                                                  |
|   |                                                            |
|   8:AGGREGATE (merge finalize)                                 |
|   |  group by: 3: lo_custkey, 36: lo_shipmode                  |
|   |                                                            |
|   7:EXCHANGE                                                   |
|                                                                |
| PLAN FRAGMENT 4                                                |
|  OUTPUT EXPRS:                                                 |
|   colocate exec groups: ExecGroup{groupId=8, nodeIds=[5, 6]}   |
|   PARTITION: RANDOM                                            |
|                                                                |
|   STREAM DATA SINK                                             |
|     EXCHANGE ID: 07                                            |
|     HASH_PARTITIONED: 3: lo_custkey, 36: lo_shipmode           |
|                                                                |
|   6:AGGREGATE (update serialize)                               |
|   |  STREAMING                                                 |
|   |  group by: 3: lo_custkey, 36: lo_shipmode                  |
|   |                                                            |
|   5:OlapScanNode                                               |
|      TABLE: lineorder                                          |
|      PREAGGREGATION: ON                                        |
|      partitions=1/1                                            |
|      rollup: lineorder                                         |
|      tabletRatio=3/3                                           |
|      tabletList=17147,17148,17149                              |
|      cardinality=6001215                                       |
|      avgRowSize=8.285305                                       |
|                                                                |
| PLAN FRAGMENT 5                                                |
|  OUTPUT EXPRS:                                                 |
|   colocate exec groups: ExecGroup{groupId=3, nodeIds=[0, 1]}   |
|   PARTITION: RANDOM                                            |
|                                                                |
|   STREAM DATA SINK                                             |
|     EXCHANGE ID: 02                                            |
|     HASH_PARTITIONED: 20: lo_custkey, 35: lo_shipmode          |
|                                                                |
|   1:AGGREGATE (update serialize)                               |
|   |  STREAMING                                                 |
|   |  group by: 20: lo_custkey, 35: lo_shipmode                 |
|   |                                                            |
|   0:OlapScanNode                                               |
|      TABLE: lineorder                                          |
|      PREAGGREGATION: ON                                        |
|      partitions=1/1                                            |
|      rollup: lineorder                                         |
|      tabletRatio=3/3                                           |
|      tabletList=17147,17148,17149                              |
|      cardinality=6001215                                       |
|      avgRowSize=8.285305                                       |
+----------------------------------------------------------------+
119 rows in set (0.03 sec)

StarRocks version (Required)

stdpain commented 4 months ago

caused by lowcardinality V2. try to run with set low_cardinality_optimize_v2 = false;

kaijianding commented 4 months ago

@stdpain false works, 👍🏻 but there is notable performance decreasement if set to false，hope this issue could be fixed soon