MySQL Bugs: #64088: no select works against a view created with limit (x)

Bug #64088	no select works against a view created with limit (x)
Submitted:	20 Jan 2012 19:18	Modified:	20 Jan 2012 19:33
Reporter:	Alex Didenko	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	all	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	limit, VIEW

Description:
if you create a view against a large table, and include "limit 1", what you are saying is: "any select statement against this view has to return at most one row", which is a very valid business restriction. But what happens is this: if the select has "where field=xxx" and that "xxx" is located in row nnn using the primary key, then no select will ever work. Only the first row,based on the primary key, of the the underlying table is retrievable in this scenario. This not what is needed. We need that full select with the "where" clause be evaluated against the underlying table, and the "limit (x)" clause be also passed on, appended to the "where" clause. That is not happening.
 

How to repeat:
CREATE TABLE `Table1` (
  `col1` char(12) NOT NULL,
  `col2` int(10) unsigned DEFAULT NULL,
  `col3` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

select * from Table1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| fifth  | NULL | NULL |
| first  | NULL | NULL |
| fourth | NULL | NULL |
| second | NULL | NULL |
| third  | NULL | NULL |
+--------+------+------+
5 rows in set (0.00 sec)

create view myview as select col1 from Table1 limit 1;

select * from myview where col1='third';
Empty set (0.00 sec)

as you can see, this result is absurd.

For me everything looks correct and expected:

macbook-pro:5.5 openxs$ bin/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 28
Server version: 5.5.20-debug-log Source distribution

Copyright (c) 2000, 2011, 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 `Table1` (
    ->   `col1` char(12) NOT NULL,
    ->   `col2` int(10) unsigned DEFAULT NULL,
    ->   `col3` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`col1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
    -> ;
Query OK, 0 rows affected (0.28 sec)

mysql> insert into `Table1`(col1) values ('fifth'), ('fourth'), ('third'), ('second'), ('first');
Query OK, 5 rows affected (0.11 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from Table1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| fifth  | NULL | NULL |
| first  | NULL | NULL |
| fourth | NULL | NULL |
| second | NULL | NULL |
| third  | NULL | NULL |
+--------+------+------+
5 rows in set (0.04 sec)

mysql> select col1 from Table1 limit 1;
+-------+
| col1  |
+-------+
| fifth |
+-------+
1 row in set (0.03 sec)

mysql> create view myview as select col1 from Table1 limit 1;
Query OK, 0 rows affected (0.20 sec)

mysql> select * from myview;
+-------+
| col1  |
+-------+
| fifth |
+-------+
1 row in set (0.20 sec)

mysql> select * from myview where col1 = 'third';
Empty set (0.10 sec)

So, where absurd starts?

shouldn't merge algorithm be able to do this?

http://dev.mysql.com/doc/refman/5.5/en/view-algorithms.html

it seems so, but can't, because limit is used in the view :(
"MERGE cannot be used if the view contains any of the following constructs:
...
LIMIT
"