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
My testcase, based on the above:
## My testcase, based on yours:
|
|
|
CREATE TABLE t1 (
|
Election int(10) unsigned NOT NULL
|
);
|
|
INSERT INTO t1 (Election) VALUES (1);
|
|
CREATE TABLE t2 (
|
VoteID int(10),
|
ElectionID int(10),
|
UserID int(10)
|
);
|
INSERT INTO t2 (ElectionID, UserID) VALUES (2, 30), (3, 30);
|
|
|
# Disable join buffering. It's easier to debug basic joins.
|
set join_cache_level=0;
|
|
|
select * FROM t1
|
LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T
|
ON T.ElectionID = t1.Election
|
LIMIT 9;
|
|
|
create view v1 as
|
select * FROM t1
|
LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T
|
ON T.ElectionID = t1.Election
|
LIMIT 9;
|
|
|
select * from v1;
|
This gives:
+----------+-------+------------+
|
| Election | Voted | ElectionID |
|
+----------+-------+------------+
|
| 1 | NULL | NULL |
|
+----------+-------+------------+
|
|
|
+----------+-------+------------+
|
| Election | Voted | ElectionID |
|
+----------+-------+------------+
|
| 1 | Y | NULL |
|
+----------+-------+------------+
|
|
Note that ElectionID is fine. The issue is with Voted column which comes from:
SELECT 'Y' AS Voted
|
Also note that
If one does
set optimizer_switch='table_elimination=off'
|
the bug is still there (and the EXPLAIN has no change)
If one does
set optimizer_switch='derived_merge=off'
|
then the bug goes away.
with null compliment bug
|
T@50 : | | | | | | | | | | | >JOIN_CACHE::join_null_complements
|
T@50 : | | | | | | | | | | | | >JOIN_CACHE::generate_full_extensions
|
T@50 : | | | | | | | | | | | | | >JOIN_CACHE:check_match
|
T@50 : | | | | | | | | | | | | | <JOIN_CACHE:check_match
|
T@50 : | | | | | | | | | | | | | >end_send
|
T@50 : | | | | | | | | | | | | | | >select_send::send_data
|
T@50 : | | | | | | | | | | | | | | | >Protocol::send_result_set_row
|
T@50 : | | | | | | | | | | | | | | | <Protocol::send_result_set_row
|
T@50 : | | | | | | | | | | | | | | | >Protocol::write
|
T@50 : | | | | | | | | | | | | | | | | packet_header: Memory: 0x7f Bytes: (4)
|
04 00 00 06
|
T@50 : | | | | | | | | | | | | | | | <Protocol::write
|
T@50 : | | | | | | | | | | | | | | <select_send::send_data
|
T@50 : | | | | | | | | | | | | | <end_send
|
T@50 : | | | | | | | | | | | | <JOIN_CACHE::generate_full_extensions
|
T@50 : | | | | | | | | | | | <JOIN_CACHE::join_null_complements
|
|
|
without null compliment bug
|
T@50 : | | | | | | | | | | | | | | | | | | >JOIN_CACHE::join_null_complements
|
T@50 : | | | | | | | | | | | | | | | | | | | >JOIN_CACHE::generate_full_extensions
|
T@50 : | | | | | | | | | | | | | | | | | | | | >JOIN_CACHE:check_match
|
T@50 : | | | | | | | | | | | | | | | | | | | | <JOIN_CACHE:check_match
|
T@50 : | | | | | | | | | | | | | | | | | | | | >end_send
|
T@50 : | | | | | | | | | | | | | | | | | | | | | >fill_record
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | >save_field_in_field
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | <save_field_in_field
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | >save_field_in_field
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | <save_field_in_field
|
T@50 : | | | | | | | | | | | | | | | | | | | | | <fill_record
|
T@50 : | | | | | | | | | | | | | | | | | | | | | >heap_write
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | >next_free_record_pos
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | | >my_malloc
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | | | my: size: 126992 flags: 65552
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | | | info: thd memory_used: 47640 size: 127016
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | | | exit: ptr: 0x7f
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | | <my_malloc
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | | exit: Used new position: 0x7f
|
T@50 : | | | | | | | | | | | | | | | | | | | | | | <next_free_record_pos
|
T@50 : | | | | | | | | | | | | | | | | | | | | | <heap_write
|
T@50 : | | | | | | | | | | | | | | | | | | | | <end_send
|
T@50 : | | | | | | | | | | | | | | | | | | | <JOIN_CACHE::generate_full_extensions
|
T@50 : | | | | | | | | | | | | | | | | | | <JOIN_CACHE::join_null_complements
|
incorrect result being written here
(gdb) where
|
#0 fill_record (thd=0x7fff94000db8, table=0x7fff942f2a80, ptr=0x7fff942f33e8, values=..., ignore_errors=true, use_value=false)
|
at /home/rex/src/mariadb/server/sql/sql_base.cc:9187
|
#1 0x0000555556080069 in select_unit::send_data (this=0x7fff942abd38, values=...) at /home/rex/src/mariadb/server/sql/sql_union.cc:119
|
#2 0x0000555555fffcd7 in select_result_sink::send_data_with_check (this=0x7fff942abd38, items=..., u=0x7fff94018298, sent=0)
|
at /home/rex/src/mariadb/server/sql/sql_class.h:5721
|
#3 0x0000555555fe5d24 in end_send (join=0x7fff942abe20, join_tab=0x7fff942af810, end_of_records=false) at /home/rex/src/mariadb/server/sql/sql_select.cc:23078
|
#4 0x000055555616fbb1 in JOIN_CACHE::generate_full_extensions (this=0x7fff942b0320, rec_ptr=0x7fff942f8979 "\001\001")
|
at /home/rex/src/mariadb/server/sql/sql_join_cache.cc:2478
|
#5 0x000055555616fed7 in JOIN_CACHE::join_null_complements (this=0x7fff942b0320, skip_last=false) at /home/rex/src/mariadb/server/sql/sql_join_cache.cc:2626
|
#6 0x000055555616f30a in JOIN_CACHE::join_records (this=0x7fff942b0320, skip_last=false) at /home/rex/src/mariadb/server/sql/sql_join_cache.cc:2194
|
#7 0x0000555555fe25b2 in sub_select_cache (join=0x7fff942abe20, join_tab=0x7fff942af458, end_of_records=true)
|
at /home/rex/src/mariadb/server/sql/sql_select.cc:21614
|
#8 0x0000555555fe27bc in sub_select (join=0x7fff942abe20, join_tab=0x7fff942af0a0, end_of_records=true) at /home/rex/src/mariadb/server/sql/sql_select.cc:21785
|
#9 0x0000555555fe1f3f in do_select (join=0x7fff942abe20, procedure=0x0) at /home/rex/src/mariadb/server/sql/sql_select.cc:21387
|
#10 0x0000555555fb3a62 in JOIN::exec_inner (this=0x7fff942abe20) at /home/rex/src/mariadb/server/sql/sql_select.cc:4822
|
#11 0x0000555555fb2ad7 in JOIN::exec (this=0x7fff942abe20) at /home/rex/src/mariadb/server/sql/sql_select.cc:4600
|
#12 0x0000555555fb4408 in mysql_select (thd=0x7fff94000db8, tables=0x7fff942f43c0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
|
proc_param=0x0, select_options=2201187781376, result=0x7fff942abd38, unit=0x7fff94018298, select_lex=0x7fff94019f28)
|
at /home/rex/src/mariadb/server/sql/sql_select.cc:5080
|
#13 0x0000555555ee494a in mysql_derived_fill (thd=0x7fff94000db8, lex=0x7fff94005120, derived=0x7fff94016eb0)
|
at /home/rex/src/mariadb/server/sql/sql_derived.cc:1289
|
#14 0x0000555555ee1882 in mysql_handle_single_derived (lex=0x7fff94005120, derived=0x7fff94016eb0, phases=96)
|
at /home/rex/src/mariadb/server/sql/sql_derived.cc:200
|
#15 0x0000555555fcf06a in st_join_table::preread_init (this=0x7fff942b1a78) at /home/rex/src/mariadb/server/sql/sql_select.cc:14588
|
#16 0x0000555555fe2846 in sub_select (join=0x7fff942aaff8, join_tab=0x7fff942b1a78, end_of_records=false) at /home/rex/src/mariadb/server/sql/sql_select.cc:21802
|
#17 0x0000555555fe1ed5 in do_select (join=0x7fff942aaff8, procedure=0x0) at /home/rex/src/mariadb/server/sql/sql_select.cc:21385
|
#18 0x0000555555fb3a62 in JOIN::exec_inner (this=0x7fff942aaff8) at /home/rex/src/mariadb/server/sql/sql_select.cc:4822
|
#19 0x0000555555fb2ad7 in JOIN::exec (this=0x7fff942aaff8) at /home/rex/src/mariadb/server/sql/sql_select.cc:4600
|
#20 0x0000555555fb4408 in mysql_select (thd=0x7fff94000db8, tables=0x7fff94016eb0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
|
proc_param=0x0, select_options=2164525824, result=0x7fff942f7f60, unit=0x7fff940051f8, select_lex=0x7fff940168d0)
|
at /home/rex/src/mariadb/server/sql/sql_select.cc:5080
|
#21 0x0000555555fa3493 in handle_select (thd=0x7fff94000db8, lex=0x7fff94005120, result=0x7fff942f7f60, setup_tables_done_option=0)
|
at /home/rex/src/mariadb/server/sql/sql_select.cc:581
|
#22 0x0000555555f4b4e9 in execute_sqlcom_select (thd=0x7fff94000db8, all_tables=0x7fff94016eb0) at /home/rex/src/mariadb/server/sql/sql_parse.cc:6265
|
#23 0x0000555555f426fd in mysql_execute_command (thd=0x7fff94000db8, is_called_from_prepared_stmt=false) at /home/rex/src/mariadb/server/sql/sql_parse.cc:3949
|
#24 0x0000555555f50117 in mysql_parse (thd=0x7fff94000db8, rawbuf=0x7fff94016850 "select * from v1", length=16, parser_state=0x7fffe0107390)
|
at /home/rex/src/mariadb/server/sql/sql_parse.cc:8000
|
#25 0x0000555555f3c742 in dispatch_command (command=COM_QUERY, thd=0x7fff94000db8, packet=0x7fff9400b929 "", packet_length=16, blocking=true)
|
at /home/rex/src/mariadb/server/sql/sql_parse.cc:1894
|
#26 0x0000555555f3b11b in do_command (thd=0x7fff94000db8, blocking=true) at /home/rex/src/mariadb/server/sql/sql_parse.cc:1407
|
#27 0x0000555556116142 in do_handle_one_connection (connect=0x555558a444f8, put_in_cache=true) at /home/rex/src/mariadb/server/sql/sql_connect.cc:1415
|
#28 0x0000555556115ead in handle_one_connection (arg=0x555558a444f8) at /home/rex/src/mariadb/server/sql/sql_connect.cc:1317
|
#29 0x0000555556616b42 in pfs_spawn_thread (arg=0x55555895d528) at /home/rex/src/mariadb/server/storage/perfschema/pfs.cc:2201
|
#30 0x00007ffff7940ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#31 0x00007ffff752daef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Johnston,
Without your patch we have:
MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT (SELECT COUNT(*) FROM t1) AS Elections, Election FROM t1) S LEFT JOIN ( SELECT ( SELECT COUNT(*) FROM t2) AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
|
+-----------+----------+-------+------------+
|
| Elections | Election | Voted | ElectionID |
|
+-----------+----------+-------+------------+
|
| 1 | 1 | 2 | NULL |
|
+-----------+----------+-------+------------+
|
and
MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
|
+-----------+----------+-------+------------+
|
| Elections | Election | Voted | ElectionID |
|
+-----------+----------+-------+------------+
|
| 1 | 1 | Y | NULL |
|
+-----------+----------+-------+------------+
|
With your patch we have:
MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT (SELECT COUNT(*) FROM t1) AS Elections, Election FROM t1) S LEFT JOIN ( SELECT ( SELECT COUNT(*) FROM t2) AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
|
+-----------+----------+-------+------------+
|
| Elections | Election | Voted | ElectionID |
|
+-----------+----------+-------+------------+
|
| 1 | 1 | 2 | NULL |
|
+-----------+----------+-------+------------+
|
and
MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
|
+-----------+----------+-------+------------+
|
| Elections | Election | Voted | ElectionID |
|
+-----------+----------+-------+------------+
|
| 0 | 1 | NULL | NULL |
|
+-----------+----------+-------+------------+
|
It looks like the problem is pretty hard.
For the queries below we have correct answers without any patch:
MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT R.a AS Voted, ElectionID FROM (SELECT 'Y' AS a FROM DUAL) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
|
+-----------+----------+-------+------------+
|
| Elections | Election | Voted | ElectionID |
|
+-----------+----------+-------+------------+
|
| 1 | 1 | NULL | NULL |
|
+-----------+----------+-------+------------+
|
|
|
MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT R.c AS Voted, ElectionID FROM (SELECT COUNT(*) AS c FROM t2) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT;
|
+-----------+----------+-------+------------+
|
| Elections | Election | Voted | ElectionID |
|
+-----------+----------+-------+------------+
|
| 1 | 1 | NULL | NULL |
|
+-----------+----------+-------+------------+
|
|
with patch at 3d92430
|
|
MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT R.a AS Voted, ElectionID FROM (SELECT 'Y' AS a FROM DUAL) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; |
+-----------+----------+-------+------------+ |
| Elections | Election | Voted | ElectionID |
|
+-----------+----------+-------+------------+ |
| 1 | 1 | NULL | NULL | |
+-----------+----------+-------+------------+ |
1 row in set (0.011 sec) |
|
|
MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT R.c AS Voted, ElectionID FROM (SELECT COUNT(*) AS c FROM t2) R, t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; |
+-----------+----------+-------+------------+ |
| Elections | Election | Voted | ElectionID |
|
+-----------+----------+-------+------------+ |
| 1 | 1 | NULL | NULL | |
+-----------+----------+-------+------------+ |
1 row in set (0.007 sec) |
|
|
SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) T ON T.ElectionID = S.Election LIMIT 9) DT; |
+-----------+----------+-------+------------+ |
| Elections | Election | Voted | ElectionID |
|
+-----------+----------+-------+------------+ |
| 1 | 1 | NULL | NULL | |
+-----------+----------+-------+------------+ |
1 row in set (0.008 sec) |
|
I'm pretty sure that "Voted" in the last query should be NULL not 'Y'.
(Commenting based on the patch, not testcases):
Johnston, One obvious thing that the patch misses is that it has the code to set item to NULL but doesn't have the code to clear it...
Looking at the issue:
Outer join computation is done as follows: for
t1 left join t2 on ...
|
table t2 may have a NULL-complemented row. This is achieved by calling mark_as_null_row() for t2, which makes all of columns in t2 to get NULL values.
In our example, however, there is a column Voted which is not a column of t2:
t1
|
LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T |
The query that is not using the view produces the correct result (Voted=NULL)...
Looking how does it manage to do that... select_send::send_data() calls this:
|
|
bool Item_direct_view_ref::send(Protocol *protocol, st_value *buffer)
|
{
|
if (check_null_ref()) |
return protocol->store_null(); |
...
|
check_null_ref() is how 'Y as Voted' changes its value depending on whether t2 has NULL-complemented row or not.
There is an accompanying setup function set_null_ref_table()...
Now, debugging the failing query... I see calls are made to Item_direct_view_ref::set_null_ref_table...
However Item_ref::save_in_field() doesn't call check_null_ref():
int Item_ref::save_in_field(Field *to, bool no_conversions) |
{
|
int res; |
if (result_field) // Not taken |
{
|
....
|
}
|
res= (*ref)->save_in_field(to, no_conversions); |
}
|
So, the fix is probably to make Item_ref::save_in_field() do the check...
Note that there are Item_direct_view_ref::save_org_in_field and Item_direct_view_ref::save_in_result_field which do call check_null_ref()...
In order to do this, we'll need to pull this
TABLE *null_ref_table;
|
|
|
void set_null_ref_table()
|
{
|
if (!view->is_inner_table_of_outer_join() ||
|
!(null_ref_table= view->get_real_join_table()))
|
null_ref_table= NO_NULL_TABLE;
|
}
|
|
|
bool check_null_ref()
|
{
|
DBUG_ASSERT(null_ref_table);
|
if (null_ref_table != NO_NULL_TABLE && null_ref_table->null_row)
|
{
|
null_value= 1;
|
return TRUE;
|
}
|
return FALSE;
|
}
|
from Item_direct_view_ref into it's parent's parent class Item_ref. Or equivalent.
Another observation: without any patch for this query we have:
MariaDB [test]> SELECT * FROM ( SELECT * FROM (SELECT 1 AS Elections, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted ) T ON S.Election = 20 LIMIT 9) DT;
|
+-----------+----------+-------+
|
| Elections | Election | Voted |
|
+-----------+----------+-------+
|
| 1 | 1 | NULL |
|
+-----------+----------+-------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM ( SELECT * FROM (SELECT 1 AS Electors, Election FROM t1) S LEFT JOIN ( SELECT 'Y' AS Voted ) T ON S.Election = 20 LIMIT 9) DT;
|
+------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
|
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | |
|
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
|
| 4 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
+------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|
|
So here T is materialized. It could not be otherwise because in any case we need the right operand for left join,
Alternative patch: https://github.com/MariaDB/server/commit/af37017ccb52a325a5894e2a051970bb19bddcc1
(Tried all posted examples on the "alternative patch". They all produce correct result)
Slightly updated commit to fix compile errors in BB: e2cc436a6a7b1c2438e8db1c02838e7fd68e8024
Here's a good test case to check that it works correctly:
create table t2 (b int); |
insert into t2 values (3),(7),(1); |
create table t3 (c int); |
insert into t3 values (3),(1); |
create table t1 (a int); |
insert into t1 values (1),(2),(7),(1); |
|
|
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 |
limit 9
|
) dt;
|
|
|
create view v3(x,c) as select * from (select 'X' as x, t3.c from t3) dt3; |
create view v2(y,b) as select * from (select 'Y' as y, t2.b from t2) dt2; |
create view v(y,b,x,c) as select * from v2 left join v3 on v2.b=v3.c; |
create view v1 as select 'Z' as z, t1.a, v.* from t1 left join v on t1.a=v.b limit 9; |
select * from v1; |
Expected result set (from PostgreSQL 9.6)
z a y b x c
|
Z 7 Y 7 (null) (null)
|
Z 1 Y 1 X 1
|
Z 1 Y 1 X 1
|
Z 2 (null) (null) (null) (null)
|
Please add it to the patch and run with join_cache_level=0 and join_cache_level=default.
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 | 1
|
Z | 1 | Y | 1 | X | 1
|
Z | 2 | | | |
|
(4 rows)
|
vs
10.11 + patch
z a y b x c
|
Z 1 Y 1 X 1
|
Z 1 Y 1 X 1
|
Z 7 Y 7 NULL NULL
|
Z 2 NULL NULL NULL NULL
|
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
Simplest egregious activation so far...
CREATE TABLE t1 (Election int(10) unsigned NOT NULL);INSERT INTO t1 (Election) VALUES (1);CREATE TABLE t2 (VoteID int(10),ElectionID int(10),UserID int(10));INSERT INTO t2 (ElectionID, UserID) VALUES (2, 30), (3, 30);-- INSERT INTO t2 (ElectionID, UserID) VALUES (1, 30);set @foo = "select * FROM t1LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS TON T.ElectionID = t1.ElectionLIMIT 9";-- limit needed for bugprepare mq from @foo;execute mq;prepare mq from concat( "CREATE VIEW v1 as ", @foo );execute mq;SELECT * FROM v1;DROP VIEW v1;DROP TABLE t1,t2;Election Voted ElectionID1 NULL NULLElection Voted ElectionID1 Y NULL