unload statement error introduced post 16.0.0.1324

[dhkom]

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.

[Breck_Carter]

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.

CREATE TABLE work_order (
wk_ord_id INTEGER, 
wk_ord_no INTEGER );

CREATE TABLE work_order_part_part (
wk_ord_id INTEGER PRIMARY KEY );

CREATE TABLE part (
wk_ord_id INTEGER REFERENCES work_order_part_part ( wk_ord_id ),
prt_id INTEGER,
prt_cd INTEGER ); 

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

[dhkom]

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

[VolkerBarth]

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

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:

A comma works like a join operator, but is not one. A comma creates a cross product exactly as the keyword CROSS JOIN does. However, join keywords create table expressions, and commas create lists of table expressions.

(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.

[Breck_Carter]

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

[dhkom]

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".

[Breck_Carter]

(tip: use explicit html pre tags for code in comments)

The following script runs without error on 17.0.9.4882

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

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

SELECT 'create view view_test is OK';

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

SELECT 'create view view_test_2 is OK';

SELECT @@version;

create view view_test is OK   
create view view_test_2 is OK   
17.0.9.4882

[Breck_Carter]

> 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 🙂

[VolkerBarth]

it simply performs a one-to-many join of "wo" to "wopp", and a one-to-one join of "wopp" to "p".

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?

However the join structure in our example with join keywords, and join conditions when necessary, performs as inner key joins

Well, then I would write that as INNER JOINs, so nobody needs to know how it performs...

[VolkerBarth]

Hm, I do use CROSS JOINs rarely - but they are clearly defined, so I don't see the nightmare here...

[dhkom]

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.

[dhkom]

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.

[VolkerBarth]

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.

[Breck_Carter]

OK, but the create view did have the syntax error when using my prior guesswork at the schema... sigh 🙂

[Breck_Carter]

The problem can be demonstrated by wrapping the select with a simple unload (no view, no variable):

unload 
  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 
to 'c:\\\\temp\\\\x.txt';

Could not execute statement.
Column 'prt_id' not found
SQLCODE=-143, ODBC 3 State="42S22"
Line 1, column 1

I don't know if this FROM clause does what you want, but it eliminates the syntax error:

unload 
  select 
    wo.wk_ord_id,
    wo.wk_ord_no,
    p.prt_id,
    p.prt_cd 
  from
    work_order as wo 
       inner join work_order_part_part as wopp 
          on (wo.wk_ord_id = wopp.wk_ord_id) 
       inner join part as p 
          on (wopp.prt_id = p.prt_id)
to 'c:\\\\temp\\\\x.txt';

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.

[Breck_Carter]

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.

[Breck_Carter]

> 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.

[dhkom]

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.

[VolkerBarth]

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>".

[dhkom]

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.

[VolkerBarth]

One bottom line is that I agree with Breck that there is a bug here.

In my understanding, all three of us agree on that. 🙂