(datafusion) branch main updated: Automate sqllogictest for StringView (for one function, `substr`) (#12433)

[alamb]

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

alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git


The following commit(s) were added to refs/heads/main by this push:
     new f71b0e0475 Automate sqllogictest for StringView (for one function, 
`substr`) (#12433)
f71b0e0475 is described below

commit f71b0e047566c4ccb1578d73ec83b6c00d55de9b
Author: Yongting You <2010you...@gmail.com>
AuthorDate: Fri Sep 13 00:06:26 2024 +0800

    Automate sqllogictest for StringView (for one function, `substr`) (#12433)
    
    * automate sqllogictest for different string types
    
    * Update 
datafusion/sqllogictest/test_files/string_functions/substr/substr_table.slt.part
    
    Co-authored-by: Andrew Lamb <and...@nerdnetworks.org>
    
    ---------
    
    Co-authored-by: Andrew Lamb <and...@nerdnetworks.org>
---
 datafusion/sqllogictest/test_files/functions.slt   | 167 ---------------------
 .../substr/substr_literal.slt.part                 | 136 +++++++++++++++++
 .../string_functions/substr/substr_runner.slt      |  72 +++++++++
 .../string_functions/substr/substr_table.slt.part  |  45 ++++++
 4 files changed, 253 insertions(+), 167 deletions(-)

diff --git a/datafusion/sqllogictest/test_files/functions.slt 
b/datafusion/sqllogictest/test_files/functions.slt
index 9bee9b8184..228d7ca2f0 100644
--- a/datafusion/sqllogictest/test_files/functions.slt
+++ b/datafusion/sqllogictest/test_files/functions.slt
@@ -442,173 +442,6 @@ SELECT strpos('joséésoj', NULL)
 ----
 NULL
 
-query T
-SELECT substr('alphabet', -3)
-----
-alphabet
-
-query T
-SELECT substr('alphabet', 0)
-----
-alphabet
-
-query T
-SELECT substr('alphabet', 1)
-----
-alphabet
-
-query T
-SELECT substr('alphabet', 2)
-----
-lphabet
-
-query T
-SELECT substr('alphabet', 3)
-----
-phabet
-
-query T
-SELECT substr('alphabet', 30)
-----
-(empty)
-
-query T
-SELECT substr('alphabet', 3, 2)
-----
-ph
-
-query T
-SELECT substr('alphabet', 3, 20)
-----
-phabet
-
-# test range ouside of string length
-query TTTTTTTTTTTT
-SELECT
-    substr('hi🌏', 1, 3),
-    substr('hi🌏', 1, 4),
-    substr('hi🌏', 1, 100),
-    substr('hi🌏', 0, 1),
-    substr('hi🌏', 0, 2),
-    substr('hi🌏', 0, 4),
-    substr('hi🌏', 0, 5),
-    substr('hi🌏', -10, 100),
-    substr('hi🌏', -10, 12),
-    substr('hi🌏', -10, 5),
-    substr('hi🌏', 10, 0),
-    substr('hi🌏', 10, 10);
-----
-hi🌏 hi🌏 hi🌏 (empty) h hi🌏 hi🌏 hi🌏 h (empty) (empty) (empty)
-
-query TTTTTTTTTTTT
-SELECT
-    substr('', 1, 3),
-    substr('', 1, 4),
-    substr('', 1, 100),
-    substr('', 0, 1),
-    substr('', 0, 2),
-    substr('', 0, 4),
-    substr('', 0, 5),
-    substr('', -10, 100),
-    substr('', -10, 12),
-    substr('', -10, 5),
-    substr('', 10, 0),
-    substr('', 10, 10);
-----
-(empty) (empty) (empty) (empty) (empty) (empty) (empty) (empty) (empty) 
(empty) (empty) (empty)
-
-# Nulls
-query TTTTTTTTTT
-SELECT 
-  substr('alphabet', NULL),
-  substr(NULL, 1),
-  substr(NULL, NULL),
-  substr('alphabet', CAST(NULL AS int), -20),
-  substr('alphabet', 3, CAST(NULL AS int)),
-  substr(NULL, 3, -4),
-  substr(NULL, NULL, 4),
-  substr(NULL, 1, NULL),
-  substr('', NULL, NULL),
-  substr(NULL, NULL, NULL);
-----
-NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
-
-query T
-SELECT substr('Hello🌏世界', 5)
-----
-o🌏世界
-
-query T
-SELECT substr('Hello🌏世界', 5, 3)
-----
-o🌏世
-
-statement ok
-create table test_substr (
-    c1 VARCHAR
-) as values ('foo'), ('hello🌏世界'), ('💩'), ('ThisIsAVeryLongASCIIString'), 
(''), (NULL);
-
-statement ok
-create table test_substr_stringview as
-select c1 as c1, arrow_cast(c1, 'Utf8View') as c1_view from test_substr;
-
-# `substr()` on `StringViewArray`'s implementation operates directly on view's
-# logical pointers, so check it's consistent with `StringArray`
-query BBBBBBBBBBBBBB
-select 
-    substr(c1, 1) = substr(c1_view, 1),
-    substr(c1, 3) = substr(c1_view, 3),
-    substr(c1, 100) = substr(c1_view, 100),
-    substr(c1, -1) = substr(c1_view, -1),
-    substr(c1, 0, 0) = substr(c1_view, 0, 0),
-    substr(c1, -1, 2) = substr(c1_view, -1, 2),
-    substr(c1, -2, 10) = substr(c1_view, -2, 10),
-    substr(c1, -100, 200) = substr(c1_view, -100, 200),
-    substr(c1, -10, 10) = substr(c1_view, -10, 10),
-    substr(c1, -100, 10) = substr(c1_view, -100, 10),
-    substr(c1, 1, 100) = substr(c1_view, 1, 100),
-    substr(c1, 5, 3) = substr(c1_view, 5, 3),
-    substr(c1, 100, 200) = substr(c1_view, 100, 200),
-    substr(c1, 8, 0) = substr(c1_view, 8, 0)
-from test_substr_stringview;
-----
-true true true true true true true true true true true true true true
-true true true true true true true true true true true true true true
-true true true true true true true true true true true true true true
-true true true true true true true true true true true true true true
-true true true true true true true true true true true true true true
-NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
-
-# Check for non-ASCII strings
-query TT
-select substr(c1_view, 1), substr(c1_view, 5,3) from test_substr_stringview;
-----
-foo (empty)
-hello🌏世界 o🌏世
-💩 (empty)
-ThisIsAVeryLongASCIIString IsA
-(empty) (empty)
-NULL NULL
-
-statement ok
-drop table test_substr;
-
-statement ok
-drop table test_substr_stringview;
-
-
-statement error The SUBSTR function can only accept strings, but got Int64.
-SELECT substr(1, 3)
-
-statement error The SUBSTR function can only accept strings, but got Int64.
-SELECT substr(1, 3, 4)
-
-statement error Execution error: negative substring length not allowed
-select substr(arrow_cast('foo', 'Utf8View'), 1, -1);
-
-statement error Execution error: negative substring length not allowed
-select substr('', 1, -1);
-
 query T
 SELECT translate('12345', '143', 'ax')
 ----
diff --git 
a/datafusion/sqllogictest/test_files/string_functions/substr/substr_literal.slt.part
 
b/datafusion/sqllogictest/test_files/string_functions/substr/substr_literal.slt.part
new file mode 100644
index 0000000000..dac5901662
--- /dev/null
+++ 
b/datafusion/sqllogictest/test_files/string_functions/substr/substr_literal.slt.part
@@ -0,0 +1,136 @@
+# 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.
+
+query T
+SELECT substr('alphabet', -3)
+----
+alphabet
+
+query T
+SELECT substr('alphabet', 0)
+----
+alphabet
+
+query T
+SELECT substr('alphabet', 1)
+----
+alphabet
+
+query T
+SELECT substr('alphabet', 2)
+----
+lphabet
+
+query T
+SELECT substr('alphabet', 3)
+----
+phabet
+
+query T
+SELECT substr('alphabet', 30)
+----
+(empty)
+
+query T
+SELECT substr('alphabet', 3, 2)
+----
+ph
+
+query T
+SELECT substr('alphabet', 3, 20)
+----
+phabet
+
+query TT
+select 
+    substr(arrow_cast('alphabet', 'LargeUtf8'), 3, 20), 
+    substr(arrow_cast('alphabet', 'Utf8View'), 3, 20);
+----
+phabet phabet
+
+# test range ouside of string length
+query TTTTTTTTTTTT
+SELECT
+    substr('hi🌏', 1, 3),
+    substr('hi🌏', 1, 4),
+    substr('hi🌏', 1, 100),
+    substr('hi🌏', 0, 1),
+    substr('hi🌏', 0, 2),
+    substr('hi🌏', 0, 4),
+    substr('hi🌏', 0, 5),
+    substr('hi🌏', -10, 100),
+    substr('hi🌏', -10, 12),
+    substr('hi🌏', -10, 5),
+    substr('hi🌏', 10, 0),
+    substr('hi🌏', 10, 10);
+----
+hi🌏 hi🌏 hi🌏 (empty) h hi🌏 hi🌏 hi🌏 h (empty) (empty) (empty)
+
+query TTTTTTTTTTTT
+SELECT
+    substr('', 1, 3),
+    substr('', 1, 4),
+    substr('', 1, 100),
+    substr('', 0, 1),
+    substr('', 0, 2),
+    substr('', 0, 4),
+    substr('', 0, 5),
+    substr('', -10, 100),
+    substr('', -10, 12),
+    substr('', -10, 5),
+    substr('', 10, 0),
+    substr('', 10, 10);
+----
+(empty) (empty) (empty) (empty) (empty) (empty) (empty) (empty) (empty) 
(empty) (empty) (empty)
+
+# Nulls
+query TTTTTTTTTT
+SELECT 
+  substr('alphabet', NULL),
+  substr(NULL, 1),
+  substr(NULL, NULL),
+  substr('alphabet', CAST(NULL AS int), -20),
+  substr('alphabet', 3, CAST(NULL AS int)),
+  substr(NULL, 3, -4),
+  substr(NULL, NULL, 4),
+  substr(NULL, 1, NULL),
+  substr('', NULL, NULL),
+  substr(NULL, NULL, NULL);
+----
+NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
+
+query T
+SELECT substr('Hello🌏世界', 5)
+----
+o🌏世界
+
+query T
+SELECT substr('Hello🌏世界', 5, 3)
+----
+o🌏世
+
+statement error The SUBSTR function can only accept strings, but got Int64.
+SELECT substr(1, 3)
+
+statement error The SUBSTR function can only accept strings, but got Int64.
+SELECT substr(1, 3, 4)
+
+statement error Execution error: negative substring length not allowed
+select substr(arrow_cast('foo', 'Utf8View'), 1, -1);
+
+statement error Execution error: negative substring length not allowed
+select substr('', 1, -1);
diff --git 
a/datafusion/sqllogictest/test_files/string_functions/substr/substr_runner.slt 
b/datafusion/sqllogictest/test_files/string_functions/substr/substr_runner.slt
new file mode 100644
index 0000000000..d61cfa2b74
--- /dev/null
+++ 
b/datafusion/sqllogictest/test_files/string_functions/substr/substr_runner.slt
@@ -0,0 +1,72 @@
+# 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.
+
+# --------------------------------------
+# Test `substr()` with literal arguments
+# --------------------------------------
+include ./substr_literal.slt.part
+
+# --------------------------------------
+# Setup test tables with different physical string types 
(Utf8/Utf8View/LargeUtf8)
+# and repeat tests in `substr_table.slt.part`
+# --------------------------------------
+statement ok
+create table test_substr_base (
+    col1 VARCHAR
+) as values ('foo'), ('hello🌏世界'), ('💩'), ('ThisIsAVeryLongASCIIString'), 
(''), (NULL);
+
+#
+# Run1: Utf8
+#
+statement ok
+create table test_substr as
+select arrow_cast(col1, 'Utf8') as c1 from test_substr_base;
+
+include ./substr_table.slt.part
+
+statement ok
+drop table test_substr;
+
+#
+# Run2: Utf8View
+#
+statement ok
+create table test_substr as
+select arrow_cast(col1, 'Utf8View') as c1 from test_substr_base;
+
+include ./substr_table.slt.part
+
+statement ok
+drop table test_substr;
+
+#
+# Run3: LargeUtf8
+#
+statement ok
+create table test_substr as
+select arrow_cast(col1, 'LargeUtf8') as c1 from test_substr_base;
+
+include ./substr_table.slt.part
+
+statement ok
+drop table test_substr;
+
+# --------------------------------------
+# Cleanup
+# --------------------------------------
+statement ok
+drop table test_substr_base;
\ No newline at end of file
diff --git 
a/datafusion/sqllogictest/test_files/string_functions/substr/substr_table.slt.part
 
b/datafusion/sqllogictest/test_files/string_functions/substr/substr_table.slt.part
new file mode 100644
index 0000000000..0d8e62af84
--- /dev/null
+++ 
b/datafusion/sqllogictest/test_files/string_functions/substr/substr_table.slt.part
@@ -0,0 +1,45 @@
+# 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.
+
+# This file is intended to be run with tables already defined
+# with standard values, but different types in string columns
+# (String, StringView, etc.)
+
+query TTTTTTTTTTTTTT
+select 
+    substr(c1, 1),
+    substr(c1, 3),
+    substr(c1, 100),
+    substr(c1, -1),
+    substr(c1, 0, 0),
+    substr(c1, -1, 2),
+    substr(c1, -2, 10),
+    substr(c1, -100, 200),
+    substr(c1, -10, 10),
+    substr(c1, -100, 10),
+    substr(c1, 1, 100),
+    substr(c1, 5, 3),
+    substr(c1, 100, 200),
+    substr(c1, 8, 0)
+from test_substr;
+----
+foo o (empty) foo (empty) (empty) foo foo (empty) (empty) foo (empty) (empty) 
(empty)
+hello🌏世界 llo🌏世界 (empty) hello🌏世界 (empty) (empty) hello🌏世 hello🌏世界 (empty) 
(empty) hello🌏世界 o🌏世 (empty) (empty)
+💩 (empty) (empty) 💩 (empty) (empty) 💩 💩 (empty) (empty) 💩 (empty) (empty) 
(empty)
+ThisIsAVeryLongASCIIString isIsAVeryLongASCIIString (empty) 
ThisIsAVeryLongASCIIString (empty) (empty) ThisIsA ThisIsAVeryLongASCIIString 
(empty) (empty) ThisIsAVeryLongASCIIString IsA (empty) (empty)
+(empty) (empty) (empty) (empty) (empty) (empty) (empty) (empty) (empty) 
(empty) (empty) (empty) (empty) (empty)
+NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
\ No newline at end of file


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@datafusion.apache.org
For additional commands, e-mail: commits-h...@datafusion.apache.org