| 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: | |
| 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 | ||
[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
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)