|
Could this be a bug?....other relational DB that support lateral this works. Table ITEMS
SELECT I.Number as N, T1.number FROM "ITEMS" AS "I" CROSS JOIN LATERAL(select * from Items where items.Number = I.NUMBER) AS "T1" ORDER BY "I"."NUMBER", "T1"."NUMBER" In other DBs that support LATERAL the results is:
However, I'm getting Could not execute statement. Illegal reference to correlation name 'I' SQLCODE=-824, ODBC 3 State="42S02" Line 1, column 1 This is on the WHERE ITEMS.NUMBER = I.NUMBER of the lateral statement. If I remove the correlation name 'I' from that statement then it executes but with the wrong result set, as in: SELECT I.Number as N, T1.number FROM "ITEMS" AS "I" CROSS JOIN LATERAL(select * from Items where items.Number = NUMBER) AS "T1" ORDER BY "I"."NUMBER", "T1"."NUMBER"
This is a failing test for a integration adapter test for many other DBs. The query may not make sense in the real world and you would probably do it a different way, but this is a simple test ot see if the DB supports LATERAL statement like sql anywhere is suppose to. If you must know, it is for a Ruby gem, sequel. I'm wrapping up creating the adapater code now and I know LATERAL is supported. If this does look like a bug and it may be fixed in the future, I can mark the test as pending or break apart the test into the ones that pass and the ones that don't and make those pending. Thanks, Greg Ditrick |
|
Try using a comma instead of CROSS JOIN, as described in the section "lateral-derived-table" in this Help topic: "The table and the outer reference must be separated by a comma." SELECT I.Number as N,
T1.number
FROM "ITEMS" AS "I",
LATERAL ( select *
from Items
where items.Number = I.NUMBER ) AS "T1"
ORDER BY "I"."NUMBER",
"T1"."NUMBER"
N,number
10,10
20,20
|
|
Ah yes, that simple test was/is working. It was the joining tests (cross, inner, left, etc) that are failing. So, sql anywhere does not support joining with a lateral. Maybe it should. I'll split the test then into 2 tests one for basic lateral support and joining with lateral...and I'll mark the joining ones as pending in the hopes that sql anywhere will some day support joining a lateral. This way at least basic lateral statements can be generated by the adapter module. Regards, -GregD 1
Please show a non-trivial example of what you want. The example you showed doesn't need LATERAL since it is just (effectively) an INNER JOIN.
(27 Sep '13, 14:44)
Breck Carter
1
Please show us an example of another DBMS that supports INNER JOIN LATERAL... I can't find one, nor do I see the point (if you can do an INNER JOIN then why do you need LATERAL?)
(27 Sep '13, 14:50)
Breck Carter
No point in getting into a pissing match. I'm okay if that syntax is and will never be valid. HSQLDB, Postgres and DB2 support joining with a lateral. The test is trivial because it is only testing that the sql is valid and returns the appropriate results. But, a nontrivial example can be found here in Postgres: I can see using an openxml similar to their example since that is what we are using in production on a column to get a key or even just some data and then joining that via a lateral to another table. Especially when dealing with a XML interface. Again, maybe it is not needed and can always be done with the simple case. Our production code is only doing a simple or trivial operation. It was just a simple question and you have answered it. I'm just trying to get the adapter as fully functional as possible before I commit the code to the open source community.
(27 Sep '13, 15:32)
gditrick
Replies hidden
FWIW, instead of a LEFT JOIN with LATERAL, SQL Anywhere supports OUTER APPLY (I guess, similar to MS SQL Server). Cf. this blog article from Glenn Paulley:
(27 Sep '13, 15:47)
Volker Barth
Thanks. This worked. Looking at the MSSQL adapter, there was a module that simulated lateral with cross apply and outer apply. I did it similar, however only for joining a lateral and kept the simple lateral with the comma available. So, ruby code like this will use lateral: @ds.from(:itemsi, @ds.where(:itemsnumber=>:inumber).lateral).select_order_map([:i__numbern, :t1__number]).should == [[10, 10], [20, 20]] However, ruby code like this will use cross apply and outer apply: @ds.from(:itemsi).cross_join(@ds.where(:itemsnumber=>:inumber).lateral).select_order_map([:i__numbern, :t1__number]).should == [[10, 10], [20, 20]] @ds.from(:itemsi).join(@ds.where(:itemsnumber=>:inumber).lateral, 1=>1).select_order_map([:i__numbern, :t1__number]).should == [[10, 10], [20, 20]] @ds.from(:itemsi).join(@ds.where(:itemsnumber=>:inumber).lateral, 1=>0).select_order_map([:i__numbern, :t1__number]).should == [] @ds.from(:itemsi).left_join(@ds.from(:itemsi2).where(:i2number=>:inumber).lateral, 1=>1).select_order_map([:inumber_n, :t1__number]).should == [[10, 10], [20, 20]] @ds.from(:itemsi).left_join(@ds.from(:itemsi2).where(:i2number=>:inumber).lateral, 1=>0).select_order_map([:inumber_n, :t1__number]).should == [[10, nil], [20, nil]]
(30 Sep '13, 13:15)
gditrick
Comment Text Removed
So you seem to have been quite successful in that respect - from your point of view? (I'm just asking out of curiosity since I'm not familiar with Ruby adapters...)
(30 Sep '13, 15:34)
Volker Barth
Yes. It looks like the MSSQL module in Sequel simulates literal with cross apply and outer apply and since Sql Anywhere also has that capability together with a simple literal, I think I have provided what will be needed. Also, I think it is a rare/fringe case to use literal anyway and joining a literal is probably more rare.
(01 Oct '13, 07:31)
gditrick
Personally, I've literally rarely made use of LATERAL:)
(01 Oct '13, 07:39)
Volker Barth
Personally I've ONLY used LATERAL with procedure calls, everything else seems to be possible with ordinary joins... all the published examples using tables seem contrived.
(01 Oct '13, 08:36)
Breck Carter
Now that is funny. method defined as literal that I type more than I type lateral.
(01 Oct '13, 08:51)
gditrick
Though my statement is true (and I fully share Breck's experience and conclusion), I'd primarily tried to make some fun with l[ia]teral:)
(01 Oct '13, 09:05)
Volker Barth
|
Ah, I remember seeing some code in the MS adapter module that might be doing something like that. Thanks, this will make me investigate what they are doing and hopefully it will work.