Bug #75596 Problem with a view, a literal and an order by
Submitted: 23 Jan 2015 9:27 Modified: 23 Jan 2015 10:44
Reporter: Louis Schimchowitsch Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.18 OS:Any
Assigned to: CPU Architecture:Any

[23 Jan 2015 9:27] Louis Schimchowitsch
Description:
I created a view that contains a literal 

create or replace view test_view as select value1 as value_with_order_by, 'a' as my_constant from test_table1, test_table2;

If I select everything from the view I get the expected result with a column 'my_constant' that contains a, but if I select everything from the view and use order by on 'value_with_order_by', the column 'my_constant' is empty :

select * from test_view order by value_with_order_by;
returns 
+---------------------+-------------+
| value_with_order_by | my_constant |
+---------------------+-------------+
| 1 | |
+---------------------+-------------+

I run mysql enterprise edition 5.6.18, it was tested using mysql-workbench and directly in the command line

How to repeat:
create table test_table1 (value1 integer);
insert into test_table1 values (1);
create table test_table2 (value2 integer);
insert into test_table2 values (1); 
create or replace view test_view as select value1 as value_with_order_by, 'a' as my_constant from test_table1, test_table2;
select * from test_view order by value_with_order_by;
[23 Jan 2015 10:44] MySQL Verification Team
Thank you for the report.
I cannot repeat this issue with the latest GA build.
Could you please check with 5.6.22?

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

mysql> create table test_table1 (value1 integer);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_table1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> create table test_table2 (value2 integer);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_table2 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> create or replace view test_view as select value1 as value_with_order_by, 'a' as my_constant from test_table1, test_table2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_view order by value_with_order_by;
+---------------------+-------------+
| value_with_order_by | my_constant |
+---------------------+-------------+
|                   1 | a           |
+---------------------+-------------+
1 row in set (0.00 sec)