After upgrading TiDB from 5.4 to 6.5, the application reported an error. It was later discovered that the exists in the following SQL returns a boolean type, whereas in 5.4 it returned 0 and 1 (int)

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

Original topic: tidb 5.4升级到6.5后,应用报错,后来发现如下sql的exists返回的是布尔型,本来在5.4返回的是0和1 (int)

| username: MartinTsang

[Test Environment] TiDB
[TiDB Version] 6.5.0

@Query(value = “SELECT exists(SELECT * from t_paper_question_operation_log where target_id = ?1)”, nativeQuery = true)
int existsTargetId(String targetId);

The above business code was originally fine in version 5.4.2, but after upgrading TiDB to 6.5.0, the same code throws the following error:

Preliminary judgment is that in 6.5.0, exists returns a boolean type, but in the original 5.4.2, it returned an integer type of 1 and 0. If the application needs to adapt to 6.5.0, it requires extensive modifications. Is there any way to solve this?

I have searched through the official documentation but did not find any relevant information about exists.

| username: 我是咖啡哥 | Original post link

This is indeed difficult to handle. I wonder if there is any reference to control the optimizer version… It seems I couldn’t find one. If it doesn’t work, you might have to test with a lower version.

| username: BraveChen | Original post link

Add a CASE WHEN in SQL to forcibly convert it to boolean.

| username: MartinTsang | Original post link

Legacy issues require a lot of code changes~~ wondering if there’s any way to avoid it.

| username: BraveChen | Original post link

Perform upgrade rollback :grin:

| username: MartinTsang | Original post link

The upgrade is mainly to solve the issue of the global ID being discontinuous. :joy:

| username: BraveChen | Original post link

Auto-increment ID not continuous? Can 6 solve it?

| username: MartinTsang | Original post link

Yes, the official documentation states that this issue has been resolved starting from version 6.4.

| username: BraveChen | Original post link

Oh, but it’s best to do some testing in advance before upgrading. :joy: