Unexpected sorting behavior after update from MariaDB 10.1 to 10.3

TL;DR The sorting behavior changed from MariaDB 10.1 to 10.2 due to a bug in MariaDB 10.1 

After updating from Ubuntu 18.04 LTS to 20.4 LTS a previously working a PHP application which contains a data export suddenly did not return the expected result any more. I debugged this scenario by comparing the database query results in the data export and obviously, something in the sorting changed from MariaDB 10.1 to MariaDB 10.3

In order to fully reproduce the problem, I created a really simple use case as shown in the SQL dump below.

CREATE TABLE `test` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(255) NOT NULL,
  `c` text NOT NULL,
  `d` varchar(255) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES (1,'A','\r\n','CRLF'),(2,'A','',''),(3,'A','',''),(4,'A','\r\n','CRLF'),(5,'A','','');

So we have a really simple table structure with some data. The only special thing is, that 2 values in column "c" contain a carriage return and line feed (CRLF). Since this is not printed when selecting data, I also added column d which contains the value "CRLF" for rows, where column c is a CRLF.

So now I select some data. 

SELECT * FROM test;


+---+---+----+------+
| a | b | c  | d    |
+---+---+----+------+
| 1 | A |    | CRLF |
| 2 | A |    |      |
| 3 | A |    |      |
| 4 | A |    | CRLF |
| 5 | A |    |      |
+---+---+----+------+

This result is as I would expect it. Now sorting comes into the game...

Ubuntu 18.04 with MariaDB 10.1.47

SELECT * FROM test ORDER BY b ASC, c ASC, a ASC;


+---+---+----+------+
| a | b | c  | d    |
+---+---+----+------+
| 2 | A |    |      |
| 3 | A |    |      |
| 5 | A |    |      |
| 1 | A |    | CRLF |
| 4 | A |    | CRLF |
+---+---+----+------+

OK, so the sorting of column c puts the CRLF values at the end for MariaDB 10.1. Now I try the same on another system.

Ubuntu 20.04 with MariaDB 10.3.25

SELECT * FROM test ORDER BY b ASC, c ASC, a ASC;


+---+---+----+------+
| a | b | c  | d    |
+---+---+----+------+
| 1 | A |    | CRLF |
| 4 | A |    | CRLF |
| 2 | A |    |      |
| 3 | A |    |      |
| 5 | A |    |      |
+---+---+----+------+

As you notice, the sorting for column c is now reversed...

I did not find a setting in MariaDB 10.3 to switch back to the sorting as it was in MariaDB 10.1. I could also reproduce the same behavior on MySQL 8.0. So... bug or feature - who knows? I think the described scenario can be considered as an edge case, but if you somehow depend on, that sorting for a column with CRLF values is exactly the same, this can hit you really hard.

I created an issue in the MariaDB bug tracker. I'm curious if this is supposed behavior or not.

Update 23.11.2020: It has been confirmed, that the sorting behavior is as expected in MariaDB 10.2+ and that it was wrong in 10.1

How to extend existing FlexForm select options of a TYPO3 plugin using Page TSconfig

Sometimes existing options of a TYPO3 plugin may not fully suite the project requirements. As an example, I refer to my TYPO3 extension "Event Management and Registration" (sf_event_mgt). The extension allows to select the ordering of records by a specific field in the FlexForm plugin options as shown on the screenshot below.

The 3 options shown are configured in the Flexform options for the field "settings.orderField".

In a project it was required to order by a custom field which was not part of the main extension. So I added the custom field named "slot" to the extension using an extending extension for sf_event_mgt.

In order to allow the new field as sorting field, the field "slot" needs to be added to the allowed ordering fields using TypoScript (note, this step is only specific to the extension sf_event_mgt).

plugin.tx_sfeventmgt {
  settings {
    orderFieldAllowed = uid,title,startdate,enddate,slot
  }
}

Finally the FlexForm of the plugin needs to be extended, so the new field appears in the "Sort by" select field. In order to do so, the following Page TSconfig has been added:

TCEFORM.tt_content.pi_flexform.sfeventmgt_pievent.sDEF.settings\.orderField {
  addItems.slot = slot
  altLabels.slot = Slot
}

You might notice the backslash before the dot in "settings\.orderField". This is required to escape the dot of the fieldname "settings.orderField", since Page TSconfig also uses dots to separate between configuration options/levels.

After adding the Page TSconfig, the plugin now shows the new field.

Pretty cool and not a single line of PHP code required :-) 

Reference: TYPO3 TCEFORM