Query SQL Issues

Translated
1

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

Original topic: 查询sql问题，

| username: TiDBer_aZrsY0ca

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path]
The same SQL query returns the same data, but this does not happen in MySQL.

select * from dst_org where org_pid='OTM3NDA=' and ORG_ENABLE='1'
select * from dst_org where org_pid=0 and ORG_ENABLE='1'

image
image997×63 2.08 KB

image
image1115×65 2.39 KB

This is the table structure.
image
image767×149 2.88 KB

Different condition queries return the same result.
[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

2
| username: 小龙虾爱大龙虾 | Original post link

I don’t understand what it means. The same SQL query returns the same data, isn’t that expected? :grinning:

3
| username: forever | Original post link

It should be said that different SQL queries yield the same result. The above SQL passes a string for the decimal type.

4
| username: forever | Original post link

It’s the same in MySQL too.

5
| username: 洪七表哥 | Original post link

What is the org_pid field in the results that is also the same?

6
| username: zhaokede | Original post link

Is org_pid a numeric type? Will there be no error when querying with ‘OTM3NDA=’?

7
| username: zhaokede | Original post link

Or the table structure is not correctly copied.

8
| username: TIDB-Learner | Original post link

I don’t understand. Please post the table structure and test data, and give it a try.

9
| username: ziptoam | Original post link

It would be more appropriate to change the data type of org_pid.

10
| username: tidb菜鸟一只 | Original post link

Does converting a string to decimal not throw an error?

11
| username: TiDBer_HUfcQIJx | Original post link

Implicit conversion.

12
| username: 小龙虾爱大龙虾 | Original post link

Look at the warning message.

13
| username: forever | Original post link

When a string is compared to a number, the string is converted to a number. The conversion is based on the numeric prefix of the string. If the string does not have a numeric prefix, the conversion result is 0.

14
| username: forever | Original post link

Implicit conversion will occur.

15
| username: 霸王龙的日常 | Original post link

The org_pid is of decimal type, but you passed a string value, which was implicitly converted.

16
| username: TiDBer_rvITcue9 | Original post link

There might have been an implicit conversion.

17
| username: 小于同学 | Original post link

Implicit conversion

18
| username: TiDBer_aZrsY0ca | Original post link

The SQL conditions above are different.

19
| username: 郑旭东石家庄 | Original post link

It should be an automatic implicit conversion. You can check if there are any warning messages similar to this one:
image

20
| username: Kongdom | Original post link

:thinking: Is this also controlled by sql_mode? If it’s in strict mode, will it directly report an error?