MySQL Bugs: #73856: Wrong results returned when a view definition contains a constant

Bug #73856	Wrong results returned when a view definition contains a constant
Submitted:	9 Sep 2014 12:55	Modified:	9 Sep 2014 13:39
Reporter:	Marcin Mańk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
See repro steps below. x should not be 0 in the last query.

How to repeat:
mysql> create table a as select 1 as aa;
Query OK, 1 row affected (0.42 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create table b as select 1 as bb;
Query OK, 1 row affected (0.52 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create view v as select *, 1 as x from a left join b on aa=bb;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from v;
+----+------+---+
| aa | bb   | x |
+----+------+---+
|  1 |    1 | 1 |
+----+------+---+
1 row in set (0.00 sec)

mysql> select * from v order by aa;
+----+------+---+
| aa | bb   | x |
+----+------+---+
|  1 |    1 | 0 |
+----+------+---+
1 row in set (0.00 sec)

Thank you for the report.
I could not repeat with latest builds, could you please indicate which exact version of MySQL server are you using?

mysql> use test
Database changed
mysql> create table a as select 1 as aa;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from a;
+----+
| aa |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> create table b as select 1 as bb;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from b;
+----+
| bb |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> create view v as select *, 1 as x from a left join b on aa=bb;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from v;
+----+------+---+
| aa | bb   | x |
+----+------+---+
|  1 |    1 | 1 |
+----+------+---+
1 row in set (0.02 sec)

mysql> select * from v order by aa;
+----+------+---+
| aa | bb   | x |
+----+------+---+
|  1 |    1 | 1 |
+----+------+---+
1 row in set (0.05 sec)

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.22                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.22-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

// 5.5.41

mysql> use test
Database changed
mysql>
mysql> create table a as select 1 as aa;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from a;
+----+
| aa |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> create table b as select 1 as bb;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from b;
+----+
| bb |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> create view v as select *, 1 as x from a left join b on aa=bb;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select * from v;
+----+------+---+
| aa | bb   | x |
+----+------+---+
|  1 |    1 | 1 |
+----+------+---+
1 row in set (0.00 sec)

mysql> select * from v order by aa;
+----+------+---+
| aa | bb   | x |
+----+------+---+
|  1 |    1 | 1 |
+----+------+---+
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.41                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.41-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

Last login: Tue Sep  9 07:22:18 on console
[10:27:25][pochita:]~ miguel$ 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.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table a as select 1 as aa;
Query OK, 1 row affected (0.16 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create table b as select 1 as bb;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create view v as select *, 1 as x from a left join b on aa=bb;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from v;
+----+------+---+
| aa | bb   | x |
+----+------+---+
|  1 |    1 | 1 |
+----+------+---+
1 row in set (0.00 sec)

mysql> select * from v order by aa;
+----+------+---+
| aa | bb   | x |
+----+------+---+
|  1 |    1 | 1 |
+----+------+---+
1 row in set (0.00 sec)

mysql>

It was 5.6.17 . So I guess this can be closed...