Expected behavior of WHERE clause referencing expressions in a MaterializedResultSet?

[Thomas Nielsen <Th...@Sun.COM>]

Hi,

I've run across (yet) a(nother) issue in my work with DERBY-2998.

This time about the expected behaviour of a WHERE clause referencing an 
expression in a MaterializedResultSet.

Given the sql query shaped like:

    SELECT ... FROM (
       SELECT <window function>, ... FROM ...
    ) AS tmp;

the in-progress fix for DERBY-2998 today materializes the subquery 
containing the window function, and the outer select pulls rows from 
this materialized resultset. This works fine.

Then we add an expression to the inner select:

    SELECT ... FROM (
       SELECT <window function>, t.A+t.B AS sum, ... FROM t,...
    ) AS tmp;

which also work fine.

But, if the aliased expression in the *inner* (subquery) select is 
referenced in the outer selects WHERE clause:

    SELECT ... FROM (
       SELECT <window function>, t.A+t.B AS sum, ... FROM t,...
    ) AS tmp
       WHERE sum ... ;

the query starts failing in code generation of the WHERE clause.

 From debugging I see that the WHERE clause tries to generate bytecode 
for the restriction based on the original table/column references even 
though the below result set has been materialized.

Attempting to generate restriction code based on the original references 
in this scenario seems like a bug to me? Shouldn't it check a column 
reference in the MaterializedResultSet in this case? Or is there 
something I'm missing?

<code blurp>
If I'm correct, then once the MaterializedResultSet is inserted, the 
where clause of the ProjectRestrictNode topping the 
MaterializedResultSet needs to have its expression-pointing 
(Virtual)ColumnReferences changed to simple (Virtual)ColumnReferences 
into the MaterializedResultSet?
This happens about here: impl/sql/compile/ProjectRestrictNode.java @1670
</code blurp>

Thomas
-- 
Thomas Nielsen

Re: Expected behavior of WHERE clause referencing expressions in a MaterializedResultSet?

[Rick Hillegas <Ri...@Sun.COM>]

Hi Thomas,

It sounds like you're headed in the right direction. One way to test 
theories like this is to run some experiments in a subversion client 
which doesn't have your changes. Try creating a query which generates a 
MaterializedResultSet and then look at the processing to see where (and 
how) the remapping occurs in that case.

Regards,
-Rick

Thomas Nielsen wrote:
> Hi,
>
> I've run across (yet) a(nother) issue in my work with DERBY-2998.
>
> This time about the expected behaviour of a WHERE clause referencing 
> an expression in a MaterializedResultSet.
>
> Given the sql query shaped like:
>
>    SELECT ... FROM (
>       SELECT <window function>, ... FROM ...
>    ) AS tmp;
>
> the in-progress fix for DERBY-2998 today materializes the subquery 
> containing the window function, and the outer select pulls rows from 
> this materialized resultset. This works fine.
>
> Then we add an expression to the inner select:
>
>    SELECT ... FROM (
>       SELECT <window function>, t.A+t.B AS sum, ... FROM t,...
>    ) AS tmp;
>
> which also work fine.
>
> But, if the aliased expression in the *inner* (subquery) select is 
> referenced in the outer selects WHERE clause:
>
>    SELECT ... FROM (
>       SELECT <window function>, t.A+t.B AS sum, ... FROM t,...
>    ) AS tmp
>       WHERE sum ... ;
>
> the query starts failing in code generation of the WHERE clause.
>
> From debugging I see that the WHERE clause tries to generate bytecode 
> for the restriction based on the original table/column references even 
> though the below result set has been materialized.
>
> Attempting to generate restriction code based on the original 
> references in this scenario seems like a bug to me? Shouldn't it check 
> a column reference in the MaterializedResultSet in this case? Or is 
> there something I'm missing?
>
> <code blurp>
> If I'm correct, then once the MaterializedResultSet is inserted, the 
> where clause of the ProjectRestrictNode topping the 
> MaterializedResultSet needs to have its expression-pointing 
> (Virtual)ColumnReferences changed to simple (Virtual)ColumnReferences 
> into the MaterializedResultSet?
> This happens about here: impl/sql/compile/ProjectRestrictNode.java @1670
> </code blurp>
>
> Thomas

Re: Expected behavior of WHERE clause referencing expressions in a MaterializedResultSet?

[Kristian Waagan <Kr...@Sun.COM>]

Thomas Nielsen wrote:
> Hi,
> 
> I've run across (yet) a(nother) issue in my work with DERBY-2998.
> 
> This time about the expected behaviour of a WHERE clause referencing an 
> expression in a MaterializedResultSet.
> 
> Given the sql query shaped like:
> 
>    SELECT ... FROM (
>       SELECT <window function>, ... FROM ...
>    ) AS tmp;
> 
> the in-progress fix for DERBY-2998 today materializes the subquery 
> containing the window function, and the outer select pulls rows from 
> this materialized resultset. This works fine.
> 
> Then we add an expression to the inner select:
> 
>    SELECT ... FROM (
>       SELECT <window function>, t.A+t.B AS sum, ... FROM t,...
>    ) AS tmp;
> 
> which also work fine.
> 
> But, if the aliased expression in the *inner* (subquery) select is 
> referenced in the outer selects WHERE clause:
> 
>    SELECT ... FROM (
>       SELECT <window function>, t.A+t.B AS sum, ... FROM t,...
>    ) AS tmp
>       WHERE sum ... ;
> 
> the query starts failing in code generation of the WHERE clause.
> 
>  From debugging I see that the WHERE clause tries to generate bytecode 
> for the restriction based on the original table/column references even 
> though the below result set has been materialized.
> 
> Attempting to generate restriction code based on the original references 
> in this scenario seems like a bug to me? Shouldn't it check a column 
> reference in the MaterializedResultSet in this case? Or is there 
> something I'm missing?

Hi Thomas,

Your analysis seems plausible to me, but I don't have the required 
insight to confirm it.

It would be helpful if someone with more knowledge could comment on the 
issue.
Just a thought, are you able to construct the same situation without 
using your patch (and window function)?


Since your intent is to finish DERBY-2998 for 10.4, I believe all help 
will be warmly appreciated :)



-- 
Kristian

> 
> <code blurp>
> If I'm correct, then once the MaterializedResultSet is inserted, the 
> where clause of the ProjectRestrictNode topping the 
> MaterializedResultSet needs to have its expression-pointing 
> (Virtual)ColumnReferences changed to simple (Virtual)ColumnReferences 
> into the MaterializedResultSet?
> This happens about here: impl/sql/compile/ProjectRestrictNode.java @1670
> </code blurp>
> 
> Thomas