I'm seeing some odd behaviour on MariaDB 10.1.22 that's got me scratching my head. My production servers (a master and two slaves) are returning incorrect query results, but when I run the same query on the same data elsewhere I always get the correct results, even on a VM with matching OS, H/W arch and MariaDB version.

The problem is triggered by using "ORDER BY x DESC LIMIT 1" in a dependent subquery on a single table. The query is being run on a small InnoDB table that very rarely changes. Each row contains a date, an integer identifier, a sequence number and a handful of applicable settings that are all integers.

Unfortunately I can't create a simplified reproducible test case, but the following example shows the sort of query I'm working with. Imagine a table that looks like this:

CREATE TABLE `settings` ( `date` date NOT NULL, `id` int(11) NOT NULL, `seq` int(11) NOT NULL, `value` int(11) NOT NULL, UNIQUE KEY `date_id_seq` (`date`,`id`,`seq`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The table is designed to store a sequence of values belonging to something identified by the ID. Each sequence of values has a date stamp, and can be replaced at a later date by a new sequence of values:

+------------+----+-----+-------+ | date | id | seq | value | +------------+----+-----+-------+ | 2011-11-01 | 1 | 0 | 6 | | 2011-11-01 | 1 | 1 | 3 | | 2011-11-01 | 1 | 2 | 9 | | 2011-11-01 | 2 | 0 | 6 | | 2011-11-01 | 2 | 1 | 1 | | 2011-11-01 | 3 | 0 | 6 | | 2011-11-01 | 3 | 1 | 8 | | 2012-04-01 | 1 | 0 | 4 | | 2012-04-01 | 1 | 1 | 3 | | 2013-06-01 | 3 | 0 | 5 | +------------+----+-----+-------+

The query is asking for all the IDs that had a particular first value set as of a particular date, in this case a first value of 6 as of '2012-07-01':

SELECT s1.id FROM settings s1 WHERE s1.seq = 0 and s1.value = 6 and s1.date = ( SELECT s2.date FROM settings s2 WHERE s2.id = s1.id and s2.date <= '2012-07-01' ORDER BY s2.date DESC LIMIT 1 );

This query correctly returns IDs 2 and 3, and not ID 1 since the data for '2012-04-01' overrides the data for '2011-11-01'. In my equivalent situation, my production servers are returning 1, 2 and 3. Everywhere else I try to run it I get just 2 and 3 as expected.

The table in question changes rarely, so it's trivial to show that the test system I've set up is working on the same data as the production systems. I've selected the entire table to out-files and diffed them, I've restored the test system from a mysqldump backup of production, and I've even run XtraBackup and prepared it with --export, then followed the instructions to import the table files into my test system just in case there's something weird in the production table's .ibd file. No matter what I do, I can't reproduce the problem anywhere but on my production systems.

Testing has shown that minor changes to the query will fix the result set. Changing it to use "max()" instead of "order/limit" fixes it. Removing the date test from the where clause also fixes it.

I don't want to just change all my queries like this to use MAX() and be done with it, because this weirdness will always be in the back of my mind. Is my query insufficiently specific? Have my queries of this type only ever returned the right results by accident until I moved to MariaDB recently?

I haven't tried adding an index to the date column yet, because it too might make the problem vanish and I'll be none the wiser. I'd rather work out how to make the problem reproducible then dig in further to expose the cause. I thought that my InnoDB file could have been corrupt, but preparing the XtraBackup didn't detect it. Perhaps I've triggered a bug in MariaDB that needs very special conditions to appear? That seems unlikely but I'm at a bit of a loss now.

Any suggestions anyone might have would be very gratefully received!

Kind regards,

Sam