Example of sort operation with different collation settings in MariaDB

Check the table collation (database is created as default collation & character set).

select table_collation,table_name from INFORMATION_SCHEMA.tables where table_name = 'testsort';
table_collation  |table_name|
-----------------+----------+
latin1_swedish_ci|testsort  |

--Check for the database level:
SELECT schema_name,Default_Character_Set_name,default_collation_name FROM INFORMATION_SCHEMA.SCHEMATA; 
schema_name       |Default_Character_Set_name|default_collation_name|
------------------+--------------------------+----------------------+
information_schema|utf8mb3                   |utf8mb3_general_ci    |
classicmodels     |latin1                    |latin1_swedish_ci     |

Create a table and insert data as small and upper case mixed in the data as follows to show how sorting is performing in MariaDB:

create table testsort (name varchar(100))

insert into testsort values ('A');
insert into testsort values ('b');
insert into testsort values ('B');
insert into testsort values ('b');
insert into testsort values ('C');

select * from testsort order by name ;

name|
----+
A   |
b   |
B   |
b   |
C   |

As output, you find both ‘B’ = ‘b’ in the default collation. If we change the collation setting to give sort as:

Keeping the Character set is same and changing only collate setting for the table as:

ALTER TABLE testsort  CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

Now test the order by statement again:

select * from testsort order by name;

name|
----+
A   |
B   |
C   |
b   |
b   |

Note: In this case, the upper case is not equal to the small case.