Bug #15055 Using ORDER BY on a column that you select twice duplicates alias

• Submitted: 18 Nov 2005 20:24
• Modified: 15 Feb 2006 11:07
• Reporter: Mark Leith
• Status: Can't repeat
• Category: MySQL Server: Views
• Severity: S3 (Non-critical)
• Version: 5.0.16-GA
• OS: Windows (Windows Only)
• Assigned to: Evgeny Potemkin
• CPU Architecture: Any

[18 Nov 2005 20:24] Mark Leith

Description:
Using ORDER BY on a column that you SELECT from a VIEW, whilst selecting the column twice (once with an alias) will cause the alias to be re-used for the column across all returns based on that column. 

The view seems to have to be joined to a normal table also

How to repeat:

DROP TABLE t1;
DROP TABLE t2;
DROP VIEW v1;

CREATE TABLE  t1 (x INT, y INT);

CREATE VIEW v1 (x, y) AS 
SELECT x, y
FROM t1;

CREATE TABLE t2 (x INT, y INT);

INSERT INTO t1 VALUES (1,1);
INSERT INTO t2 VALUES (1,1);

SELECT v1.x, v1.x x1, v1.x x2
  FROM v1, t2
WHERE v1.x = t2.y
ORDER BY 1;

SELECT v1.x, v1.x x1, v1.x x2
  FROM v1, t2
WHERE v1.x = t2.y;

[15 Feb 2006 11:07] Evgeny Potemkin

mysql> CREATE TABLE  t1 (x INT, y INT);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE VIEW v1 (x, y) AS 
    -> SELECT x, y
    -> FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE t2 (x INT, y INT);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO t1 VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT v1.x, v1.x x1, v1.x x2
    ->   FROM v1, t2
    -> WHERE v1.x = t2.y
    -> ORDER BY 1;
+------+------+------+
| x    | x1   | x2   |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT v1.x, v1.x x1, v1.x x2
    ->   FROM v1, t2
    -> WHERE v1.x = t2.y;
+------+------+------+
| x    | x1   | x2   |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
1 row in set (0.00 sec)

mysql> 
mysql> select VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.0.18-debug |
+--------------+
1 row in set (0.00 sec)