[
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.
