<?xml version="1.0" encoding="UTF-8"?>
<!--
  ~ Licensed to the Apache Software Foundation (ASF) under one or more
  ~ contributor license agreements.  See the NOTICE file distributed with
  ~ this work for additional information regarding copyright ownership.
  ~ The ASF licenses this file to You under the Apache License, Version 2.0
  ~ (the "License"); you may not use this file except in compliance with
  ~ the License.  You may obtain a copy of the License at
  ~
  ~     http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific language governing permissions and
  ~ limitations under the License.
  -->

<sql-cases>
    <sql-case id="select_pagination_with_group_by_and_order_by" value="SELECT i.user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.item_id ORDER BY i.item_id DESC LIMIT ?, ?" db-types="MySQL,H2" />
    <sql-case id="select_pagination_with_diff_group_by_and_order_by" value="SELECT i.user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.user_id ORDER BY i.item_id DESC LIMIT ?, ?" db-types="MySQL" />
<!--    TODO :Correct parsing for subquery is first step.-->
<!--    <sql-case id="select_pagination_with_top_and_group_by_and_order_by" value="SELECT * FROM (SELECT TOP ? row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.item_id) AS row_ WHERE row_.rownum_ &gt; ?" db-types="SQLServer" />-->
<!--    <sql-case id="select_pagination_with_top_and_group_by_and_order_by_and_parentheses" value="SELECT * FROM (SELECT TOP (?) row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.item_id) AS row_ WHERE row_.rownum_ &gt; ?" db-types="SQLServer" />-->
<!--    <sql-case id="select_pagination_with_top_and_diff_group_by_and_order_by" value="SELECT * FROM (SELECT TOP ? row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.user_id ) AS row_ WHERE row_.rownum_ &gt; ?" db-types="SQLServer" />-->
<!--    <sql-case id="select_pagination_with_top_and_diff_group_by_and_order_by_and_parentheses" value="SELECT * FROM (SELECT TOP (?) row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.user_id ) AS row_ WHERE row_.rownum_ &gt; ?" db-types="SQLServer" />-->
<!--    <sql-case id="select_pagination_with_row_number_and_group_by_and_order_by" value="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? GROUP BY i.item_id ORDER BY i.item_id DESC) row_ WHERE rownum &lt;= ?) t WHERE t.rownum_ &gt; ?" db-types="Oracle" />-->
<!--    <sql-case id="select_pagination_with_row_number_and_diff_group_by_and_order_by" value="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? GROUP BY i.user_id ORDER BY i.item_id DESC) row_ WHERE rownum &lt;= ?) t WHERE t.rownum_ &gt; ?" db-types="Oracle" />-->
</sql-cases>
