<h2 dir="auto">Bug Report</h2>
<h3 dir="auto">Which version of ShardingSphere did you use?</h3>
<p dir="auto">4.0.0-RC2</p>
<h3 dir="auto">Which project did you use? Sharding-JDBC or Sharding-Proxy?</h3>
<p dir="auto">sharding-jdbc-core</p>
<h3 dir="auto">Expected behavior</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="dsl.select(f1.Id)
                .from(f1)
                .where(f1.Id.le(id))
                .limit(1)
//                .getSQL()
                .fetchOne().into(Long::class.java)"><pre class="notranslate"><code class="notranslate">dsl.select(f1.Id)
                .from(f1)
                .where(f1.Id.le(id))
                .limit(1)
//                .getSQL()
                .fetchOne().into(Long::class.java)
</code></pre></div>
<h3 dir="auto">Actual behavior</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer

	at org.jooq_3.13.0-SNAPSHOT.MYSQL.debug(Unknown Source)
	at org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination.getValue(Pagination.java:60)
	at org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination.&lt;init&gt;(Pagination.java:55)"><pre class="notranslate"><code class="notranslate">java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer

	at org.jooq_3.13.0-SNAPSHOT.MYSQL.debug(Unknown Source)
	at org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination.getValue(Pagination.java:60)
	at org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination.&lt;init&gt;(Pagination.java:55)
</code></pre></div>
<h3 dir="auto">Reason analyze (If you can)</h3>
<p dir="auto">org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination<br>
line 60</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    private int getValue(final PaginationValueSegment paginationValueSegment, final List&lt;Object&gt; parameters) {
        return paginationValueSegment instanceof ParameterMarkerPaginationValueSegment
                ? (int) parameters.get(((ParameterMarkerPaginationValueSegment) paginationValueSegment).getParameterIndex())
                : ((NumberLiteralPaginationValueSegment) paginationValueSegment).getValue();
    }
// ? (int) parameters.get(((ParameterMarkerPaginationValueSegment) paginationValueSegment).getParameterIndex())
// -&gt; parameters.get(((ParameterMarkerPaginationValueSegment) paginationValueSegment).getParameterIndex()).intValue"><pre class="notranslate"><code class="notranslate">    private int getValue(final PaginationValueSegment paginationValueSegment, final List&lt;Object&gt; parameters) {
        return paginationValueSegment instanceof ParameterMarkerPaginationValueSegment
                ? (int) parameters.get(((ParameterMarkerPaginationValueSegment) paginationValueSegment).getParameterIndex())
                : ((NumberLiteralPaginationValueSegment) paginationValueSegment).getValue();
    }
// ? (int) parameters.get(((ParameterMarkerPaginationValueSegment) paginationValueSegment).getParameterIndex())
// -&gt; parameters.get(((ParameterMarkerPaginationValueSegment) paginationValueSegment).getParameterIndex()).intValue
</code></pre></div>
<h3 dir="auto">Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.</h3>
<h3 dir="auto">Example codes for reproduce this issue (such as a github link).</h3>
<p dir="auto">JOOQ use <code class="notranslate">Number</code> as limit() parameter, and past <code class="notranslate">Long</code> to preparedstatement</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="dsl.select(f1.Id)
                .from(f1)
                .where(f1.Id.le(id))
                .limit(1)
//                .getSQL()
                .fetchOne().into(Long::class.java)"><pre class="notranslate"><code class="notranslate">dsl.select(f1.Id)
                .from(f1)
                .where(f1.Id.le(id))
                .limit(1)
//                .getSQL()
                .fetchOne().into(Long::class.java)
</code></pre></div>
<h2 dir="auto">Bug Report</h2>
<p dir="auto">Duplicate groups founded, due to the incorrect merge.</p>
<h3 dir="auto">Which version of ShardingSphere did you use?</h3>
<p dir="auto">4.1.1</p>
<h3 dir="auto">Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?</h3>
<p dir="auto">ShardingSphere-JDBC</p>
<h3 dir="auto">Expected behavior</h3>
<p dir="auto">from table_A order by  group_name:</p>
<table role="table">
<thead>
<tr>
<th>group_name</th>
<th>count</th>
</tr>
</thead>
<tbody>
<tr>
<td>A</td>
<td>1</td>
</tr>
</tbody>
</table>
<p dir="auto">from table_B order by  group_name:</p>
<table role="table">
<thead>
<tr>
<th>group_name</th>
<th>count</th>
</tr>
</thead>
<tbody>
<tr>
<td>a</td>
<td>1</td>
</tr>
<tr>
<td>A</td>
<td>1</td>
</tr>
</tbody>
</table>
<p dir="auto">the result should be:</p>
<table role="table">
<thead>
<tr>
<th>group_name</th>
<th>count</th>
</tr>
</thead>
<tbody>
<tr>
<td>A</td>
<td>2</td>
</tr>
<tr>
<td>a</td>
<td>1</td>
</tr>
</tbody>
</table>
<h3 dir="auto">Actual behavior</h3>
<table role="table">
<thead>
<tr>
<th>group_name</th>
<th>count</th>
</tr>
</thead>
<tbody>
<tr>
<td>A</td>
<td>1</td>
</tr>
<tr>
<td>a</td>
<td>1</td>
</tr>
<tr>
<td>A</td>
<td>1</td>
</tr>
</tbody>
</table>
<h3 dir="auto">Reason analyze (If you can)</h3>
<p dir="auto">the comparison result of the priority queue in class <a href="https://github.com/apache/shardingsphere/blob/8fd2507f4a7e18831f001687b118ad55ed57c307/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/orderby/OrderByStreamMergedResult.java#L42">OrderByStreamMergedResult</a> is different from the result of postgreSQL, as the <a href="https://github.com/apache/shardingsphere/blob/96375f420074a8a4cba5307f1a308f3a87b3c7bf/sharding-core/sharding-core-merge/src/main/java/org/apache/shardingsphere/sharding/merge/dql/orderby/OrderByValue.java#L70">getOrderValuesCaseSensitiveFromTables</a> in OrderByValue always returns true, it should be false as 'a' &gt; 'A' according to the resultSet of PG, e.g.<br>
<code class="notranslate">select * from (values ('A'),('a')) as a(test) order by test</code></p>
<table role="table">
<thead>
<tr>
<th>test</th>
</tr>
</thead>
<tbody>
<tr>
<td>a</td>
</tr>
<tr>
<td>A</td>
</tr>
</tbody>
</table>
<p dir="auto">I solved this issue by forcing the <code class="notranslate">order by</code> case-sensitive, is there any other way? thx<br>
<code class="notranslate">select * from (values ('A'),('a')) as a(test) order by test collate "C"</code></p>
<table role="table">
<thead>
<tr>
<th>test</th>
</tr>
</thead>
<tbody>
<tr>
<td>A</td>
</tr>
<tr>
<td>a</td>
</tr>
</tbody>
</table>