Merge olga.mysql.com:/home/igor/mysql-5.0-opt

into  olga.mysql.com:/home/igor/dev-opt/mysql-5.0-opt-bug29392

2 files changed, 102 insertions, 0 deletions

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 9adb3f96142..c51a4c30960 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3500,4 +3500,51 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
 DROP VIEW v1;
 DROP TABLE t1;
+CREATE TABLE t1 (
+person_id int NOT NULL PRIMARY KEY,
+username varchar(40) default NULL,
+status_flg char(1) NOT NULL default 'A'
+);
+CREATE TABLE t2 (
+person_role_id int NOT NULL auto_increment PRIMARY KEY,
+role_id int NOT NULL,
+person_id int NOT NULL,
+INDEX idx_person_id (person_id),
+INDEX idx_role_id (role_id)
+);
+CREATE TABLE t3 (
+role_id int NOT NULL auto_increment PRIMARY KEY,
+role_name varchar(100) default NULL,
+app_name varchar(40) NOT NULL,
+INDEX idx_app_name(app_name)
+);
+CREATE VIEW v1 AS 
+SELECT profile.person_id AS person_id
+FROM t1 profile, t2 userrole, t3 role
+WHERE userrole.person_id = profile.person_id AND
+role.role_id = userrole.role_id AND
+profile.status_flg = 'A'
+  ORDER BY profile.person_id,role.app_name,role.role_name;
+INSERT INTO  t1 VALUES
+(6,'Sw','A'), (-1136332546,'ols','e'), (0,'    *\n','0'),
+(-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0');
+INSERT INTO t2 VALUES
+(1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10);
+INSERT INTO t3 VALUES 
+(1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'),
+(3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'),
+(5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'),
+(7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'),
+(9,'RTOS_DCFSPOS_SUPERVISOR','RTOS');
+EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	profile	const	PRIMARY	PRIMARY	4	const	1	Using temporary; Using filesort
+1	SIMPLE	userrole	ref	idx_person_id,idx_role_id	idx_person_id	4	const	2	
+1	SIMPLE	role	eq_ref	PRIMARY	PRIMARY	4	test.userrole.role_id	1	
+SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
+a	b
+6	6
+6	6
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
 End of 5.0 tests.
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index f670ac8a49d..c7f722a18a5 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3348,4 +3348,59 @@ EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
 DROP VIEW v1;
 DROP TABLE t1;
 
+#
+# Bug #29392: SELECT over a multi-table view with ORDER BY  
+#             selecting the same view column with two different aliases  
+#
+
+CREATE TABLE t1 (
+  person_id int NOT NULL PRIMARY KEY,
+  username varchar(40) default NULL,
+  status_flg char(1) NOT NULL default 'A'
+);
+
+CREATE TABLE t2 (
+  person_role_id int NOT NULL auto_increment PRIMARY KEY,
+  role_id int NOT NULL,
+  person_id int NOT NULL,
+  INDEX idx_person_id (person_id),
+  INDEX idx_role_id (role_id)
+);
+
+CREATE TABLE t3 (
+  role_id int NOT NULL auto_increment PRIMARY KEY,
+  role_name varchar(100) default NULL,
+  app_name varchar(40) NOT NULL,
+  INDEX idx_app_name(app_name)
+);
+
+CREATE VIEW v1 AS 
+SELECT profile.person_id AS person_id
+  FROM t1 profile, t2 userrole, t3 role
+    WHERE userrole.person_id = profile.person_id AND
+          role.role_id = userrole.role_id AND
+          profile.status_flg = 'A'
+  ORDER BY profile.person_id,role.app_name,role.role_name;
+
+INSERT INTO  t1 VALUES
+ (6,'Sw','A'), (-1136332546,'ols','e'), (0,'    *\n','0'),
+ (-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0');
+
+INSERT INTO t2 VALUES
+  (1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10);
+
+INSERT INTO t3 VALUES 
+  (1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'),
+  (3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'),
+  (5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'),
+  (7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'),
+  (9,'RTOS_DCFSPOS_SUPERVISOR','RTOS');
+ 
+EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
+SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
 --echo End of 5.0 tests.
+