Bug #23182 Got an error when using aliases in a view, thought the select works

• Submitted: 11 Oct 2006 16:18
• Modified: 15 Dec 2006 22:24
• Reporter: Katalin Hornyik
• Status: Can't repeat
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.27-BK, 5.0.24a
• OS: Linux (Linux app 2.6.18 )
• Assigned to: Evgeny Potemkin
• CPU Architecture: Any

[11 Oct 2006 16:18] Katalin Hornyik

Description:
I got an error when using aliases in a view, thought the select or the view without aliases works. This is a simple example, but if i have to use the same table twice or more in one select then i don't have workaround.

Example:

create table t1 (id int, myfield int);

create table t2 (id int, myfield int);

create or replace view almakorte as
select alma.id , alma.myfield - coalesce(sum(korte.myfield), 0)  as myfield
from
t1 as alma left join t2 as korte on alma.id=korte.id
group by alma.id, alma.myfield;

create or replace view t1t2 as
select t1.id , t1.myfield - coalesce(sum(t2.myfield), 0)  as myfield
from
t1 left join t2 on t1.id=t2.id
group by t1.id, t1.myfield;

SELECT * FROM t1t2 t;
/* success */

SELECT * FROM almakorte a;
/* i got an error: Column 'myfield' in field list is ambiguous*/

select alma.id , alma.myfield - coalesce(sum(korte.myfield), 0)  as myfield
from
t1 as alma left join t2 as korte on alma.id=korte.id
group by alma.id, alma.myfield;
/* Success */

How to repeat:
See the description.

[11 Oct 2006 16:21] Katalin Hornyik

I forgot the information: it's about the table aliases.

[11 Oct 2006 16:26] Katalin Hornyik

create or replace view almakorte as
select alma.id , alma.myfield - coalesce(sum(korte.myfield), 0)  as x
from
t1 as alma left join t2 as korte on alma.id=korte.id
group by alma.id, alma.myfield;

SELECT * FROM almakorte a;
/* Column 'myfield' in field list is ambiguous*/

[12 Oct 2006 14:28] Valeriy Kravchuk

Thank you for a bug report. Verified just as described with 5.0.27-BK on Linux.

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.27-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1, t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (id int, myfield int);

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> create table t2 (id int, myfield int);
Query OK, 0 rows affected (0.01 sec)

mysql> create or replace view almakorte as
    -> select alma.id , alma.myfield - coalesce(sum(korte.myfield), 0)  as x
    -> from
    -> t1 as alma left join t2 as korte on alma.id=korte.id
    -> group by alma.id, alma.myfield;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM almakorte a;
ERROR 1052 (23000): Column 'myfield' in field list is ambiguous
mysql> create or replace view t1t2 as
    -> select t1.id , t1.myfield - coalesce(sum(t2.myfield), 0)  as myfield
    -> from
    -> t1 left join t2 on t1.id=t2.id
    -> group by t1.id, t1.myfield;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t1t2 t;
Empty set (0.01 sec)

[15 Dec 2006 22:24] Evgeny Potemkin

Can't repeat. Tested on 5.0.32-debug version.