where .. in (select ) by index does not work anymore in some cases that worked before

[den-crane]

drop table if exists t ;
drop table if exists temp ;

CREATE TABLE t
(
    game String,
    round String,
    casino Int32)
ENGINE = MergeTree() ORDER BY (casino, game, round);

CREATE TABLE temp
(
    game String,
    round String,
    casino Int32)
ENGINE = MergeTree() ORDER BY (casino, game, round);

insert into t(casino, game, round)
select number%103, toString(cityHash64(number%999)), toString(number) from numbers(3, 50000000);

insert into temp(casino, game, round)
select number%103, toString(cityHash64(number%999)), toString(number) from numbers(5000);

Q1: SELECT count(*)
FROM temp WHERE (casino, game, round) NOT IN
(     SELECT  casino, game, round
    FROM t
    WHERE (casino, game, round) IN
    (
        SELECT casino, game, round
        FROM temp
    )
)

21.3.1.5944  Elapsed: 4.464 sec. Processed 5.00 thousand rows, 225.89 KB (1.12 thousand rows/s., 50.60 KB/s.)

20.8.11.17    Elapsed: 0.611 sec. Processed 5.00 thousand rows, 225.89 KB (8.18 thousand rows/s., 369.73 KB/s.)

18.14.19.    Elapsed: 0.341 sec. Processed 5.00 thousand rows, 225.89 KB (14.66 thousand rows/s., 662.46 KB/s.)

[CurtizJ]

Introduced in #19007.

[CurtizJ]

@KochetovNicolai, can you take a look, please? Regression was beetween e2a2ab6 and b03f28f commits.

[CurtizJ]

With build from e2a2ab6.

SELECT version()

┌─version()───┐
│ 21.1.1.5664 │
└─────────────┘

SELECT count(*)　FROM temp WHERE (casino, game, round) NOT IN　(     SELECT  casino, game, round　    FROM t　    WHERE (casino, game, round) IN　    (　        SELECT casino, game, round　        FROM temp ))

Query id: dd18adcd-5729-459f-8781-a4a389201a8d

┌─count()─┐
│       3 │
└─────────┘

1 rows in set. Elapsed: 0.213 sec. Processed 5.00 thousand rows, 225.89 KB (23.48 thousand rows/s., 1.06 MB/s.)

[CurtizJ]

With build from b03f28f.

SELECT version()

┌─version()───┐
│ 21.1.1.5668 │
└─────────────┘

1 rows in set. Elapsed: 0.002 sec. 

SELECT count(*)　FROM temp WHERE (casino, game, round) NOT IN　(     SELECT  casino, game, round　    FROM t　    WHERE (casino, game, round) IN　    (　        SELECT casino, game, round　        FROM temp ))

┌─count()─┐
│       3 │
└─────────┘

1 rows in set. Elapsed: 6.147 sec. Processed 5.00 thousand rows, 225.89 KB (813.44 rows/s., 36.75 KB/s.) 

[den-crane]

Interesting, that a trick with order by (Int64 ) is still OK.

create table data(K Int64, V String) engine=MergeTree order by K;
insert into data select number, toString(number) from numbers(100,100000000);
optimize table data final;
create table buffer(K Int64, V String) engine=Memory;
insert into buffer select number, toString(number) from numbers(0,1000);

select count() from buffer where K not in (select K from data where K in (select K from buffer));
┌─count()─┐
│     100 │
└─────────┘

1 rows in set. Elapsed: 0.002 sec. Processed 9.19 thousand rows, 73.54 KB (4.76 million rows/s., 38.09 MB/s.)

21.3.1.5998.

Though, maybe I misunderstand and it's another case because it's working with set force_primary_key=1;

set force_primary_key=1;
select count() from buffer where K not in (select K from data where K in (select K from buffer));
┌─count()─┐
│     100 │
└─────────┘
1 rows in set. Elapsed: 0.002 sec. Processed 9.19 thousand rows, 73.54 KB (5.38 million rows/s., 43.00 MB/s.)

[filimonov]

Since it affects IN (select ... ) - may be it also related to #20558 ?