TiDB reports "ON condition doesn't support subqueries yet", poor compatibility with MySQL

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb报ON condition doesn’t support subqueries yet,对mysql兼容性差

| username: czxin788

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] v7.3
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]
mysql> SELECT *
→ from test.pm_Detail Detail
→ LEFT JOIN test.biz_product bp ON Detail.ProductID=bp.ProductID AND bp.EnterpriseID IN (
→ SELECT enterpriseid FROM test.biz_enterprise WHERE PID = ‘12345’
→ )
→ limit 1;
ERROR 1105 (HY000): ON condition doesn’t support subqueries yet

The above SQL is not supported in TiDB.
It needs to be rewritten as follows for TiDB to run normally:
mysql> SELECT *
→ from test.pm_Detail Detail
→ LEFT JOIN test.biz_product bp ON Detail.ProductID=bp.ProductID
→ where
→ bp.EnterpriseID IN (
→ SELECT enterpriseid FROM test.biz_enterprise WHERE PID = ‘12345’
→ )
→ limit 1;
Data was output normally
1 row in set (0.05 sec)

This makes it very difficult to switch to TiDB for systems that have been running for many years. A lot of SQL needs to be modified.

This is just one SQL incompatibility I encountered, and I don’t know how many similar SQL compatibility issues there are.

| username: Billmay表妹 | Original post link

Which version of MySQL are you using?

| username: czxin788 | Original post link

I am using MySQL 5.7.34.

| username: h5n1 | Original post link

Not supported yet

| username: Billmay表妹 | Original post link

Comparison with MySQL Compatibility

You can take a look at this~
TiDB is highly compatible with the MySQL protocol, as well as the commonly used features and syntax of MySQL 5.7 and MySQL 8.0.
You can also check out some unsupported features.

| username: czxin788 | Original post link

It seems that this issue was raised in 2019, but it is still not supported.
It looks like TiDB is suitable for newly developed projects. Existing projects cannot be directly migrated over yet.

| username: czxin788 | Original post link

Okay, thank you.
The unsupported select syntax, we are not planning to change it either, it seems that using TiDB is not the best choice for us at the moment.

| username: Billmay表妹 | Original post link

There is a related issue recently that you can take a look at.

| username: Billmay表妹 | Original post link

Manually modify it or wait for support. You can choose either.

| username: czxin788 | Original post link

Alright, it seems that’s the only way. Thank you for your reply.

| username: ShawnYan | Original post link

Another related problem with this enhancement #46376
Query result is not compatible with MySQL while using if clause #46376

| username: czxin788 | Original post link

Okay, thank you. It seems that TiDB does not support subqueries very well.