Re: [Fwd: Result of UNION depends on order of fields in SELECT]

On Wed, 2003-11-19 at 17:45, Sinisa Milivojevic wrote:
> Vincenzo Ciaschini writes:
> > consider the following database:
> > CREATE TABLE t (
> >   val1 int(11) default NULL,
> >   vl2 int(11) default NULL
> > ) TYPE=MyISAM;
> > 
> > --
> > -- Dumping data for table 't'
> > --
> > 
> > INSERT INTO t VALUES (1,2);
> > INSERT INTO t VALUES (3,4);
> > 
> > Now execute this query:
> > select val1, vl2 from t where val1=3 union select val1, vl2 from t where
> > val1=1;
> > 
> > The result is:
> > 
> > +------+------+
> > | val1 | vl2  |
> > +------+------+
> > |    3 |    4 |
> > |    1 |    2 |
> > +------+------+
> > 2 rows in set (0.00 sec)
> > 
> > Correct
> > 
> > Now execute this query:
> > 
> > select val1, vl2 from t where val1=3 union select vl2, val1 from t where
> > val1=1;
> > 
> > The result is:
> > 
> > +------+------+
> > | val1 | vl2  |
> > +------+------+
> > |    3 |    4 |
> > |    2 |    1 |
> > +------+------+
> > 2 rows in set (0.00 sec)
> > 
> > Wrong.
> > 
> > Shouldn't the union statement reorder the fields in the second select?
> > 
> > Bye,
> >    Vincenzo
> 
> Hi!
> 
> No, union should not reorder columns.
> 
> Why would it be done ???
I didn't explain myself well. I thought that, because the two queries
returned an identical field list, the fields in the second query would
be inverted to properly merge with the first, obtaining in effect:

row[1].val1 row[1].vl2
row[2].val1 row[2].vl2

instead of:

row[1].val1 row[1].vl2
row[2].vl2  row[2].val1

that is what the UNION returned.

But apparently I didn't understand correctly what UNION was supposed to
do... 

Bye,
   Vincenzo

> 
> You will always get the columns in the order you asked.
> 
> -- 
> 
> Sincerely,
> 
> -- 
> For technical support contracts, go to https://order.mysql.com/?ref=msmi
>    __  ___     ___ ____  __
>   /  |/  /_ __/ __/ __ \/ /    Mr. Sinisa Milivojevic <[email protected]>
>  / /|_/ / // /\ \/ /_/ / /__   MySQL AB
> /_/  /_/\_, /___/\___\_\___/   Fulltime Developer and Support Coordinator
>        <___/   www.mysql.com   Larnaca, Cyprus
> 


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]