Clickhouse: Not found column for qualified name in PREWHERE

[osbertngok]

Version: ClickHouse 1.1.54236

Minimal reproducible test case:

Preparation:

CREATE TABLE IF NOT EXISTS implus.test_a 
(
    OldColumn String DEFAULT '',
    EventDate Date DEFAULT toDate(EventTime),
    EventTime DateTime
) ENGINE = MergeTree(EventDate, EventTime, 8192);

CREATE TABLE IF NOT EXISTS implus.test_b
(
    OldColumn String DEFAULT '',
    NewColumn String DEFAULT '',
    EventDate Date DEFAULT toDate(EventTime),
    EventTime DateTime
) ENGINE = MergeTree(EventDate, EventTime, 8192);

INSERT INTO implus.test_a (OldColumn, EventTime) VALUES('1', now());

INSERT INTO implus.test_b (OldColumn, NewColumn, EventTime) VALUES('1', '1a', now());
INSERT INTO implus.test_b (OldColumn, NewColumn, EventTime) VALUES('2', '2a', now());

ALTER TABLE implus.test_a ADD COLUMN NewColumn String DEFAULT '' AFTER OldColumn;

INSERT INTO implus.test_a (OldColumn, NewColumn,EventTime) VALUES('2', '2a', now());

Query:

SELECT NewColumn
FROM implus.test_a
ANY INNER JOIN 
(SELECT OldColumn, NewColumn FROM implus.test_b)
Using OldColumn
PREWHERE NewColumn != '';

Expected Result:

1a
2a

Actual Result:

Code: 10, e.displayText() = DB::Exception: Not found column: 'NewColumn', e.what() = DB::Exception

Workaround:

SELECT NewColumn
FROM (SELECT * FROM implus.test_a)
ANY INNER JOIN 
(SELECT OldColumn, NewColumn FROM implus.test_b)
Using OldColumn
WHERE NewColumn != ''

[ludv1x]

Confirmed.

It applies WHERE to left table (implus.test_a) instead of joined table in the following query:

SELECT *
FROM implus.test_a 
ANY INNER JOIN 
(
    SELECT 
        OldColumn, 
        NewColumn
    FROM implus.test_b 
) USING (OldColumn)
WHERE NewColumn != ''

[alexey-milovidov]

We have quite a stupid behaviour: asterisk is expanded only to the list of columns from "left" table.
We are going to fix it.

[osbertngok]

Related question, when we do ALTER TABLE implus.test_a ADD COLUMN NewColumn String DEFAULT '' AFTER OldColumn;, what exactly happens to the existing row in implus.test_a? Is the expected behavior that it would be padded with NewColumn = '', or the column of that row will just remain undefined?

[alexey-milovidov]

New column in existing rows will have default values.
(Although this default values are not physically written on disk during ALTER.)

[alexey-milovidov]

We have fixed behaviour of asterisk.
But the issue still exist.

SELECT NewColumn
FROM test.test_a 
ANY INNER JOIN 
(
    SELECT 
        OldColumn, 
        NewColumn
    FROM test.test_b 
) USING (OldColumn)
PREWHERE NewColumn != ''

┌─NewColumn─┐
│ 2a        │
└───────────┘

1 rows in set. Elapsed: 0.037 sec. 

milovidov-Latitude-E7470 :) SELECT NewColumn FROM test.test_a ANY INNER JOIN  (SELECT OldColumn, NewColumn FROM test.test_b) AS b Using OldColumn PREWHERE b.NewColumn != '';

SELECT NewColumn
FROM test.test_a 
ANY INNER JOIN 
(
    SELECT 
        OldColumn, 
        NewColumn
    FROM test.test_b 
) AS b USING (OldColumn)
PREWHERE b.NewColumn != ''

Received exception from server (version 18.11.0):
Code: 47. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Unknown identifier: b.NewColumn, projection layer . 

0 rows in set. Elapsed: 0.096 sec. 

milovidov-Latitude-E7470 :) SELECT NewColumn FROM test.test_a ANY INNER JOIN  (SELECT OldColumn, NewColumn FROM test.test_b) AS b Using OldColumn WHERE b.NewColumn != '';

SELECT NewColumn
FROM test.test_a 
ANY INNER JOIN 
(
    SELECT 
        OldColumn, 
        NewColumn
    FROM test.test_b 
) AS b USING (OldColumn)
WHERE b.NewColumn != ''

Received exception from server (version 18.11.0):
Code: 10. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: No such name in Block::erase(): 'b.NewColumn'. 

0 rows in set. Elapsed: 0.103 sec. 

milovidov-Latitude-E7470 :)

[4ertus2]

Reproduced on master with PREWHERE only. WHERE section knows about qualified name.
No matter was there ALTER before or not.

[grmorozov]

Any updates on this issue?

I got the same error in a bit different scenario:

create table alias_test (date Date, source_id UInt32, source alias source_id) Engine = MergeTree() PARTITION BY toYYYYMM(date) ORDER BY (date, source_id)

insert into alias_test (date, source_id) values ('2019-10-29', 2000050001)

then

select date, count() from alias_test prewhere source = 2000050001 where date = '2019-10-29' group by date

returns correct

|date|count()|
|------------|-|
|2019-10-29|1|

but

select date, count() from alias_test prewhere source = 2000050001 where date = '2019-10-28' group by date

(date for which no data in the table) throws exception:

Received exception from server (version 19.13.6):
Code: 10. DB::Exception: Received from localhost:9000. DB::Exception: Not found column: 'source'. 

[filimonov]

Any updates on this issue?

@grmorozov your case differs from the original issue, your case was fixed in #7911 (you can try latest testing release).