同样的查询条件,展示字段不同,得出来结果不一样。


如图所示,查询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';

试了下,是一样的

检查下 https://docs.pingcap.com/zh/tidb/dev/sql-statement-admin-check-table-index

这个跟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;


在正式环境,把动态裁剪关闭了,查询也正常了

1 Like

复现了

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 Like

那就是稳定复现了,看官方什么时候修复吧