TiDB 5.4 Self-Join Query Returns Incorrect Number of Rows

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

Original topic: tidb5.4 自连接查询,返回条数错误

| username: qinxiaojian1028

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Problem Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]



The first image is a TiDB query with incorrect data volume returned, and the second image is a MySQL query with the correct data volume returned.

| username: RenlySir | Original post link

Please send the execution plans for MySQL and TiDB separately.

| username: TiDBer_vfJBUcxl | Original post link

Display all the columns involved in the query result SQL, especially the columns involved in the judgment and addition. This will help in troubleshooting.

| username: cassblanca | Original post link

Listing the fields used in the query plan and filter conditions can help pinpoint the issue more effectively. It is suspected that the problem might be related to data types.

| username: TiDB_C罗 | Original post link

Check the execution plan.
Try rewriting it with WITH.

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

First, check if the data filtered by conditions from table y in TiDB and MySQL are the same.

| username: redgame | Original post link

The query results are incorrect, are the tables the same?

| username: qinxiaojian1028 | Original post link

This is the execution plan for TiDB,
This is the execution plan for MySQL

| username: qinxiaojian1028 | Original post link

val4 is of string type, y.id is of bigint type.

| username: qinxiaojian1028 | Original post link

The data is the same, both are 55 entries.

This is TiDB’s.
This is MySQL’s.

| username: RenlySir | Original post link

In this case, I suspect it might be due to implicit type conversion. How about actually executing it in TiDB and checking? Explain analyze select …;

| username: qinxiaojian1028 | Original post link

It should be a conversion error. When I convert the number to a string, only one result is returned.

| username: RenlySir | Original post link

Okay, got it.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.