Skip to content

Nullable key and where != produces wrong result. #23634

@den-crane

Description

@den-crane

20.8.18.32, 21.3.8.76, 21.4.5.46

CREATE TABLE test001
(id Nullable(String), s Nullable(String), s1 Nullable(String))
ENGINE = MergeTree() ORDER BY (id,s,s1) SETTINGS allow_nullable_key = 1;

INSERT into test001 values ('s','s','s'), (null,null,'s1'), ('s2','s2','s2');

select * from test001 where id !='';
0 rows in set.   <--- expected 2 rows



select * from test001 where s  !='';
┌─id─┬─s──┬─s1─┐
│ s  │ s  │ s  │
│ s2 │ s2 │ s2 │
└────┴────┴────┘

select * from test001 where s1 !='';
┌─id───┬─s────┬─s1─┐
│ s    │ s    │ s  │
│ s2   │ s2   │ s2 │
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ s1 │
└──────┴──────┴────┘



seems it has been fixed in the master 
21.5.1.6605

select * from test001 where s  !='';
┌─id─┬─s──┬─s1─┐
│ s  │ s  │ s  │
│ s2 │ s2 │ s2 │
└────┴────┴────┘

Metadata

Metadata

Assignees

Labels

bugConfirmed user-visible misbehaviour in official releasecomp-datatype-wrapperType modifiers/wrappers (Nullable, LowCardinality, etc.).help wantedminorPriority: minor

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions