A SQL Anywhere code construct that worked up to and including 16.0.0.1324 produces an error in 16.0.0.2798 and 17.0.10.5963. This seems related to the topic "¿Bug in Unload Select?", however I thought I'd publish a clean post - especially since I have the problem pretty well isolated. I also found a work-around to prevent the problem - namely, restructuring the joins. Please see my "//" comments embedded with the code below: create view view_test as select wo.wk_ord_id, wo.wk_ord_no, p.prt_id, p.prt_cd from work_order as wo join work_order_part_part as wopp on (wo.wk_ord_id = wopp.wk_ord_id), work_order_part_part as wopp join part as p go -- Following works for all tested SQL Anywhere versions: select * from view_test; -- Following: -- 1) Works for SQL Anywhere 16.0.0.1324 -- 2) Issues error for SQL Anywhere 16.0.0.2798, 17.0.10.5963: "Column 'prt_id' not found" begin declare s_result_set long varchar; -- unload select * from view_test into variable s_result_set delimited by '\x09' quotes off; end go -- Create a view representing the same query but restructured joins: create view view_test_2 as select wo.wk_ord_id, wo.wk_ord_no, p.prt_id, p.prt_cd from work_order as wo join work_order_part_part as wopp on (wo.wk_ord_id = wopp.wk_ord_id) join part as p on (wopp.prt_id = p.prt_id) go -- Following works for all tested SQL Anywhere versions: select * from view_test_2; -- *** FOLLOWING WORKS *** for all tested SQL Anywhere versions: begin declare s_result_set long varchar; -- unload select * from view_test_2 into variable s_result_set delimited by '\x09' quotes off; end go Certainly I can restructure my joins to keep the later versions of SQL Anywhere happy, but it would be nice not to. |
Please show us the CREATE TABLE schema for these tables.
Does work_order_part_part contain prt_id? ...because it doesn't have to, for one of the queries to work (the one with an implied KEY JOIN involving part: join part as p).
A KEY JOIN is implied if the keyword JOIN is used without an ON clause.
Thanks for putting some time into this, Breck. The following creates a simplified schema that exhibits the problem (I don't see how to code-format the following in my reply here):
create table part ( prt_id integer NOT NULL DEFAULT autoincrement, prt_cd char(30) NOT NULL unique, PRIMARY KEY (prt_id) ) go
create table work_order ( wk_ord_id integer NOT NULL DEFAULT autoincrement, wk_ord_no char(30) NULL, primary key (wk_ord_id) ) go
create table work_order_part ( wk_ord_id integer NOT NULL references work_order on delete cascade, wk_ord_prt_key_no smallint NOT NULL, PRIMARY KEY (wk_ord_id, wk_ord_prt_key_no) ) go
create table work_order_part_part ( wk_ord_id integer NOT NULL, wk_ord_prt_key_no smallint NOT NULL, wk_ord_prt_prt_no smallint NOT NULL, prt_id integer references part, primary key (wk_ord_id, wk_ord_prt_key_no, wk_ord_prt_prt_no) ) go
alter table work_order_part_part add constraint work_order_part not null foreign key (wk_ord_id, wk_ord_prt_key_no) references work_order_part ( wk_ord_id, wk_ord_prt_key_no ) ON DELETE CASCADE go
Do you deliberately use a comma in the FROM clause instead of a familiar join operator (which semantically would be a CROSS JOIN)?
To cite the docs:
(Note, I don't claim to understand the last sentence.)
I have not tried to understand your query but in my experience CROSS JOINs are far less frequently used than INNER or LEFT/RIGHT JOINs, and comma operators are even more exotic (so I ususally don't "get" them)... - and according to your sample, there are FK relationships between all three tables so an (KEY) INNER or (KEY) LEFT/RIGHT JOIN might be more expected than a CROSS JOIN.
Note that the original view has two identical references to the same table in the FROM clause:
work_order_part_part as wopp
work_order_part_part as wopp
Thanks, Volker. I found the documentation you quote. The documentation example has no joins between the tables, which would give you cross joins. However the join structure in our example with join keywords, and join conditions when necessary, performs as inner key joins, produces the expected results, and performs well, using the foreign key indexes. We know a cross join when we experience one, and we occasionally encounter them when developing when leaving out a join expression. They get fixed quickly. We find this example's way of writing joins clearly documents which table joins to which and how.
As for the query - it simply performs a one-to-many join of "wo" to "wopp", and a one-to-one join of "wopp" to "p".
(tip: use explicit html pre tags for code in comments)
The following script runs without error on 17.0.9.4882
> We know a cross join when we experience one
LOL!
The two separate references to wopp in FROM clause is (almost) never seen in practice, and I have no idea how SQL Anywhere interprets it... but perhaps is overrides The Nightmare That Is Cross Join :)
Well, if so, why do you need a comma operator then?
Isn't that simply wo -> wopp -> p, so just two joins, so no need for a list of table expression?
Well, then I would write that as INNER JOINs, so nobody needs to know how it performs...
Hm, I do use CROSS JOINs rarely - but they are clearly defined, so I don't see the nightmare here...
The "create view" statements were never the problem, nor was "select * from <view>". The problem was the "unload" statement within the block as shown in my original post. It worked on some versions of SQL Anywhere, and blew up on later versions.
Indeed we don't need a comma here, and could simply join "a to b to c". In this example we should have. However we have Where clauses that join in many more tables where (I argue) the pattern with repeated table names (and aliases) results in easier to understand join lists. For example, "a to b", "b to c", "b to d to e". This example could also be written as "a to b to c", "b to d to e" - the "b" is repeated, but I think adds to readability and clarity.
Well, I guess we have different ideas of readability here, I generelly prefer one single occurence of a particular table instance (aka table alias) in the FROM clause and prefer to express the join condition by clearly telling which aliases are joined to others. Sometimes indenting the table names does help...
Bt of course YMMV.
As to the basic question, if the view is correct SQL Anywhere syntax (and the comma operator surely is valid), the unload should work, as well, so I'd consider that a bug.
OK, but the create view did have the syntax error when using my prior guesswork at the schema... sigh :)
The problem can be demonstrated by wrapping the select with a simple unload (no view, no variable):
I don't know if this FROM clause does what you want, but it eliminates the syntax error:
If that FROM clause does NOT do what you want, I highly recommend you re-code the FROM clause
(a) without mixing INNER JOIN and comma operators
(b) without coding the same table name more than once and
(c) without using the default KEY join operator.
The nightmares came during my SQL childhood, when the comma join was the only game in town, and an accidental omission from the WHERE clause transformed the comma into a cross join. It was the very first SQL question I asked on a CompuServe forum, and Joe Celko answered it.
> I'd consider that a bug
Yes, it does seem to be a bug... for some reason, the introduction of the UNLOAD keyword causes a syntax error in the FROM clause... not the SELECT clause because you still get the error if you remove prt_id from the SELECT.
Re "prefer to express the join condition by clearly telling which aliases are joined to others": I like to avoid specifying Join conditions when SQL Anywhere can figure them out (i.e. only one way to key join), and by repeating the table/alias, I can see what table joins to what other one without a join condition. It seems to me that SQL Anywhere is just ignoring duplicated table/aliases in the FROM clause. Get your alias wrong, and you have a cross join.
Well, although I certainly do like KEY JOINs, I primarily use them for ad-hoc queries - for permanent code, I often prefer the explicit conditions, just because key joins are less obvious, and one might add another FOREIGN KEY, and they no longer work... See an old question on KEY JOINs and particularly Glenn's response.
But if I do use KEY JOINs, I always code them as KEY JOINs, just as I'm always specifying INNER JOINs as such.
When I started to use SQL Anywhere (around 1996/97), I remember I was very happy that it supported the full JOIN syntax whereas others systems I had used before (primarily MS SQL and ASE) did not support that and supported just a list of tables in the FROM clause and required you to specify joins conditions like all other conditions in the WHERE clause - the classic " FROM table_1, table_2 WHERE table_1.x = table_2.x and table_1.col2 = <whatever>".
I remember, and don't miss, the old "join conditions in where clause" days, myself.
Thanks to both of you, Volker and Breck. I think we covered it, and I will think more about your viewpoints. One bottom line is that I agree with Breck that there is a bug here. We have already worked around it. Hopefully the bug gets put in a hopper, although I wouldn't call it an urgent bug.
In my understanding, all three of us agree on that. :)