Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 同样的查询条件，展示字段不同，得出来结果不一样。
|
username: dba-kit
As shown in the figure, querying c.serial_id can get the correct value, but querying c.occur_amount returns an empty result.
Could you provide the version, table structure, statistics, and SQL statements?
It is best to provide the table creation statement, test data, and verification SQL. It is difficult to analyze with the naked eye.
Strange, try changing the first “and” to “where” and see what happens.
This is an example. The query SQL is as follows, and the same error occurs when imported into another 6.1.1 cluster:
|
username: 
|
username: 
|
username: -- This query does not return results
select
c.occur_amount
from capital_history c
join txn_account_info t
on c.txt_account_id=t.txn_account_id
and c.capital_sub_class='CUST'
and t.account_id='1142927' and t.broker='0009'
and c.occur_trade_date = '2022-11-17';
-- Using the primary key, it returns results
select
c.serial_id, c.occur_amount
from capital_history c
join txn_account_info t
on c.txt_account_id=t.txn_account_id
and c.capital_sub_class='CUST'
and t.account_id='1142927' and t.broker='0009'
and c.occur_trade_date = '2022-11-17';
This has nothing to do with admin check, right? Using the above SQL and data, I can reproduce it with just one row of data for each table.
I tested it here, no problem.
The result is the same as executing it in MySQL.
I tried 6.1.1 and 6.1.2, both creating new tables and importing data, and it can be reproduced. Could it be a specific version bug?
I think it really needs to be checked. Look at ADMIN CHECK INDEX capital_historyidx_txnday_ta_code; It’s best to see which index is used in the execution plan of your actual SQL. It’s possible that there is an issue with that index, and the data you are querying is not in the index.
6.1.2, I did not detect your issue, my query results are normal.
|
username: 
|
username: 
|
username:
TiDB root@10.18.13.224:test> select
-> c.serial_id, c.occur_amount
-> from capital_history c
-> join txn_account_info t
-> on c.txt_account_id=t.txn_account_id
-> and c.capital_sub_class='CUST'
-> and t.account_id='1142927' and t.broker='0009'
-> and c.occur_trade_date = '2022-11-17';
+------------------+--------------+
| serial_id | occur_amount |
+------------------+--------------+
| 2022111700196920 | -2.01 |
+------------------+--------------+
1 row in set
Time: 0.139s
TiDB root@10.18.13.224:test> select
-> c.occur_amount
-> from capital_history c
-> join txn_account_info t
-> on c.txt_account_id=t.txn_account_id
-> and c.capital_sub_class='CUST'
-> and t.account_id='1142927' and t.broker='0009'
-> and c.occur_trade_date = '2022-11-17';
+--------------+
| occur_amount |
+--------------+
| -2.01 |
+--------------+
1 row in set
Time: 0.094s
TiDB root@10.18.13.224:test> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.1.2 |
+--------------------+
1 row in set
Time: 0.022s
TiDB root@10.18.13.224:test>
That’s true. I started a new cluster using the playground, and I can query it. However, in the old cluster, I created a new table in the test database and imported these two pieces of data, but I couldn’t query them. I’ll compare the parameter settings to see if that’s the issue.
Please try executing the following two commands first to enable clustered index and partition dynamic pruning features, then try creating the table and importing data. I started a new instance using tiup playground and found that there were issues executing under these two parameters.
set global tidb_partition_prune_mode='dynamic';
set global tidb_enable_clustered_index=ON;
In the production environment, after turning off dynamic pruning, the query returned to normal.
Reproduced
TiDB root@10.18.13.224:test> show variables like '%tidb_partition_prune_mode%'
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| tidb_partition_prune_mode | dynamic |
+---------------------------+---------+
1 row in set
Time: 0.027s
TiDB root@10.18.13.224:test> show variables like 'tidb_enable_clustered_index%'
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| tidb_enable_clustered_index | ON |
+-----------------------------+-------+
1 row in set
Time: 0.021s
TiDB root@10.18.13.224:test> select
-> c.occur_amount
-> from capital_history c
-> join txn_account_info t
-> on c.txt_account_id=t.txn_account_id
-> and c.capital_sub_class='CUST'
-> and t.account_id='1142927' and t.broker='0009'
-> and c.occur_trade_date = '2022-11-17';
+--------------+
| occur_amount |
+--------------+
+--------------+
0 rows in set
Time: 0.030s
TiDB root@10.18.13.224:test> select
-> c.serial_id, c.occur_amount
-> from capital_history c
-> join txn_account_info t
-> on c.txt_account_id=t.txn_account_id
-> and c.capital_sub_class='CUST'
-> and t.account_id='1142927' and t.broker='0009'
-> and c.occur_trade_date = '2022-11-17';
+------------------+--------------+
| serial_id | occur_amount |
+------------------+--------------+
| 2022111700196920 | -2.01 |
+------------------+--------------+
1 row in set
Time: 0.029s
TiDB root@10.18.13.224:test> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.1.2 |
+--------------------+
1 row in set
Time: 0.019s
TiDB root@10.18.13.224:test>
That means it can be stably reproduced. Let’s wait for the official fix.