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 |
[3 Apr 2014 17:40]
g -
[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