Bug #94829 INSERT INTO (...) SELECT ... FROM view LIMIT X 'forgets' order defined in VIEW
Submitted: 29 Mar 2019 9:14 Modified: 29 Mar 2019 14:34
Reporter: Simon Banaan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[29 Mar 2019 9:14] Simon Banaan 
Description:
When a VIEW is defined AS

CREAT VIEW view AS SELECT * FROM table ORDER BY RAND()

SELECT * FROM view LIMIT 5 -> returns 5 random records (expected)
CREATE TABLE t2 AS SELECT * FROM view LIMIT 5 -> select 5 rows in fixed order (wrong)

How to repeat:
Run the script attached to this bug, that completely reproduces the bug
[29 Mar 2019 9:15] Simon Banaan 
This script reproduces the bug

Attachment: view_bug.sql (application/octet-stream, text), 1.06 KiB.
[29 Mar 2019 13:42] MySQL Verification Team 
Hi,

Thank you for your bug report.

However, it is not a bug. MySQL RDBMS follows strictly SQL standard. SQL standard stipulates that, if ORDER BY, is not specified, tuples can be returned in any possible order. This does not preclude sorted order, particularly if a storage engine orders its records by PK.

Not a bug.
[2 Apr 2019 12:40] MySQL Verification Team 
True, but our documentation also contains references that imply that in many cases ORDER BY can be totally ignored, like in CREATE from SELECT .....

i