Strange MariaDB behavior with Sequential GUIDs

[dylim]

Hi Guys,

I use sequential GUIDs for both my Invoice header and detail tables. Ever since I moved to MariaDb, it was only now that I noticed that if I do a select of the invoice detail rows of a particular header row, MariaDB shows the rows in REVERSE order! I have to explicitly add a "ORDER BY ID" to show the real order.

Attached are screenshots to show you guys. Is this how MariaDb really behaves? Thanks in advance.

 

[mJindrova]

If you do not specify ORDER BY, then "order" in result is randomly - VFP, MySQL, MariaDB, ORACLE, PostgreSQL, Firebird, etc - it's a feature.

 

[mjcmkrsr]

Hi,

VFP SQL behaves the same way - you have to specify ORDER BY if you want the data to show in a certain order - otherwise they will show in "natural" order

hth

MarK

 

[Chriss Miller]

If you query without order by, then by ANSI definition the order is undefined. Most databases still give the same reproducible order. VFP will simply give records in physical order. But not all storage of data is as simple as VFPs DBFs are with record after record in a physical order in the DBF file. Other database storage engines use trees to store data and trees have no single specific order as they branch - they are not a list, records only become a list in the form of a query result, but are not stored that way.

I doubt you always get the inverse order, but lets assume that's how MariaDB reads records whereas MySQL reads them in id order. Well, then that's that, you know how you can address that, add the ORDER BY id.

One culprit could also be how you moved data from MySQL over to MariaDB.

 

[dylim]

If you do not specify ORDER BY, then "order" in result is randomly - VFP, MySQL, MariaDB, ORACLE, PostgreSQL, Firebird, etc - it's a feature.

Hi,

If you execute the same using MySQL, it orders it sequentially with out adding an "ORDER BY id".

Strangely though, if you:

select * from SODTL

without any WHERE clause, it will arrange it "correctly" - sequentially.

Only when you place a WHERE clause will it show the rows in reverse order.

 

[dylim]

If you query without order by, then by ANSI definition the order is undefeined. Most databases still give the same reproducible order. VFP simply in physical order. But not all storage of data is as simple as VFPs DBFs are with record after record in a physical order in the DBF file. Other database storage engines use trees to store data and trees have no single specific order as they branch - tehy are not a list.

I doubt you always get the inverse order, but lets assume that's how MariaDB reads records whereas MySQL reads them in id order. Well, then that's that, you know how you can address that, add the ORDER BY id.

Kindly check my reply to mJindrova.

I have been using MySQL and it always shows the records sequentially - by the sequential GUID - without the need to put "ORDER BY id". It is only when I migrated to MariaDB recently that I noticed this behavior. If this really is the default behavior, then no worries then. It is a matter of placing an order clause.

I simply shared that MySQL and MariaDb has this slight difference.

 

[mJindrova]

If tables are MyISAM on MySQL, then SELECT - SQL can same behavior as SELECT - SQL in VFP - may be return sequentially result.<br>
But axiom is: SELECT - SQL without ORDER BY return randomly result.

 

[Chriss Miller]

By the way: The main feature of Sequential GUIDs is not to sort data in that sequence, too. You should in general not want to sort by physical or ID order, also when using numeric IDs which are generated in a sequence/autoincrement.

If you want items of invoices (or orderitems or ingredients of recipes - whatever) in a specific order you should simply introduce an itemorder column and then sort by that in secondary order, explicitly. Mainly order by foreign key, i.e. the invoice id, order id, recipe id, of course, but secondary order determines the order of items within the same invoice, order, recipe, etc. That's the recommended way to deal with subordering.

 

[dylim]

If tables are MyISAM on MySQL, then SELECT - SQL can same behavior as SELECT - SQL in VFP - may be return sequentially result.<br>
But axiom is: SELECT - SQL without ORDER BY return randomly result.

This is really strange. Ever since I have used MySQL, I have never used "ORDER BY id" to make sure they are in the 'natural' sequential order.

No worries though. I will explicitly put "ORDER BY id" from hereon out since I am now with MariaDB.

Thanks.

 

[dylim]

By the way: The main feature of Sequential GUIDs is not to sort data in that sequence, too. You should in general not want to sort by physical or ID order, also when using numeric IDs which are generated in a sequence/autoincrement.

If you want items of invoices (or orders or recipes, whatever) in a specific order you should simply introduce an itemoder column and then sort by that, explicitly. That's the recommended way to deal with subordering.

The reason I used sequential GUIDs is just to make sure they are ordered in the "natural order" of entry of the invoice details (child) rows.

This has worked for me ever since I used MySQL, as a "workaround" to our VFP table record number.

 

[mJindrova]

Dylim:
What is database engine used for your tables in MariaDB?
Is primary key on lines table clustered or not?

 

[dylim]

Dylim:
What is database engine used for your tables in MariaDB?
Is primary key on lines table clustered or not?

I use InnoDb, not MyISAM, because I need transactions. And yes, the ID field is the primary key.

 

[mJindrova]

InnoDB+Primary key - is clustered in default mode. Rows in lines table are organized by "id" field.
If you do not specify ORDER BY in SELECT - SQL, then MariaDB may return result set organized by "id" field because it's fast.

MySQL :: MySQL 8.4 Reference Manual :: 17.6.2.1 Clustered and Secondary Indexes

dev.mysql.com

 

[Chriss Miller]

Even if the db would sort by id by default, you may not have the right order simply ordering by id:

Imagine a simple case: You store an invoice, you have an item that is added later, after a few further invoices are stored, then you'd have a later id not in sequence. It would still be the highest id and sort correctly. But simply ordering items by id then means you can have items of other invoices interleaved, you'd need to order by foreignkey,primarykey anyway, main and sub order. The problem isn't important whenever you only query items of one invoice, but when you query several invoices you have to group them by main invoice id first, anyway.

 

[dylim]

InnoDB+Primary key - is clustered in default mode. Rows in lines table are organized by "id" field.
If you do not specify ORDER BY in SELECT - SQL, then MariaDB may return result set organized by "id" field because it's fast.

MySQL :: MySQL 8.4 Reference Manual :: 17.6.2.1 Clustered and Secondary Indexes

dev.mysql.com

Well, for MariaDB, from hereon out, I will use "ORDER BY id" then.

Again, kinda weird, coz MySQL has always shown the resulting rows already arranged.

Thanks!

 

[dylim]

Even if the db would sort by id by default, you may not have the right order simply ordering by id:

Imagine a simple case: You store an invoice, you have an item that is added later, after a few further invoices are stored, then you'd have a later id not in sequence. It would stil be the highest id and order correctly, but simply ordering items by id then means you can have items of other invoices interleaved, you'd need to order by foreignkey,primarykey anyway, main and sub order. The problem isn't important whenever you only query items of one invoice, but when you query several invoices you have to group them by main invoice id first, anyway.

Well, for MariaDB, from hereon out, I will use "ORDER BY id" then.

Again, kinda weird, coz MySQL has always shown the resulting rows already arranged.

Thanks!

 

[mJindrova]

IMHO:
MySQL (MyISAM?/InnoDB?) use unique key for readding data and sorting (by hdrid+id)
MariaDB (InnoDB) use unique key for readding data and primary key for sorting data.

That's all.
Joke is so you don't know what (key, index, mechanism) MySQL/MariaDB use for reading and sorting - EXPLAIN is your friend.

 

[Chriss Miller]

I think overall any VFP developer is just spoiled by how simple the DBF file format organizes data in rows which have a recno() by file position. In storage engines organizing data by a clustered index and further index types not known to VFP and organizing data in heaps, trees, pages, the order of records depends on how you traverse such data structures.

But SQL only guarantees a specific order if you ask for it using ORDER BY, that's all to know in general.

You can dig into how different database engines handle this.

Using sequential IDs, no matter if integer or sequential guids is not meaning data is automatically stored in that order, too. If the guid column is the major field organizing a clustered index, which determines the order in which records are stored, that's still not as simple a list as in a DBF, when you have a data organized in pages and pages are the leaf nodex of an index tree structure you still have a different organization of the records as in a simple DBF file. When a page becomes full in a page oriented storage engine, the page is split and half the records move into a new page, you end up with two half full pages and the new record in one of them. Sequential GUIDs help to minimize page splits, indeed, but they don't mean records are organized in a sequential list, you still get fragmentation of records.

The organization of records in pages is very common in other database storage engines and reson for not getting the order as you expect it, even different reading in traversing these more complex data structured can lead to different result orders even if you would have been able to use the MySQL data files with a MariaDB server as a so called drop in replacement. Where just the new server maintains the already exisitng data files. Possible through the compatibility of MariaDB. It still can use other strategies to read the same files.

I second mJindrova, EXPLAIN might tell a bit about that, though I'd not bet explain would give information about how it orders a query result for a query without ORDER BY. mJindrova has a good thought, the databse server developers do a good job if they then don't intentioanlly randomize the result but simply use the fastest way to retrieve the data, which can be in id order, but may not be. It simply depends on what makes it simplest to traverse all records given any other conditions like the where clause and indexes used to optimize that.