GV

GeezerVison

The World Wide Web, restored to 800 pixels of glory.

Home|About|Gaming cabinet|Archives|How it works|Starter sites

On this site

  • Sign in
  • ActionsCI/CD and automation
  • CodespacesCloud development environments
  • All Features
  • Documentation
  • Training
  • Certifications
  • Build with Copilot
  • Customize Copilot
  • Integrations
  • Trending repos
  • GitHub Sponsors
  • Developer articles
  • Enterprises

This view

  • Original page
  • New address

GeezerVison is showing github.com — [SQL] Add the STARTSWITH and ENDSWITH functions · feldera/feldera@111d1cd

File tree

Expand file treeCollapse file tree
Open diff view settings
Filter options
  • crates/sqllib/src
    • binary.rs
    • string.rs
  • docs.feldera.com/docs/sql
    • binary.md
    • function-index.md
    • string.md
  • sql-to-dbsp-compiler/SQL-compiler/src
    • main/java/org/dbsp/sqlCompiler/compiler/frontend
      • ExpressionCompiler.java
      • calciteCompiler/functions
        • CalciteFunctions.java
    • test/java/org/dbsp/sqlCompiler/compiler/sql/functions
      • FunctionsTest.java
Expand file treeCollapse file tree
Open diff view settings
Collapse file

‎crates/sqllib/src/binary.rs‎

Copy file name to clipboardExpand all lines: crates/sqllib/src/binary.rs
+14Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -509,6 +509,20 @@ pub fn binary_position__(needle: ByteArray, haystack: ByteArray) -> i32 {
509509

510510
some_function2!(binary_position, ByteArray, ByteArray, i32);
511511

512+
#[doc(hidden)]
513+
pub fn binary_starts_with__(value: ByteArray, prefix: ByteArray) -> bool {
514+
value.as_slice().starts_with(prefix.as_slice())
515+
}
516+
517+
some_function2!(binary_starts_with, ByteArray, ByteArray, bool);
518+
519+
#[doc(hidden)]
520+
pub fn binary_ends_with__(value: ByteArray, suffix: ByteArray) -> bool {
521+
value.as_slice().ends_with(suffix.as_slice())
522+
}
523+
524+
some_function2!(binary_ends_with, ByteArray, ByteArray, bool);
525+
512526
#[doc(hidden)]
513527
pub fn binary_substring2__(source: ByteArray, left: i32) -> ByteArray {
514528
// SQL indexing starts at 1
Collapse file

‎crates/sqllib/src/string.rs‎

Copy file name to clipboardExpand all lines: crates/sqllib/src/string.rs
+14Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -383,6 +383,20 @@ pub fn position__(needle: SqlString, haystack: SqlString) -> i32 {
383383

384384
some_function2!(position, SqlString, SqlString, i32);
385385

386+
#[doc(hidden)]
387+
pub fn starts_with__(value: SqlString, prefix: SqlString) -> bool {
388+
value.str().starts_with(prefix.str())
389+
}
390+
391+
some_function2!(starts_with, SqlString, SqlString, bool);
392+
393+
#[doc(hidden)]
394+
pub fn ends_with__(value: SqlString, suffix: SqlString) -> bool {
395+
value.str().ends_with(suffix.str())
396+
}
397+
398+
some_function2!(ends_with, SqlString, SqlString, bool);
399+
386400
#[doc(hidden)]
387401
pub fn char_length_(value: SqlString) -> i32 {
388402
value.str().chars().count() as i32
Collapse file

‎docs.feldera.com/docs/sql/binary.md‎

Copy file name to clipboardExpand all lines: docs.feldera.com/docs/sql/binary.md
+10Lines changed: 10 additions & 0 deletions
  • Display the source diff
  • Display the rich diff
Original file line numberDiff line numberDiff line change
@@ -51,6 +51,11 @@ aggregation functions `BIT_AND`, `BIT_OR`, and `BIT_XOR`.
5151
<td>Concatenation of binary values</td>
5252
<td><code>x'ab' || x'cd'</code> => <code>x'ABCD'</code></td>
5353
</tr>
54+
<tr>
55+
<td><a id="endswith"></a><code>ENDSWITH</code>(binary, suffix)</td>
56+
<td>Returns <code>TRUE</code> if the bytes of <code>binary</code> end with the bytes of <code>suffix</code>. An empty <code>suffix</code> gives <code>TRUE</code>. If any argument is <code>NULL</code>, returns <code>NULL</code>.</td>
57+
<td><code>ENDSWITH(x'abcdef', x'ef')</code> => <code>TRUE</code></td>
58+
</tr>
5459
<tr>
5560
<td><a id="gunzip"></a><code>GUNZIP</code>(binary)</td>
5661
<td>
@@ -99,6 +104,11 @@ aggregation functions `BIT_AND`, `BIT_OR`, and `BIT_XOR`.
99104
<td>Returns last <code>count</code> bytes in the byte array. If any argument is <code>NULL</code>, return <code>NULL</code>.</td>
100105
<td><code>right(x'abcdef', 2)</code> => <code>cdef</code></td>
101106
</tr>
107+
<tr>
108+
<td><a id="startswith"></a><code>STARTSWITH</code>(binary, prefix)</td>
109+
<td>Returns <code>TRUE</code> if the bytes of <code>binary</code> start with the bytes of <code>prefix</code>. An empty <code>prefix</code> gives <code>TRUE</code>. If any argument is <code>NULL</code>, returns <code>NULL</code>.</td>
110+
<td><code>STARTSWITH(x'abcdef', x'ab')</code> => <code>TRUE</code></td>
111+
</tr>
102112
<tr>
103113
<td><a id="substring"></a><code>SUBSTRING</code>(binary FROM integer)</td>
104114
<td>Generate a substring of binary starting at the given offset in bytes. The first offset is 1. If the start position integer is less than 1, it is treated as 1</td>
Collapse file

‎docs.feldera.com/docs/sql/function-index.md‎

Copy file name to clipboardExpand all lines: docs.feldera.com/docs/sql/function-index.md
+2Lines changed: 2 additions & 0 deletions
  • Display the source diff
  • Display the rich diff
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,7 @@
104104
* `DOW`: [datetime](datetime.md#date_dayofweek), [datetime](datetime.md#timestamp_dayofweek)
105105
* `DOY`: [datetime](datetime.md#doy)
106106
* `ELEMENT`: [array](array.md#element)
107+
* `ENDSWITH`: [string](string.md#endswith), [binary](binary.md#endswith)
107108
* `EQUALS`: [datetime](datetime.md#equals)
108109
* `ESCAPE`: [string](string.md#like)
109110
* `EVERY` (aggregate): [aggregates](aggregates.md#every)
@@ -247,6 +248,7 @@
247248
* `SPLIT`: [string](string.md#split)
248249
* `SPLIT_PART`: [string](string.md#split_part)
249250
* `SQRT`: [float](float.md#sqrt)
251+
* `STARTSWITH`: [string](string.md#startswith), [binary](binary.md#startswith)
250252
* `STDDEV` (aggregate): [aggregates](aggregates.md#stddev)
251253
* `STDDEV_POP` (aggregate): [aggregates](aggregates.md#stddev_pop)
252254
* `STDDEV_SAMP` (aggregate): [aggregates](aggregates.md#stddev_samp)
Collapse file

‎docs.feldera.com/docs/sql/string.md‎

Copy file name to clipboardExpand all lines: docs.feldera.com/docs/sql/string.md
+10Lines changed: 10 additions & 0 deletions
  • Display the source diff
  • Display the rich diff
Original file line numberDiff line numberDiff line change
@@ -144,6 +144,11 @@ example.
144144
<td>String concatenation with separator <code>sep</code>. Can have any number of arguments. <code>sep</code> is intercalated between all strings. If <code>sep</code> is <code>NULL</code> result is <code>NULL</code>. Other <code>NULL</code> arguments are ignored.</td>
145145
<td><code>CONCAT_WS(',', 'Post', 'greSQL', NULL, '1')</code> => <code>Post,greSQL,1</code></td>
146146
</tr>
147+
<tr>
148+
<td><a id="endswith"></a><code>ENDSWITH(string, suffix)</code></td>
149+
<td>Returns <code>TRUE</code> if <code>string</code> ends with <code>suffix</code>. The comparison is exact: it is case-sensitive and it does not normalize Unicode characters. An empty <code>suffix</code> gives <code>TRUE</code>. If any argument is <code>NULL</code>, returns <code>NULL</code>.</td>
150+
<td><code>ENDSWITH('Feldera', 'era')</code> => <code>TRUE</code></td>
151+
</tr>
147152
<tr>
148153
<td><a id="initcap"></a><code>INITCAP ( string )</code></td>
149154
<td>Converts the first letter of each word in <code>string</code> to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.</td>
@@ -243,6 +248,11 @@ example.
243248
<code>SPLIT_PART('a|b|c|', '|', 5)</code> => <code>''</code>
244249
</td>
245250
</tr>
251+
<tr>
252+
<td><a id="startswith"></a><code>STARTSWITH(string, prefix)</code></td>
253+
<td>Returns <code>TRUE</code> if <code>string</code> starts with <code>prefix</code>. The comparison is exact: it is case-sensitive and it does not normalize Unicode characters. An empty <code>prefix</code> gives <code>TRUE</code>. If any argument is <code>NULL</code>, returns <code>NULL</code>.</td>
254+
<td><code>STARTSWITH('Feldera', 'Fel')</code> => <code>TRUE</code></td>
255+
</tr>
246256
<tr>
247257
<td><a id="substr"></a><code>SUBSTR (</code> string, start <code> [ ,</code> length <code>] )</code></td>
248258
<td>Extracts the substring of <code>string</code> starting at the <code>start</code>'th character, and stopping after <code>length</code> characters if the value is specified. If <code>start</code> is negative, the first character is chosen counting backwards from the end of <code>string</code>. If <code>length</code> is negative the empty string is returned. The index of the first character is 1.</td>
Collapse file

‎sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/ExpressionCompiler.java‎

Copy file name to clipboardExpand all lines: sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/ExpressionCompiler.java
+15-5Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -784,13 +784,19 @@ static DBSPExpression compileFunction(
784784
* @param expectedArgCount A list containing all known possible argument counts.
785785
*/
786786
static DBSPExpression compileStrictFunction(
787-
boolean adjustNullability, RexCall call, CalciteObject node, DBSPType resultType,
787+
boolean adjustNullability, String baseName, CalciteObject node, DBSPType resultType,
788788
List<DBSPExpression> ops, Integer... expectedArgCount) {
789789
for (DBSPExpression op: ops) {
790790
if (op.is(DBSPLiteral.class) && op.to(DBSPLiteral.class).isNull())
791791
return resultType.none();
792792
}
793-
return compileFunction(adjustNullability, call, node, resultType, ops, expectedArgCount);
793+
return compileFunction(adjustNullability, baseName, node, resultType, ops, expectedArgCount);
794+
}
795+
796+
static DBSPExpression compileStrictFunction(
797+
boolean adjustNullability, RexCall call, CalciteObject node, DBSPType resultType,
798+
List<DBSPExpression> ops, Integer... expectedArgCount) {
799+
return compileStrictFunction(adjustNullability, getCallName(call), node, resultType, ops, expectedArgCount);
794800
}
795801

796802
static DBSPExpression compileStrictFunction(
@@ -1974,15 +1980,19 @@ else if (arg0Type.is(DBSPTypeMap.class))
19741980
ops.set(1, this.makeRegex(node, ops.get(1)));
19751981
return compileFunction(call, node, type, ops, 2);
19761982
}
1977-
case POSITION: {
1983+
case POSITION:
1984+
case STARTS_WITH:
1985+
case ENDS_WITH: {
1986+
// Also called for STARTSWITH and ENDSWITH
19781987
validateArgCount(node, operationName, ops.size(), 2);
19791988
String module_prefix = "";
1980-
if (ops.get(0).type.is(DBSPTypeBinary.class)) {
1989+
if (Linq.any(ops, op -> op.type.is(DBSPTypeBinary.class))) {
19811990
module_prefix = "binary_";
19821991
} else {
19831992
this.ensureString(ops, 0);
1993+
this.ensureString(ops, 1);
19841994
}
1985-
return compileFunction(module_prefix + getCallName(call), node, type, ops, 2);
1995+
return compileStrictFunction(true, module_prefix + getCallName(call), node, type, ops, 2);
19861996
}
19871997
case ARRAY_JOIN:
19881998
case ARRAY_TO_STRING: {
Collapse file

‎sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/calciteCompiler/functions/CalciteFunctions.java‎

Copy file name to clipboardExpand all lines: sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/calciteCompiler/functions/CalciteFunctions.java
+4Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -586,6 +586,10 @@ record Func(SqlOperator function, String functionName, SqlLibrary library,
586586
runtime_aggtest/illarg_tests/test_{str_bin_type_fn,str_unicode_fn}.py|
587587
runtime_aggtest/unsigned_int_tests/test_un_int_supported_functions.py
588588
""", false),
589+
new Func(SqlLibraryOperators.STARTSWITH, "STARTSWITH", SqlLibrary.SNOWFLAKE, "string#startswith,binary#startswith",
590+
FunctionDocumentation.NO_FILE, false),
591+
new Func(SqlLibraryOperators.ENDSWITH, "ENDSWITH", SqlLibrary.SNOWFLAKE, "string#endswith,binary#endswith",
592+
FunctionDocumentation.NO_FILE, false),
589593
new Func(SqlLibraryOperators.ILIKE, "ILIKE", SqlLibrary.POSTGRESQL, "string#ilike", FunctionDocumentation.NO_FILE, false),
590594
new Func(SqlLibraryOperators.NOT_ILIKE, "NOT ILIKE", SqlLibrary.POSTGRESQL, "string#ilike", FunctionDocumentation.NO_FILE, false),
591595
new Func(SqlLibraryOperators.RLIKE, "RLIKE", SqlLibrary.MYSQL, "string#rlike",
Collapse file

‎sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/functions/FunctionsTest.java‎

Copy file name to clipboardExpand all lines: sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/functions/FunctionsTest.java
+70Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2081,6 +2081,76 @@ public void testConstantRegexUsesRustSemantics() {
20812081
);
20822082
}
20832083

2084+
@Test
2085+
public void testStartsEndsWith() {
2086+
// The optimized circuit folds the calls without characters above U+FFFF in Calcite,
2087+
// the unoptimized one evaluates all of them in Rust.
2088+
// Validated with Calcite's SqlFunctions.startsWith and endsWith.
2089+
this.qs("""
2090+
SELECT STARTSWITH('', ''), STARTSWITH('abc', ''), STARTSWITH('', 'a'), STARTSWITH('ab', 'abc'),
2091+
STARTSWITH('Abc', 'a'), STARTSWITH('abc ', 'abc'), STARTSWITH('abc', 'abc ');
2092+
a | b | c | d | e | f | g
2093+
---------------------------------------------------
2094+
true | true | false | false | false | true | false
2095+
(1 row)
2096+
2097+
SELECT ENDSWITH('', ''), ENDSWITH('abc', ''), ENDSWITH('', 'a'), ENDSWITH('bc', 'abc'),
2098+
ENDSWITH('abC', 'c'), ENDSWITH(' abc', 'abc'), ENDSWITH('abc', ' abc');
2099+
a | b | c | d | e | f | g
2100+
---------------------------------------------------
2101+
true | true | false | false | false | true | false
2102+
(1 row)
2103+
2104+
SELECT STARTSWITH('🐍x', '🐍'), ENDSWITH('x🐍', '🐍'), STARTSWITH(U&'e\\0301', 'e'),
2105+
ENDSWITH(U&'e\\0301', U&'\\0301'), STARTSWITH(U&'\\00E9', 'e'),
2106+
STARTSWITH(U&'a\\0000b', U&'a\\0000'), ENDSWITH(U&'a\\0000b', U&'\\0000b');
2107+
a | b | c | d | e | f | g
2108+
-------------------------------------------------
2109+
true | true | true | true | false | true | true
2110+
(1 row)
2111+
2112+
SELECT STARTSWITH(CAST('ab' AS CHAR(3)), 'ab '), ENDSWITH(CAST('ab' AS CHAR(3)), 'ab'),
2113+
ENDSWITH(CAST('ab' AS CHAR(3)), 'b ');
2114+
a | b | c
2115+
-------------------
2116+
true | false | true
2117+
(1 row)
2118+
2119+
SELECT STARTSWITH(x'', x''), STARTSWITH(x'01', x''), STARTSWITH(x'', x'01'), ENDSWITH(x'01', x'0102'),
2120+
ENDSWITH(CAST(x'01' AS BINARY(3)), x'00'), STARTSWITH(CAST(x'01' AS BINARY(3)), x'0100');
2121+
a | b | c | d | e | f
2122+
--------------------------------------------
2123+
true | true | false | false | true | true
2124+
(1 row)
2125+
2126+
SELECT STARTSWITH(NULL, 'a'), ENDSWITH('a', NULL), STARTSWITH(x'01', NULL), ENDSWITH(NULL, x'01');
2127+
a | b | c | d
2128+
-------------------------
2129+
NULL | NULL | NULL | NULL
2130+
(1 row)""");
2131+
var ccs = this.getCCS("""
2132+
CREATE TABLE T(s VARCHAR, p VARCHAR, b VARBINARY, q VARBINARY);
2133+
CREATE VIEW V AS SELECT STARTSWITH(s, p), ENDSWITH(s, p), STARTSWITH(b, q), ENDSWITH(b, q),
2134+
STARTSWITH(b, NULL) FROM T;""");
2135+
ccs.step("INSERT INTO T VALUES(NULL, 'a', NULL, x'01'), ('ab', 'a', x'0102', x'01')", """
2136+
a | b | c | d | e | weight
2137+
---------------------------------------------
2138+
NULL | NULL | NULL | NULL | NULL | 1
2139+
true | false | true | false | NULL | 1""");
2140+
}
2141+
2142+
@Test
2143+
public void testPositionNullLiteral() {
2144+
var ccs = this.getCCS("""
2145+
CREATE TABLE T(s VARCHAR, b VARBINARY);
2146+
CREATE VIEW V AS SELECT POSITION(s IN NULL), POSITION(NULL IN s), POSITION(b IN NULL),
2147+
POSITION(NULL IN b), POSITION(s IN 'abc'), POSITION(b IN x'0102') FROM T;""");
2148+
ccs.step("INSERT INTO T VALUES('b', x'02')", """
2149+
a | b | c | d | e | f | weight
2150+
---------------------------------------------
2151+
NULL | NULL | NULL | NULL | 2 | 2 | 1""");
2152+
}
2153+
20842154
@Test
20852155
public void testSequence() {
20862156
this.qst("""

0 commit comments

Comments
0 (0)
You are visitor 01402520 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.