Could this be a bug?....other relational DB that support lateral this works.

Table ITEMS

  • ID NUMBER
  • 1 10
  • 2 20

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:

  • N NUMBER
  • 10 10
  • 20 20

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"

  • N NUMBER
  • 10 10
  • 10 10
  • 20 20
  • 20 20

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

asked 27 Sep '13, 13:44

gditrick's gravatar image

gditrick
31114
accept rate: 0%

edited 30 Sep '13, 15:35

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819

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.

(27 Sep '13, 16:00) gditrick

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
permanent link

answered 27 Sep '13, 14:10

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 27 Sep '13, 14:11

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

permanent link

answered 27 Sep '13, 14:20

gditrick's gravatar image

gditrick
31114
accept rate: 0%

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:

http://www.postgresonline.com/journal/archives/285-PostgreSQL-9.3-Lateral-Part2-The-Lateral-Left-Join.html

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:

From the Archives: Cross and Outer Apply

(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

I'm just trying to get the adapter as fully functional as possible ...

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

to use literal anyway

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
showing 3 of 11 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×43
×14
×5
×1
×1

question asked: 27 Sep '13, 13:44

question was seen: 4,694 times

last updated: 01 Oct '13, 09:05