¿Bug in Unload Select?

[Former Member]

SQLANYWHERE 17.0.4.2053

DEVELOPER EDITION

DROP VARIABLE IF EXISTS @response; CREATE VARIABLE @response long nvarchar;

DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS c;

CREATE TABLE a ( id_key integer );

CREATE TABLE b ( id_key integer, id_table_a integer );

CREATE TABLE c ( id_key integer, id_table_a integer );

INSERT INTO a VALUES ( 1 ); INSERT INTO b VALUES ( 1, 1 ); INSERT INTO c VALUES ( 1, 1 );

CREATE OR REPLACE VIEW v AS SELECT a.id_key AS a1, b.id_key AS b1, c.id_key AS c1 FROM a INNER JOIN b ON ( a.id_key = b.id_table_a ), a INNER JOIN c ON ( a.id_key = c.id_table_a ) ;

UNLOAD SELECT * FROM v INTO VARIABLE @response; -- Could not execute statement. -- Illegal reference to correlation name 'a' -- SQLCODE=-824, ODBC 3 State="42S02"

[VolkerBarth]

Does the SELECT work without unload? Does a simple select from your view work?

I think your view's FROM clause is written rather unorthodox... If you just want to join a to b and c, two simple inner join conditions would be sufficient, no need for brackets or a comma operator...

[Breck_Carter]

> Does the SELECT work without unload?

IMO that is the bug... it shouldn't work, but it does 🙂

[Breck_Carter]

To expand on what Volker said...

Your view SELECT has two references to table a without any alias name:

CREATE OR REPLACE VIEW v AS 
SELECT a.id_key AS a1, 
b.id_key AS b1, 
c.id_key AS c1 
FROM a 
INNER JOIN b 
ON ( a.id_key = b.id_table_a ), 
a INNER JOIN c 
ON ( a.id_key = c.id_table_a ) ;

Try simplifying the FROM clause to get rid of the extra a and the superfluous comma-join operator:

CREATE OR REPLACE VIEW v AS 
SELECT a.id_key AS a1, 
b.id_key AS b1, 
c.id_key AS c1 
FROM a 
INNER JOIN b 
ON ( a.id_key = b.id_table_a )
INNER JOIN c 
ON ( a.id_key = c.id_table_a ) ;

[VolkerBarth]

I'd also omit the parantheses around the join condition... 🙂

[Former Member]

The view is Ok.

select * from v;

a1 b1 c1

1 1 1

"Your view SELECT has two references to table a without any alias name:",

See: http://dcx.sap.com/index.html#sqla170/en/html/81858d366ce21014b644dd6961b5a41f.html

I still think it's a bug.

[VolkerBarth]

That's why I had written

I think your view's FROM clause is written rather unorthodox

but not wrong...

Is there a reason why you do not use two simple ANSI inner joins here?

AFAIK, the star join syntax mentioned in the link is basically a Transact-SQL/ASE compatibility feature usually only needed there for particular complex cases of outer joins (which AFAIK can always be specified using ANSI JOIN syntax in SQL Anywhere). For a simple inner join between three tables, there is no need to use that unusual syntax IMHO.

As Breck has shown, the view written with two common inner join conditions does not lead to that error with UNLOAD.

---

I still think it's a bug.

As the view's definition is allowed, I agree that UNLOAD should work there, too.

[Breck_Carter]

Challenge: Explain why the following SELECT is valid, what it means, and why the result set is correct 🙂

select * from dummy, dummy, dummy, dummy, dummy;

dummy_col   
----------- 
0           
(1 rows)
Execution time: 0.015 seconds

[VolkerBarth]

Easy:

select rewrite('select * from dummy, dummy, dummy, dummy, dummy;')

returns

select dummy.dummy_col from dummy

It's also documented in the mentioned doc page:

The duplicate names must be in different joins for the syntax to make sense. When a table name or view name is used twice in the same join, the second instance is ignored. For example, FROM A,A and FROM A CROSS JOIN A are both interpreted as FROM A.

Yes, I'm reading out the docs, I'm aware that this is not at all the same as explaining the topic...

[VolkerBarth]

To add: I'm sure we both agree one should not write such kind of queries, and I have never used syntax like in this question, for a reason:)

[Breck_Carter]

When someone codes "FROM A CROSS JOIN A" that is clearly a mistake, and it should raise an error so the mistake will be noticed and fixed.

At least, that's what The Watcom Rule implies 🙂

I make lots and lots of mistakes, and I don't want any of them to be ignored.

[Breck_Carter]

> for a reason:)

You're not a dummy, dummy, dummy, dummy, dummy 🙂

[VolkerBarth]

Hm, that's probably an academic question: As a cross join returns the combination of every row from the first table and of the second table, what does that mean if it is the same table, and no alias is used to specify that two instances of the same table are meant? - I do not know the ANSI specs but IMHO it seems reasonable to return just all rows of the one and only table then, as SQL Aynwhere apparently does.

I agree that this may come as a surprise but I use cross joins rather rarely so I would not consider that a "typical mistake"...