[DERBY-4389] ArrayIndexOutOfBoundsException in RIGHT JOIN with COALESCE

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.0.2.1, 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.3.0, 10.6.1.0
• Component/s: SQL
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Knut Anders Hatlen
• Created: 25/Sep/09 21:14
• Updated: 02/Jun/10 11:22
• Resolved: 02/Jun/10 11:22

Description

Dag H. Wanvik identified this problematic query in DERBY-4342:

ij> create table t(x int, y int);
0 rows inserted/updated/deleted
ij> insert into t values (1,2);
1 row inserted/updated/deleted
ij> select * from t t1 right join t t2 on t1.x=t2.x where coalesce(t1.x,t1.y) = 1;
ERROR 38000: The exception 'java.lang.ArrayIndexOutOfBoundsException: -1' was thrown while evaluating an expression.
ERROR XJ001: Java exception: '-1: java.lang.ArrayIndexOutOfBoundsException'.

With a debug build, an assert failure is raised instead:

ij> select * from t t1 right join t t2 on t1.x=t2.x where coalesce(t1.x,t1.y) = 1;
ERROR XJ001: Java exception: 'ASSERT FAILED sourceResultSetNumber expected to be >= 0 for T1.X: org.apache.derby.shared.common.sanity.AssertFailure'.

Issue Links

• duplicates: DERBY-4594 ArrayIndexOutOfBoundsException thrown in PreparedStatement execution
• relates to: DERBY-4342 SQLSTATE 38000 (NullPointerException) at inner self join and value(x1, x2...)