[MCOL-4454] "ORDER BY BINARY a" is not like in InnoDB - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.4.3
Fix Version/s: 5.5.2
Component/s: PrimProc
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.

Attachments

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2020-12-16 13:52

Alexander Barkov made changes - 2020-12-16 13:53

Field	Original Value	New Value
Description	{code:sql} 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; {code} {noformat} +----------+ \| QUOTE(a) \| +----------+ \| 'A ' \| \| 'A' \| +----------+ {noformat} {code:sql} 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; {code} {noformat} +----------+ \| QUOTE(a) \| +----------+ \| 'A ' \| \| 'A' \| +----------+ {noformat}	{code:sql} 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; {code} {noformat} +----------+ \| QUOTE(a) \| +----------+ \| 'A ' \| \| 'A' \| +----------+ {noformat} {code:sql} 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; {code} {noformat} +----------+ \| QUOTE(a) \| +----------+ \| 'A' \| \| 'A ' \| +----------+ {noformat}

Alexander Barkov made changes - 2020-12-16 13:53

Description

{code:sql}
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;
{code}
{noformat}
+----------+
| QUOTE(a) |
+----------+
| 'A ' |
| 'A' |
+----------+
{noformat}

{code:sql}
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;
{code}
{noformat}
+----------+
| QUOTE(a) |
+----------+
| 'A' |
| 'A ' |
+----------+
{noformat}

Alexander Barkov added a comment - 2020-12-16 13:57

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 '     |

+----------+

Alexander Barkov added a comment - 2020-12-16 13:57 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 ' | +----------+

Alexander Barkov added a comment - 2020-12-16 14:19 - edited

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;

Alexander Barkov added a comment - 2020-12-16 14:19 - edited 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 ;

Todd Stoffel (Inactive) made changes - 2020-12-22 20:35

Rank

Ranked higher

Todd Stoffel (Inactive) made changes - 2020-12-22 20:36

Rank

Ranked higher

Todd Stoffel (Inactive) made changes - 2020-12-22 20:37

Rank

Ranked higher

Todd Stoffel (Inactive) made changes - 2020-12-22 20:37

Rank

Ranked higher

Todd Stoffel (Inactive) made changes - 2020-12-22 20:38

Fix Version/s		5.6.1 [ 25031 ]
Fix Version/s	5.5.1 [ 25030 ]

Alexander Barkov made changes - 2021-01-12 16:23

Assignee

Alexander Barkov [ bar ]

David Hall (Inactive) made changes - 2021-01-12 18:37

Sprint

2021-1 [ 480 ]

David Hall (Inactive) made changes - 2021-01-12 18:37

Rank

Ranked higher

Todd Stoffel (Inactive) made changes - 2021-01-12 18:53

Rank

Ranked higher

Alexander Barkov made changes - 2021-01-13 13:42

Component/s		PrimProc [ 13700 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

David Hall (Inactive) made changes - 2021-01-20 22:32

Fix Version/s

5.5.2 [ 25601 ]

Gregory Dorman (Inactive) made changes - 2021-01-22 17:22

Fix Version/s

5.6.1 [ 25031 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2020-12-16 13:52

Updated:: 2021-01-22 17:22

Resolved:: 2021-01-13 13:42

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB ColumnStore

Details

Description

Attachments

Activity

People

Dates

Git Integration