2.0.2 关联查询不到结果问题

Viewed 46

CREATE TABLE test1 (
id int(11) NOT NULL,
user_id int(11) NOT NULL,
brand int(11) NOT NULL ,
regulate int(11) NOT NULL,
) ENGINE=OLAP
UNIQUE KEY(id, user_id, brand, regulate)
COMMENT 'OLAP'
PARTITION BY LIST(regulate)
(PARTITION P_VJPSVG_UMFSC_MOSL VALUES IN ("77714","77711","77710"),
PARTITION P_STSVG_STASIC VALUES IN ("77715","77725"))
DISTRIBUTED BY HASH(user_id, brand, regulate) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"store_row_column" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

CREATE TABLE test2 (
id int(11) NOT NULL,
brand int(11) NOT NULL ,
regulate int(11) NOT NULL,
user_id int(11) NOT NULL,
) ENGINE=OLAP
UNIQUE KEY(id, brand, regulate, user_id)
COMMENT 'OLAP'
PARTITION BY LIST(regulate)
(PARTITION P_VJPSVG_UMFSC_MOSL VALUES IN ("77714","77711","77710"),
PARTITION P_STSVG_STASIC VALUES IN ("77715","77725"))
DISTRIBUTED BY HASH(user_id, brand, regulate) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"store_row_column" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

INSERT INTO test1(id,user_id,brand,regulate) values(66297,910369,5,77711);
INSERT INTO test2(id,brand,regulate,user_id) values(79220,5,77711,910369);

SELECT
a.user_id,
a.brand,
a.regulate,
c.user_id AS c_user_id
FROM
test1 a
inner JOIN test2 c ON ( a.user_id = c.user_id AND a.brand = c.brand AND a.regulate = c.regulate)
WHERE
a.id = 66297
AND a.brand = 5
AND a.regulate = 77711;

SELECT * from test1 a where a.id = 66297 AND a.brand = 5 AND a.regulate = 77711;

SELECT * from test2 c where c.user_id =910369 and c.brand=5 and c.regulate = 77711;

------关联查询:查不出数据(去点a.id条件或者去掉一个关联条件都能查出数据):----------
image.png

------------------单独查询test1:-------------------------------
image.png

------------------单独查询test2:------------------------------------
image.png

1 Answers

应该是老版本缺陷导致;
可以考虑升级至>=2.0.15 或 2.1.6的稳定版本,在2.1.6本地测试的话是正常的哈。