Lazy execution of alias columns

[filimonov]

Currently expressions in alias columns are executed in 'greedy' way (so they are calculated also for the rows which will be thrown away).

drop table if exists aliases_lazyness;

create table aliases_lazyness (x UInt32, y ALIAS sleepEachRow(0.1)) Engine=MergeTree ORDER BY x;

insert into aliases_lazyness(x) select * from numbers(40);

SELECT x,y FROM aliases_lazyness WHERE x = 1; 
-- that will calc y for all 40 raws (and die because of too big sleep time requested)

SELECT x, sleepEachRow(0.1) as y FROM aliases_lazyness WHERE x = 1; 
-- that will calc y for 4 rows (one data block?). Acceptable.

SELECT x, y FROM aliases_lazyness PREWHERE x = 1; 
-- that will calc y for 1 row. Perfect.

Why can't first select work as 3rd or at least second?

Motivation: We've created some handy aliases for some complicated expressions which involve dict lookups, and some processing to make it easier for users to access them without copy & paste of those complicated expressions. But when users try to use that aliases - it works much slower than when expressions appears in selects directly.

[derekperkins]

I'm also very interested in adding dict aliases if they are equally performant with putting it into the query.

[KochetovNicolai]

SELECT x, sleepEachRow(0.1) as y FROM aliases_lazyness WHERE x = 1; 

In this case column y from table isn't calculated. What's calculated is constant column y with value sleepEachRow(0.1). For some reason it's calculated 4 times during reprocessing, and it's ok.
In other words, query works exactly the same as

SELECT x, sleepEachRow(0.1) as z FROM aliases_lazyness WHERE x = 1; 

SELECT 
    x, 
    sleepEachRow(0.1) AS z
FROM aliases_lazyness 
WHERE x = 1

┌─x─┬─z─┐
│ 1 │ 0 │
└───┴───┘

1 rows in set. Elapsed: 0.403 sec.

SELECT x, y FROM aliases_lazyness PREWHERE x = 1; 

This query reads column x, then filters it because of prewhere condition, then calculates single row for y.

SELECT x,y FROM aliases_lazyness WHERE x = 1; 

This query reads column x, then calculates column y (and fails), then filter full block because of where condition.
Actually, ClickHouse has "move to prewhere" optimization, but it isn't performed because x is already in primary key. In case we exclude x from pk the query will be optimized:

create table aliases_lazyness_2 (x UInt32, y ALIAS sleepEachRow(0.1)) Engine=MergeTree ORDER BY tuple();
insert into aliases_lazyness_2 (x) select * from numbers(40);
SELECT x, y FROM aliases_lazyness_2 WHERE x = 1; 

SELECT 
    x, 
    y
FROM aliases_lazyness_2 
WHERE x = 1

┌─x─┬─y─┐
│ 1 │ 0 │
└───┴───┘

1 rows in set. Elapsed: 0.102 sec.

Also in logs you can see:

2018.07.03 11:46:32.168683 [ 44 ] <Debug> InterpreterSelectQuery: MergeTreeWhereOptimizer: condition `x = 1` moved to PREWHERE

[filimonov]

In this case column y from table isn't calculated.

OK, you right. That sample was not good, use that instead:

SELECT x, sleepEachRow(0.1)+pow(x,2) as y FROM aliases_lazyness WHERE x=1;

As you can see that perfectly works without calculating y for whole table. And that behavior seems logical.

The same but with condition on y (for example WHERE y=1) will fail because it require to calc y values for all the rows (and that's also logical):

About where / prewhere - i know about that optimization. I just want to say that alias columns should always be calculated AFTER all types of real columns filtering (why should they be calculated if they will be thrown away?). For real columns and real data it can be sometimes desirable to read whole column data from required blocks at once (to use advantages of block reads) and later throw away some rows, but for aliases (which are always calculated in memory) that's quite strange to calc it "in advance" just to throw it away.

[filimonov]

Another demo showing that existing approach in not correct:

create table aliases_lazyness3 (x UInt32, y ALIAS runningDifference(x)) Engine=MergeTree ORDER BY x;
insert into aliases_lazyness3 select * from numbers(20);
select x,y from aliases_lazyness3 where x in (5,10,15);
┌──x─┬─y─┐
│  5 │ 1 │
│ 10 │ 1 │
│ 15 │ 1 │
└────┴───┘

3 rows in set. Elapsed: 0.002 sec. 

select x,runningDifference(x) as y from aliases_lazyness3 where x in (5,10,15);
┌──x─┬─y─┐
│  5 │ 0 │
│ 10 │ 5 │
│ 15 │ 5 │
└────┴───┘

3 rows in set. Elapsed: 0.002 sec. 

-- And that is strange (how does it happen?? o_O )
select x,runningDifference(x) as z from aliases_lazyness3 where z = 1 and x in (5,10,15);
┌──x─┬─z─┐
│  5 │ 0 │
│ 10 │ 5 │
│ 15 │ 5 │
└────┴───┘

3 rows in set. Elapsed: 0.002 sec. 

[alexey-milovidov]

runningDifference is moved to PREWHERE and calculated two times.
This behaviour was fixed in master:

milovidov-Pro-P30 :) select x,runningDifference(x) as z from aliases_lazyness3 where z = 1 and x in (5,10,15);

SELECT 
    x, 
    runningDifference(x) AS z
FROM aliases_lazyness3 
WHERE (z = 1) AND (x IN (5, 10, 15))

┌──x─┬─z─┐
│  5 │ 1 │
│ 10 │ 1 │
│ 15 │ 1 │
└────┴───┘

Now expressions in PREWHERE are calculated only single time.

But the context of evaluation is still implementation specific.
That's why I don't like all functions that implicitly depend on context.

[alexey-milovidov]

We cannot find simple solution for this task.
It probably will be fixed after implementation of "processors".

"Processors" will be discussed at Database Saturday event in Ekaterinburg:
https://events.yandex.ru/events/meetings/18oct-2018/

[filimonov]

I didn't look on the code, but thought it's possibile just to 'inline' aliases, by replacing alias nodes in AST to expressions from table definition during parse /ast optimization time. Inlined expressions work much better than aliases, and that seems not logical.

[alexey-milovidov]

Fixed here: #16995