Bug #72227 Missing values when querying views with literal values in view definition
Submitted: 3 Apr 2014 17:40 Modified: 28 May 2014 11:25
Reporter: g - Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.17, 5.7.5-m15 OS:Any
Assigned to: CPU Architecture:Any
Tags: literal, Views

[3 Apr 2014 17:40] g -
Description:
When a view is defined with a literal string value for a column, the values of that column are returned as blanks in the results of some but not all queries.

This problem appeared after upgrading from 5.5 to 5.6.

How to repeat:
CREATE TABLE `foos` (`name` VARCHAR(255) ) ENGINE=InnoDB;
INSERT INTO `foos` (`name`) values ('foo 1'), ('foo 2');

CREATE OR REPLACE VIEW foos_view AS
    SELECT
        name               as name,
        "foo"              as literal_type,
        IFNULL(NULL,"foo") as expression_type
    FROM foos
;   

# this works as expected:

mysql> SELECT literal_type FROM foos_view;
+--------------+
| literal_type |
+--------------+
| foo          |
| foo          |
+--------------+
2 rows in set (0.00 sec)

# adding DISTINCT should return a single result with "foo", but instead a blank record is returned:

mysql> SELECT DISTINCT literal_type FROM foos_view;
+--------------+
| literal_type |
+--------------+
|              |
+--------------+
1 row in set (0.00 sec)

# a workaround for this is to wrap the literal value in any kind of redundant expression when defining the view

# this then works as expected:

mysql> SELECT DISTINCT expression_type FROM foos_view;
+-----------------+
| expression_type |
+-----------------+
| foo             |
+-----------------+
1 row in set (0.00 sec)

# the problem also strangely appears when performing joins and ordering by something in the joined table

# this works as expected:

mysql> SELECT foos_view_1.literal_type, foos_view_2.literal_type FROM foos_view as foos_view_1 LEFT JOIN foos_view AS foos_view_2 ON foos_view_1.name =  foos_view_2.name;
+--------------+--------------+
| literal_type | literal_type |
+--------------+--------------+
| foo          | foo          |
| foo          | foo          |
+--------------+--------------+
2 rows in set (0.01 sec)

# adding ORDER BY should not change the results, but instead empty values are returned:
 
mysql> SELECT foos_view_1.literal_type, foos_view_2.literal_type FROM foos_view as foos_view_1 LEFT JOIN foos_view A S foos_view_2 ON foos_view_1.name =  foos_view_2.name ORDER BY foos_view_2.name;
+--------------+--------------+
| literal_type | literal_type |
+--------------+--------------+
|              |              |
|              |              |
+--------------+--------------+
2 rows in set (0.00 sec)
[4 Apr 2014 6:01] g -
Updated OS from FreeBSD to Any since this also happens on Mac OS X.
[4 Apr 2014 7:33] MySQL Verification Team
Hello!!

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[4 Apr 2014 7:35] MySQL Verification Team
// 5.6.17

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `foos` (`name` VARCHAR(255) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `foos` (`name`) values ('foo 1'), ('foo 2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE OR REPLACE VIEW foos_view AS
    ->     SELECT
    ->         name               as name,
    ->         "foo"              as literal_type,
    ->         IFNULL(NULL,"foo") as expression_type
    ->     FROM foos
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT literal_type FROM foos_view;
+--------------+
| literal_type |
+--------------+
| foo          |
| foo          |
+--------------+
2 rows in set (0.00 sec)

mysql> SELECT DISTINCT literal_type FROM foos_view;
+--------------+
| literal_type |
+--------------+
|              |
+--------------+
1 row in set (0.00 sec)

mysql> select * from  foos_view;
+-------+--------------+-----------------+
| name  | literal_type | expression_type |
+-------+--------------+-----------------+
| foo 1 | foo          | foo             |
| foo 2 | foo          | foo             |
+-------+--------------+-----------------+
2 rows in set (0.00 sec)
[28 May 2014 11:25] Erlend Dahl
Fixed as a duplicate of the final fix for

Bug#52582 Incorrect results with COUNT DISTINCT and BIG_TABLES