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

--=-MElEFLtPi9pQ0HcSAez0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit



--=-MElEFLtPi9pQ0HcSAez0
Content-Disposition: inline
Content-Description: Forwarded message - Result of UNION depends on order
of fields in SELECT
Content-Type: message/rfc822

Return-Path:
Received: from iris.cnaf.infn.it ([unix socket]) by iris.cnaf.infn.it
(Cyrus v2.1.9) with LMTP; Wed, 19 Nov 2003 13:42:01 +0100
X-Sieve: CMU Sieve 2.2
Received: from turandot.cnaf.infn.it (turandot.cnaf.infn.it [131.154.3.53])
by iris.cnaf.infn.it (8.12.10/8.12.10) with ESMTP id hAJCg0nB010510; Wed,
19 Nov 2003 13:42:00 +0100
Subject: Result of UNION depends on order of fields in SELECT
From: Vincenzo Ciaschini
To: bugs@list.mysql.com
Cc: Vincenzo Ciaschini
Content-Type: text/plain
Organization:
Message-Id: <1069245692.15189.82.camel@turandot.cnaf.infn.it>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5)
Date: 19 Nov 2003 13:41:33 +0100
X-Spam-Status: No, hits=-10.4 required=5.0
X-Spam-Level:
X-Spam-Checker-Version: SpamAssassin 2.52 (1.174.2.8-2003-03-24-exp)
X-RAVMilter-Version: 8.4.1(snapshot 20020919) (iris.cnaf.infn.it)
Content-Transfer-Encoding: 7bit

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





--=-MElEFLtPi9pQ0HcSAez0
Content-Type: text/plain; charset=us-ascii

--
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
--=-MElEFLtPi9pQ0HcSAez0--

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
> / /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org

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

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 ???

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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

Vincenzo Ciaschini wrote:


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


It is as simple as that UNION combines *results* of two queries.

Just think this way.

Columns in result are *not* the same as columns in underlying tables. This is
one of most important concepts of Realtional Database Model.

Queries do not just retrieve data from database. Instead by Query you generate
*new* data based on existing.

Always keep thinking this way and you will avoid a loty of common confusions.

Database != Storage only :)

> Bye,
> Vincenzo

Best regards

- --
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/u59ueehWBZ4HcYkRAjCOAJ4voM4PoGuHjmGlb24DdtR+oxM6FwCf R+MJ
64TOBPGr3ucG9giTyVlC7xA=
=pQ76
-----END PGP SIGNATURE-----


--
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