[MDEV-8606] Wrong results with optimize_join_buffer_size=on Created: 2015-08-12  Updated: 2022-12-05  Resolved: 2022-12-05

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Cannot Reproduce Votes: 0
Labels: None

Attachments: File t4.test    
Issue Links:
Relates
relates to MDEV-7844 Wrong result with optimize_join_buffe... Closed

 Description   

MariaDB [test]> SELECT t3.v1
    -> FROM t3 LEFT JOIN t4 LEFT JOIN t2 JOIN t1 
    ->   ON t2.pk = t1.pk ON t4.i1 = t1.i1 ON t3.pk = t4.i1
    -> WHERE t1.v2  BETWEEN 'b' AND 'z'
    ->   OR t1.v2 > 'd' AND t1.v2 < 'z' OR t4.v3 > 'c' AND t4.v3 < 'z';
Empty set (0.00 sec)
 
MariaDB [test]> set optimizer_switch = "optimize_join_buffer_size=on";
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> SELECT t3.v1
    -> FROM t3 LEFT JOIN t4 LEFT JOIN t2 JOIN t1 
    ->   ON t2.pk = t1.pk ON t4.i1 = t1.i1 ON t3.pk = t4.i1
    -> WHERE t1.v2  BETWEEN 'b' AND 'z'
    ->   OR t1.v2 > 'd' AND t1.v2 < 'z' OR t4.v3 > 'c' AND t4.v3 < 'z';
+-------+
| v1    |
+-------+
| OORGO |
| z     |
+-------+
2 rows in set (0.00 sec)

Test case is here.



 Comments   
Comment by Elena Stepanova [ 2019-01-24 ]

Still reproducible on 10.x.

alice, could you please clean up and file it if it turns out worth filing?

Comment by Alice Sherepa [ 2019-01-24 ]

Reproducible with InnoDB/MyISAM on 10.0-10.4

CREATE TABLE t1 (pk int,i1 int,v2 varchar(50));
INSERT INTO t1 VALUES (1,9,'l'),(2,NULL,'g'),(3,0,'dHUOO'),(4,-919994368,'d'),(5,NULL,'had'),(6,816447488,'vxdj'),(7,NULL,'o'),(8,2,'KVTXE'),(9,NULL,'then'),(10,8,'d');
 
CREATE TABLE t2 (pk int) ;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
 
CREATE TABLE t3 (pk int,v1 varchar(10),KEY (v1));
INSERT INTO t3 VALUES (42,'FKOPE'),(33,'fldelivdfp'),(29,'g'),(65,'GWIJO'),(26,'GXTFA'),(15,'have'),(73,'have'),(25,'here'),(45,'him'),(4,'how'),(35,'I'),(69,'I'),(70,'I\'m'),(60,'ifhhtcmswv'),(52,'ILWMR'),(41,'in'),(28,'j'),(40,'jrhhnkhrui'),(17,'knemrpuywl'),(51,'know'),(18,'m'),(57,'m'),(66,'m'),(49,'MZFUS'),(16,'n'),(48,'NLWOS'),(9,''),(37,''),(21,'NWVPO'),(13,'oetxbeozsq'),(7,'on'),(30,'on'),(43,'ONATO'),(2,'OORGO'),(11,'q'),(27,'q'),(23,'r'),(47,'r'),(55,'r'),(76,'t'),(31,'the'),(3,'think'),(53,'towfrumhku'),(20,'TPBOE'),(67,'TXIKB'),(12,'u'),(46,'UKIVE'),(63,'up'),(62,'were'),(54,'wfrumhkupd'),(75,'with'),(74,'would'),(10,'x'),(58,'XOVZM'),(6,'xpptxcinmt'),(59,'YDJBR'),(50,'yiccitowfr'),(64,'your'),(38,'YQKXT'),(8,'z'),(72,'z');
 
CREATE TABLE t4 ( pk int,v3 varchar(10),i1 int);
 
SET use_stat_tables=PREFERABLY;
ANALYZE TABLE t1,t2,t3,t4;
 
let $query = 
SELECT t3.v1
FROM t3 LEFT JOIN t4 LEFT JOIN t2 JOIN t1 
  ON t2.pk = t1.pk ON t4.i1 = t1.i1 ON t3.pk = t4.i1
WHERE t1.v2  BETWEEN 'b' AND 'z'
  OR t1.v2 > 'd' AND t1.v2 < 'z' OR t4.v3 > 'c' AND t4.v3 < 'z';
 
eval $query;
 set optimizer_switch = "optimize_join_buffer_size=on";
eval $query;
 
drop table t1,t2,t3,t4;

Comment by Alice Sherepa [ 2022-12-05 ]

Currently, 10.3 180b2bcd5389082e200f - 10.11 return expected (empty) result in both cases.

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