Mariadb – Wrong results on queries with join for some MariaDB versions

join;mariadb

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.

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

Related Solutions

Mysql – Retrieving most recent X rows for each given user from a table

I hope you are willing to do some programming outside of SQL.

To find the recipients that need the most pruning:

SELECT recipient
    FROM inbox
    GROUP BY recipient
    HAVING COUNT(*) > 30
    ORDER BY COUNT(*) DESC
    LIMIT 100

Then, for each recipient, first find the 31st id:

SELECT id
    FROM inbox
    WHERE recipient = $recipient
    ORDER BY id DESC
    LIMIT 30,1

And delete the excess baggage:

DELETE FROM inbox
    WHERE recipient = $recipient
      AND id < $id

You would need INDEX(recipient, id).

This process could be running continually. The first SQL would be the slowest, but it would run "Using index" and probably not be too bad, maybe 0.1 sec for 200K rows in inbox.

If you want to stay in SQL, this would let you do one recipient at a time:

BEGIN;
SELECT @recipient := recipient
    FROM inbox
    GROUP BY recipient
    HAVING COUNT(*) > 30
    ORDER BY COUNT(*) DESC
    LIMIT 1
    FOR UPDATE;
SELECT @id := id
    FROM inbox
    WHERE recipient = @recipient
    ORDER BY id DESC
    LIMIT 30,1
    FOR UPDATE;
DELETE FROM inbox
    WHERE recipient = @recipient
      AND id < @id;
COMMIT;

Then, put that in a Stored Procedure with a loop around it. And, if you want to be further 'nice', add a SELECT SLEEP(1); in the loop but outside the transaction.

(Trying to do the entire thing in a single statement makes my brain hurt.)

MySQL – Cannot Create Federated View in MariaDB

You are using FederatedX engine and since you are not specifying column definitions explicitly, it is trying to perform assisted discovery to retrieve the structure of the table. It cannot do it with the underlying "table" being a view, so you are getting the error.

Instead, try to specify the columns:

-- remote
CREATE VIEW `DB_1`.`testView` AS SELECT "test_line"

-- local
CREATE TABLE `DB_1`.`testView` (test_line varchar(16))
ENGINE=FEDERATED CONNECTION='remote_server';

Best Answer

Related Solutions

Mysql – Retrieving most recent X rows for each given user from a table

MySQL – Cannot Create Federated View in MariaDB

Related Question