SQL Execution: One Record in Data, Two Records in Query Result

Translated
1

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

Original topic: SQL运行，数据中有一条，查询出来是两条

| username: 湛兮tidb

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.1.0
[Reproduction Path] Two tables, one slightly larger table: etldr2125.tbl_adt_admits_discharges (about 4 million) and one smaller table etldr21252023_08_14.pid_table (1 million, only one column id).

  1. Running in the MySQL client:
select tbl_adt_admits_discharges.* 
from etldr2125.tbl_adt_admits_discharges 
inner join etldr21252023_08_14.pid_table 
on tbl_adt_admits_discharges.patient_id = etldr21252023_08_14.pid_table.patient_id 
where etldr2125.tbl_adt_admits_discharges.yidu_t1_removed = '0' 
or (etldr2125.tbl_adt_admits_discharges.yidu_t1_removed is null 
and etldr21252023_08_14.pid_table.patient_id='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

It runs for about 20 seconds and returns two identical results.
Execution plan:

MySQL [hn3yjcyj]> explain analyze select tbl_adt_admits_discharges.* 
from etldr2125.tbl_adt_admits_discharges 
join etldr21252023_08_14.pid_table 
on tbl_adt_admits_discharges.patient_id = etldr21252023_08_14.pid_table.patient_id 
where etldr2125.tbl_adt_admits_discharges.yidu_t1_removed = '0' 
or (etldr2125.tbl_adt_admits_discharges.yidu_t1_removed is null 
and etldr21252023_08_14.pid_table.patient_id='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

Execution plan details omitted for brevity.

  1. Removing the first or condition: etldr2125.tbl_adt_admits_discharges.yidu_t1_removed = '0'.
    Running in the MySQL client:
select tbl_adt_admits_discharges.* 
from etldr2125.tbl_adt_admits_discharges 
inner join etldr21252023_08_14.pid_table 
on tbl_adt_admits_discharges.patient_id = etldr21252023_08_14.pid_table.patient_id 
where (etldr2125.tbl_adt_admits_discharges.yidu_t1_removed is null 
and etldr21252023_08_14.pid_table.patient_id='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

Only one result, and it returns instantly.
Execution plan:

+---------------------------------+---------+---------+-----------+---------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                              | estRows | actRows | task      | access object                                                                                                 | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                       | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | memory    | disk |
+---------------------------------+---------+---------+-----------+---------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| Projection_10                   | 1.43    | 1       | root      |                                                                                                               | time:4.08ms, loops:2, RU:1.009824, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                   | etldr2125.tbl_adt_admits_discharges.inpatient_visit_id, etldr2125.tbl_adt_admits_discharges.patient_id, etldr2125.tbl_adt_admits_discharges.dom_hospital_no, etldr2125.tbl_adt_admits_discharges.inpatient_no, etldr2125.tbl_adt_admits_discharges.inpatient_case_no, etldr2125.tbl_adt_admits_discharges.inpatient_visit_count, etldr2125.tbl_adt_admits_discharges.cdt_admit_department, etldr2125.tbl_adt_admits_discharges.cdt_admit_dept_speciality, etldr2125.tbl_adt_admits_discharges.rstd_admit_dept_name, etldr2125.tbl_adt_admits_discharges.rstd_admit_speciality, etldr2125.tbl_adt_admits_discharges.cdt_admit_ward, etldr2125.tbl_adt_admits_discharges.cdt_discharge_department, etldr2125.tbl_adt_admits_discharges.cdt_discharge_dept_speciality, etldr2125.tbl_adt_admits_discharges.rstd_discharge_dept_name, etldr2125.tbl_adt_admits_discharges.rstd_discharge_speciality, etldr2125.tbl_adt_admits_discharges.cdt_discharge_ward, etldr2125.tbl_adt_admits_discharges.admit_datetime, etldr2125.tbl_adt_admits_discharges.discharge_datetime, etldr2125.tbl_adt_admits_discharges.patient_age, etldr2125.tbl_adt_admits_discharges.cdt_fee_type, etldr2125.tbl_adt_admits_discharges.cdt_visit_type, etldr2125.tbl_adt_admits_discharges.cdt_attending_physician, etldr2125.tbl_adt_admits_discharges.cdt_attending_physician_id, etldr2125.tbl_adt_admits_discharges.primary_respon_doctor_name, etldr2125.tbl_adt_admits_discharges.primary_respon_doctor_id, etldr2125.tbl_adt_admits_discharges.cdt_adt_status, etldr2125.tbl_adt_admits_discharges.birth_date, etldr2125.tbl_adt_admits_discharges.admitted_time, etldr2125.tbl_adt_admits_discharges.yidu_mapping_source, etldr2125.tbl_adt_admits_discharges.yidu_adt_status, etldr2125.tbl_adt_admits_discharges.yidu_visit_type, etldr2125.tbl_adt_admits_discharges.yidu_data_credibility, etldr2125.tbl_adt_admits_discharges.admit_dept_code, etldr2125.tbl_adt_admits_discharges.rstd_admit_dept_code, etldr2125.tbl_adt_admits_discharges.admit_ward_code, etldr2125.tbl_adt_admits_discharges.discharge_dept_code, etldr2125.tbl_adt_admits_discharges.rstd_discharge_dept_code, etldr2125.tbl_adt_admits_discharges.discharge_ward_code, etldr2125.tbl_adt_admits_discharges.weight, etldr2125.tbl_adt_admits_discharges.special_flag, etldr2125.tbl_adt_admits_discharges.yidu_mapping_date, etldr2125.tbl_adt_admits_discharges.yidu_remarks, etldr2125.tbl_adt_admits_discharges.etldr_rowkey, etldr2125.tbl_adt_admits_discharges.yidu_t1_update_time, etldr2125.tbl_adt_admits_discharges.yidu_t1_removed, etldr21252023_08_14.pid_table.patient_id | 676.9 KB  | N/A  |
| └─IndexHashJoin_18              | 1.43    | 1       | root      |                                                                                                               | time:4.04ms, loops:2, inner:{total:2.97ms, concurrency:5, task:1, construct:25.9µs, fetch:2.92ms, build:24.6µs, join:13µs}                                                                                                                                                                                                                                                                                                                                           | inner join, inner:IndexLookUp_15, outer key:etldr21252023_08_14.pid_table.patient_id, inner key:etldr2125.tbl_adt_admits_discharges.patient_id, equal cond:eq(etldr21252023_08_14.pid_table.patient_id, etldr2125.tbl_adt_admits_discharges.patient_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | 714.5 KB  | N/A  |
|   ├─IndexReader_29(Build)       | 1.01    | 1       | root      |                                                                                                               | time:902.5µs, loops:3, cop_task: {num: 1, max: 1.03ms, proc_keys: 1, tot_proc: 132.3µs, tot_wait: 82µs, rpc_num: 1, rpc_time: 992µs, copr_cache_hit_ratio: 0.00, build_task_duration: 14µs, max_distsql_concurrency: 1}                                                                                                                                                                                                                                              | index:IndexRangeScan_28                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | 359 Bytes | N/A  |
|   │ └─IndexRangeScan_28         | 1.01    | 1       | cop[tikv] | table:pid_table, index:pid_table_pid(patient_id)                                                              | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 157, total_keys: 2, get_snapshot_time: 28.8µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 8}}}                                                                                                                                                                                                                                                             | range:["xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A       | N/A  |
|   └─IndexLookUp_15(Probe)       | 1.43    | 1       | root      |                                                                                                               | time:2.82ms, loops:2, index_task: {total_time: 1.21ms, fetch_handle: 1.2ms, build: 1.73µs, wait: 1.98µs}, table_task: {total_time: 1.42ms, num: 1, concurrency: 5}, next: {wait_index: 1.36ms, wait_table_lookup_build: 71.9µs, wait_table_lookup_resp: 1.34ms}                                                                                                                                                                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 71.2 KB   | N/A  |
|     ├─Selection_13(Build)       | 1.43    | 1       | cop[tikv] |                                                                                                               | time:1.19ms, loops:3, cop_task: {num: 1, max: 1.11ms, proc_keys: 1, tot_proc: 254.1µs, tot_wait: 111.8µs, rpc_num: 1, rpc_time: 1.09ms, copr_cache_hit_ratio: 0.00, build_task_duration: 43.3µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 111, total_keys: 2, get_snapshot_time: 46.1µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 9}}}                               | not(isnull(etldr2125.tbl_adt_admits_discharges.patient_id))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A       | N/A  |
|     │ └─IndexRangeScan_11       | 1.43    | 1       | cop[tikv] | table:tbl_adt_admits_discharges, index:tbl_adt_admits_discharges_76d1d3337584a6cae15cd61503cba6c3(patient_id) | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                                                                                                                                                                                                                         | range: decided by [eq(etldr2125.tbl_adt_admits_discharges.patient_id, etldr21252023_08_14.pid_table.patient_id)], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | N/A       | N/A  |
|     └─Selection_14(Probe)       | 1.43    | 1       | cop[tikv] |                                                                                                               | time:1.26ms, loops:2, cop_task: {num: 1, max: 1.17ms, proc_keys: 1, tot_proc: 365.3µs, tot_wait: 94.5µs, rpc_num: 1, rpc_time: 1.14ms, copr_cache_hit_ratio: 0.00, build_task_duration: 16.9µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 340, total_keys: 2, get_snapshot_time: 43.2µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 18}}}     | isnull(etldr2125.tbl_adt_admits_discharges.yidu_t1_removed)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A       | N/A  |
|       └─TableRowIDScan_12       | 1.43    | 1       | cop[tikv] | table:tbl_adt_admits_discharges                                                                               | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                                                                                                                                                                                                                         | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A       | N/A  |
+---------------------------------+---------+---------+-----------+---------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
9 rows in set (0.007 sec)
  1. Removing (etldr2125.tbl_adt_admits_discharges.yidu_t1_removed is null and etldr21252023_08_14.pid_table.patient_id='pidxxxxxx') and executing:
select tbl_adt_admits_discharges.* 
from etldr2125.tbl_adt_admits_discharges 
inner join etldr21252023_08_14.pid_table 
on tbl_adt_admits_discharges.patient_id = etldr21252023_08_14.pid_table.patient_id 
where etldr2125.tbl_adt_admits_discharges.yidu_t1_removed = '0';

Returns 0 rows.

  1. Individually querying pid_table and tbl_adt_admits_discharges with the condition patient_id='pidxxxxxx', both return only one row.

  2. Using TiSpark to query the statement in step 1, it returns one row as expected:

scala> spark.sql("select tbl_adt_admits_discharges.* 
from tidb_catalog.etldr2125.tbl_adt_admits_discharges 
join tidb_catalog.etldr21252023_08_14.pid_table 
on tbl_adt_admits_discharges.patient_id = etldr21252023_08_14.pid_table.patient_id 
where tidb_catalog.etldr2125.tbl_adt_admits_discharges.yidu_t1_removed = '0' 
or (tidb_catalog.etldr2125.tbl_adt_admits_discharges.yidu_t1_removed is null 
and tidb_catalog.etldr21252023_08_14.pid_table.patient_id='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')").show();

Result:

+--------------------+--------------------+---------------+----------------+-----------------+---------------------+--------------------+-------------------------+--------------------+---------------------+--------------+------------------------+-----------------------------+------------------------+-------------------------+------------------+-------------------+-------------------+-----------+------------+--------------+-----------------------+--------------------------+--------------------------+------------------------+--------------+-------------------+-------------+-------------------+---------------+---------------+---------------------+---------------+--------------------+---------------+-------------------+------------------------+-------------------+------+------------+-------------------+------------+------------+-------------------+---------------+
|  inpatient_visit_id|          patient_id|dom_hospital_no|    inpatient_no|inpatient_case_no|inpatient_visit_count|cdt_admit_department|cdt_admit_dept_speciality|rstd_admit_dept_name|rstd_admit_speciality|cdt_admit_ward|cdt_discharge_department|cdt_discharge_dept_speciality|rstd_discharge_dept_name|rstd_discharge_speciality|cdt_discharge_ward|     admit_datetime| discharge_datetime|patient_age|cdt_fee_type|cdt_visit_type|cdt_attending_physician|cdt_attending_physician_id|primary_respon_doctor_name|primary_respon_doctor_id|cdt_adt_status|         birth_date|admitted_time|yidu_mapping_source|yidu_adt_status|yidu_visit_type|yidu_data_credibility|admit_dept_code|rstd_admit_dept_code|admit_ward_code|discharge_dept_code|rstd_discharge_dept_code|discharge_ward_code|weight|special_flag|  yidu_mapping_date|yidu_remarks|etldr_rowkey|yidu_t1_update_time|yidu_t1_removed|
+--------------------+--------------------+---------------+----------------+-----------------+---------------------+--------------------+-------------------------+--------------------+---------------------+--------------+------------------------+-----------------------------+------------------------+-------------------------+------------------+-------------------+-------------------+-----------+------------+--------------+-----------------------+--------------------------+--------------------------+------------------------+--------------+-------------------+-------------+-------------------+---------------+---------------+---------------------+---------------+--------------------+---------------+-------------------+------------------------+-------------------+------+------------+-------------------+------------+------------+-------------------+---------------+
|xxxx...|xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx|      11|11|11| 11|11|                   11|                11|                     11|                null|                 null|          11|                    11|                         1111                    null|                     null|              1|2023-08-11 16:04:00|2023-08-14 14:30:01|       null|        null|          11|                   null|                      null|                      null|                    null|          11|2015-01-10 00:00:00|         null|               null|           null|           null|                 null|           null|                null|           null|               null|                    null|               null|  null|        null|2023
2
| username: 湛兮tidb | Original post link

The column etldr2125.tbl_adt_admits_discharges.yidu_t1_removed is entirely null. I am not sure if this issue is triggered by specific data conditions.

3
| username: Jellybean | Original post link

Is there a unique key in the table?

Refer to this post and give it a try: 数据库产生主键id相同的重复数据 - TiDB 的问答社区

4
| username: TiDBer_vfJBUcxl | Original post link

Show more columns of data, are there any differences between the two pieces of data?

5
| username: 湛兮tidb | Original post link

The table tbl_adt_admits_discharges has a unique primary key.
The table pid_table does not have a primary key, it only has one column patient_id with no duplicates.

6
| username: 湛兮tidb | Original post link

There is no difference between the two pieces of data at all.

  1. Run in the MySQL client:
select tbl_adt_admits_discharges.* from etldr2125.tbl_adt_admits_discharges inner join etldr21252023_08_14.pid_table on tbl_adt_admits_discharges.patient_id = etldr21252023_08_14.pid_table.patient_id where etldr2125.tbl_adt_admits_discharges.yidu_t1_removed = '0' or (etldr2125.tbl_adt_admits_discharges.yidu_t1_removed is null and etldr21252023_08_14.pid_table.patient_id='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

If you select distinct, it is just one record.

7
| username: redgame | Original post link

Is it a Cartesian product?

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

Check if the indexes are inconsistent.

9
| username: Kongdom | Original post link

Adding DISTINCT results in 1 row, without DISTINCT it results in 2 rows, this should be a duplication, right?

10
| username: 湛兮tidb | Original post link

The issue was resolved after truncating the table tbl_adt_admits_discharges and then re-importing the data.

11
| username: Kongdom | Original post link

Is the amount of data in this table the same before and after truncation?

12
| username: 湛兮tidb | Original post link

Update again:
@tidb菜鸟一只’s guess was correct. Other tables also encountered similar issues, and this time after running admin check, the index indeed had problems.

MySQL [(none)]> admin check table etldr2125.tbl_doc_first_medical_note;
ERROR 8134 (HY000): data inconsistency in table: tbl_doc_first_medical_note, index: tbl_doc_first_medical_note_5441d68506a77a377da86928a7cb4f8d, col: visit_id, handle: "{428200337|8fIpugP4RsSSISdj1ZZJ7Q==}", index-values:"KindString 428200337|IP|1078686-6-97755" != record-values:"KindString 428200337|IP|1078686-24-97755", compare err:<nil>

Deleted the index and then rebuilt it, and it worked.
Not sure why this situation occurred.

13
| username: system | Original post link

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