Details
Description
Table Elimination works wrong for the view. Ex:
(EDIT : Table Elimination doesn't have anything to do with this bug)
Test:
CREATE TABLE t1 ( |
PostID int(10) unsigned NOT NULL |
) DEFAULT CHARSET=utf8; |
|
|
INSERT INTO t1 (PostID) VALUES (1), (2); |
|
|
CREATE TABLE t2 ( |
VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
EntityID int(10) unsigned NOT NULL, |
UserID int(10) unsigned NOT NULL, |
UNIQUE KEY EntityID (EntityID,UserID) |
) DEFAULT CHARSET=utf8; |
|
INSERT INTO t2 (EntityID, UserID) VALUES (1, 30), (2, 30); |
|
|
CREATE VIEW v1 as SELECT t1.*, T.Voted as Voted |
FROM |
t1 LEFT JOIN ( |
SELECT 1 AS Voted, EntityID |
FROM t2 |
WHERE t2.UserID = '20' ) AS T |
ON T.EntityID = t1.PostID |
WHERE t1.PostID='1' |
LIMIT 1;
|
SELECT * FROM v1; |
|
|
DROP VIEW v1; |
DROP TABLE t1,t2; |
Actual result:
PostID Voted
|
1 1
|
Expected result:
PostID Voted
|
1 NULL |
Attachments
Issue Links
- causes
-
MDEV-31277 Wrong result on 2-nd execution of PS to select from view using derived
-
- Closed
-
- is part of
-
MDEV-27691
make working view-protocol
-
- Closed
-
Activity
Another good query to test:
select * from |
(
|
select * from |
(select 'Z' as z, t1.a from t1) dt1 |
left join |
(select 'Y' as y, t2.b from t2) dt2 |
left join |
(select 'X' as x, t3.c from t3) dt3 |
on dt2.b=dt3.c |
on dt1.a=dt2.b and x IS NULL |
limit 9
|
) dt;
|
Another observation: the setting null_value= 1; in the added method Item_dIrect_view_ref:: save_in_field is useless as Item_direct_view_ref::check_null_ref() already has this setting.
Thanks, all input from sanja and [igor is now taken into account in patch variant #3:
https://github.com/MariaDB/server/commit/f3647b8b2e694423f3575b9bd227777476c2fb8d
I've tested the above query on 10.11 + SergeiP's patch & pg-11.7 and it produces the same result set.
PG-11.7
z | a | y | b | x | c---+---+---+---+---+---Z | 7 | Y | 7 | |Z | 1 | Y | 1 | X | 1Z | 1 | Y | 1 | X | 1Z | 2 | | | |(4 rows)vs
10.11 + patch
z a y b x cZ 1 Y 1 X 1Z 1 Y 1 X 1Z 7 Y 7 NULL NULLZ 2 NULL NULL NULL NULL