Workflow [PR], commit [055406d]

This PR also adds mergeTreeProjection table function to read from projection parts directly. It is used for introspection.

TBH, there is actually requests/cases when people want to read projection data directly.
#35017

In the example query:

SELECT
    count()
FROM events
WHERE (_part, _part_offset) IN (
    SELECT _part, _part_offset
    FROM events
    WHERE user_id = 42
)

what happens if there's a merge between executing the inner SELECT and the outer one? Could it return a _part that is not in the new merged table, and then the results are incomplete? I thought if the two queries used different snapshots than this wouldn't work. I'd be very happy to be wrong! If it means that this query will just work once this is merged, then I'm super excited!

what happens if there's a merge between executing the inner SELECT and the outer one?

It currently uses different snapshots, which can lead to the expected issues. This will be addressed in the next phase of the projection index, where we will avoid relying on subqueries to index rows during queries.

test_keeper_auth/test.py::test_partial_auth[get_genuine_zk] #78981

test_storage_rabbitmq/test.py::test_rabbitmq_tsv_with_delimiter #71049

I cannot find report for TSan crash in 01154_move_partition_long, but it exists in many other PR's CI runs too.

Fuzzer:

SELECT (1 OR (isNullable(1) OR ((1 OR (8 OR (toNullable(8) IS NULL) OR 1 OR 8 OR isZeroOrNull(materialize(8))) OR 1 OR (NOT 19)) < 0) OR 8)) < 0, json.a.r.:`Array(JSON)`.^b, *, NOT isNullable(materialize(76)) FROM test PREWHERE (NOT 19) OR 1 OR ((1 OR (8 OR (0 <= ((toLowCardinality(8) OR 1 OR 8 OR (8 IS NULL)) OR 1 OR (NOT 19) OR toNullable(1))))) >= 0) QUALIFY materialize(19) ORDER BY 4 ASC, id DESC NULLS LAST FORMAT `Null`

not related.

I'm seeing some strange behavior with a projection containing _part_offset when parts are merged. It looks like the _parent_part_offset column stays equal to the original offset before the merge, while _part has the expected behavior of changing to the new higher-level part. See fiddle:

CREATE TABLE t (id UInt32, projection by_id (select _part_offset order by id)) ENGINE=MergeTree ORDER BY tuple();

-- Insert a lot of rows so background merges happen automatically
INSERT INTO t WITH 100000000 AS n SELECT n - number FROM numbers(n);

CREATE OR REPLACE VIEW TableUsingProjection AS
SELECT *, _part, _part_offset FROM t WHERE (_part, _part_offset) IN (SELECT (_part, _part_offset) FROM t WHERE id = 1);
CREATE OR REPLACE VIEW IntrospectProjection AS
SELECT *, _part FROM mergeTreeProjection(currentDatabase(), t, by_id) WHERE id = 1;

-- Before some automatic merges
SELECT * FROM TableUsingProjection;
SELECT * FROM IntrospectProjection;

-- Give some time for merges (non-determinstic)
SELECT sleep(3);

-- After some automatic merges
SELECT * FROM TableUsingProjection;
SELECT * FROM IntrospectProjection;

it's not guaranteed due to timing of merges, but I saw a result like this:

   +-id-+-_part-------+-_part_offset-+
1. |  1 | all_90_90_0 |      1036182 |
   +----+-------------+--------------+
   +-id-+-_parent_part_offset-+-_part-------+
1. |  1 |             1036182 | all_90_90_0 |
   +----+---------------------+-------------+
   +-sleep(3)-+
1. |        0 |
   +----------+
   +------id-+-_part-------+-_part_offset-+
1. | 5559766 | all_85_90_1 |      1036182 |
   +---------+-------------+--------------+
   +-id-+-_parent_part_offset-+-_part-------+
1. |  1 |             1036182 | all_85_90_1 |
   +----+---------------------+-------------+

Notably, I cannot reproduce the problem using OPTIMIZE TABLE t FINAL to force merges. When I do this, the _parent_part_offset column correctly updates to the new offset.

Is this expected given the current in-progress state of projection indexes?

@EmeraldShift The issue stems from MergeTree tables that lack a sorting key. While optimizations are intended for this case, they haven't been fully implemented yet. I’ll address it later this week.

Oh, interesting. I hadn't considered using tuple() for testing would change the behavior. For what it's worth, I'm not planning on using any unsorted tables in production. I'll keep playing around with tables with a sort key.

@EmeraldShift Fixed in #81188.