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
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.
According to https://mariadb.com/kb/en/create-view/ the options can be used, if they will be taken is undefined.
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.
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.
|
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.
|
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.