[MDEV-16922] Problems with Mroonga storage engine Created: 2018-08-08  Updated: 2021-11-09

Status: Confirmed
Project: MariaDB Server
Component/s: Storage Engine - Mroonga
Affects Version/s: 10.1, 10.3.8, 10.2, 10.3
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: Josep Sanz Assignee: Kouhei Sutou
Resolution: Unresolved Votes: 0
Labels: count, mroonga
Environment:

GNU/Linux Centos 7.5

Attachments: File bugcount.sql    

 Description   

I have detected an issue when execute a select count of another query that internaly uses myisam with mroonga with an index.

I have attached a file that demostrates the problem.

The result of the sql script is:

SELECT COUNT(*) FROM tbl_aaa;
 
 
 
COUNT(*)
 
10
 
 
 
SELECT COUNT(*) FROM tbl_bbb;
 
 
 
COUNT(*)
 
20
 
 
 
SELECT COUNT(*)
 
FROM (
 
	SELECT a.id
 
	FROM tbl_aaa a
 
	LEFT JOIN tbl_bbb b ON b.id_aplicacion=21 AND b.id_registro=a.id
 
) c;
 
 
 
COUNT(*)
 
10
 
 
 
CREATE INDEX ccc ON tbl_bbb (id_registro);
 
 
 
SELECT COUNT(*)
 
FROM (
 
	SELECT a.id
 
	FROM tbl_aaa a
 
	LEFT JOIN tbl_bbb b ON b.id_aplicacion=21 AND b.id_registro=a.id
 
) c;
 
 
 
COUNT(*)
 
15



 Comments   
Comment by Alice Sherepa [ 2018-08-13 ]

Thanks a lot for the report and test case!
Reproducible on MariaDB 10.1-10.3, not on 10.0

--source ../../include/mroonga/have_mroonga.inc
 
CREATE TABLE t1 (id int);
 
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 
 
 
CREATE TABLE t2 (n1 int,n2 int) ENGINE=Mroonga ;
 
INSERT INTO `t2` VALUES (21,1),(21,2),(21,3),(21,4),(21,5),(21,6),(21,7),(21,8),(21,9),(21,10);
 
INSERT INTO `t2` VALUES (22,6),(22,7),(22,8),(22,9),(22,10),(22,11),(22,12),(22,13),(22,14),(22,15);
 
 
 
SELECT COUNT(*)
 
FROM ( SELECT t1.id FROM t1 LEFT JOIN t2 ON t2.n1=21 AND t2.n2=t1.id) c;
 
 
 
CREATE INDEX ccc ON t2 (n2);
 
 
 
SELECT COUNT(*)
 
FROM ( SELECT t1.id FROM t1 LEFT JOIN t2 ON t2.n1=21 AND t2.n2=t1.id) c; #wrong results
 
 
 
SELECT COUNT(*),1
 
FROM ( SELECT 1 FROM t1 LEFT JOIN t2 ON t2.n1=21 AND t2.n2=t1.id) c; # correct results, when select not only count(*), but also smth else
 
 
 
drop table t1,t2;
 
--source ../../include/mroonga/have_mroonga_deinit.inc

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