2

I have a question on subqueries in advantage. As I was analyzing several SQL queries I stumbled upon a strange situation.

When I executed the following SQL, I got these results:

select *
from orderlyn
where OLWArtnr in (select OlwArtnr from prijs)

But OlwArtNr doesn’t exist in the table Prijs.

CREATE TABLE Prijs (
      Nummer Char( 20 ),
      Lijst Char( 15 ),
      Verkprijs Double( 0 ),
      Aankprijs Double( 0 ),
      BTW Char( 2 ),
      Naam1 Char( 30 ),
      Naam2 Char( 30 ),
      Naam3 Char( 30 ),
      Naam4 Char( 30 ),
      Vervangnr Char( 20 ),
      Kortcode Char( 10 ),
      Datum Date,
      Vpeuro Double( 0 ),
      A1 Char( 20 )) IN DATABASE;

What worried me most of all, is the fact that it gives different results, when I used select nummer from prijs as subquery.

Why does advantage gives me results, when the column doesn’t even exist inside the other table? I think it should give an error.

If advantage is making interpretations, on which parameters is it doing this?

StackExchange What The Heck
  • 2,656
  • 7
  • 39
  • 51
Hanne
  • 39
  • 3
  • Wow! Fully qualify OLWArtbnr Orderlyn.OLWArtnr and prijs.OlwArtnr and see what it says then. That would be an awful bug / feature if you are right. – Tony Hopkinson Mar 27 '12 at 10:19
  • When I fully qualify the columns the Advantage gives an error. But when i don't qualify in the subquery he still gives results. – Hanne Apr 04 '12 at 11:56
  • Have a plus for the question. 16 years I've been doing SQL, never ran into it.... – Tony Hopkinson Apr 04 '12 at 21:34

1 Answers1

5

This is not a bug. It is working correctly.

The OlwArtnr in the subquery, when not fully qualified, resolves to the OlwArtnr column in the parent query. Unqualified column in the subquery is resolved using the table in the subquery first. If it is not one of the columns from the tables in the subquery, it will go to the parent query, using the tables in the parent query. This process continues up the chain until either the column resolves to a table or an error is generated.

You will get the same with SQLServer or other database.

Alex W
  • 3,283
  • 1
  • 19
  • 25
  • I Tried this on a SQLServer. But he gave me an error that the column doesn't exist..... – Hanne Apr 04 '12 at 11:53
  • You get a plus for being right (just tested in SQL Server), I'm going to vehemently disagree that it's correct though... – Tony Hopkinson Apr 04 '12 at 12:39
  • 1
    @Tony It is correct according to the ANSI SQL standard. Although I agree that it can lead to incorrect SQL statement, such as the one above, being executed, it makes perfect sense if one understand scoping rule in the SQL statement. If the scoping rule is not allowed, then all outer reference in the subquery will need to be fully qualified. It will make statements clearer but they will be longer. I guess the people setting the standard think that there is still merit to shorter SQL statements. Or it could be a legacy from the initial standard. – Alex W Apr 04 '12 at 20:39
  • @Hanne, Which version of SQLServer did you test it on? Are you certain that the tables are set up correctly? Both Tony and I had verified the behavior. – Alex W Apr 04 '12 at 20:43
  • Fully qualified it fails. I know if I'd have wrote this code I would consider it a bug, not a feature. If I'd designed it to work like this, I would consider it a flaw. – Tony Hopkinson Apr 04 '12 at 21:30
  • I tested it on SQL server 2008 – Hanne Apr 11 '12 at 08:13
  • I just tried this with MySQL 5.5.12 and the behavior is the same as Advantage. It is consistent with the behavior described by @AlexW. – Mark Wilkins Apr 11 '12 at 15:00
  • @Hanne If you're using SQL 2008, you should be able to run the following and see that @Alex is right. `DECLARE @t1 table (C1 int) DECLARE @t2 table (C2 int) INSERT INTO @t1 VALUES (1) INSERT INTO @t2 VALUES (2) SELECT * FROM @t1 WHERE C1 IN (SELECT C1 FROM @t2)` – Disillusioned Aug 10 '15 at 08:34