[DERBY-4411] Scalar subquery erroneously rejected for not returning exactly one row

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.5.3.1, 10.6.1.0
• Fix Version/s: 10.5.3.1, 10.6.1.0
• Component/s: SQL
• Issue & fix info: Repro attached
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 15/Oct/09 20:50
• Updated: 28/Oct/09 08:17
• Resolved: 28/Oct/09 08:17

Description

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.

Issue Links

• relates to: DERBY-4391 NullPointerException when comparing indexed column with result from a set operation