UNION, ORDER BY, error 104, and the mysterious kb q89932 (longish but interesting)

UNION, ORDER BY, error 104, and the mysterious kb q89932 (longish but interesting)

Post by LarryLa » Wed, 26 Jun 2002 02:02:08



Here's a fun experiment for you. It all takes place on SQL Server
2000, in the pubs database:

select title_id,
(select top 1 stor_id from sales where sales.title_id=titles.title_id
order by ord_date desc)
from titles

ie, give me all title IDs, and for each one, the ID of the store where
that title was most recently sold.

(Those of you who want to re-formulate this query, just hang on; the
query itself isn't actually the point)

Output is fine, 18 rows, no problem. NULLs in the second column for
those titles that have never been sold, no problem.

Now, we all know what UNION does, right? So long as we've done obvious
things like get the number of columns right, UNION means "do the first
select, do the second select, and concatentate the rowsets". For
example the straightforward

select 0,1
union
select 2,3

quite obviously gives

----------- -----------
0           1
2           3

(2 row(s) affected)

Sometimes we might use UNION to append a totals row. Sometimes we
might want to do something like this:

select title_id,
(select top 1 stor_id from sales where sales.title_id=titles.title_id
order by ord_date desc)
from titles
-- original query from above
union
select '', ''
-- arbitary extra row

Why would we want to do that? Beats me, I'm just making up examples.
The point is that it's perfectly obvious what the semantics of this
query are. And the output should hold no surprises; it should be the
18 rows returned by the original query, plus a row with '' in both
columns.

Try it. Go on, go and try it now, I don't want to spoil the surprise.

Did you get what I got? I get

Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement
contains a UNION operator.

The first thing I thought when I saw this was "Huh?", then I read it
and thought "What the ...?"

So I tinkered with the query to try and make it happy. If I say I want
all title IDs, plus the *date* of the last sale of that title, thus:

select title_id,
(select top 1 ord_date from sales where sales.title_id=titles.title_id
order by ord_date desc)
from titles
union
select '', getdate()

(so I have changed stor_id to ord_date, and matched the type in the
arbitrary extra row), it's perfectly happy. Because, as per the error
message, the "ORDER BY item" now appears in the "SELECT list". Of the
*subquery*. Which should be *already evaluated* by the time the UNION
is processed.

Now, I can see this message making (some) sense if I were doing:

select A, B, C from T1
union
select AA, BB, CC from T2
order by D

I would be happy(-ier) for SQL Server to complain that D is not in the
select list here - after all, it is being asked to order the *UNIONed*
rowset over a column not actually in it. Fair enough.

But I can't for the life of me see why an ORDER BY clause in a
*subquery* - which semantically evaluates to a single value - should
so affect the UNION.

I can't be alone in thinking this is a bug. In terms of logical
processing order, the two rowsets should *exist* by the time they are
to be UNIONed, so the *syntax* that defines them should have *no
effect* on the UNION operation. It looks suspiciously as if some part
of the *parser* is saying "Hmm, I have a UNION to do, and I also have
an ORDER BY item not in a SELECT list - I can't possibly do this!",
and not letting the semantically-aware bit even get to work.

While googling for a solution, I came across this page:

http://www.localweb.com/jkc/devtips/kb/sq3.htm

I can't say how old it is (old, I think), but I do note it has this
reference on it:

Q89932 BUG: UNION Operator w/ ORDER BY Clause Causes Error 104

with a link to

ftp://ftp.microsoft.com/bussys/sql/kb/q89/9/32.txt

Save your time - that last target does not exist. Nor does q89932 in
the latest MSDN library, or the online KB; nor indeed is this q89932
mentioned anywhere else that google has trawled.

The clear message that 'BUG: UNION Operator w/ ORDER BY Clause Causes
Error 104' sends, combined with the apparent non-existence of q89932,
on top of the this-simply-does-not-make-sense-ness of the problem
itself, have all combined to make this afternoon *extremely*
frustrating. Can anyone help? You would have my eternal thanks...

--
Larry Lard
Replies to group please.
This email address bounces everything.

 
 
 

UNION, ORDER BY, error 104, and the mysterious kb q89932 (longish but interesting)

Post by Steve Kas » Wed, 26 Jun 2002 02:29:56


Larry,

  I don't know if I'd call it a bug, but it's true that the parser is
too aggressive in finding order by clauses in union queries.
Fortunately there's always a workaround, as far as I've seen.

Workaround:

select title_id, (
  select stor_id from (
    select top 1 stor_id, ord_date from sales
    where sales.title_id=titles.title_id
    order by ord_date desc
  ) T
)
from titles
-- original query from above
union
select '', ''
-- arbitary extra row
go

Steve Kass
Drew University


> Here's a fun experiment for you. It all takes place on SQL Server
> 2000, in the pubs database:

> select title_id,
> (select top 1 stor_id from sales where sales.title_id=titles.title_id
> order by ord_date desc)
> from titles

> ie, give me all title IDs, and for each one, the ID of the store where
> that title was most recently sold.

> (Those of you who want to re-formulate this query, just hang on; the
> query itself isn't actually the point)

> Output is fine, 18 rows, no problem. NULLs in the second column for
> those titles that have never been sold, no problem.

> Now, we all know what UNION does, right? So long as we've done obvious
> things like get the number of columns right, UNION means "do the first
> select, do the second select, and concatentate the rowsets". For
> example the straightforward

> select 0,1
> union
> select 2,3

> quite obviously gives

> ----------- -----------
> 0           1
> 2           3

> (2 row(s) affected)

> Sometimes we might use UNION to append a totals row. Sometimes we
> might want to do something like this:

> select title_id,
> (select top 1 stor_id from sales where sales.title_id=titles.title_id
> order by ord_date desc)
> from titles
> -- original query from above
> union
> select '', ''
> -- arbitary extra row

> Why would we want to do that? Beats me, I'm just making up examples.
> The point is that it's perfectly obvious what the semantics of this
> query are. And the output should hold no surprises; it should be the
> 18 rows returned by the original query, plus a row with '' in both
> columns.

> Try it. Go on, go and try it now, I don't want to spoil the surprise.

> Did you get what I got? I get

> Server: Msg 104, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if the statement
> contains a UNION operator.

> The first thing I thought when I saw this was "Huh?", then I read it
> and thought "What the ...?"

> So I tinkered with the query to try and make it happy. If I say I want
> all title IDs, plus the *date* of the last sale of that title, thus:

> select title_id,
> (select top 1 ord_date from sales where sales.title_id=titles.title_id
> order by ord_date desc)
> from titles
> union
> select '', getdate()

> (so I have changed stor_id to ord_date, and matched the type in the
> arbitrary extra row), it's perfectly happy. Because, as per the error
> message, the "ORDER BY item" now appears in the "SELECT list". Of the
> *subquery*. Which should be *already evaluated* by the time the UNION
> is processed.

> Now, I can see this message making (some) sense if I were doing:

> select A, B, C from T1
> union
> select AA, BB, CC from T2
> order by D

> I would be happy(-ier) for SQL Server to complain that D is not in the
> select list here - after all, it is being asked to order the *UNIONed*
> rowset over a column not actually in it. Fair enough.

> But I can't for the life of me see why an ORDER BY clause in a
> *subquery* - which semantically evaluates to a single value - should
> so affect the UNION.

> I can't be alone in thinking this is a bug. In terms of logical
> processing order, the two rowsets should *exist* by the time they are
> to be UNIONed, so the *syntax* that defines them should have *no
> effect* on the UNION operation. It looks suspiciously as if some part
> of the *parser* is saying "Hmm, I have a UNION to do, and I also have
> an ORDER BY item not in a SELECT list - I can't possibly do this!",
> and not letting the semantically-aware bit even get to work.

> While googling for a solution, I came across this page:

> http://www.localweb.com/jkc/devtips/kb/sq3.htm

> I can't say how old it is (old, I think), but I do note it has this
> reference on it:

> Q89932 BUG: UNION Operator w/ ORDER BY Clause Causes Error 104

> with a link to

> ftp://ftp.microsoft.com/bussys/sql/kb/q89/9/32.txt

> Save your time - that last target does not exist. Nor does q89932 in
> the latest MSDN library, or the online KB; nor indeed is this q89932
> mentioned anywhere else that google has trawled.

> The clear message that 'BUG: UNION Operator w/ ORDER BY Clause Causes
> Error 104' sends, combined with the apparent non-existence of q89932,
> on top of the this-simply-does-not-make-sense-ness of the problem
> itself, have all combined to make this afternoon *extremely*
> frustrating. Can anyone help? You would have my eternal thanks...

> --
> Larry Lard
> Replies to group please.
> This email address bounces everything.


 
 
 

UNION, ORDER BY, error 104, and the mysterious kb q89932 (longish but interesting)

Post by LarryLa » Wed, 26 Jun 2002 20:30:30


Thanks for that great workaround!


> Larry,

>   I don't know if I'd call it a bug, but it's true that the parser is
> too aggressive in finding order by clauses in union queries.
> Fortunately there's always a workaround, as far as I've seen.

> Workaround:

> select title_id, (
>   select stor_id from (
>     select top 1 stor_id, ord_date from sales
>     where sales.title_id=titles.title_id
>     order by ord_date desc
>   ) T
> )
> from titles
> -- original query from above
> union
> select '', ''
> -- arbitary extra row
> go

I had *almost* got this myself, except I wasn't aliasing the innermost
subquery output as T (or anything else), and was getting a syntax
error at the second )

--
Larry Lard
Replies to group please.
This email address bounces everything.

 
 
 

1. Error 104: Using the UNION operator

One question: When you combine all for parts of the union, do you have an
ORDER BY following it all? (the message suggests that yes)

One potential problem is in the derived table definition following the 6th
element in the SELECT list:
(SELECT TOP 1 tblHDpresc.td FROM tblHDPresc WHERE
tblHDPresc.patID=[tblHDrecords].[patID] ORDER BY tblHDPresc.date DESC) AS td

It defines a derived table (from then on you can refer to "td" as a table
inside the query), but you are not referring to it anywhere. This table has
a single column "td" (confusing?) but as is it does not contribute to the
select list. That may be the problem of the ORDER BY mismatch.

Lubor


2. SQL Server Stored Procedures from ASP

3. FourGen Order Entry crasher **System Error -104**

4. MS SQL SERVER & WINDOWS XP COMPATIBILITY

5. Interbase 4 Script Error SQLCODE 104 Token unknown

6. Arguments "against" OODBMS?

7. I/O error 104 while parsing text file

8. FileMaker Pro 4.1 <-> ADABAS D

9. jdbc, use of nested ResultSet loops.(longish, interesting I hope)

10. jdbc, use of nested ResultSet loops.(longish, interesting I

11. US-NC-CRM - SELLING POINT CONFIGURATOR (ORT-104)

12. Reason Code 104 in Text Extender - Please Help