After spending the last hour tracking down a problem with my large
query, I have isolated this simple test case:

SELECT 1 AS Test1, 2 As Test2
UNION ALL
SELECT 2 As Test2, 1 AS Test1

Produces:

Test1 Test2
1 2
2 1

This looks very obviously wrong to me, but just wanted to check it's
not me misunderstanding some kind of usage rules with UNION or
something.

Cheers,
Jeremy

Jeremy@thebunnyshed.co.uk <Jeremy@thebunnyshed.co.uk> wrote
in
<e832a67b-d352-46e0-9e3f-7714f75b73f9@m34g2000hsf.googlegroups.com>:
> After spending the last hour tracking down a problem with
> my large query, I have isolated this simple test case:
>
> SELECT 1 AS Test1, 2 As Test2
> UNION ALL
> SELECT 2 As Test2, 1 AS Test1
>
> Produces:
>
> Test1 Test2
> 1 2
> 2 1
>
> This looks very obviously wrong to me, but just wanted to
> check it's not me misunderstanding some kind of usage
> rules with UNION or something.

11.2.7.3. UNION syntax. Read the second sentence. Then the
third sentence. Repeat until enlightened.

--
When all you have is a transformation engine, everything
looks like a tree.

On 5 Feb, 12:13, Jer...@thebunnyshed.co.uk wrote:
> After spending the last hour tracking down a problem with my large
> query, I have isolated this simple test case:
>
> SELECT 1 AS Test1, 2 As Test2
> UNION ALL
> SELECT 2 As Test2, 1 AS Test1
>
> Produces:
>
> Test1 Test2
> 1 2
> 2 1
>
> This looks very obviously wrong to me, but just wanted to check it's
> not me misunderstanding some kind of usage rules with UNION or
> something.
>
> Cheers,
> Jeremy

What amazes me here is that the excellenet explanation in the second
and third paragraphs of the manual section on UNION syntax did not
answer your question when you read it before bothering to post here???

http://dev.mysql.com/doc/refman/5.0/en/union.html

Jeremy@thebunnyshed.co.uk wrote:
> After spending the last hour tracking down a problem with my large
> query, I have isolated this simple test case:
>
> SELECT 1 AS Test1, 2 As Test2
> UNION ALL
> SELECT 2 As Test2, 1 AS Test1
>
> Produces:
>
> Test1 Test2
> 1 2
> 2 1
>
> This looks very obviously wrong to me, but just wanted to check it's
> not me misunderstanding some kind of usage rules with UNION or
> something.
>
> Cheers,
> Jeremy
>

That is correct. You are retrieving two columns - A and B, in that
order. And that is how both rows are displayed.

MySQL command line does show the names at the top of the column for your
convenience, and when using a UNION, those names come from the first
clause. But MySQL does not resequence the columns based on the names.
Those are there only for your convenience.

In a program, if you return the elements in an indexed (non-associative)
array, you will get as shown. But you retrieve them as an associative
array, [Test1] will contain 1 in both rows and [Test2] will contain 2 in
both rows.



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 5 Feb, 12:36, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> But you retrieve them as an associative
> array, [Test1] will contain 1 in both rows and [Test2] will contain 2 in
> both rows.
Hmmm, now based on what the manual says, this is not the behaviour I
would expect.

Captain Paralytic wrote:
> On 5 Feb, 12:36, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> But you retrieve them as an associative
>> array, [Test1] will contain 1 in both rows and [Test2] will contain 2 in
>> both rows.
> Hmmm, now based on what the manual says, this is not the behaviour I
> would expect.
>

Now that I read it more closely, Paul, you're right. But it makes sense.

I shouldn't try to answer questions like this before my first cuppa joe. :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Tue, 05 Feb 2008 13:36:18 +0100, Jerry Stuckle
<jstucklex@attglobal.net> wrote:

> Jeremy@thebunnyshed.co.uk wrote:
>> After spending the last hour tracking down a problem with my large
>> query, I have isolated this simple test case:
>> SELECT 1 AS Test1, 2 As Test2
>> UNION ALL
>> SELECT 2 As Test2, 1 AS Test1
>> Produces:
>> Test1 Test2
>> 1 2
>> 2 1
>> This looks very obviously wrong to me, but just wanted to check it's
>> not me misunderstanding some kind of usage rules with UNION or
>> something.
>> Cheers,
>> Jeremy
>>
>
> That is correct. You are retrieving two columns - A and B, in that
> order. And that is how both rows are displayed.
>
> MySQL command line does show the names at the top of the column for your
> convenience, and when using a UNION, those names come from the first
> clause. But MySQL does not resequence the columns based on the names.
> Those are there only for your convenience.
>
> In a program, if you return the elements in an indexed (non-associative)
> array, you will get as shown. But you retrieve them as an associative
> array, [Test1] will contain 1 in both rows and [Test2] will contain 2 in
> both rows.

Hmm, not in PHP:
<?php
$sql = 'SELECT 1 AS Test1, 2 As Test2
UNION ALL
SELECT 2 As Test2, 1 AS Test1';
$result = mysql_query($sql);
while($row = mysql_fetch_row($result)) print_r($row);
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)) print_r($row);
?>
Array
(
[0] => 1
[1] => 2
)
Array
(
[0] => 2
[1] => 1
)
Array
(
[Test1] => 1
[Test2] => 2
)
Array
(
[Test1] => 2
[Test2] => 1
)

Names for columns in the second argument just plainly don't count.
--
Rik Wasmus

On 5 Feb, 13:06, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 05 Feb 2008 13:36:18 +0100, Jerry Stuckle
>
>
>
> <jstuck...@attglobal.net> wrote:
> > Jer...@thebunnyshed.co.uk wrote:
> >> After spending the last hour tracking down a problem with my large
> >> query, I have isolated this simple test case:
> >> SELECT 1 AS Test1, 2 As Test2
> >> UNION ALL
> >> SELECT 2 As Test2, 1 AS Test1
> >> Produces:
> >> Test1 Test2
> >> 1 2
> >> 2 1
> >> This looks very obviously wrong to me, but just wanted to check it's
> >> not me misunderstanding some kind of usage rules with UNION or
> >> something.
> >> Cheers,
> >> Jeremy
>
> > That is correct. You are retrieving two columns - A and B, in that
> > order. And that is how both rows are displayed.
>
> > MySQL command line does show the names at the top of the column for your
> > convenience, and when using a UNION, those names come from the first
> > clause. But MySQL does not resequence the columns based on the names.
> > Those are there only for your convenience.
>
> > In a program, if you return the elements in an indexed (non-associative)
> > array, you will get as shown. But you retrieve them as an associative
> > array, [Test1] will contain 1 in both rows and [Test2] will contain 2 in
> > both rows.
>
> Hmm, not in PHP:
> <?php
> $sql = 'SELECT 1 AS Test1, 2 As Test2
> UNION ALL
> SELECT 2 As Test2, 1 AS Test1';
> $result = mysql_query($sql);
> while($row = mysql_fetch_row($result)) print_r($row);
> $result = mysql_query($sql);
> while($row = mysql_fetch_assoc($result)) print_r($row);
> ?>
> Array
> (
> [0] => 1
> [1] => 2
> )
> Array
> (
> [0] => 2
> [1] => 1
> )
> Array
> (
> [Test1] => 1
> [Test2] => 2
> )
> Array
> (
> [Test1] => 2
> [Test2] => 1
> )
>
> Names for columns in the second argument just plainly don't count.
> --
> Rik Wasmus

That is how I read the manual.

> 11.2.7.3. UNION syntax. Read the second sentence. Then the
> third sentence. Repeat until enlightened.

D'oh!

Sorry guys. Exceedingly guilty of not RTFM.

<slinks away>

(Thanks anyway! :-)