Bug #100050 order by with set range results in wrong result

• Submitted: 30 Jun 2020 12:39
• Modified: 1 Jul 2020 6:56
• Reporter: Yushan ZHANG
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.30
• OS: Any
• CPU Architecture: Any
• Tags: order by, range

[30 Jun 2020 12:39] Yushan ZHANG

Description:
-- correct
mysql> select a1 from t1 order by (a in (0,1)) asc;
+------+
| a1   |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

-- incorrect
mysql> select a1 from t1 order by (a in (0,1)) desc;
+------+
| a1   |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

How to repeat:
drop table if exists t1;
create table t1 (a int, a1 varchar(10));
insert into t1 values (0,"a"),(0,"b"),(1,"c");
-- correct
select a1 from t1 order by (a in (0,1)) asc;
-- incorrect
select a1 from t1 order by (a in (0,1)) desc;

[30 Jun 2020 13:23] Yushan ZHANG

I'm not sure about whether the `desc` applies to all columns, but here is a more obvious example:

-- correct
mysql> select a1 from t1 order by (a in (1,2)) asc;
+------+
| a1   |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

-- incorect
mysql> select a1 from t1 order by (a in (1,2)) desc;
+------+
| a1   |
+------+
| c    |
| a    |
| b    |
+------+
3 rows in set (0.00 sec)

[30 Jun 2020 13:38] MySQL Verification Team

Hi Mr. ZHANG,

Thank you for your bug report.

However, this is not a bug.

When your sorting criteria is defined as they are, then you are NOT sorting by the column in that expression, but by the BOOLEAN result of that expression.

Not a bug.

[1 Jul 2020 5:18] Yushan ZHANG

mysql> select a in (1,2) from t1;
+------------+
| a in (1,2) |
+------------+
|          0 |
|          0 |
|          1 |
+------------+
3 rows in set (0.00 sec)

I suppose I could transform the original query with this expression '001' or b'001'.

Then I have:

mysql> select a1 from t1 order by 001 desc;
+------+
| a1   |
+------+
| c    |
| b    |
| a    |
+------+
3 rows in set (0.00 sec)

Another possibility:

mysql> select a1 from t1 order by binary 001 desc;
+------+
| a1   |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

The result for the original query is still wrong.

[1 Jul 2020 5:19] Yushan ZHANG

And if it is considered as BOOLEAN,

mysql> select a1 from t1 order by TRUE desc;
+------+
| a1   |
+------+
| c    |
| b    |
| a    |
+------+
3 rows in set (0.00 sec)

[1 Jul 2020 6:56] Yushan ZHANG

I understand the process now, the order is decided by the Boolean expression of the column.

Agree with it is not a bug. Thank you!

[1 Jul 2020 12:17] MySQL Verification Team

You are truly welcome.