如图所示，查询
c.serial_id可以得到正确的值，查询c.occur_amount返回结果就是空
版本、表结构、统计信息、SQL 语句可以提供一下么
最好提供建表语句，测试数据，验证sql。方便大家验证。肉眼很难分析的
诡异，试下把第一个and改成where看看
dump.sql (10.4 KB)
这个是示例，查询SQL为，导入到另外一个6.1.1的集群也是一样报错：
-- 这个查不出来结果
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';
-- 换成主键，能查出来
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';
试了下，是一样的
这个跟admin check没关系吧，我用上面的SQL和数据，只用每个表各一行数据就能复现。
持续关注
select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.4.0 |
+--------------------+
1 row in set (0.01 sec)
root@:4000[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 (0.08 sec)
root@:4000[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 (0.10 sec)
我这测试了下，没问题。
跟在MySQL里面执行的结果一样。
MySQL [sbtest]> SELECT VERSION();
±-------------------+
| VERSION() |
±-------------------+
| 5.7.25-TiDB-v5.4.3 |
±-------------------+
1 row in set (0.00 sec)
MySQL [sbtest]> 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 (0.03 sec)
MySQL [sbtest]> 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 (0.02 sec)
MySQL [sbtest]>
我这也没问题。。。
我试了6.1.1和6.1.2，都是新建表导入数据，可以复现的。难道是特定版本BUG么？
把执行计划发出来看看。
我觉得真需要校验一下，你看下ADMIN CHECK INDEX capital_historyidx_txnday_ta_code;最好看下你实际sql里面执行计划走的索引是哪个，有可能那个索引有问题，你查的这条数据，索引里面没有
6.1.2,没有测出你的问题，我这查询是正常的。
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>
还真是，我用playground起了个新的集群，能查出来。但是老的集群在test库新建表导入这两条数据，查不到。我再对比一下是不是参数设置问题
麻烦你试下先执行下面两个，开启聚簇索引和分区动态裁剪功能，再试下新建表和导入数据。我使用tiup playground起了了新实例，发现这两个参数下执行有问题
set global tidb_partition_prune_mode='dynamic';
set global tidb_enable_clustered_index=ON;
复现了
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>
1 个赞
那就是稳定复现了，看官方什么时候修复吧