0

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:

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.

  • This looks like fiddle or used MariaDB version bug. All another versions of MySQL/MariaDB gives correct result. – Akina Feb 11 at 7:18
  • @Akina it's not a fiddle bug. I have MariaDB 10.4.8 installed on my machine and it happens the same. – True Soft Feb 11 at 7:57
  • Try to change applied DBMS on your fiddle. 10.3 and 10.4 gives correct result whereas 10.2 fails. – Akina Feb 11 at 7:58
  • I know that. I just want to find out which versions are affected. I have many clients with different versions, and I would like to know which ones must be updated. – True Soft Feb 11 at 8:00
  • 1
    if you recreate view using SQL standard - GROUP BY tab_parent.id, tab_parent.f_date, tab_parent.f_value it will work properly – Nikita Feb 11 at 15:34
1

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 the ONLY_FULL_GROUP_BY SQL_MODE is used.
https://mariadb.com/kb/en/group-by/

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

|improve this answer|||||
  • You have a point, but the id is unique, and each group has one row only. – True Soft Feb 11 at 19:04
  • @TrueSoft As I wrote there is no guarantee that the value for non-grouped column will be from the same row/group. – Nikita Feb 11 at 19:10
  • @Nikita - No. The only_full... does not apply since it is grouping by the PRIMARY KEY. That eliminates any ambiguity or need for MAX(). (It is cryptically alluded to with "a value computed from a non-grouped column".) – Rick James Feb 16 at 7:03
  • @RickJames "a value computed from a non-grouped column" is just some expression based on this non-grouped column. For example "f_value + 1". And as we can see in above quote "it is undefined which row the returned value is taken from". Why do you think f_value should be returned from the same row if you use group by on primary key? – Nikita Feb 17 at 9:46
  • @Nikita - I have a table of cities in Canada, PK=id. When I GROUP BY id the query is happy; when I GROUP BY province it says: "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.canada.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by '. The query is SELECT * FROM canada GROUP BY ___ LIMIT 3; The OP is doing GROUP BY id`. – Rick James Feb 17 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.