ALTER DELETE unexpectedly deletes NULL rows

[chu1070y]

When I delete specific rows, CH also delete NULL rows too.
Is it normal?
How can I control this?

SELECT *
FROM test2

┌──EventDate─┬─CounterID─┬─UserID─┐
│ 2020-01-02 │           │      2 │
│ 2020-01-03 │ aaa       │      0 │
└────────────┴───────────┴────────┘
┌──EventDate─┬─CounterID─┬─UserID─┐
│ 2020-01-02 │           │      2 │
│ 2020-01-02 │           │      2 │
│ 2020-01-02 │ ᴺᵁᴸᴸ      │      2 │
│ 2020-01-03 │ aaa       │      0 │
│ 2020-01-03 │ aaa       │      0 │
│ 2020-01-03 │ aaa       │   ᴺᵁᴸᴸ │
└────────────┴───────────┴────────┘

8 rows in set. Elapsed: 0.002 sec. 


ALTER TABLE test2
    DELETE WHERE UserID = 0


Ok.

0 rows in set. Elapsed: 0.085 sec. 


SELECT *
FROM test2

┌──EventDate─┬─CounterID─┬─UserID─┐
│ 2020-01-02 │           │      2 │
└────────────┴───────────┴────────┘
┌──EventDate─┬─CounterID─┬─UserID─┐
│ 2020-01-02 │           │      2 │
│ 2020-01-02 │           │      2 │
│ 2020-01-02 │ ᴺᵁᴸᴸ      │      2 │
└────────────┴───────────┴────────┘

4 rows in set. Elapsed: 0.002 sec. 

[4ertus2]

Could you attach your schema here too?

[chu1070y]

CREATE TABLE default.test2
(
    `EventDate` Date,
    `CounterID` Nullable(String),
    `UserID` Nullable(UInt32)
)
ENGINE = MergeTree()
ORDER BY EventDate;

INSERT INTO test2 VALUES ('2020-01-02', '', 2)('2020-01-03', 'aaa', 0);
INSERT INTO test2 VALUES ('2020-01-02', '', 2)('2020-01-02', '', 2)('2020-01-02', NULL, 2)('2020-01-03', 'aaa', 0)('2020-01-03', 'aaa', 0)('2020-01-03', 'aaa', NULL);

[4ertus2]

Cannot reproduce on master build. Could you update to the last release and reproduce it?

[den-crane]

reproduced 19.13.1.11, 19.13.6.51, 19.17.8.54 , 20.1.2, 20.2.1.2343, 20.2.1.2362 -- today nightbuilt

cat t_del.sql

drop table if exists default.test2;

CREATE TABLE default.test2
(
    `EventDate` Date,
    `CounterID` Nullable(String),
    `UserID` Nullable(UInt32)
)
ENGINE = MergeTree()
ORDER BY EventDate;

INSERT INTO default.test2 VALUES ('2020-01-02', '', 2)('2020-01-03', 'aaa', 0);
INSERT INTO default.test2 VALUES ('2020-01-02', '', 2)('2020-01-02', '', 2)('2020-01-02', NULL, 2)('2020-01-03', 'aaa', 0)('2020-01-03', 'aaa', 0)('2020-01-03', 'aaa', NULL);


SELECT * FROM default.test2;
ALTER TABLE default.test2 DELETE WHERE UserID = 0;
select sleep(3) format Null;
select '--------';
SELECT * FROM default.test2;

cat t_del.sql |clickhouse-client -mn

2020-01-02		2
2020-01-02		2
2020-01-02	\N	2
2020-01-03	aaa	0
2020-01-03	aaa	0
2020-01-03	aaa	\N
2020-01-02		2
2020-01-03	aaa	0
--------
2020-01-02		2
2020-01-02		2
2020-01-02		2
2020-01-02	\N	2

[filimonov]

Reason:

SELECT 
    UserID, 
    UserID = 0, 
    if(UserID = 0, 'delete', 'leave')
FROM default.test2
FORMAT TSV

2	0	leave
0	1	delete
2	0	leave
2	0	leave
2	0	leave
0	1	delete
0	1	delete
\N	\N	delete

[filimonov]

Reproduces on 20.4.1 (master).

SELECT arrayJoin([0, 1, 3, NULL]) AS x,  x = 0,  if(x = 0, 'x=0', 'x<>0');

Related #5238
/cc @janplus

[alexey-milovidov]

SELECT arrayJoin([0, 1, 3, NULL]) AS x, x = 0, if(x = 0, 'x=0', 'x<>0');

@filimonov The query result is totally correct and it does not relate to the issue.

[filimonov]

Are you sure? :) In the recent versions, it's correct... But at the moment of posting it wasn't. And still is not correct in supported 20.1 & 20.3 versions:

Connected to ClickHouse server version 20.3.12 revision 54433.

0d2c584f12cc :) SELECT arrayJoin([0, 1, 3, NULL]) AS x,  x = 0,  if(x = 0, 'x=0', 'x<>0');

SELECT 
    arrayJoin([0, 1, 3, NULL]) AS x, 
    x = 0, 
    if(x = 0, 'x=0', 'x<>0')

┌────x─┬─equals(arrayJoin([0, 1, 3, NULL]), 0)─┬─if(equals(arrayJoin([0, 1, 3, NULL]), 0), 'x=0', 'x<>0')─┐
│    0 │                                     1 │ x=0                                                      │
│    1 │                                     0 │ x<>0                                                     │
│    3 │                                     0 │ x<>0                                                     │
│ ᴺᵁᴸᴸ │                                  ᴺᵁᴸᴸ │ x=0                                                      │
└──────┴───────────────────────────────────────┴──────────────────────────────────────────────────────────┘

Connected to ClickHouse server version 20.4.6 revision 54434.

9cbd2f247232 :) SELECT arrayJoin([0, 1, 3, NULL]) AS x,  x = 0,  if(x = 0, 'x=0', 'x<>0');

SELECT 
    arrayJoin([0, 1, 3, NULL]) AS x, 
    x = 0, 
    if(x = 0, 'x=0', 'x<>0')

┌────x─┬─equals(arrayJoin([0, 1, 3, NULL]), 0)─┬─if(equals(arrayJoin([0, 1, 3, NULL]), 0), 'x=0', 'x<>0')─┐
│    0 │                                     1 │ x=0                                                      │
│    1 │                                     0 │ x<>0                                                     │
│    3 │                                     0 │ x<>0                                                     │
│ ᴺᵁᴸᴸ │                                  ᴺᵁᴸᴸ │ x<>0                                                     │
└──────┴───────────────────────────────────────┴──────────────────────────────────────────────────────────┘

P.S. May be it was fixed in #11807