MySQL Bugs: #14861: aliased column names are not preserved.

Bug #14861	aliased column names are not preserved.
Submitted:	11 Nov 2005 11:35	Modified:	21 Dec 2005 10:38
Reporter:	Anthony Milbourne	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.17-BK, 5.0.15	OS:	Linux (Linux, Windows)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
I am connecting to MySQL 5.0.15 through the command line client on Windows XP.

I have several views on a table and an performing a select on one of the views.  When the table contains certain data the column aliases in the query are lost.  With other data in the table they are preserved.

I originally thought this was a connector/J bug and (prematurely) logged it as bug #14789.

This bug appears to manifest through JDBC and Connector/J as well as through the command line client.

It breaks the SQL generated by the MySQL dialect in Hibernate.

How to repeat:
Create a database schema something like:

--- cut ---
DROP TABLE IF EXISTS t_receipt;
CREATE TABLE t_receipt (
	id numeric NOT NULL,
	order_id char(8),
	warehouse_id numeric(5),
	product_id char(8),
	quantity numeric,
	KEY warehouse_id (warehouse_id),
	KEY product_id (product_id),
	CONSTRAINT t_receipt PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP VIEW IF EXISTS v_receipt;
CREATE VIEW v_receipt AS 
	SELECT min(id) AS id, order_id
	FROM t_receipt 
	GROUP BY order_id;

DROP VIEW IF EXISTS v_warehouse_receipt;
CREATE VIEW v_warehouse_receipt AS 
	SELECT min(base.id) AS id, base.warehouse_id, rcpt.id AS receipt_id 
	FROM t_receipt base, v_receipt rcpt 
	WHERE base.order_id = rcpt.order_id 
	GROUP BY receipt_id, warehouse_id;

DROP VIEW IF EXISTS v_receipt_item;
CREATE VIEW v_receipt_item AS 
	SELECT base.id AS id, whse.id AS warehouse_receipt_id, product_id, quantity
	FROM t_receipt base, v_warehouse_receipt whse, v_receipt rcpt 
	WHERE base.warehouse_id = whse.warehouse_id AND base.order_id = rcpt.order_id AND rcpt.id = whse.receipt_id;
--- cut ---

insert some data into the table:
--- cut ---
insert into t_receipt (id, warehouse_id, order_id, product_id, quantity) values(1, 1, 'ORD-1000', 'PROD-001', 45);
insert into t_receipt (id, warehouse_id, order_id, product_id, quantity) values(2, 1, 'ORD-1000', 'PROD-002', 20);
--- cut ---

run a query:
--- cut ---
select v_receipt_item.id, v_receipt_item.warehouse_receipt_id as alias1, v_receipt_item.warehouse_receipt_id as alias2 from v_receipt_item order by v_receipt_item.id;
--- cut ---

and you get the correct result:
--- cut ---
+----+--------+--------+
| id | alias1 | alias2 |
+----+--------+--------+
|  1 |      1 |      1 |
|  2 |      1 |      1 |
+----+--------+--------+
2 rows in set (0.00 sec)
--- cut ---

BUT...
insert some more data into the table:
--- cut ---
insert into t_receipt (id, warehouse_id, order_id, product_id, quantity) values(3, 2, 'ORD-1000', 'PROD-001', 10);
insert into t_receipt (id, warehouse_id, order_id, product_id, quantity) values(4, 2, 'ORD-1000', 'PROD-002', 7);
insert into t_receipt (id, warehouse_id, order_id, product_id, quantity) values(5, 1, 'ORD-1001', 'PROD-001', 50);
insert into t_receipt (id, warehouse_id, order_id, product_id, quantity) values(6, 1, 'ORD-1001', 'PROD-002', 15);
--- cut ---

then rerun the same query and the column aliases are wrong:
--- cut ---
+----+--------+--------+
| id | alias2 | alias2 |
+----+--------+--------+
|  1 |      1 |      1 |
|  2 |      1 |      1 |
|  3 |      3 |      3 |
|  4 |      3 |      3 |
|  5 |      5 |      5 |
|  6 |      5 |      5 |
+----+--------+--------+
6 rows in set (0.00 sec)
--- cut ---

Suggested fix:
Don't know - but removing the order by clause from the select fixes the problem for some reason.

Thank you for a bug report. Verified just as described on 5.0.17-BK (ChangeSet@1.1967, 2005-11-11 13:39:46+04:00, ...) on Linux:

mysql> insert into t_receipt (id, warehouse_id, order_id, product_id, quantity)
    -> values(1, 1, 'ORD-1000', 'PROD-001', 45);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_receipt (id, warehouse_id, order_id, product_id, quantity)
    -> values(2, 1, 'ORD-1000', 'PROD-002', 20);
Query OK, 1 row affected (0.00 sec)

mysql> select v_receipt_item.id, v_receipt_item.warehouse_receipt_id as alias1,
    -> v_receipt_item.warehouse_receipt_id as alias2 from v_receipt_item order by
    -> v_receipt_item.id;
+----+--------+--------+
| id | alias1 | alias2 |
+----+--------+--------+
|  1 |      1 |      1 |
|  2 |      1 |      1 |
+----+--------+--------+
2 rows in set (0.01 sec)

mysql> insert into t_receipt (id, warehouse_id, order_id, product_id, quantity)
    -> values(3, 2, 'ORD-1000', 'PROD-001', 10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_receipt (id, warehouse_id, order_id, product_id, quantity)
    -> values(4, 2, 'ORD-1000', 'PROD-002', 7);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_receipt (id, warehouse_id, order_id, product_id, quantity)
    -> values(5, 1, 'ORD-1001', 'PROD-001', 50);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_receipt (id, warehouse_id, order_id, product_id, quantity)
    -> values(6, 1, 'ORD-1001', 'PROD-002', 15);
Query OK, 1 row affected (0.00 sec)

mysql> select v_receipt_item.id, v_receipt_item.warehouse_receipt_id as alias1,
v_receipt_item.warehouse_receipt_id as alias2 from v_receipt_item order by v_receipt_item.id;
+----+--------+--------+
| id | alias2 | alias2 |
+----+--------+--------+
|  1 |      1 |      1 |
|  2 |      1 |      1 |
|  3 |      3 |      3 |
|  4 |      3 |      3 |
|  5 |      5 |      5 |
|  6 |      5 |      5 |
+----+--------+--------+
6 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/33017

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/245

Fixed in 5.0.18

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.18 changelog. Closed.