DBLE date分区类型不支持between语法, 并且> < 会扫描全部库？

[JeremyYu2018]

• dble version： 3.22.01.1 GitVersion 6c90fba9b199f2829d8478cb7c6ce8ae2b922925 BuildTime 20220615064550 MavenVersion 3.22.01.1 GitUrl https://github.com/actiontech/dble WebSite https://opensource.actionsky.com/ QQGroup 669663113

• preconditions ：
  no

• configs：

cluster.cnf

bootstrap.cnf

db.xml

user.xml

sharding.xml

<?xml version="1.0"?>
<!--
  ~ Copyright (C) 2016-2022 ActionTech.
  ~ License: http://www.gnu.org/licenses/gpl.html GPL version 2 or higher.
  -->

<dble:sharding xmlns:dble="http://dble.cloud/" version="4.0">

    <schema name="foodgame"  sqlMaxLimit="100">
        <shardingTable name="user_money_log" shardingNode="dn$0-9" function="func_partbydate" shardingColumn="LOG_DATE"/>
    </schema>
    <!-- sharding testdb2 route to database named dn5 in localhost2  -->
    <shardingNode name="dn0" dbGroup="dbGroup1"  database="foodgame_00"/>
    <shardingNode name="dn1" dbGroup="dbGroup1"  database="foodgame_01"/>
    <shardingNode name="dn2" dbGroup="dbGroup1"  database="foodgame_02"/>
    <shardingNode name="dn3" dbGroup="dbGroup1"  database="foodgame_03"/>
    <shardingNode name="dn4" dbGroup="dbGroup1"  database="foodgame_04"/>
    <shardingNode name="dn5" dbGroup="dbGroup1"  database="foodgame_05"/>
    <shardingNode name="dn6" dbGroup="dbGroup1"  database="foodgame_06"/>
    <shardingNode name="dn7" dbGroup="dbGroup1"  database="foodgame_07"/>
    <shardingNode name="dn8" dbGroup="dbGroup1"  database="foodgame_08"/>
    <shardingNode name="dn9" dbGroup="dbGroup1"  database="foodgame_09"/>

   <function name="func_partbydate" class="date">
       <property name="dateFormat">yyyy-MM-dd</property>
       <property name="sBeginDate">2022-01-01</property>
       <property name="sPartionDay">30</property>
       <property name="defaultNode">0</property>
  </function>

</dble:sharding>

insert into user_money_log(id,user_id,type,begin_num, log_date) values( 2, 1, 1, 3, '2022-09-29 17:33:03'); insert into user_money_log(id,user_id,type,begin_num, log_date) values( 2, 1, 1, 3, '2022-010-29 17:33:03');

1. 不支持between语法

2. < 会扫描所有的库

3. 只有=能定位到具体某一个分区

[PanternBao]

can join QQGroup 669663113 to communicate

[JeremyYu2018]

can join QQGroup 669663113 to communicate

请问下能复现么？

[wangjuan-action]

问题1：不支持between语法 根据你的分片配置，30天一个分区，一共10个分区，2022-10-29超过了分区范围，因此执行between语句报错，如果日期在分区范围内，between可以执行成功 问题2：< 会扫描所有的库 目前只支持between...and...语句按分区查询，< 、> 语句会扫描所有分片

[JeremyYu2018]

问题2有计划修复么

[JeremyYu2018]

问题2有计划修复么

@wangjuan-action

[wangjuan-action]

问题2有计划修复么

@wangjuan-action

暂无计划，可使用between...and...语句 @yuzhaoge

[PanternBao]

refer to https://github.com/actiontech/dble/discussions/3425