IPV4格式的字段，在进行多条件查询时，靠后的查询条件丢失。

Viewed 21
2.1

src_ip为IPV4格式的字段，在执行下面数据时

SELECT * from test where (src_ip<"10.0.0.0" OR src_ip>"10.255.255.255") AND (src_ip<"192.168.0.0" OR src_ip>"192.168.255.255")

条件 (src_ip<"192.168.0.0" OR src_ip>"192.168.255.255") 会丢失。explian结果显示：

PREDICATES: (src_ip[#15] < 10.0.0.0) OR (src_ip[#15] > 10.255.255.255)

调整语句为

SELECT * from test where (src_ip<"192.168.0.0" OR src_ip>"192.168.255.255") AND (src_ip<"10.0.0.0" OR src_ip>"10.255.255.255")

条件 (src_ip<"10.0.0.0" OR src_ip>"10.255.255.255") 会丢失，explian结果显示：

PREDICATES: (src_ip[#15] < 192.168.0.0) OR (src_ip[#15] > 192.168.255.255)

相同的测试用例，在varchar类型的字段下就不存在。

zhou1
1 Answers

建表语句

CREATE TABLE `test` (
  `event_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '事件时间',
  `device_ip` VARCHAR(48) NULL COMMENT '设备IP',
  `event_id` BIGINT NOT NULL AUTO_INCREMENT(1) COMMENT '事件ID',
  `src_ip` IPV4 NULL COMMENT '来源IPV4',
  INDEX idx_device_ip (`device_ip`) USING INVERTED,
  INDEX idx_src_port (`src_ip`) USING INVERTED
) ENGINE=OLAP
DUPLICATE KEY(`event_time`, `device_ip`, `event_id`)
COMMENT 'test'
AUTO PARTITION BY RANGE (date_trunc(`event_time`, 'month'))
(PARTITION p20240701000000 VALUES [('2024-07-01 00:00:00'), ('2024-08-01 00:00:00')),
PARTITION p20240801000000 VALUES [('2024-08-01 00:00:00'), ('2024-09-01 00:00:00')))
DISTRIBUTED BY HASH(`event_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);
zhou1