Problem using MyODBC (Linux version) - bracketed UNION

[Adrian]

Dear All

I've come across a strange issue when using UNION via MyODBC on my
Fedora linux box (I'm using MySQL 4.1.3-standard-beta)

**This doesn't work:

(select a from tbl_name where a=10 and b=1 order by a)
union
(select a from tbl_name where a=11 and b=2 order by a)
order by 1

(took this straight from the MySQL manual)

**Removing parentheses doesn't work either:

select a from tbl_name where a=10 and b=1 order by a
union
select a from tbl_name where a=11 and b=2 order by a
order by 1

**But this does work:

select a from tbl_name where a=10 and b=1
union
select a from tbl_name where a=11 and b=2
order by 1
- so it appears that ORDER BY is not allowed in an individual select
when it is being amalgamated with UNION (or UNION ALL).

Interestingly, the first statement works fine when executing from the
MySQL command line.

Has anyone else spotted this, and is there a workaround (other than
not ordering select clauses that are UNIONed together)?

Regards
Adrian

[Bill Karwin]

Adrian wrote:

- so it appears that ORDER BY is not allowed in an individual select
when it is being amalgamated with UNION (or UNION ALL).

It could be that ODBC is doing some pre-parsing to ensure valid SQL.
The feature of ordering each component query and then ordering the
resulting union may be a nonstandard feature (I'm not certain of this),
and should rightly be disallowed in standard SQL.

In any case, why are you trying to order each query if you're just going
to re-order the resulting union? The preliminary ordering is
effectively a no-op, and therefore a waste of work.

Regards,
Bill K.