Querying Two Tables with One-to-Many Relationship Results in Multiple Duplicate Records

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

Original topic: 一对多的两表联查出多条重复数据

| username: shuyu_zhihui

【TiDB Usage Environment】Production Environment / Testing / Poc
【TiDB Version】5.7.25-TiDB-v6.1.5
【Reproduction Path】Issue with joining two tables
【Encountered Problem: Phenomenon and Impact】The result on MySQL is 3 rows, while the result on TiDB is 42 rows
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】
TiDB query:
The SQL is as follows:

    t_new_inspect_item_config t1
    LEFT JOIN t_new_inspect_config t2 ON t1.inspect_id = t2.id 
    AND t1.STATUS = 1 
    AND t1.is_deleted = 0 
    AND t2.STATUS = 1 
    AND t2.is_deleted = 0 
    t1.tenant_id = '730000' 
    AND t1.inspect_id = '1372816766597782118';
SELECT * FROM t_new_inspect_config t WHERE t.tenant_id = '730000' AND t.id = '1372816766597782118';
SELECT * FROM t_new_inspect_item_config WHERE tenant_id = '730000' AND inspect_id = '1372816766597782118';

Query results are as follows:

The same SQL on MySQL:

| username: 像风一样的男子 | Original post link


| username: zhanggame1 | Original post link

If the associated field is a string, check whether case is ignored, the result will be different.

| username: dbaspace | Original post link

Is there a type usage error with the join field, using varchar as int?

| username: 舞动梦灵 | Original post link

You only have one sentence here, how are we supposed to analyze it? :joy: At least post an SQL statement or a screenshot of the query results.

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

When you run the SQL, it’s best to also post the table structure and the data that caused the error.

| username: Hacker007 | Original post link

Post the SQL and the data from both tables to take a look. In theory, TiDB should be stricter than MySQL by default. TiDB has higher requirements for case sensitivity and leading/trailing spaces.

| username: shuyu_zhihui | Original post link

It’s possible, let me confirm.

| username: shuyu_zhihui | Original post link

The process of editing the question was interrupted.

| username: shuyu_zhihui | Original post link

If strict, it will report an error or not produce any result, which should be due to an unreasonable type conversion mechanism.

| username: shuyu_zhihui | Original post link

Indeed, this is the issue. We need to convert the value.

 t2.tag_status, t1.*
 t_new_inspect_item_config t1
 LEFT JOIN t_new_inspect_config t2 ON t1.inspect_id = CONCAT('', t2.id)

Converting to string type will be fine.

| username: Kongdom | Original post link

  • If your issue has been resolved:

  • If you resolved it yourself, please include your solution and mark your solution as [Useful to Me].

  • If someone else helped you solve the problem, please select the [Most Valuable] reply and mark it as [Useful to Me]. This is a way to reward and appreciate the person who helped you.

    • Only questions marked as [Useful to Me] can be searched, which helps others find answers more efficiently. Marking [Useful to Me] also earns you 5 points and 5 experience points.
  • If your issue is not yet resolved, please continue to ask questions and provide feedback on the problems you are encountering.

| username: shuyu_zhihui | Original post link

How do I mark it as useful? I couldn’t find such an option.

| username: Kongdom | Original post link

Click here for the reply that is considered the solution :handshake: :handshake: :handshake:

| username: system | Original post link

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