SQL0338N

[Lennart]

I noticed the other day that an sql that worked in 8.2 (I cannot
verify this since I don't have any 8.2 installation, but it is
contained in code that have worked before), no longer works 9.5. I'm
not sure whether this is intentional (it sure is an ugly mix of
implicit and explicit join) or if it is a bug. Any comments?

-- error: is T1 out of scope in on clause for T3?
select *
from lateral(values (1)) T1(a), lateral(values(1)) T2 (a)
left outer join lateral(values(2)) T3(a)
on T1.a = T3.a

SQL0338N An ON clause associated with a JOIN operator or in a MERGE
statement
is not valid. SQLSTATE=42972

-- using T2 in on clause works
select *
from lateral(values (1)) T1(a),
lateral(values(1)) T2 (a)
left outer join lateral(values(2)) T3(a)
on T2.a = T3.a

A A A
----------- ----------- -----------
1 1 -

-- of course using T1 works if T1 and T2 are swapped
select *
from lateral(values (1)) T2(a), lateral(values(1)) T1 (a)
left outer join lateral(values(2)) T3(a)
on T1.a = T3.a

A A A
----------- ----------- -----------
1 1 -

-- and using explicit join all the way works as well
select *
from lateral(values (1)) T1(a)
inner join lateral(values(1)) T2 (a)
on T1.a = T2.a
left outer join lateral(values(2)) T3(a)
on T1.a = T3.a"

A A A
----------- ----------- -----------
1 1 -

/Lennart

[Lennart]

I forgot the where clause for the 3 first examples, but since there is
only one row in T1 and T2 that matches, it should not make a
difference.

/Lennart

[Serge Rielau]

Lennart wrote:
> On 3 Juni, 16:57, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
>> I noticed the other day that an sql that worked in 8.2 (I cannot
>> verify this since I don't have any 8.2 installation, but it is
>> contained in code that have worked before), no longer works 9.5. I'm
>> not sure whether this is intentional (it sure is an ugly mix of
>> implicit and explicit join) or if it is a bug. Any comments?
>>
>> -- error: is T1 out of scope in on clause for T3?
>> select *
>> from lateral(values (1)) T1(a), lateral(values(1)) T2 (a)
>> left outer join lateral(values(2)) T3(a)
>> on T1.a = T3.a
>>
>> SQL0338N An ON clause associated with a JOIN operator or in a MERGE
>> statement
>> is not valid. SQLSTATE=42972

I don't recall any conscious changes here. Feel free to open a PMR if
you want us to get to the bottom of this.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

[Lennart]

Well, It is not critical, and I don't like the idea of mixing join
styles in the same query, but perhaps I should open a PMR then.
Meanwhile I was merely interested in comments on whether the
construction is legal or not.

/Lennart