Wrong results on queries with join for some MariaDB versions

Posted on

Question :

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`)
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

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:

Query results

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.

Answer :

I don’t think it’s a bug. You use GROUP BY and at that moment server leaves only one random value for f_date in each row. After that you apply your filter for results returned by the query inside the view.
According to MariaDB documentation:

You can use any of the grouping functions in your select expression.
Their values will be calculated based on all the rows that have been
grouped together for each result row. If you select a non-grouped
column or a value computed from a non-grouped column, it is undefined
which row the returned value is taken from. This is not permitted if

There is no guarantee that value for non-grouped column will be returned from the same group of rows.
I would recommend you to enable ONLY_FULL_GROUP_BY SQL_MODE and add all non-grouped columns to GROUP BY part or apply grouping functions for them (for example, you could use MAX(f_date)).

Leave a Reply

Your email address will not be published. Required fields are marked *