Fwd: Union not handled properly

--- Original Message ---
From: "Ron Savage"
To: MySQL: Bugs
Cc:
Sent: Thu, 9 Oct 2003 09:42:08 +1000
Subject: Union not handled properly

> Hi Folks
>
> I have 3 tables, and 2 have the same number of records. So if I
> issue
> this command:
>
> select 'A', count(*)
> from person
> union
> select 'B', count(*)
> from role
> union
> select 'C', count(*)
> from connexion
>
> I get:
>
> A 7213
> B 7910
> C 7910
>
> as expected. But if I drop the letters 'A' etc from the command:
>
> select count(*)
> from person
> union
> select count(*)
> from role
> union
> select count(*)
> from connexion
>
> I only get:
>
> 7213
> 7910
>
> I expected 3 lines of output in this case too.


--
Ron Savage, rons@deakin.edu.au on 9/10/2003. Room EF 312
Deakin University, 221 Burwood Highway, Burwood, VIC 3125, Australia
Phone: +61-3-9251 7067, Fax: +61-3-9251 7604
http://www.deakin.edu.au/~rons



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

RE: Union not handled properly

You're getting the expected result. You'll need to specify "UNION ALL"
to get wha you want.

Check http://www.mysql.com/doc/en/UNION.html, especially:

"If you don't use the keyword ALL for the UNION, all returned rows will
be unique, as if you had done a DISTINCT for the total result set. If
you specify ALL, then you will get all matching rows from all the used
SELECT statements. "

This conforms to the standards, and the behavior of other DBMS's.

> > Hi Folks
> >
> > I have 3 tables, and 2 have the same number of records. So
> if I issue
> > this command:
> >
> > select 'A', count(*)
> > from person
> > union
> > select 'B', count(*)
> > from role
> > union
> > select 'C', count(*)
> > from connexion
> >
> > I get:
> >
> > A 7213
> > B 7910
> > C 7910
> >
> > as expected. But if I drop the letters 'A' etc from the command:
> >
> > select count(*)
> > from person
> > union
> > select count(*)
> > from role
> > union
> > select count(*)
> > from connexion
> >
> > I only get:
> >
> > 7213
> > 7910
> >
> > I expected 3 lines of output in this case too.
>
>
> --
> Ron Savage, rons@deakin.edu.au on 9/10/2003. Room EF 312
> Deakin University, 221 Burwood Highway, Burwood, VIC 3125, Australia
> Phone: +61-3-9251 7067, Fax: +61-3-9251 7604
> http://www.deakin.edu.au/~rons
>
>
>
> --
> MySQL Bugs Mailing List
>
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe: http://lists.mysql.com/bugs?unsub=kfries@kurant.com
>
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org