union and union all

How-To-Repeat:
Windows MySQL 4.0.11

create table tmp(a integer);
insert into tmp values (1);

Bug 1: returns 1 row only
select 'a' as b from tmp union 'cde' as b from tmp;
b
-----
a

Bug 2: returns shortest string only
select 'a' as b from tmp union all 'cde' as b from tmp;
b
----
a
c


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13936@lists.mysql.com
To unsubscribe, e-mail

Re: union and union all

At 9:26 +0800 3/11/03, wkwing@cc.hku.hk wrote:
>How-To-Repeat:
>Windows MySQL 4.0.11
>
>create table tmp(a integer);
>insert into tmp values (1);
>
>Bug 1: returns 1 row only
>select 'a' as b from tmp union 'cde' as b from tmp;
>b
>-----
>a

Hard to comment on this because the statement you show is syntactically
illegal. (No "select" after "union".) When I change the statement to
the following one, I get the expected result:

mysql> select 'a' as b from tmp union select 'cde' as b from tmp;
+---+
| b |
+---+
| a |
| c |
+---+


>
>Bug 2: returns shortest string only
>select 'a' as b from tmp union all 'cde' as b from tmp;
>b
>----
>a
>c

This behavior is as expected (aside from the missing "select" after
"union" again. The names *and types* of the result set columns come
from the first table. For the query shown, the type is char(1), so
'cde' gets truncated to 'c'.


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13937@lists.mysql.com
To unsubscribe, e-mail

Re: union and union all

Hi,

wkwing@cc.hku.hk wrote:
> How-To-Repeat:


> create table tmp(a integer);
> insert into tmp values (1);
>
> Bug 1: returns 1 row only
> select 'a' as b from tmp union 'cde' as b from tmp;

This query should return Syntax error because of missing select after UNION

> Bug 2: returns shortest string only
> select 'a' as b from tmp union all 'cde' as b from tmp;

Same here.

> b
> ----
> a
> c

About Stgring truncation: this is known diffrence with SQL-99. MySQL is more
strict in applying rule that columns from first query in determine the column
types for the rest of the queries.

We have scheduled this SQL-99 conformance for MySQL 5.0

Enetered in bugs database as http://bugs.mysql.com/bug.php?id=96


Best regards

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13940@lists.mysql.com
To unsubscribe, e-mail