Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Default value of EPHEMERAL column written by materialized view instead of actual value #56405

Closed
qoega opened this issue Nov 7, 2023 · 0 comments · Fixed by #57461
Closed
Assignees
Labels
potential bug To be reviewed by developers and confirmed/rejected.

Comments

@qoega
Copy link
Member

qoega commented Nov 7, 2023

CREATE TABLE raw
(
  name String,
  ts String
) ENGINE = MergeTree
ORDER BY (name, ts);

CREATE TABLE parsed
(
  name String,
  ts_ephemeral Nullable(DateTime64(9)), -- no EPHEMERAL
  ts DateTime64(9, 'UTC') MATERIALIZED if(ts_ephemeral IS NULL, date(0), ts_ephemeral),
) ENGINE = MergeTree 
ORDER BY (name, ts);

CREATE TABLE parsed_eph
(
  name String,
  ts_ephemeral Nullable(DateTime64(9)) EPHEMERAL, -- with EPHEMERAL
  ts DateTime64(9, 'UTC') MATERIALIZED if(ts_ephemeral IS NULL, date(0), ts_ephemeral),
) ENGINE = MergeTree 
ORDER BY (name, ts);

CREATE MATERIALIZED VIEW parse_mv_eph
TO parsed_eph
AS
SELECT
  name,
  toDateTime64OrNull(ts, 9 ,'UTC') as ts_ephemeral
FROM raw;

CREATE MATERIALIZED VIEW parse_mv
TO parsed
AS
SELECT
  name,
  toDateTime64OrNull(ts, 9 ,'UTC') as ts_ephemeral
FROM raw;

INSERT INTO raw VALUES ('abc', '1451611580')

SELECT 'input_parsed';
SELECT
  name,
  toDateTime64OrNull(ts, 9 ,'UTC') as ts_ephemeral
FROM raw;

SELECT 'parsed';
SELECT name, ts FROM parsed;

SELECT 'parsed_eph';
SELECT name, ts FROM parsed_eph;
input_parsed
abc	2016-01-01 01:26:20.000000000
parsed
abc	2016-01-01 01:26:20.000000000
parsed_eph
abc	1970-01-01 00:00:00.000000000

https://fiddle.clickhouse.com/0f10a874-8f3f-464a-a21a-5bf39d8d786a

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
potential bug To be reviewed by developers and confirmed/rejected.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants