why return empty when just one conditon is empty

[IcebergXTY]

Question

code location。(shardingshphere 5.3.2)

See the sql below

select concat(data_source_id, '_', cost_item_name) as ruleKey,
    sum(ori_amount) as ori_amount
from cost_item_order_detail
where tenant_code = ?
    and platform_code = ?
    and shop_code = ?
    and order_date between ? and ?
    and (
        data_source_id = ?
        and cost_item_name = ?
    )
    or (
        data_source_id = ?
        and cost_item_name = ?
    )
group by data_source_id,
    cost_item_name

shardingsphere will analyse two AndPredicate, one is tenant_code = ? platform_code = ? and shop_code = ? and order_date between ? and ? and (data_source_id = ? and cost_item_name = ?) and the other is data_source_id = ? and cost_item_name = ?.

We use tenant_code = ? platform_code = ? and shop_code = ? as table sharding rule so the shardingConditionValues from the first AndPredicate is OK, but the second is empty. But in the code above, if there is any empty shardingConditionValues, it will return Collections.emptyList() which raise an error in our project.

I wonder to know why return empty when just one conditon is empty here 🤨

[RaigorJiang]

Hi @IcebergXTY
Can you provide a sharding configuration and table structure for testing?

[IcebergXTY]

@RaigorJiang Of course, I push here.
Just explain it, we set actualDataNodes: ds_$->{0..1}.cost_item_order_detail_$->{0..999}, but we may just use ds_$->{0..1}.cost_item_order_detail_$->{0..10}, so the number of tables in the database are 11 rather than 1000.
We use shop_code to judge the actual sharding table and the user can add shop at any time so we need a large enough range like $->{0..999} .
In this scenario shown in the demo, empty shardingcondition will lead to a whole route so it will try to execute sql on the all 1000 table and then throw a exception Table 'cost_item_order_detail_11' doesn't exist

[RaigorJiang]

@IcebergXTY There is no sharding key when database routing, so all data sources are routed.

[IcebergXTY]

@IcebergXTY There is no sharding key when database routing, so all data sources are routed.

tenant_code = 'a' and platform_code = 'b' and shop_code = 'c' are not valid sharding key?🤨

[RaigorJiang]

In this scenario shown in the demo, empty shardingcondition will lead to a whole route so it will try to execute sql on the all 1000 table and then throw a exception Table 'cost_item_order_detail_11' doesn't exist

@IcebergXTY Because you said that there was an empty sharding condition.

[IcebergXTY]

@IcebergXTY Because you said that there was an empty sharding condition.

That's the question, I have set all the values, but shardingsphere consider there is no sharding condition

[IcebergXTY]

As said in the first, the code here are weird.

private Collection<ShardingCondition> createShardingConditions(final ExpressionSegment expression, final List<Object> params, final Map<String, String> columnExpressionTableNames) {
    Collection<AndPredicate> andPredicates = ExpressionExtractUtil.getAndPredicates(expression);
    Collection<ShardingCondition> result = new LinkedList<>();
    for (AndPredicate each : andPredicates) {
        Map<Column, Collection<ShardingConditionValue>> shardingConditionValues = createShardingConditionValueMap(each.getPredicates(), params, columnExpressionTableNames);
        // If any `AndPredicate` is empty, then all this method will return empty
        if (shardingConditionValues.isEmpty()) {
            return Collections.emptyList();
        }
        ShardingCondition shardingCondition = createShardingCondition(shardingConditionValues);
        shardingCondition.setStartIndex(expression.getStartIndex());
        result.add(shardingCondition);
    }
    return result;
}