Query SQL Issues

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'



This is the table structure.

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]

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

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

| 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.

| username: forever | Original post link

It’s the same in MySQL too.

| username: 洪七表哥 | Original post link

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

| username: zhaokede | Original post link

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

| username: zhaokede | Original post link

Or the table structure is not correctly copied.

| username: TIDB-Learner | Original post link

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

| username: ziptoam | Original post link

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

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

Does converting a string to decimal not throw an error?

| username: TiDBer_HUfcQIJx | Original post link

Implicit conversion.

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

Look at the warning message.

| 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.

| username: forever | Original post link

Implicit conversion will occur.

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

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

| username: TiDBer_rvITcue9 | Original post link

There might have been an implicit conversion.

| username: 小于同学 | Original post link

Implicit conversion

| username: TiDBer_aZrsY0ca | Original post link

The SQL conditions above are different.

| 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

| 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?