[MDEV-26749] Optimizer_switch split_materialized Returns Wrong Results Created: 2021-10-02  Updated: 2024-01-15

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.11, 10.5.12, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Kathryn Sizemore Assignee: Igor Babaev
Resolution: Unresolved Votes: 7
Labels: None
Environment:

Centos7

Attachments: File generatebug.sql     File mariadb_optimizer_bug_replication.sql    

 Description   

There is a bug with split_materialized.
When optimizer_switch='split_materialized=on'; the attached query returns very wrong results. After disabling the split_materialized, it returns the correct results.

This bug starts in 10.5.10, which is the version that includes the fixes from 10.4.19. 10.4.19 includes MDEV-25128 fix which could be a blame option. The bug does not happen in 10.5.9.

I've attached the .sql to reproduce the bug.

 Comments   
Comment by Alice Sherepa [ 2021-10-05 ]

Thank you! I repeated on 10.3-10.6:

-- source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (usernum bigint(20), id bigint(20), KEY id (id)) engine=innodb;
 
insert into t1 values (1,1),(1,2);
 
 
 
CREATE TABLE t2 (id bigint(20), parent bigint(20), PRIMARY KEY (id)) engine=innodb;
 
insert into t2 values (1,0),(2,0);
 
 
 
CREATE TABLE t3 (num bigint(20), id bigint(20), KEY id (id)) engine=innodb;
 
insert into t3 values (11,1),(12,1),(13,1),(14,1),(15,1),(16,1),(17,1),(18,1),(19,1),(20,2),(21,2),(22,2),(23,2),(24,2),(25,2),(26,2),(27,2),(28,2),(29,2);
 
 
 
 
 
CREATE TABLE t4 ( num bigint(20), a varchar(20), dt datetime, st tinyint(3) unsigned, 
  PRIMARY KEY (num), KEY st (st)) engine=innodb;
 
INSERT INTO t4 VALUES (11,'','2021-10-05 15:43:04',2),(12,'','2021-10-05 15:43:04',0),(13,'','2021-10-05 15:43:04',2),(14,'','2021-10-05 15:43:04',0),(15,'','2021-10-05 15:43:04',2),(16,'','2021-10-05 15:43:04',0),(17,'','2021-10-05 15:43:04',2),(18,'','2021-10-05 15:43:04',0),(19,'','2021-10-05 15:43:04',2),(20,'','2021-10-05 15:43:04',0),(21,'','2021-10-05 15:43:04',2),(22,'','2021-10-05 15:43:04',0),(23,'','2021-10-05 15:43:04',2),(24,'','2021-10-05 15:43:04',0),(25,'','2021-10-05 15:43:04',2),(26,'','2021-10-05 15:43:04',0),(27,'','2021-10-05 15:43:04',2),(28,'','2021-10-05 15:43:04',0),(29,'','2021-10-05 15:43:04',2);
 
 
 
 
 
set session optimizer_switch='split_materialized=on'; 
 
 
select  coalesce(dt.count,0) 
from t2
 
left join t1 on t1.id = t2.id
 
left join t2 t2a on t2a.id = t2.parent
 
left join ( 
  select t3.id, count(*) 'count'
 
  from t3 join t4 on t4.num = t3.num
 
  and not(coalesce(t4.a,'') != '' and t4.dt > now())
 
  where t4.st = 0 group by t3.id
 
) dt on dt.id = t2.id
 
where t1.usernum = 1;
 
 
 
set session optimizer_switch='split_materialized=off'; 
 
 
select  coalesce(dt.count,0) 
from t2
 
left join t1 on t1.id = t2.id
 
left join t2 t2a on t2a.id = t2.parent
 
left join ( 
  select t3.id, count(*) 'count'
 
  from t3 join t4 on t4.num = t3.num
 
  and not(coalesce(t4.a,'') != '' and t4.dt > now())
 
  where t4.st = 0 group by t3.id
 
) dt on dt.id = t2.id
 
where t1.usernum = 1;
 
 
 
drop table t1,t2,t3,t4;


MariaDB [test]>  select  coalesce(dt.count,0)  from t2 left join t1 on t1.id = t2.id left join t2 t2a on t2a.id = t2.parent left join (    select t3.id, count(*) 'count'   from t3 join t4 on t4.num = t3.num   and not(coalesce(t4.a,'') != '' and t4.dt > now())   where t4.st = 0 group by t3.id ) dt on dt.id = t2.id where t1.usernum = 1;
 
+----------------------+
 
| coalesce(dt.count,0) |
 
+----------------------+
 
|                    9 |
 
|                   10 |
 
+----------------------+
 
2 rows in set (0.019 sec)
 
 
 
MariaDB [test]> explain extended select  coalesce(dt.count,0)  from t2 left join t1 on t1.id = t2.id left join t2 t2a on t2a.id = t2.parent left join (    select t3.id, count(*) 'count'   from t3 join t4 on t4.num = t3.num   and not(coalesce(t4.a,'') != '' and t4.dt > now())   where t4.st = 0 group by t3.id ) dt on dt.id = t2.id where t1.usernum = 1;
 
+------+-----------------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------+
 
| id   | select_type     | table      | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                                           |
 
+------+-----------------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------+
 
|    1 | PRIMARY         | t2         | ALL    | PRIMARY       | NULL    | NULL    | NULL        | 2    |   100.00 |                                                 |
 
|    1 | PRIMARY         | t1         | ALL    | id            | NULL    | NULL    | NULL        | 2    |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | PRIMARY         | <derived2> | ref    | key0          | key0    | 9       | test.t2.id  | 2    |   100.00 |                                                 |
 
|    2 | LATERAL DERIVED | t3         | ref    | id            | id      | 9       | test.t1.id  | 1    |   100.00 | Using where                                     |
 
|    2 | LATERAL DERIVED | t4         | eq_ref | PRIMARY,st    | PRIMARY | 8       | test.t3.num | 1    |    47.37 | Using where                                     |
 
+------+-----------------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------+
 
5 rows in set, 1 warning (0.008 sec)
 
 
 
Note (Code 1003): /* select#1 */ select coalesce(`dt`.`count`,0) AS `coalesce(dt.count,0)` from `test`.`t2` join `test`.`t1` left join (/* select#2 */ select `test`.`t3`.`id` AS `id`,count(0) AS `count` from `test`.`t3` join `test`.`t4` where (coalesce(`test`.`t4`.`a`,'') = '' or `test`.`t4`.`dt` <= <cache>(current_timestamp())) and `test`.`t3`.`id` = `test`.`t1`.`id` and `test`.`t3`.`id` = `test`.`t2`.`id` and `test`.`t4`.`st` = 0 and `test`.`t4`.`num` = `test`.`t3`.`num` group by `test`.`t3`.`id`) `dt` on(`dt`.`id` = `test`.`t2`.`id`) where `test`.`t1`.`usernum` = 1 and `test`.`t1`.`id` = `test`.`t2`.`id`
 
 
 
MariaDB [test]> set session optimizer_switch='split_materialized=off';
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]>  select  coalesce(dt.count,0)  from t2 left join t1 on t1.id = t2.id left join t2 t2a on t2a.id = t2.parent left join (    select t3.id, count(*) 'count'   from t3 join t4 on t4.num = t3.num   and not(coalesce(t4.a,'') != '' and t4.dt > now())   where t4.st = 0 group by t3.id ) dt on dt.id = t2.id where t1.usernum = 1;
 
+----------------------+
 
| coalesce(dt.count,0) |
 
+----------------------+
 
|                    4 |
 
|                    5 |
 
+----------------------+
 
2 rows in set (0.016 sec)
 
 
 
MariaDB [test]> explain extended select  coalesce(dt.count,0)  from t2 left join t1 on t1.id = t2.id left join t2 t2a on t2a.id = t2.parent left join (    select t3.id, count(*) 'count'   from t3 join t4 on t4.num = t3.num   and not(coalesce(t4.a,'') != '' and t4.dt > now())   where t4.st = 0 group by t3.id ) dt on dt.id = t2.id where t1.usernum = 1;
 
+------+-------------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------+
 
| id   | select_type | table      | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                                           |
 
+------+-------------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------+
 
|    1 | PRIMARY     | t2         | ALL    | PRIMARY       | NULL    | NULL    | NULL        | 2    |   100.00 |                                                 |
 
|    1 | PRIMARY     | <derived2> | ref    | key0          | key0    | 9       | test.t2.id  | 2    |   100.00 |                                                 |
 
|    1 | PRIMARY     | t1         | ALL    | id            | NULL    | NULL    | NULL        | 2    |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    2 | DERIVED     | t3         | ALL    | NULL          | NULL    | NULL    | NULL        | 19   |   100.00 | Using where; Using temporary; Using filesort    |
 
|    2 | DERIVED     | t4         | eq_ref | PRIMARY,st    | PRIMARY | 8       | test.t3.num | 1    |    47.37 | Using where                                     |
 
+------+-------------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------+
 
5 rows in set, 1 warning (0.008 sec)
 
 
 
Note (Code 1003): /* select#1 */ select coalesce(`dt`.`count`,0) AS `coalesce(dt.count,0)` from `test`.`t2` join `test`.`t1` left join (/* select#2 */ select `test`.`t3`.`id` AS `id`,count(0) AS `count` from `test`.`t3` join `test`.`t4` where `test`.`t4`.`st` = 0 and `test`.`t4`.`num` = `test`.`t3`.`num` and (coalesce(`test`.`t4`.`a`,'') = '' or `test`.`t4`.`dt` <= <cache>(current_timestamp())) group by `test`.`t3`.`id`) `dt` on(`dt`.`id` = `test`.`t2`.`id`) where `test`.`t1`.`usernum` = 1 and `test`.`t1`.`id` = `test`.`t2`.`id`

Comment by Johny Hatcher [ 2022-01-26 ]

We have seen very similar behavior. I enclose file mariadb_optimizer_bug_replication.sql with another bug demonstration. On the `10.5.9-MariaDB-1:10.5.9+maria~focal` version everything worked fine. On `10.5.11-MariaDB-1:10.5.11+maria~focal`, query results depend on `split_materialized` optimizer setting (corect results are returned for optimizer_switch='split_materialized=off').

Comment by Igor Babaev [ 2022-01-31 ]

Re test case from the file generatebug.sql:
I failed to reproduce the problem on 10.4 (debug build) with mtr. However when I removed all ANALYZE TABLE statements the problem became reproducible.
I checked that the patch for MDEV-27132 already resolves the problem with the wrong result set. Yet it does not resolve the problem of MDEV-25128 that introduced a regression. For this the patch of MDEV-27510 has to be applied.

Comment by Igor Babaev [ 2022-02-01 ]

Re test case of alice
Here we have a similar situation. The test case reproduces the problem on 10.4 as it is. Yet if I add ANALYZE TABLE for t1,t2,t3,t4 the query returns the right result set with any setting for 'split_materialized'. This test case is also resolved by the patch for MDEV-27132.

Comment by Igor Babaev [ 2022-02-01 ]

Re the test case from mariadb_optimizer_bug_replication.sql:
I failed to reproduce any problem with this test case on 10.5.11 (Release/Debug builds).
johny.hatcher,
Could you please return outputs from EXPLAIN and EXPLAIN FORMAT=JSON?

Comment by Johny Hatcher [ 2022-02-01 ]

Sure, I added new version of the file mariadb_optimizer_bug_replication.sql with EXPLAIN and EXPLAIN FORMAT=JSON outputs in the comments.

Comment by Tomáลก Huda [ 2023-08-16 ]

I think we have the same problem with 10.5.21-MariaDB-1:10.5.21+maria~deb10-log on server with replication.
With split_materialized=on query returns correct results for 70 rows. 71+ rows returns correct result only for first row other rows returns null values for joins.

Example of result:
70 ids:
editionNames | editionsIds | p.id
rock oldies | 1 | 1
rock oldies | 1 | 2
indies | 2 | 3

71+ ids:
editionNames | editionsIds | p.id
rock oldies | 1 | 1
null | null | 2
null | null | 3

The ids are not the problem I tried to swap them.

Local version 10.5.17-MariaDB works fine but there is no replication. I will try to reproduce the problem locally with 10.5.21-MariaDB-1:10.5.21+maria~ubu2004-log.

Problem should not be caused by data, because it appeared right after upgrade from 10.3.x to 10.5.21.

Comment by Julien Fritsch [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Comment by JiraAutomate [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Comment by Alice Sherepa [ 2024-01-15 ]

tttpapi If you still have this problem, could you please report it separately? The other reported tests return the expected results now.

Generated at Thu Feb 08 09:47:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.