Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Description
Test:
CREATE TABLE t1 (i INT); |
create view v1 as SELECT 1 FROM t1 |
UNION
|
SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 |
FOR UPDATE; |
select * from v1; |
drop view v1; |
Expected result:
1
|
1
|
Actual result:
query 'select * from v1' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order by 1' at line 1 |
Attachments
Issue Links
- is part of
-
MDEV-27691
make working view-protocol
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue is part of |
| Description |
*Test:*
{code:sql} create view v1 as SELECT 1 FROM t1 UNION SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 FOR UPDATE; select * from v1; drop view v1; {code} *Expected result:* {code:sql} 1 1 {code} *Actual result:* {code:sql} query 'select * from v1' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order by 1' at line 1 {code} |
*Test:*
{code:sql} CREATE TABLE t1 (i INT); create view v1 as SELECT 1 FROM t1 UNION SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 FOR UPDATE; select * from v1; drop view v1; {code} *Expected result:* {code:sql} 1 1 {code} *Actual result:* {code:sql} query 'select * from v1' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order by 1' at line 1 {code} |
marko, I agree with you, but since the documentation does not prohibit this and in version 10.3 this test passes successfully, I was forced to create a bug.
| Labels | view-protocol |
| Assignee | Lena Startseva [ JIRAUSER50478 ] | Oleksandr Byelkin [ sanja ] |
According to https://mariadb.com/kb/en/create-view/ the options can be used, if they will be taken is undefined.
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] |
| Fix Version/s | 10.7 [ 24805 ] |
| Fix Version/s | 10.8 [ 26121 ] |
| Fix Version/s | 10.9 [ 26905 ] |
| Fix Version/s | 10.10 [ 27530 ] |
| Link |
This issue relates to |
| Fix Version/s | 10.4 [ 22408 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
It printed in view as:
select 1 AS `1` from `test`.`t1` union select 1 AS `1` from DUAL where 1 group by 1 having 1 for update order by 1
|
lock type should go to fake_select but somehow it appeared in the last select:
(gdb) p fake_select_lex->lock_type
|
$1 = TL_READ_DEFAULT
|
(gdb) p first_select()->lock_type
|
$2 = TL_READ_DEFAULT
|
(gdb) p first_select()->next_select()->lock_type
|
$3 = TL_WRITE
|
It looks like it is correct was to set the lock, so it also should be looked there.
| Priority | Minor [ 4 ] | Major [ 3 ] |
commit 365e7db6990aa10f6137ce37d2e5251d6c64ad9f (HEAD -> bb-10.5-MDEV-29537, origin/bb-10.5-MDEV-29537)
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Fri Sep 20 14:58:23 2024 +0200
|
|
|
MDEV-29537 Creation of view with UNION and SELECT ... FOR UPDATE in definition is failed with error
|
|
lock_type writen in the last SELECT of the unit even it parsed last,
|
so it should be printed last from the last select of the unit.
|
| Assignee | Oleksandr Byelkin [ sanja ] | Alexander Barkov [ bar ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Alexander Barkov [ bar ] | Nikita Malyavin [ nikitamalyavin ] |
commit 7043423f37d8a58fe9ac99a1b3ef25dd6f2d796c (HEAD -> bb-10.5-MDEV-29537, origin/bb-10.5-MDEV-29537)
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Fri Sep 20 14:58:23 2024 +0200
|
|
|
MDEV-29537 Creation of view with UNION and SELECT ... FOR UPDATE in definition is failed with error
|
|
lock_type writen in the last SELECT of the unit even it parsed last,
|
so it should be printed last from the last select of the unit.
|
| Component/s | Views [ 10111 ] | |
| Fix Version/s | 10.5.27 [ 29902 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Fix Version/s | 10.6.20 [ 29903 ] | |
| Fix Version/s | 10.11.10 [ 29904 ] | |
| Fix Version/s | 11.2.6 [ 29906 ] | |
| Fix Version/s | 11.4.4 [ 29907 ] |
Is it a good idea to allow locking reads in a view, or in joins whose execution order might not be deterministic, depending on the query plan? Note: there is also LOCK IN SHARE MODE.