[MDEV-23975] SELECT Query Does NOT Return Syntax Error Created: 2020-10-15  Updated: 2021-04-19  Resolved: 2020-10-16

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Maria M Pflaum Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

The following query does not return a syntax error, but it seems like it should:

select * from mytest ORDER BY score DESC, individual_id AND service_id=1;

REPO:

create table mytest (
 
    individual_id       int(11),
 
    score               int(11),
 
    service_id          int(11)
 
);
 
 
 
insert into mytest
 
            values (1, 10, 5), (2, 11, 5), (3, 12, 2), (4, 20, 2),
 
                   (5, 21, 1), (6, 22, 1), (7, 30, 1), (8, 31, 4),
 
                   (9, 32, 6);
 
 
 
select * from mytest
 
ORDER BY score DESC, individual_id AND service_id=1;


+---------------+-------+------------+
 
| individual_id | score | service_id |
 
+---------------+-------+------------+
 
|             9 |    32 |          6 |
 
|             8 |    31 |          4 |
 
|             7 |    30 |          1 |
 
|             6 |    22 |          1 |
 
|             5 |    21 |          1 |
 
|             4 |    20 |          2 |
 
|             3 |    12 |          2 |
 
|             2 |    11 |          5 |
 
|             1 |    10 |          5 |
 
+---------------+-------+------------+



 Comments   
Comment by Sergei Golubchik [ 2020-10-16 ]

Why would that be a bug? You sort by two values.
First by score, then by the result of individual_id AND service_id=1 which is a valid boolean expression

Generated at Thu Feb 08 09:26:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.