[jira] Updated: (DERBY-4411) Scalar subquery erroneously rejected for not returning exactly one row

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-4411?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen updated DERBY-4411:
--------------------------------------

    Attachment: constantRestriction.diff

When the problematic query is compiled, the byte code for the UnionNode is generated twice. I'm not sure why it's needed twice in this case, but it looks like the byte code generation in general is prepared for it, so I assume it's OK for now.

The right side of the UnionNode contains a ProjectRestrictNode with the predicate 1=0. The first time PRN.generate() is called, predicates are taken out from restrictionList and put into restriction or constantRestriction, depending on the nature of the predicate. restrictionList is nulled out. The second time PRN.generate() is called on the same object, the predicates are supposed to be taken from the restriction and constantRestriction fields instead of the, so all the predicates should be preserved between the invocations.

However, PRN has a method nopProjectRestrict() which checks whether the PRN actually restricts the result. This method checks if there are predicates restrictionList and restriction. But since 1=0 is a constant expression and has been moved to constantRestriction, the method doesn't detect that the PRN actually does restrict the result, and the code to restrict the result is not generated.

The attached patch makes nopProjectRestrict() check the constant restrictions as well. This makes the query return the expected result. The patch does not add any regression tests, nor has any regression tests been run with the patch.

> Scalar subquery erroneously rejected for not returning exactly one row
> ----------------------------------------------------------------------
>
>                 Key: DERBY-4411
>                 URL: https://issues.apache.org/jira/browse/DERBY-4411
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Knut Anders Hatlen
>         Attachments: constantRestriction.diff
>
>
> This sequence of statements causes an error because the scalar sub-query supposedly doesn't return exactly one row, even though it does return exactly one row:
> ij> create table t (x int unique, y int);
> 0 rows inserted/updated/deleted
> ij> insert into t values (1,2);
> 1 row inserted/updated/deleted
> ij> select * from t where x > (values 1 union select y from t where 1=0);
> ERROR 21000: Scalar subquery is only allowed to return a single row.
> ij> values 1 union select y from t where 1=0;
> 1          
> -----------
> 1          
> 1 row selected
> Before DERBY-4391, the problematic statement would have caused a NullPointerException.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.