Clickhouse: ALTER UPDATE using joinGet() corrupts column if no match is found.

[yehiaelbehery]

Describe the bug or unexpected behaviour
ALTER UPDATE using joinGet() corrupts updated column if no match is found.
eg. ALTER TABLE main_table UPDATE value_to_get_from_join = joinGet('join_table', 'value_to_update_main_table', ID) WHERE 1; corrupts value_to_get_from_join and renders it a non-value but also not null.

How to reproduce
CREATE TABLE main_table (
ID Integer,
value_to_get_from_join Nullable(Date) --- or Date
) Engine = MergeTree()
ORDER BY ID;

INSERT INTO main_table VALUES (1, toDate('2019-12-21')), (2, toDate('2019-12-22')), (3, toDate('2019-12-23')), (4, toDate('2019-12-24'));

CREATE TABLE join_table (
ID Integer,
value_to_update_main_table Date
) Engine = Join(Any, Left, ID);

ALTER TABLE main_table UPDATE value_to_get_from_join = joinGet('join_table', 'value_to_update_main_table', ID) WHERE 1;

SELECT * FROM main_table where value_to_get_from_join is null;
Returns 0 results instead of 4

SELECT * FROM main_table where value_to_get_from_join is not null;
Returns 4 results instead of 0

• Which ClickHouse server version to use
  ClickHouse server version 19.17.2 revision 54428 on Docker

Expected behavior
Either:

• Update the rows that have no correspoding value to Null
• Throw an exception if a single row doesn't have a corresponding value

[den-crane]

it's expected behavior.

joinGet() returns empty values so the alter updates using this empty values.

WHERE 1; -- wrong
WHERE joinGet('join_table', 'value_to_update_main_table', ID) <> '' -- right

[den-crane]

Also joinGet does not support Nulls. It is by design as well.

SELECT joinGet('join_table', 'value_to_update_main_table', ID) AS x
FROM main_table

┌──────────x─┐
│ 0000-00-00 │
│ 0000-00-00 │
│ 0000-00-00 │
│ 0000-00-00 │
└────────────┘

You can use nullIf or any other conditional functions.

SELECT nullIf(joinGet('join_table', 'value_to_update_main_table', ID), toDate(0)) AS x
FROM main_table

┌────x─┐
│ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │
└──────┘

[yehiaelbehery]

I understand, but how can I query these empty values after I ran this query that returned no errors?
If joinGet() only works with (<> ''), shouldn't be implicit or not work at all?

[den-crane]

I understand, but how can I query these empty values after I ran this query that returned no errors?

SELECT * FROM main_table where value_to_get_from_join = Date(0);

joinGet does not return errors if a key does not exists.
It returns default value for value type -- String: '', Int* : 0, Date: Date(0), Float: 0.0

defaultValueOfArgumentType https://clickhouse.yandex/docs/en/query_language/functions/other_functions/#defaultvalueofargumenttype

(<> '') -- it was only example for String type.

[yehiaelbehery]

It returns default value for value type -- String: '', Int* : 0, Date: Date(0), Float: 0.0

This is what I was missing. Awesome, Thank you very much.

[filimonov]

Maybe joinGetOrNull should be added

[yehiaelbehery]

+1

[blinkov]

@yehiaelbehery, do you have any further questions?

[yehiaelbehery]

No, Thanks for your help.