db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4411) Scalar subquery erroneously rejected for not returning exactly one row
Date Sun, 18 Oct 2009 18:54:31 GMT

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


Mime
View raw message