The data of the join table engine is not the newest after the server restarted

[wangmingfeng6666]

You have to provide the following information whenever possible.

Clickhouse 23.3.3.52 (official build).

hi, I have created a join table and inserted or updated several items for the table.

CREATE TABLE IF NOT EXISTS project_join (
    uuid String,
    name String,
    status_uuid String
) ENGINE = Join(Any, Left, uuid)
SETTINGS join_any_take_last_row = 1;

139cb5860a4f :) insert into project_join values ('ER2jENrjYHoW9TmT','hahaha','in_progress');
139cb5860a4f :) select * from project_join;
┌─uuid─────────────┬─name───────────────────┬─status_uuid─┐
│ ER2jENrjYHoW9TmT │ hahaha                 │ in_progress │
└──────────────────┴────────────────────────┴─────────────┘
... ...
139cb5860a4f :) insert into project_join values ('ER2jENrjYHoW9TmT','hahaha2','in_progress');
139cb5860a4f :) select * from project_join;

┌─uuid─────────────┬─name───────────────────┬─status_uuid─┐
│ ER2jENrjYHoW9TmT │ hahaha2                │ in_progress │
└──────────────────┴────────────────────────┴─────────────┘

Then I restarted the server. But I got the old data from the table.

┌─uuid─────────────┬─name───────────────────┬─status_uuid─┐
│ ER2jENrjYHoW9TmT │ hahaha                 │ in_progress │
└──────────────────┴────────────────────────┴─────────────┘

A clear and concise description of what works not as it is supposed to.

This is the table data dir:

root@139cb5860a4f:/var/lib/clickhouse/data/default/project_join# ls -lt
total 32
drwxr-x--- 2 clickhouse clickhouse 4096 Jun 15 08:46 tmp
-rw-r----- 1 clickhouse clickhouse  101 Jun 15 08:46 14.bin
-rw-r----- 1 clickhouse clickhouse  101 Jun 15 08:46 13.bin
-rw-r----- 1 clickhouse clickhouse  206 Jun 15 08:46 12.bin
-rw-r----- 1 clickhouse clickhouse  150 Jun 15 08:45 11.bin
-rw-r----- 1 clickhouse clickhouse   94 Jun 15 08:45 10.bin
-rw-r----- 1 clickhouse clickhouse  149 Jun 15 08:45 9.bin
-rw-r----- 1 clickhouse clickhouse  149 Jun 15 08:45 8.bin

This is the server restart logs:

2023.06.15 08:48:11.427848 [ 2768 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/267/26798441-0291-4e58-8eb4-0f97f7a50da5/11.bin. 2 rows, 156.00 B. State has 2 unique rows.
2023.06.15 08:48:11.427911 [ 2768 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/267/26798441-0291-4e58-8eb4-0f97f7a50da5/10.bin. 1 rows, 67.00 B. State has 2 unique rows.
2023.06.15 08:48:11.427975 [ 2768 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/267/26798441-0291-4e58-8eb4-0f97f7a50da5/12.bin. 33 rows, 2.35 KiB. State has 2 unique rows.
2023.06.15 08:48:11.428030 [ 2768 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/267/26798441-0291-4e58-8eb4-0f97f7a50da5/9.bin. 2 rows, 147.00 B. State has 2 unique rows.
2023.06.15 08:48:11.428085 [ 2768 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/267/26798441-0291-4e58-8eb4-0f97f7a50da5/14.bin. 1 rows, 77.00 B. State has 2 unique rows.
2023.06.15 08:48:11.428158 [ 2768 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/267/26798441-0291-4e58-8eb4-0f97f7a50da5/13.bin. 1 rows, 77.00 B. State has 2 unique rows.
2023.06.15 08:48:11.428206 [ 2768 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/267/26798441-0291-4e58-8eb4-0f97f7a50da5/8.bin. 2 rows, 147.00 B. State has 2 unique rows.

The latest data is stored in 14.bin, does it seems not load in sequence during restore?

Really hope to reply, thanks a lot.

A link to reproducer in https://fiddle.clickhouse.com/.

Does it reproduce on recent release?

The list of releases

Enable crash reporting

A clear and concise description of what you expected to happen.

Error message and/or stacktrace

If applicable, add screenshots to help explain your problem.

Additional context

Add any other context about the problem here.

[antimack]

+1, also interested in this

[richard-formulate]

We have the same problem. A colleague believes this unsorted retrieval of file names is the source of the issue:

ClickHouse/src/Storages/StorageSet.cpp

Line 195 in d072100

for (auto dir_it{disk->iterateDirectory(path)}; dir_it->isValid(); dir_it->next())

[j-eid-regnology]

+1

[jsajlovic]

+1

[acoj1993]

+1

[acoj1993]

I can confirm what has been posted so far. The issue is easy to reproduce, and occurs also if "join_any_take_last_row" is not used. The problem seems to be indeed with the order of how the backup files are loaded after a restart of the server.

The way I see it, this issue is a big problem for users that rely on JOIN engine to ignore the duplicate keys with the ANY strictness (as is currently stated in the documentation).

Find below step-by-step executed statements and ClickHouse logs.

:) SELECT version()
┌─version()──┐
│ 23.3.19.32 │
└────────────┘
 :) CREATE DATABASE `test`
 :) CREATE TABLE `test`.`test_join_table` ( TEST_ID String, ID UInt32 ) ENGINE = Join(ANY, LEFT, TEST_ID);
 :) INSERT INTO `test`.`test_join_table`(TEST_ID , ID) VALUES ('test1', 1);
 :) INSERT INTO `test`.`test_join_table`(TEST_ID , ID) VALUES ('test1', 2);
 :) INSERT INTO `test`.`test_join_table`(TEST_ID , ID) VALUES ('test1', 3);
 :) SELECT * FROM `test`.`test_join_table`;
┌─TEST_ID─┬─ID─┐
│ test1   │  1 │
└─────────┴────┘
 :) SELECT joinGet(`test`.`test_join_table`, 'ID', 'test1');
┌─joinGet(test.test_join_table, 'ID', 'test1')─┐
│                                            1 │
└──────────────────────────────────────────────┘

# ClickHouse server restarted

root@clickhouse-0:/# clickhouse-client
 :) SELECT joinGet(`test`.`test_join_table`, 'ID', 'test1');
┌─joinGet(test.test_join_table, 'ID', 'test1')─┐
│                                            2 │
└──────────────────────────────────────────────┘
 :) SELECT * FROM `test`.`test_join_table`;
┌─TEST_ID─┬─ID─┐
│ test1   │  2 │
└─────────┴────┘

2024.03.26 00:10:54.947118 [ 1 ] {} <Information> DatabaseAtomic (test): Metadata processed, database test has 1 tables and 0 dictionaries in total.
2024.03.26 00:10:54.947176 [ 1 ] {} <Information> TablesLoader: Parsed metadata of 1 tables in 2 databases in 0.002713108 sec
2024.03.26 00:10:54.947237 [ 1 ] {} <Information> TablesLoader: Loading 1 tables with dependency level 0
2024.03.26 00:10:54.950145 [ 298 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/e4e/e4e6b7f8-cd8c-42cc-854b-dfba7b28a61d/2.bin. 1 rows, 18.00 B. State has 1 unique rows.
2024.03.26 00:10:54.950992 [ 298 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/e4e/e4e6b7f8-cd8c-42cc-854b-dfba7b28a61d/1.bin. 1 rows, 18.00 B. State has 1 unique rows.
2024.03.26 00:10:54.951706 [ 298 ] {} <Information> StorageSetOrJoinBase: Loaded from backup file store/e4e/e4e6b7f8-cd8c-42cc-854b-dfba7b28a61d/3.bin. 1 rows, 18.00 B. State has 1 unique rows.
2024.03.26 00:10:54.951845 [ 1 ] {} <Information> DatabaseAtomic (default): Starting up tables.
2024.03.26 00:10:54.951865 [ 1 ] {} <Information> DatabaseAtomic (test): Starting up tables. ```

[vdimir]

Thanks everybody for reporting and for reproducers!

@richard-formulate it seems your assumption is correct, great catch!
I prepared straightforward fix for that #61972

[acoj1993]

Hi, can you give us an estimate when this fix will be released?

We were hoping it would be included in the latest version, but I noticed it's not part of the v24.3.2.23-lts. It's a very important fix for us.

[den-crane]

@acoj1993 it was fixed after v24.3.2.23 was released. It will be in the next release 24.3.3.