[MDEV-4959] Join with const table produces incorrect query result Created: 2013-08-28  Updated: 2013-08-28  Resolved: 2013-08-28

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.33
Fix Version/s: 5.5.33

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

In 5.5, revision 3589, we've had:

CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (NULL);
 
CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM;
 
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
 
INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3);
 
SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
 
i1	i2	a	b
 
# CHECK:
 
SELECT * FROM t1 JOIN t2 ON i1 = i2 WHERE a < b;
 
i1	i2	a	b
 
DROP VIEW v2;
 
DROP TABLE t1,t2;

in revision 3860, we get

CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (NULL);
 
CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM;
 
CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
 
INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3);
 
SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
 
i1	i2	a	b
 
NULL	NULL	2	3
 
NULL	NULL	1	2
 
# CHECK:
 
SELECT * FROM t1 JOIN t2 ON i1 = i2 WHERE a < b;
 
i1	i2	a	b
 
DROP VIEW v2;
 
DROP TABLE t1,t2;

Note that the query that used to return zero records now returns two records. This is incorrect.



 Comments   
Comment by Sergei Petrunia [ 2013-08-28 ]

The difference in make_join_select/add_not_null_conds. The optimizer used to add equality, but doesn't do it anymore.

Comment by Sergei Petrunia [ 2013-08-28 ]
  1. EXPLAIN for the empty-set (correct) result:
    MariaDB [j12]> explain SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
    -------------------------------------------------------------------------------------------------------------+
    id select_type table type possible_keys key key_len ref rows Extra

    -------------------------------------------------------------------------------------------------------------+

    1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
    2 DERIVED t2 ALL NULL NULL NULL NULL 2  

    -------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

  1. EXPLAIN for the incorrect result:

-----------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

-----------------------------------------------------------------------------+

1 PRIMARY t1 system NULL NULL NULL NULL 1  
1 PRIMARY <derived2> ref key0 key0 5 const 0 Using where
2 DERIVED t2 ALL NULL NULL NULL NULL 2  

-----------------------------------------------------------------------------+

Comment by Sergei Petrunia [ 2013-08-28 ]

The problem started to occur after this push:
------------------------------------------------------------
revno: 3860 [merge]
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.5-trunk
timestamp: Fri 2013-08-23 08:34:35 -0700
message:
Merge
------------------------------------------------------------
revno: 3857.1.1
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.5
timestamp: Fri 2013-08-23 07:25:45 -0700
message:
Fixed bug mdev-4420.
The code of JOIN::optimize that performed substitutions for the best equal
field in all ref items did not take into account that a multiple equality
could contain the result of the single-value subquery if the subquery is
inexpensive. This code was corrected.
Also made necessary corresponding corrections in the code of make_join_select().

Generated at Thu Feb 08 07:00:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.