[MDEV-16917] Index affects query results Created: 2018-08-08  Updated: 2018-10-06  Resolved: 2018-09-15

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.8, 10.3.9, 10.3
Fix Version/s: 10.3.10

Type: Bug Priority: Major
Reporter: Frank E Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 7 3.10.0-862.9.1.el7.x86_64



 Description    

CREATE TABLE IF NOT EXISTS `test` (
 
  `n1` int(10) NOT NULL,
 
  `n2` int(10) NOT NULL,
 
  `c1` char(1) NOT NULL,
 
  KEY `c1` (`c1`) USING BTREE,
 
  KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES
 
(0, 2, 'a'),
 
(1, 3, 'a');
 
 
 
SELECT test.n1
 
FROM test
 
INNER JOIN (
 
	SELECT n1, n2
 
	FROM test
 
	WHERE
 
		c1 = 'a'
 
	GROUP BY n1
 
	) t2
 
	ON (t2.n1 = test.n1 AND t2.n2 = test.n2)
 
WHERE c1 = 'a'
 
GROUP BY n1

Running the above query, as setup above, gives the incorrect result of:
1

Removing n2 from the multi-column index (`n1`,`c1`,`n2`), or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of:
0
1

The combination of that specific multi-column index with the single-column index of c1 seems to trigger the issue.

 Comments   
Comment by Elena Stepanova [ 2018-09-02 ]

Thanks for the report and test case. Reproducible as described.

The difference was apparently introduced in 10.3.4 by this revision:

commit c5ac1f953bf6d4279967f03a8343303f715ad7aa
 
Author: Igor Babaev
 
Date:   Mon Jan 8 15:21:52 2018 -0800
 
 
 
    Fixed mdev-14880: Assertion `inj_cond_list.elements' failed
 
    in JOIN::inject_best_splitting_cond
 
    
    The value of SplM_opt_info::last_plan should be set to NULL
 
    before any search for a splitting plan for a splittable
 
    materialized table.

Comment by Igor Babaev [ 2018-09-15 ]

The EXPLAIN output for the query shows the problem:

MariaDB [test]> explain SELECT test.n1 FROM test INNER JOIN (   SELECT n1, n2   FROM test   WHERE     c1 = 'a'   GROUP BY n1   ) t2   ON (t2.n1 = test.n1 AND t2.n2 = test.n2) WHERE c1 = 'a' GROUP BY n1;
 
+------+-----------------+------------+------+---------------+----------+---------+--------------+------+--------------------------------------------------------+
 
| id   | select_type     | table      | type | possible_keys | key      | key_len | ref          | rows | Extra                                                  |
 
+------+-----------------+------------+------+---------------+----------+---------+--------------+------+--------------------------------------------------------+
 
|    1 | PRIMARY         | test       | ref  | c1,n1_c1_n2   | c1       | 3       | const        |    2 | Using index condition; Using temporary; Using filesort |
 
|    1 | PRIMARY         | <derived2> | ALL  | NULL          | NULL     | NULL    | NULL         |    2 | Using where; Using join buffer (flat, BNL join)        |
 
|    2 | LATERAL DERIVED | test       | ref  | c1,n1_c1_n2   | n1_c1_n2 | 4       | test.test.n1 |    1 | Using where; Using index                               |
 
+------+-----------------+------------+------+---------------+----------+---------+--------------+------+--------------------------------------------------------+

We can see from in execution plan that
1. splitting technique is applied to join rows of the the derived table t2
2. nested block loop is used to join t2.
This is not possible because BNL assumes that t2 is looked through once for each filling of the join buffer with rows from table 'test' .

Comment by Igor Babaev [ 2018-09-15 ]

A fix for this bug was pushed into 10.3

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