Details
Blocker Description
$ ./mtr main.delete --view
|
Logging: ./mtr main.delete --view
|
MariaDB Version 10.4.29-MariaDB-debug
|
==============================================================================
|
TEST RESULT TIME (ms) or COMMENT
|
--------------------------------------------------------------------------
|
main.delete [ fail ]
|
Test ended at 2023-02-17 09:40:49
|
|
|
CURRENT_TEST: main.delete
|
mysqltest: At line 607: query 'select * from t1
|
where $c' failed: 1356: View 'test.mysqltest_tmp_v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
|
The result from queries just before the failure was:
|
< snip >
|
#
|
# MDEV-30586: DELETE with WHERE containing nested subquery
|
# with set function aggregated in outer subquery
|
#
|
create table t1 (a int);
|
insert into t1 values (3), (7), (1);
|
create table t2 (b int);
|
insert into t2 values (2), (1), (4), (7);
|
create table t3 (a int, b int);
|
insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40);
|
select * from t1
|
where t1.a in (select t3.a from t3 group by t3.a
|
having t3.a > any (select t2.b from t2
|
where t2.b*10 < sum(t3.b)));
|
--------------------------------------------------------------------------
|
The servers were restarted 0 times
|
Spent 0.000 of 3 seconds executing testcases
|
|
|
Failure: Failed 1/1 tests, 0.00% were successful.
|
|
|
Failing test(s): main.delete
|
Attachments
Issue Links
- is caused by
-
MDEV-30586 DELETE with aggregation in subquery of WHERE returns bogus error
-
- Closed
-
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue is caused by |
| Status | Open [ 1 ] | In Progress [ 3 ] |
With the view protocol this query is executed:
select * from v; |
where v is specified as follows:
create view v as |
select * from t1 |
where t1.a in (select t3.a from t3 group by t3.a |
having t3.a > any (select t2.b from t2 |
where t2.b*10 < sum(t3.b))); |
The query returns:
MariaDB [test]> select * from v;
|
ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
Note that if we use CTE with the same specification query as for the view we get the expected result
MariaDB [test]> with cte as
|
-> (select * from t1
|
-> where t1.a in (select t3.a from t3 group by t3.a
|
-> having t3.a > any (select t2.b from t2
|
-> where t2.b*10 < sum(t3.b))))
|
-> select * from cte;
|
+------+
|
| a |
|
+------+
|
| 7 |
|
+------+
|
1 row in set (0.006 sec)
|
| Summary | main.delete fails in --view-protocol | Set function aggregated in outer select used in view definition |
| Fix Version/s | 10.7 [ 24805 ] |
| Assignee | Igor Babaev [ igor ] | Oleksandr Byelkin [ sanja ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Component/s | Authentication and Privilege System [ 13101 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Igor Babaev [ igor ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.4.29 [ 28510 ] | |
| Fix Version/s | 10.5.20 [ 28512 ] | |
| Fix Version/s | 10.6.13 [ 28514 ] | |
| Fix Version/s | 10.8.8 [ 28518 ] | |
| Fix Version/s | 10.9.6 [ 28520 ] | |
| Fix Version/s | 10.10.4 [ 28522 ] | |
| Fix Version/s | 10.11.3 [ 28524 ] | |
| Fix Version/s | 11.0.2 [ 28706 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue is duplicated by |
| Link |
This issue is duplicated by |
This bug is not caused by the fix of
MDEV-30586. The test case added in the patch forMDEV-30586demonstrates this bug with --view-protocol.