Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: SQL兼容性问题
[TiDB Usage Environment] Production Environment
[TiDB Version] 5.7.25-TiDB-v5.1.2 \ 5.7.25-TiDB-v4.0.16
[Encountered Problem: Phenomenon and Impact] The same SQL executes normally on v4.0.16 but reports a syntax error on v5.1.2
SELECT * from (
(SELECT vin, veh_series_no, veh_variable_model_no, current_version, vehicle_purpose, vehicle_status, current_vehicle_version_status
FROM xx_version
WHERE veh_series_no='xx'
and current_version in ('4.2.1-1.7.114')
and is_delete = 0
and current_vehicle_version_status in ("1")
) as a
JOIN
(SELECT row_number() over (PARTITION by vin order by event_time desc) as number, vin, event_type, target_version, event_time, error_type, error_reason, message_id
FROM xx_upgrade_status
WHERE current_version in ('4.2.1-1.7.114')
and is_delete = 0
) as b
ON a.vin = b.vin and b.number = 1
);
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]
It seems to be related to window functions. There should be relevant keywords in SQL. Try debugging and see.
Compare the execution plans and you’ll see that the differences between 4.X and 5.X are indeed significant.
Just remove the outermost parentheses, it’s still a compatibility issue with different versions. Can you help explain the principle? Why is the 5.x version not supported?
If you add an alias after the outermost parentheses, will it execute successfully?
I checked the official documentation:
Oracle and TiDB Function and Syntax Differences | PingCAP Documentation Center
This part shows that starting from version 5.4, TiDB requires every derived table to have an alias.
Try running this query to see if it still reports an error?
Oh, really? You can do it without aliases? We came from SQL Server and have always used aliases.
Try adding “as t” without removing the outermost parentheses.
Yes, it is possible to remove the outermost parentheses.
This is also fine. Shouldn’t SQL syntax in higher versions be backward compatible? The business encounters problems once the cluster is upgraded. 
It should have been added as “as t”. I guess the lack of error in the lower version was unexpected. You can try MySQL.
You can check the sqlmod of the two databases to see if the SQL format requirements for the lower version database are lower.
It is still necessary to follow development standards.
|
username: Hacker_DaUeI5uX
|
username: 
|
username: 
|
username: 
|
username: 
|
username: