Wrong query results produced when mixing UNION and INTERSECT in Sybase ASE

[lukaseder]

In the past, we had skipped a few integration tests in Sybase ASE, which had many issues related to combining and nesting set operations. We can observe:

This works

(
  select 'A' [x]
  union 
  select 'B' [x]
  union 
  select 'C' [x]
)
intersect (
  select 'A' [x]
)
order by 1

Resulting in

x|
-|
A|

This fails with an internal error

(
  select 'A' [x]
  union 
  select 'B' [x]
  union 
  select 'C' [x]
)
intersect (
  select 'A' [x]
  union all
  select 'B' [x]
)
order by 1

Raising

SQL-Fehler [202] [ZZZZZ]: Internal error -- Unable to open table at query execution time.

This works again:

(
  select 'A' [x]
  union 
  select 'B' [x]
  union 
  select 'C' [x]
)
intersect (
  select *
  from (
    select 'A' [x]
    union
    select 'B' [x]
  ) x
)
order by 1

Producing:

x|
-|
A|
B|

This fails, where INTERSECT behaves like UNION ALL:

select *
from (
  select 'A' [x]
  union 
  select 'B' [x]
  union 
  select 'C' [x]
) x
intersect (
  select *
  from (
    select 'A' [x]
    union
    select 'B' [x]
  ) x
)
order by 1

Producing

x|
-|
A|
A|
B|
B|
C|

Workaround

Obviously, Sybase seems to be full of bugs in this area. But perhaps, we can work around some of them and produce queries that produce correct results.

[lukaseder]

As a bonus, this one crashes the JDBC connection:

select *
from (
  select *
  from (
    select 'A' [x]
  ) x
  union 
  select *
  from (
    select 'B' [x]
  ) x
  union 
  select *
  from (
    select 'C' [x]
  ) x
) x
intersect 
select *
from (
  select 'A' [x]
) x
order by 1