Bug #104964 When setting sql_mode as 'PIPES_AS_CONCAT', order by lose effectiveness
Submitted: 17 Sep 2021 8:07 Modified: 17 Sep 2021 13:05
Reporter: yi qian Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.24 OS:Linux
Assigned to: CPU Architecture:x86
Tags: sql_mode,PIPES_AS_CONCAT,order by

[17 Sep 2021 8:07] yi qian
Description:
When setting sql_mode as 'PIPES_AS_CONCAT',the specified sorting of table fields does not take effect

How to repeat:
Preset data:
create table testdb.t1(tinyint_1 TINYINT  NULL, int_1  int not null primary key);
create table testdb.t2(tinyint_1 TINYINT  NULL, int_1  int not null primary key);
insert into testdb.t1 values(5,20);
insert into testdb.t1 values(5,5500);
insert into testdb.t1 values(4,10000);
insert into testdb.t1 values(4,1000);
insert into testdb.t2 values(5,20);
insert into testdb.t2 values(5,5500);
insert into testdb.t2 values(4,10000);
insert into testdb.t2 values(4,1000);

when sql_mode="STRICT_TRANS_TABLES",Execute the following SQL:

select distinct a1.tinyint_1,case a1.int_1 when 20 then 52 || 40  when 52 or 52 then 55 when 100 then 200 when 1000 then 2000 when 5200 then 5500 when 10000 then 20000 else 10 end as tt_1 ,a2.tinyint_1 from testdb.t1 as a1 join testdb.t2 as a2 on a1.tinyint_1=a2.tinyint_1 where a1.int_1 not in (1,2,3,4,5) group by tt_1 order by tt_1 desc;

the result is:
+-----------+-------+-----------+
| tinyint_1 | tt_1  | tinyint_1 |
+-----------+-------+-----------+
|         4 | 20000 |         4 |
|         4 |  2000 |         4 |
|         5 |    10 |         5 |
|         5 |     1 |         5 |
+-----------+-------+-----------+

When setting sql_mode="PIPES_AS_CONCAT",execute the same sql, the result is:
+-----------+-------+-----------+
| tinyint_1 | tt_1  | tinyint_1 |
+-----------+-------+-----------+
|         5 | 5240  |         5 |
|         4 | 20000 |         4 |
|         4 | 2000  |         4 |
|         5 | 10    |         5 |
+-----------+-------+-----------+

as we can see, when sql_mode is 'PIPES_AS_CONCAT',the function of 'order_by' is lose effectiveness.
[17 Sep 2021 13:05] MySQL Verification Team
Hi Mr. qian,

Thank you for your bug report.

However, this is not a bug.

When PIPES_AS_CONCAT are enabled in the sql_mode, then all expressions of the kind :

x || y

become the identical to:

concat(x,y);

When you use integer or integer constants as the operands, then MySQL is forced to transform them first into the strings, since CONCAT() can be applied to strings only.

In that case, you get ORDER by string values and not by integer values. Hence, what you are doing is totally expected behaviour, as described in our Reference Manual.

Not a bug.