NullPointerException using shardingsphere-jdbc 5.3.0

[Misakami]

Bug Report

I used two sharding strategies for the same table This bug should have been fixed in 5.1, but I still encountered it in 5.3.0 see the issue https://github.com/apache/shardingsphere/issues/14273

Which version of ShardingSphere did you use?

shardingsphere-jdbc-core 5.3.0

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

ShardingSphere-JDBC

Expected behavior

no error

Actual behavior

SQL: Cause: java.lang.NullPointerException at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ... 74 more Caused by: java.lang.NullPointerException at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitiveFromTables(OrderByValue.java:74) at org.apache.shardingsphere.sharding.merge.dql.orderby.OrderByValue.getOrderValuesCaseSensitive(OrderByValue.java:64)

Reason analyze (If you can)

I used two sharding strategies for the same table but just load the first sharding strategies。

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

see bug report

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

this is part of my strategies

  tb_ration_trigger_trade_flow:
    actualDataNodes: ds$->{1..4}.ration_trigger_trade_flow_$->{1..8}
    keyGenerateStrategy:
      column: key_id
      keyGeneratorName: snowflake
  hint_ration_trigger_trade_flow:
    actualDataNodes: ds$->{1..4}.ration_trigger_trade_flow_$->{1..8}
    databaseStrategy:
      hint:
        shardingAlgorithmName: hint
    keyGenerateStrategy:
      column: key_id
      keyGeneratorName: asnowflake
    tableStrategy:
      hint:
        shardingAlgorithmName: hint

@Test void contextLoads3() { List rationModifyFlow = rationModifyFlowMapper .getRationModifyFlow("e10622e1388a4ca3a44f0a2078a4c8ea", "c1a7eaab7fe54fdda09168ba174294e5"); try (HintManager hintManager = HintManager.getInstance()){ hintManager.addDatabaseShardingValue("hint_ration_modify_flow",new HintParam(String.valueOf(1), true, true)); //hintManager.addTableShardingValue("hint_ration_modify_flow",new HintParam(String.valueOf(2), true, false)); List rationModifyFlowhis = rationModifyFlowMapper .getRationModifyFlowhis("e10622e1388a4ca3a44f0a2078a4c8ea", "c1a7eaab7fe54fdda09168ba174294e5"); } }

  <select id="getRationModifyFlow" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"></include>
    from tb_ration_modify_flow
    where investor_id = #{investorId,jdbcType=VARCHAR}
      and plan_id = #{planId,jdbcType=VARCHAR}
      and is_enabled = 1
    order by create_time desc
</select>

<select id="getRationModifyFlowhis" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"></include>
    from hint_ration_modify_flow
    where investor_id = #{investorId,jdbcType=VARCHAR}
      and plan_id = #{planId,jdbcType=VARCHAR}
      and is_enabled = 1
    order by create_time desc
</select>

[RaigorJiang]

Hi @strongduanmu , Please help with this question, thank you!

[strongduanmu]

Hi @Misakami, can you check your actual tables are all exist?

ds$->{1..4}.ration_trigger_trade_flow_$->{1..8}

[Misakami]

Hi @Misakami, can you check your actual tables are all exist?

ds$->{1..4}.ration_trigger_trade_flow_$->{1..8}

Yes, I can guarantee that it all exists。 And I tried to fix it myself

[Misakami]

Hi @Misakami, can you check your actual tables are all exist?

ds$->{1..4}.ration_trigger_trade_flow_$->{1..8}

I modified this part of the code，But I can only guarantee that it works for my usage scenario, and I don't have any more time to analyze it

[strongduanmu]

  tb_ration_trigger_trade_flow:
    actualDataNodes: ds$->{1..4}.ration_trigger_trade_flow_$->{1..8}
    keyGenerateStrategy:
      column: key_id
      keyGeneratorName: snowflake
  hint_ration_trigger_trade_flow:
    actualDataNodes: ds$->{1..4}.ration_trigger_trade_flow_$->{1..8}
    databaseStrategy:
      hint:
        shardingAlgorithmName: hint
    keyGenerateStrategy:
      column: key_id
      keyGeneratorName: asnowflake
    tableStrategy:
      hint:
        shardingAlgorithmName: hint

@Misakami Why do we need to map multiple logical tables to the same actual table? This may seem unreasonable, because if we modify the table structure with the logical table tb_ration_trigger_trade_flow, the logical table hint_ration_trigger_trade_flow is not aware of it. In addition, when we use different sharding algorithms, it will also lead to data confusion.

[Misakami]

  tb_ration_trigger_trade_flow:
    actualDataNodes: ds$->{1..4}.ration_trigger_trade_flow_$->{1..8}
    keyGenerateStrategy:
      column: key_id
      keyGeneratorName: snowflake
  hint_ration_trigger_trade_flow:
    actualDataNodes: ds$->{1..4}.ration_trigger_trade_flow_$->{1..8}
    databaseStrategy:
      hint:
        shardingAlgorithmName: hint
    keyGenerateStrategy:
      column: key_id
      keyGeneratorName: asnowflake
    tableStrategy:
      hint:
        shardingAlgorithmName: hint

@Misakami Why do we need to map multiple logical tables to the same actual table? This may seem unreasonable, because if we modify the table structure with the logical table tb_ration_trigger_trade_flow, the logical table hint_ration_trigger_trade_flow is not aware of it. In addition, when we use different sharding algorithms, it will also lead to data confusion.

Because at some point for this table, I need to traverse the whole database to do some statistics，by using HintShardingAlgorithm（The amount of data is huge）

Please look at https://github.com/apache/shardingsphere/issues/8820.

[strongduanmu]

This is not a standard usage. If you need to do some statistical SQL, you can try to synchronize the data to the big data platform through CDC.

[Misakami]

This is not a standard usage. If you need to do some statistical SQL, you can try to synchronize the data to the big data platform through CDC.

For a table, there are scenarios where you need to use the user name to find data and specify to obtain all objects in a certain database table for processing。 I think this is normal