Bug #110562 values statement

• Submitted: 30 Mar 2023 8:05
• Modified: 14 Apr 2023 11:20
• Reporter: yi liu
• Status: Verified
• Category: MySQL Server: Parser
• Severity: S2 (Serious)
• Version: 8.0.32
• OS: Windows (11)
• CPU Architecture: x86 (64bit)
• Tags: values statement failed

[30 Mar 2023 8:05] yi liu

Description:

select version();
version()|
---------+
8.0.32   |

select * from (values row(1,4), ROW(2,1), ROW(3,3)) as t
column_0|column_1|
--------+--------+
       1|       4|
       2|       1|
       3|       3|

-- ERROR:　order by clause does not work
values row(1,4), ROW(2,1), ROW(3,3) order by column_1;
column_0|column_1|
--------+--------+
       1|       4|
       2|       1|
       3|       3|

-- syntax error：1064, Can not nested ?
select *　
from (select column_0, column_1 from (values row(1,4), ROW(2,1), ROW(3,3)) as t
     ) as t2;

-- syntax error：1064, Can not used for with clause ?
with temp (
values row(1,4), ROW(2,1), ROW(3,3)
)
select * from temp;

-- syntax error：1064, Can not used for with clause ?
with temp (
select column_0, column_1 from (values row(1,4), ROW(2,1), ROW(3,3)) as t
)
select * from temp;

How to repeat:
any time on windows, the issue has not been tested on other platforms

[14 Apr 2023 11:20] MySQL Verification Team

Hello yi liu,

Thank you for the report and feedback.

Case I)
>>-- ERROR:　order by clause does not work

This is known issue, this is duplicate of Roy's "Bug #100996 Table value constructor followed by ORDER BY is not sorted correctly". This is currently marked as private hence not visible. Also, Bug #110550

Case II)
>>-- syntax error：1064, Can not nested ?

This seems to be parser issue as after removing space I'm able to see the results -

mysql> select * from (select column_0, column_1 from (values row(1, 4),ROW(2, 1),ROW(3, 3)) as t) as t2;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        4 |
|        2 |        1 |
|        3 |        3 |
+----------+----------+
3 rows in set (0.00 sec)

Case III) 

-- WITH (Common Table Expressions) <-- you ate missing the "AS" keyword, after adding I can see it works:

mysql> with temp AS (values row(1,4), ROW(2,1), ROW(3,3))select * from temp;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        4 |
|        2 |        1 |
|        3 |        3 |
+----------+----------+
3 rows in set (0.00 sec)

mysql> with temp AS (
    -> select column_0, column_1 from (values row(1,4), ROW(2,1), ROW(3,3)) as t
    -> )
    -> select * from temp;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        4 |
|        2 |        1 |
|        3 |        3 |
+----------+----------+
3 rows in set (0.00 sec)

Verifying for the II case.

regards,
Umesh

[18 Apr 2023 21:01] Dag Wanvik

Posted by developer:
 
Closed as duplicate.