[MDEV-28602] Wrong result with outer join, merged derived table and view Created: 2022-05-18  Updated: 2023-05-15  Resolved: 2023-01-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.11.2, 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Critical
Reporter: Lena Startseva Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: view-protocol

Issue Links:
PartOf
is part of MDEV-27691 make working view-protocol Open
Problem/Incident
causes MDEV-31277 Wrong result on second execution of p... Stalled

 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



 Comments   
Comment by Rex Johnston [ 2022-12-11 ]

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 t1
 
  LEFT JOIN ( SELECT 'Y' AS Voted, ElectionID FROM t2 ) AS T 
    ON T.ElectionID = t1.Election
 
LIMIT 9";
 
 
 
-- limit needed for bug
 
 
 
prepare 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	ElectionID
 
1	NULL	NULL
 
Election	Voted	ElectionID
 
1	Y	NULL

Comment by Sergei Petrunia [ 2022-12-15 ]

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 |
 
+----------+-------+------------+

Comment by Sergei Petrunia [ 2022-12-15 ]

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.

Comment by Rex Johnston [ 2022-12-21 ] 

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

Comment by Rex Johnston [ 2022-12-21 ]

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

Comment by Igor Babaev [ 2022-12-26 ]

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.

Comment by Igor Babaev [ 2022-12-26 ]

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 |
 
+-----------+----------+-------+------------+

Comment by Rex Johnston [ 2022-12-27 ]

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'.

Comment by Sergei Petrunia [ 2022-12-27 ]

(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...

Comment by Sergei Petrunia [ 2022-12-27 ]

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);
 
}

Comment by Sergei Petrunia [ 2022-12-27 ]

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()...

Comment by Rex Johnston [ 2022-12-27 ]

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.

Comment by Igor Babaev [ 2022-12-28 ]

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,

Comment by Sergei Petrunia [ 2023-01-03 ]

Setting correct status.

Comment by Sergei Petrunia [ 2023-01-04 ]

Review input: https://lists.launchpad.net/maria-developers/msg13275.html

Comment by Sergei Petrunia [ 2023-01-04 ]

Alternative patch: https://github.com/MariaDB/server/commit/af37017ccb52a325a5894e2a051970bb19bddcc1

Comment by Sergei Petrunia [ 2023-01-04 ]

(Tried all posted examples on the "alternative patch". They all produce correct result)

Comment by Sergei Petrunia [ 2023-01-04 ]

sanja could you please review?

Comment by Sergei Petrunia [ 2023-01-04 ]

Slightly updated commit to fix compile errors in BB: e2cc436a6a7b1c2438e8db1c02838e7fd68e8024

Comment by Igor Babaev [ 2023-01-04 ]

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.

Comment by Rex Johnston [ 2023-01-05 ]

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

Comment by Igor Babaev [ 2023-01-06 ]

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;

Comment by Igor Babaev [ 2023-01-06 ]

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.

Comment by Sergei Petrunia [ 2023-01-09 ]

Thanks, all input from sanja and [igor is now taken into account in patch variant #3:
https://github.com/MariaDB/server/commit/f3647b8b2e694423f3575b9bd227777476c2fb8d

Comment by Oleksandr Byelkin [ 2023-01-10 ]

OK to push

Generated at Thu Feb 08 10:02:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.