[MDEV-30038] Memory engine table giving inconsistent results Created: 2022-11-18  Updated: 2022-11-25  Resolved: 2022-11-25

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Memory
Affects Version/s: 10.6.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: clint chenery Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

MariaDB 10.6.7-MariaDB-2ubuntu1.1-log - Ubuntu 22.04


 Description   

Using MariaDB 10.6.7-MariaDB-2ubuntu1.1-log - Ubuntu 22.04

I have a relatively complex application that is making use of temporary memory (engine) tables. The tables are generated from select insert statements and then the data is manipulated. After which a series of selects are performed with one or more rejoins to the same table.

The tables have 8 indexes created with the table create statement some are hash and some are btree.

Doing exactly the same process using exactly the same data I am getting slightly different results (in terms of data return and the number of rows).

It's taken me a while to get to the root of this, I have figured out that the temp memory tables are identical in terms of data and the database calls are the same.

I have let the tables be created as permanent memory tables so I can see them in myphpadmin, rename them, and let them be created again. Then run the same query against each table, and I get different results. The table checksums are identical, row count is identical. but the same query gives different results when tables as memory engine. Convert them both to INNODB and both give the same result... is the memory engine broken?

 Comments   
Comment by Daniel Black [ 2022-11-19 ]

Please include the SHOW CREATE TABLE tblname for the tables, the query, and EXPLAIN FORMAT=JSON query.

Additional table data may be required. If not possible to attach here there is a ftp server that can be used.

Comment by clint chenery [ 2022-11-20 ]

hi,

I've done a ton of work troubleshooting this, and I have found what is going on and should be pretty simple to reproduce.

looking at the below example of a query.

SELECT * FROM `tmp_lookup` T2
 
   WHERE
 
      T2.`from_location` = 'location1'
 
      T2.`to_location` = 'location2'
 
      T2.`depart_time` > '1970-01-01 22:14:00'
 
      ORDER BY T2.`depart_time` ASC
 
      LIMIT 1;

if there happen to be multiple rows in the memory table that match the query, with the same smallest value of `depart_time`, Then the single limit by row is randomly selected from the matching smallest rows and returned.

the row returned is consistent all the time the same table instance is used.

If you build a memory table and run the query multiple times, it will consistently pick the same first row, delete the table, remake the table, rerun the query multiple times, it will consistently pick the same first row, (but not the same first row when using the first table) even though the same query is used to build each table. Even though there is a primary auto increment key and even though the table checksums match across table builds.

This may be by design, but it's very confusing.

Comment by Sergei Golubchik [ 2022-11-25 ]

First, this is definitely not a bug, in the sense that SQL standard does not specify what row will be selected, and if both results are correct then either one (unspecified) can be returned.

Generally I'd expect it to depend on the history, both Aria and MyISAM can also exhibit this behavior, depending on how you mix insert/delete/update statements when reaching the final — identical — content.

You would want to modify your query to have a stable result, for example, you can add your primary auto increment key as a second column in the ORDER BY clause.

Generated at Thu Feb 08 10:13:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.