I found that on some versions of MariaDB server some simple queries return wrong results for certain data.
I have 2 tables:
CREATE TABLE `tab_parent` ( `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT, `f_date` date DEFAULT NULL, `f_value` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `tab_child` ( `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT, `parent_id` bigint(12) unsigned DEFAULT NULL, PRIMARY KEY (`id`),KEY `parent_id` (`parent_id`), CONSTRAINT `fk_2` FOREIGN KEY (`parent_id`) REFERENCES `tab_parent` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB;
And a view which counts how many children has a parent:
CREATE VIEW `tab_parent_view` AS SELECT `tab_parent`.*, COUNT(`tab_child`.`id`) AS `child_count` FROM (`tab_parent` LEFT JOIN `tab_child` ON(`tab_child`.`parent_id` = `tab_parent`.`id`)) GROUP BY `tab_parent`.`id`;
With a sample data like here: https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=0a89cb2a4bec635766313c3c59cd923c the 2 queries do not return the same number of records for versions 10.2.27 and 10.4.8 of MariaDB. But the results are correct for versions 10.1.43, 10.3.15, 10.3.16 and 10.4.12.
SELECT * FROM tab_parent_view WHERE f_date>='2020-02-07' AND f_date<'2020-02-08'; SELECT * FROM tab_parent WHERE f_date>='2020-02-07' AND f_date<'2020-02-08';
For the view only 2 records are returned, but for the table all the 4 existing ones:
Is this a known bug in MariaDB? I would like to know what causes this and on which versions is it fixed. For MySQL I did not found this problem.