[MCOL-4454] "ORDER BY BINARY a" is not like in InnoDB Created: 2020-12-16  Updated: 2021-01-22  Resolved: 2021-01-13

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 5.4.3
Fix Version/s: 5.5.2

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Sprint: 2021-1

 Description    

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(32) CHARACTER SET latin1) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES ('A'),('A ');
 
SELECT QUOTE(a) FROM t1 ORDER BY a, BINARY(a) DESC;


+----------+
 
| QUOTE(a) |
 
+----------+
 
| 'A '     |
 
| 'A'      |
 
+----------+


DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(32) CHARACTER SET latin1) ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES ('A'),('A ');
 
SELECT QUOTE(a) FROM t1 ORDER BY a, BINARY(a) DESC;


+----------+
 
| QUOTE(a) |
 
+----------+
 
| 'A'      |
 
| 'A '     |
 
+----------+

Notice, ColumnStore returns the two records in reverse order.

 Comments   
Comment by Alexander Barkov [ 2020-12-16 ]

The same problem is repeatable with HEX() instead of BINARY():

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(32) CHARACTER SET latin1) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES ('A'),('A ');
 
SELECT QUOTE(a) FROM t1 ORDER BY a, HEX(a) DESC;


+----------+
 
| QUOTE(a) |
 
+----------+
 
| 'A '     |
 
| 'A'      |
 
+----------+


DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(32) CHARACTER SET latin1) ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES ('A'),('A ');
 
SELECT QUOTE(a) FROM t1 ORDER BY a, HEX(a) DESC;


+----------+
 
| QUOTE(a) |
 
+----------+
 
| 'A'      |
 
| 'A '     |
 
+----------+

Comment by Alexander Barkov [ 2020-12-16 ]

Note, if I remove "col" from the ORDER BY list, it seems to work fine:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(32) CHARACTER SET latin1) ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES ('A'),('A ');
 
SELECT QUOTE(a) FROM t1 ORDER BY HEX(a);


+----------+
 
| QUOTE(a) |
 
+----------+
 
| 'A'      |
 
| 'A '     |
 
+----------+


SELECT QUOTE(a) FROM t1 ORDER BY HEX(a) DESC;


+----------+
 
| QUOTE(a) |
 
+----------+
 
| 'A '     |
 
| 'A'      |
 
+----------+

Without "col" in ORDER BY, it also works fine with BINARY(a):

SELECT QUOTE(a) FROM t1 ORDER BY  BINARY(a);
 
SELECT QUOTE(a) FROM t1 ORDER BY  BINARY(a) DESC;

Generated at Thu Feb 08 02:50:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.