You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2025/02/24 07:53:51 UTC

(shardingsphere) branch master updated: Added sql test cases for MySQL. (#34729)

This is an automated email from the ASF dual-hosted git repository.

duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git


The following commit(s) were added to refs/heads/master by this push:
     new a29067a482b Added sql test cases for MySQL. (#34729)
a29067a482b is described below

commit a29067a482beeab32c0898fee4e7be1d95be2584
Author: Yash-cor <ya...@coriolis.co.in>
AuthorDate: Mon Feb 24 13:23:45 2025 +0530

    Added sql test cases for MySQL. (#34729)
---
 .../resources/case/dml/select-special-function.xml | 72 +++++++++++++++++++++-
 .../sql/supported/dml/select-special-function.xml  |  5 ++
 .../main/resources/sql/supported/dml/select.xml    |  2 +-
 3 files changed, 77 insertions(+), 2 deletions(-)

diff --git a/test/it/parser/src/main/resources/case/dml/select-special-function.xml b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
index 9f200d35b38..b22f2d84522 100644
--- a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
@@ -4822,7 +4822,37 @@
                 </expr>
             </expression-projection>
         </projections>
-    </select>  
+    </select>
+
+    <select sql-case-id="select_last_insert_id">
+        <projections start-index="7" stop-index="22">
+            <expression-projection start-index="7" stop-index="22" text="LAST_INSERT_ID()">
+                <expr>
+                    <function start-index="7" stop-index="22" function-name="LAST_INSERT_ID" text="LAST_INSERT_ID()"/>
+                </expr>
+            </expression-projection>
+        </projections>
+    </select>
+
+    <select sql-case-id="select_last_value">
+        <projections start-index="7" stop-index="58" literal-start-index="7" literal-stop-index="58">
+            <expression-projection text="LAST_VALUE(AGE) OVER (PARTITION BY AGE ORDER BY AGE)" start-index="7" stop-index="58" literal-start-index="7" literal-stop-index="58">
+                <literalText>LAST_VALUE(AGE) OVER (PARTITION BY AGE ORDER BY AGE)</literalText>
+                <expr>
+                    <function function-name="LAST_VALUE" text="LAST_VALUE(AGE) OVER (PARTITION BY AGE ORDER BY AGE)" start-index="7" stop-index="58" literal-start-index="7" literal-stop-index="58">
+                        <parameter>
+                            <column name="AGE" start-index="18" stop-index="20" literal-start-index="18" literal-stop-index="20" />
+                        </parameter>
+                        <literalText>LAST_VALUE(AGE) OVER (PARTITION BY AGE ORDER BY AGE)</literalText>
+                    </function>
+                </expr>
+            </expression-projection>
+        </projections>
+        <from>
+            <simple-table name="TEST" start-index="65" stop-index="68" literal-start-index="65" literal-stop-index="68" />
+        </from>
+    </select>
+
     <select sql-case-id="select_lcase_function">
         <projections start-index="7" stop-index="28">
             <expression-projection start-index="7" stop-index="28" text="LCASE('QUADRATICALLY')">
@@ -4882,6 +4912,46 @@
         </projections>
     </select>
 
+    <select sql-case-id="select_localtime">
+        <projections start-index="7" stop-index="17">
+            <expression-projection start-index="7" stop-index="17" text="LOCALTIME()">
+                <expr>
+                    <function start-index="7" stop-index="17" function-name="LOCALTIME" text="LOCALTIME()"/>
+                </expr>
+            </expression-projection>
+        </projections>
+    </select>
+
+    <select sql-case-id="select_localtimestamp">
+        <projections start-index="7" stop-index="22">
+            <expression-projection start-index="7" stop-index="22" text="LOCALTIMESTAMP()">
+                <expr>
+                    <function start-index="7" stop-index="22" function-name="LOCALTIMESTAMP" text="LOCALTIMESTAMP()"/>
+                </expr>
+            </expression-projection>
+        </projections>
+    </select>
+
+    <select sql-case-id="select_lpad">
+        <projections start-index="7" stop-index="23">
+            <expression-projection start-index="7" stop-index="23" text="LPAD('hi',4,'??')">
+                <expr>
+                    <function start-index="7" stop-index="23" function-name="LPAD" text="LPAD('hi',4,'??')">
+                        <parameter>
+                            <literal-expression  value="hi" start-index="12" stop-index="15"/>
+                        </parameter>
+                        <parameter>
+                            <literal-expression  value="4" start-index="17" stop-index="17"/>
+                        </parameter>
+                        <parameter>
+                            <literal-expression  value="??" start-index="19" stop-index="22"/>
+                        </parameter>
+                    </function>
+                </expr>
+            </expression-projection>
+        </projections>
+    </select>
+
     <select sql-case-id="select_prediction_cost">
         <projections distinct-row="false" start-index="7" stop-index="13">
             <column-projection name="cust_id" start-delimiter="" end-delimiter="" start-index="7" stop-index="13"/>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
index ae8929d747b..8e0bc506dc2 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
@@ -278,10 +278,15 @@
     <sql-case id="select_instr" value="SELECT INSTR('foobar','bar')" db-types="Doris" />
     <sql-case id="select_strright" value="SELECT STRRIGHT('foobarbar',4)" db-types="Doris" />
     <sql-case id="select_extract_url_parameter" value="SELECT EXTRACT_URL_PARAMETER('http://foo.com/?bar=baz','bar')" db-types="Doris" />
+    <sql-case id="select_last_insert_id" value="SELECT LAST_INSERT_ID()" db-types="MySQL" />
+    <sql-case id="select_last_value" value="SELECT LAST_VALUE(AGE) OVER (PARTITION BY AGE ORDER BY AGE) from TEST" db-types="MySQL" />
     <sql-case id="select_lcase_function" value="SELECT LCASE('QUADRATICALLY')" db-types="MySQL" />
     <sql-case id="select_lower_function" value="SELECT LOWER('QUADRATICALLY')" db-types="MySQL" />
     <sql-case id="select_length" value="SELECT LENGTH('TEXT')" db-types="MySQL" />
     <sql-case id="select_locate" value="SELECT LOCATE('bar','foobarbar')" db-types="MySQL" />
+    <sql-case id="select_localtime" value="SELECT LOCALTIME()" db-types="MySQL" />
+    <sql-case id="select_localtimestamp" value="SELECT LOCALTIMESTAMP()" db-types="MySQL" />
+    <sql-case id="select_lpad" value="SELECT LPAD('hi',4,'??')" db-types="MySQL" />
     <sql-case id="select_prediction_cost" value="SELECT cust_id FROM mining_data_apply_v WHERE country_name = 'Italy' ORDER BY PREDICTION_COST(DT_SH_Clas_sample, 1 COST MODEL USING *)" db-types="Oracle" />
     <sql-case id="select_name_const" value="SELECT NAME_CONST('myname',14)" db-types="MySQL" />
     <sql-case id="select_not_in" value="SELECT (3,4) NOT IN ((1,2), (3,4))" db-types="MySQL" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index 8994bc407b6..b9064bc1f99 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -227,7 +227,7 @@
     <sql-case id="select_with_format_function" value="SELECT wi.code.format(null,'PURE_IDENTITY') as PURE_IDENTITY FROM warehouse_info wi;" db-types="Oracle" />
     <sql-case id="select_with_xml_is_schema_valid_function" value="SELECT x.xmlcol.isSchemaValid('http://www.example.com/schemas/ipo.xsd','purchaseOrder') FROM po_tab x;" db-types="Oracle" />
     <sql-case id="select_with_last_value_function" value="SELECT LAST_VALUE(AGE IGNORE NULLS) OVER (PARTITION BY AGE ORDER BY AGE) from TEST;" db-types="Oracle" />
-    <sql-case id="select_with_lead_and_lag_function" value="SELECT hire_date, LAG(hire_date, 1) OVER (ORDER BY hire_date) AS LAG1, LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS LEAD1 FROM employees WHERE department_id = 30 ORDER BY hire_date;" db-types="Oracle" />
+    <sql-case id="select_with_lead_and_lag_function" value="SELECT hire_date, LAG(hire_date, 1) OVER (ORDER BY hire_date) AS LAG1, LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS LEAD1 FROM employees WHERE department_id = 30 ORDER BY hire_date;" db-types="Oracle,MySQL" />
     <sql-case id="select_with_connect_by_root" value="SELECT CONNECT_BY_ROOT last_name 'Manager' FROM employees CONNECT BY PRIOR employee_id = manager_id" db-types="Oracle" />
     <sql-case id="select_with_ntile_function" value="SELECT NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees WHERE department_id = 100 ORDER BY last_name" db-types="Oracle,MySQL" />
     <sql-case id="select_with_percentile_functions" value="SELECT department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median cont', PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median disc' FROM employees GROUP BY department_id" db-types="Oracle" />