pk table month parition query filter incorrect when query add pk filed

MyLanPangzi commented 3 weeks ago

Steps to reproduce the behavior (Required)

CREATE TABLE dwd_daw_bonus_requests_realtime ( create_day date NOT NULL COMMENT "", parent_id bigint(20) NOT NULL COMMENT "", user_id bigint(20) NOT NULL COMMENT "", link_key varchar(65533) NOT NULL COMMENT "", id bigint(20) NULL COMMENT "", bonus_ev bigint(20) NULL COMMENT "", currency tinyint(4) NULL COMMENT "", bonus_type int(11) NULL COMMENT "", operator_id bigint(20) NULL COMMENT "", ack_msg varchar(65533) NULL COMMENT "", status tinyint(4) NULL COMMENT "", promotion_name varchar(65533) NULL COMMENT "", promotion_url varchar(65533) NULL COMMENT "", extra varchar(65533) NULL COMMENT "", event_time datetime NULL COMMENT "", update_time datetime NULL COMMENT "", create_time datetime NULL COMMENT "", insert_at datetime NULL COMMENT "" ) ENGINE=OLAP PRIMARY KEY(create_day, parent_id, user_id, link_key) PARTITION BY date_trunc('month', create_day) DISTRIBUTED BY HASH(create_day, parent_id, user_id, link_key) PROPERTIES ( "replication_num" = "3", "in_memory" = "false", "enable_persistent_index" = "true", "replicated_storage" = "true", "compression" = "LZ4" );

mysql> select link_key,create_day -> from dwd.dwd_daw_bonus_requests_realtime -> where create_day >=date('2023-01-01') and create_day <=date('2023-12-01') -> and link_key='bonus-register-734902' -> ; +-----------------------+------------+ | link_key | create_day | +-----------------------+------------+ | bonus-register-734902 | 2020-06-18 | +-----------------------+------------+ 1 row in set (0.00 sec)

Expected behavior (Required)

no result return

Real behavior (Required)

+-----------------------+------------+ | link_key | create_day | +-----------------------+------------+ | bonus-register-734902 | 2020-06-18 | +-----------------------+------------+ 1 row in set (0.00 sec)

StarRocks version (Required)

You can get the StarRocks version by executing SQL select current_version()
3.1.0

MyLanPangzi commented 3 weeks ago

explain

mysql> explain 
    -> select link_key,create_day
    -> from dwd.dwd_daw_bonus_requests_realtime
    -> where create_day >=date('2023-01-01')  and  create_day <=date('2023-12-01')
    ->   and link_key='bonus-register-734902';
+-----------------------------------------------------------------------------------------------------+
| Explain String                                                                                      |
+-----------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                                                     |
|  OUTPUT EXPRS:4: link_key | 1: create_day                                                           |
|   PARTITION: UNPARTITIONED                                                                          |
|                                                                                                     |
|   RESULT SINK                                                                                       |
|                                                                                                     |
|   1:EXCHANGE                                                                                        |
|                                                                                                     |
| PLAN FRAGMENT 1                                                                                     |
|  OUTPUT EXPRS:                                                                                      |
|   PARTITION: RANDOM                                                                                 |
|                                                                                                     |
|   STREAM DATA SINK                                                                                  |
|     EXCHANGE ID: 01                                                                                 |
|     UNPARTITIONED                                                                                   |
|                                                                                                     |
|   0:OlapScanNode                                                                                    |
|      TABLE: dwd_daw_bonus_requests_realtime                                                         |
|      PREAGGREGATION: ON                                                                             |
|      PREDICATES: 1: create_day <= '2023-12-01', 4: link_key = 'bonus-register-734902'               |
|      partitions=12/58                                                                               |
|      rollup: dwd_daw_bonus_requests_realtime                                                        |
|      tabletRatio=57/57                                                                              |
|      tabletList=8458421,8458425,8458429,8458433,8458437,8458441,8459121,8459125,8459129,8459133 ... |
|      cardinality=1                                                                                  |
|      avgRowSize=36.10247                                                                            |
|      numNodes=0                                                                                     |
+-----------------------------------------------------------------------------------------------------+
27 rows in set (0.05 sec)

MyLanPangzi commented 3 weeks ago

when i modify query

mysql> select link_key,create_day
    -> from dwd.dwd_daw_bonus_requests_realtime
    -> where create_day >date('2023-01-01')  and  create_day <=date('2023-12-01')
    ->   and link_key='bonus-register-734902';
Empty set (0.08 sec)

the result is correct.

MyLanPangzi commented 3 weeks ago

explain 
    -> select link_key,create_day
    -> from dwd.dwd_daw_bonus_requests_realtime
    -> where create_day >date('2023-01-01')  and  create_day <=date('2023-12-01')
    ->   and link_key='bonus-register-734902';
+---------------------------------------------------------------------------------------------------------------------+
| Explain String                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                                                                     |
|  OUTPUT EXPRS:4: link_key | 1: create_day                                                                           |
|   PARTITION: UNPARTITIONED                                                                                          |
|                                                                                                                     |
|   RESULT SINK                                                                                                       |
|                                                                                                                     |
|   1:EXCHANGE                                                                                                        |
|                                                                                                                     |
| PLAN FRAGMENT 1                                                                                                     |
|  OUTPUT EXPRS:                                                                                                      |
|   PARTITION: RANDOM                                                                                                 |
|                                                                                                                     |
|   STREAM DATA SINK                                                                                                  |
|     EXCHANGE ID: 01                                                                                                 |
|     UNPARTITIONED                                                                                                   |
|                                                                                                                     |
|   0:OlapScanNode                                                                                                    |
|      TABLE: dwd_daw_bonus_requests_realtime                                                                         |
|      PREAGGREGATION: ON                                                                                             |
|      PREDICATES: 1: create_day > '2023-01-01', 1: create_day <= '2023-12-01', 4: link_key = 'bonus-register-734902' |
|      partitions=12/58                                                                                               |
|      rollup: dwd_daw_bonus_requests_realtime                                                                        |
|      tabletRatio=57/57                                                                                              |
|      tabletList=8458421,8458425,8458429,8458433,8458437,8458441,8459121,8459125,8459129,8459133 ...                 |
|      cardinality=1                                                                                                  |
|      avgRowSize=36.10247                                                                                            |
|      numNodes=0                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------+
27 rows in set (0.00 sec)

StarRocks / starrocks