Bug #10265 Select statement output and view output doesn't match
Submitted: 29 Apr 2005 15:33 Modified: 1 Jul 2005 14:15
Reporter: Disha Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.4 Beta-standard OS:Windows (Windows XP)
Assigned to: Igor Babaev CPU Architecture:Any

[29 Apr 2005 15:33] Disha
Description:
When we create two table 't1' and 't2' and execute 'select' statement with 'right join' .It displays an appropriate output.But when we create an view 'v1' using 'select' statement with 'right join' on table 't1' and 't2'.it gives an incorrect output ie 'Empty set'.

How to repeat:
Repro Steps:

1. Set the delimiter as :
    Delimiter //

2. Change the database ie execute the following SQL statement:
    Use test//

3. Create a table 't1' ie execute the following SQL statement:
     create table t1 (f81 float not null DEFAULT 8.8, f82 float unsigned not null     
     DEFAULT 8.8 ) engine = innodb//

4. Insert values in table 't1' ie execute the following SQL statement:
       insert into t1 (f81,f82) values (1,1)//
       insert into t1 (f81,f82) values (2,2)//

5. Create a table 't2' ie execute the following SQL statement:
       create table t2 (f81 float not null DEFAULT 8.8, f82 float unsigned not null   
       DEFAULT 8.8 	) engine = innodb//

6. Insert values in table 't2' ie execute the following SQL statement:
        insert into t2 (f81,f82) values (1,1)//
        insert into t2 (f81,f82) values (2,2)//

7. Execute 'select' statement :
        Select t1.f81 ,t2.f81 from t1 right join t2 on t1.f81=t2.f81 order by t1.f81 ,   
        t2.f81 DESC limit 1, 1000//

8.  Create a view 'v1' ie execute the following SQL statement:
         Create view v1 as Select t1.f81 ,t2.f81 from t1 right join t2 on 
         t1.f81=t2.f81 order by t1.f81 ,t2.f81 DESC limit 1, 1000//

9. Execute 'select' statement:
          Select * from v1//

 
Expected Result: View 'v1' should display the following output.
			+------+-----+
			| f81  | f81    |
			+------+-----+
			|    2 |   2     |
			+------+-----+
			1 row in set (0.00 sec)
   

Actual Result: View 'v1' gives an message as 'Empty set'.
[29 Apr 2005 16:27] MySQL Verification Team
Verified on Windows and Linux with today BK source, shows another bug
in the create the view:

mysql>         Create view v1 as Select t1.f81 ,t2.f81 from t1 right join t2 on 
    ->          t1.f81=t2.f81 order by t1.f81 ,t2.f81 DESC limit 1, 1000//
ERROR 1060 (42S21): Duplicate column name 'f81'
[29 Apr 2005 17:06] MySQL Verification Team
Changing the columns names on t2 for to skip the create view issue:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.6-beta-nt

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

mysql> Delimiter //
mysql> Use test//
Database changed
mysql> create table t1 (f81 float not null DEFAULT 8.8, f82 float unsigned not
    -> null DEFAULT 8.8 ) engine = innodb//
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 (f81,f82) values (1,1)//
Query OK, 1 row affected (0.07 sec)

mysql> insert into t1 (f81,f82) values (2,2)//
Query OK, 1 row affected (0.03 sec)

mysql> create table t2 (f81a float not null DEFAULT 8.8, f82a float unsigned not
    -> null  DEFAULT 8.8        ) engine = innodb//
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t2 (f81a,f82a) values (1,1)//
Query OK, 1 row affected (0.05 sec)

mysql> insert into t2 (f81a,f82a) values (2,2)//
Query OK, 1 row affected (0.04 sec)

mysql> Select t1.f81 ,t2.f81a from t1 right join t2 on t1.f81=t2.f81a order by
    -> t1.f81 , t2.f81a DESC limit 1, 1000//
+------+------+
| f81  | f81a |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.01 sec)

mysql> Create view v1 as Select t1.f81 ,t2.f81a from t1 right join t2 on
    -> t1.f81=t2.f81a order by t1.f81 ,t2.f81a DESC limit 1, 1000//
Query OK, 0 rows affected (0.02 sec)

mysql> Select * from v1//
Empty set (0.03 sec)
[1 Jul 2005 14:15] Igor Babaev
I cannot repeat this bug on the current 5.0.9 tree.
I have:
mysql> Select * from v1;
+------+------+
| f81  | f81a |
+------+------+
|    2 |    2 |
+------+------+