NOT BETWEEN on sharding column is routed as BETWEEN and drops matching rows

Bug Report

Which version of ShardingSphere did you use?

master @ 101c3d56ccd

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

Both

Expected behavior

sharding_column NOT BETWEEN a AND b is excluded from sharding conditions, so the query is routed by the remaining conditions or broadcast, like NOT IN since #32076.

Actual behavior

NOT BETWEEN a AND b is routed as BETWEEN a AND b. Combined with an equality condition the sharding values intersect to an empty set and matching rows are silently missing.

Reason analyze (If you can)

ConditionValueBetweenOperatorGenerator.generate() (features/sharding/core, line 42-64) never reads BetweenExpression.isNot() and always returns Range.closed(a, b). The sibling ConditionValueInOperatorGenerator returns Optional.empty() for isNot() (#32076); BETWEEN was left out.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. Shard t_account into t_account_0 and t_account_1 by account_id % 2.
2. Insert a row with account_id = 11.
3. SELECT * FROM t_account WHERE account_id = 11 AND account_id NOT BETWEEN 1 AND 5;

Expected: the row from t_account_1. Actual: no rows, because the statement is unicast to t_account_0.

Example codes for reproduce this issue (such as a github link).

N/A