You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@ignite.apache.org by "korlov42 (via GitHub)" <gi...@apache.org> on 2025/11/17 16:08:00 UTC
[PR] IGNITE-27077 Sql. Support prefix LIKE condition in search bounds [ignite-3]
korlov42 opened a new pull request, #6996:
URL: https://github.com/apache/ignite-3/pull/6996
https://issues.apache.org/jira/browse/IGNITE-27077
This patch introduces support for LIKE predicate in search bounds. The main idea is to derive prefix string from given pattern (I will refer to this as `lowerBound`), compute smallest prefix at most of the same length which is lexicographically greater than derived one (I will refer to this as `upperBound`), and compose range condition out of these two patterns. For example, predicate like `col LIKE 'foo%'` will be converted to `col >= 'foo' AND col < 'fop'`.
The main pitfall here is that it's not always possible to compute `upperBound`. Assume that prefix already biggest possible string (consists of `Character.MAX_VALUE` only). In this case, on indexes with certain collations we should generate range bounds with open upper bound: `col >= lowerBound`. Since index bounds are created during optimization time and actual values are not known, conditional bounds were introduced.
-----
Thank you for submitting the pull request.
To streamline the review process of the patch and ensure better code quality
we ask both an author and a reviewer to verify the following:
### The Review Checklist
- [ ] **Formal criteria:** TC status, codestyle, mandatory documentation. Also make sure to complete the following:
\- There is a single JIRA ticket related to the pull request.
\- The web-link to the pull request is attached to the JIRA ticket.
\- The JIRA ticket has the Patch Available state.
\- The description of the JIRA ticket explains WHAT was made, WHY and HOW.
\- The pull request title is treated as the final commit message. The following pattern must be used: IGNITE-XXXX Change summary where XXXX - number of JIRA issue.
- [ ] **Design:** new code conforms with the design principles of the components it is added to.
- [ ] **Patch quality:** patch cannot be split into smaller pieces, its size must be reasonable.
- [ ] **Code quality:** code is clean and readable, necessary developer documentation is added if needed.
- [ ] **Tests code quality:** test set covers positive/negative scenarios, happy/edge cases. Tests are effective in terms of execution time and resources.
### Notes
- [Apache Ignite Coding Guidelines](https://cwiki.apache.org/confluence/display/IGNITE/Java+Code+Style+Guide)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@ignite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] IGNITE-27077 Sql. Support prefix LIKE condition in search bounds [ignite-3]
Posted by "korlov42 (via GitHub)" <gi...@apache.org>.
korlov42 commented on code in PR #6996:
URL: https://github.com/apache/ignite-3/pull/6996#discussion_r2541559930
##########
modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/IgniteSqlFunctions.java:
##########
@@ -761,4 +761,196 @@ private static long divide(long p, long q, RoundingMode mode) {
return increment ? div + signum : div;
}
+
+ /**
+ * Computes the next lexicographically greater string by incrementing the rightmost character that is not at its maximum value.
+ *
+ * <p>This method is primarily used in conjunction with {@link #findPrefix(String, String)} to create upper bounds for efficient
+ * range scans. Given a prefix extracted from a LIKE pattern, this method produces the smallest string that is lexicographically greater
+ * than the prefix, enabling queries like {@code WHERE key >= prefix AND key < nextGreaterPrefix}.
+ *
+ * <p>If all characters are at maximum value ({@value Character#MAX_VALUE}), the method returns {@code null} because no greater string
+ * exists within the Unicode character space.
+ *
+ * <p>Basic examples:
+ * <pre>
+ * nextGreaterPrefix("abc") → "abd"
+ * nextGreaterPrefix("test") → "tesu"
+ * nextGreaterPrefix("user") → "uses"
+ * nextGreaterPrefix("a") → "b"
+ * nextGreaterPrefix("z") → "{"
+ * nextGreaterPrefix("9") → ":"
+ * </pre>
+ *
+ * <p>Examples with maximum values:
+ * <pre>
+ * nextGreaterPrefix("abc\uFFFF") → "abd" (skip max char, increment 'c')
+ * nextGreaterPrefix("a\uFFFF\uFFFF") → "b" (skip two max chars, increment 'a')
+ * nextGreaterPrefix("test\uFFFF") → "tesu" (skip max char, increment 't')
+ * nextGreaterPrefix("\uFFFF") → null (cannot increment)
+ * nextGreaterPrefix("\uFFFF\uFFFF") → null (all chars at max)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * nextGreaterPrefix(null) → null (null input)
+ * nextGreaterPrefix("") → null (empty string, no chars to increment)
+ * nextGreaterPrefix("abc\uFFFF\uFFFF") → "abd" (truncates all trailing max values)
+ * </pre>
+ *
+ * <p>Properties:
+ * <ul>
+ * <li>The result is always the <em>smallest</em> string greater than the input</li>
+ * <li>For any valid input {@code s}, {@code nextGreaterPrefix(s).compareTo(s) > 0}</li>
+ * <li>The result may be shorter than the input (trailing max-value chars are removed)</li>
+ * <li>Trailing {@code \uFFFF} characters are effectively truncated</li>
+ * </ul>
+ *
+ * @param prefix The string to increment. If {@code null}, returns {@code null}.
+ * @return A next lexicographically greater string, or {@code null} if the input is {@code null}, empty, or consists entirely of
+ * maximum-value characters ({@code \uFFFF}). The returned string is guaranteed to be the smallest string greater than the
+ * input.
+ */
+ public static @Nullable String nextGreaterPrefix(@Nullable String prefix) {
+ if (prefix == null) {
+ return null;
+ }
+
+ // Try to increment characters from right to left
+ for (int i = prefix.length() - 1; i >= 0; i--) {
+ char c = prefix.charAt(i);
+
+ // Check if we can increment this character
+ if (c < Character.MAX_VALUE) {
+ // Increment and return
+ return prefix.substring(0, i) + ((char) (c + 1));
+ }
+
+ // This character is already max, continue to previous character
+ }
+
+ // All characters are at maximum value
+ return null; // Given prefix is the greatest.
+ }
+
+ /**
+ * Extracts the literal prefix from a SQL LIKE pattern by identifying all characters before the first unescaped wildcard.
+ *
+ * <p>This method processes SQL LIKE patterns containing wildcards ({@code %} for any sequence, {@code _} for single character) and
+ * returns the constant prefix that can be used for optimized range scans.
+ *
+ * <p>When an escape character is provided, it allows wildcards to be treated as literal characters. The escape character itself can
+ * also be escaped to include it literally in the prefix.
+ *
+ * <p>Examples without escape character:
+ * <pre>
+ * findPrefix("user%", null) → "user"
+ * findPrefix("admin_123", null) → "admin"
+ * findPrefix("test", null) → "test"
+ * findPrefix("%anything", null) → ""
+ * findPrefix("_anything", null) → ""
+ * </pre>
+ *
+ * <p>Examples with escape character:
+ * <pre>
+ * findPrefix("user\\%", "\\") → "user%" (% is literal)
+ * findPrefix("admin\\_", "\\") → "admin_" (_ is literal)
+ * findPrefix("path\\\\dir", "\\") → "path\\dir" (\\ escapes to \)
+ * findPrefix("test\\%end%", "\\") → "test%end" (first % escaped, second is wildcard)
+ * findPrefix("a\\%b\\%c", "\\") → "a%b%c" (both % are literal)
+ * findPrefix("value^%", "^") → "value%" (different escape char)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * findPrefix(null, null) → null (null pattern)
+ * findPrefix("test", "") → null (invalid escape length)
+ * findPrefix("test", "ab") → null (invalid escape length)
+ * findPrefix("", null) → "" (empty pattern)
+ * findPrefix("test\\", "\\") → "test\\" (escape at end treated as literal)
+ * </pre>
+ *
+ * @param pattern The SQL LIKE pattern to analyze; may contain wildcards {@code %} and {@code _}. If {@code null}, returns
+ * {@code null}.
+ * @param escape The escape character used to treat wildcards as literals; must be exactly one character long if provided. Use
+ * {@code null} if no escape character is defined.
+ * @return A literal prefix of the pattern before the first unescaped wildcard, with all escape sequences resolved to their literal
+ * characters. Returns an empty string if the pattern starts with a wildcard. Returns {@code null} if the pattern is
+ * {@code null} or if the escape parameter is invalid (not exactly 1 character).
+ */
+ public static @Nullable String findPrefix(@Nullable String pattern, @Nullable String escape) {
+ if (pattern == null || (escape != null && escape.length() != 1)) {
Review Comment:
> Can escape parameter be of Character type?
I don't think so.
changed the code so now assertion is thrown in case of invalid escape character.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@ignite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] IGNITE-27077 Sql. Support prefix LIKE condition in search bounds [ignite-3]
Posted by "korlov42 (via GitHub)" <gi...@apache.org>.
korlov42 commented on code in PR #6996:
URL: https://github.com/apache/ignite-3/pull/6996#discussion_r2541554204
##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItPrefixLikeToRangeScanConversionTest.java:
##########
@@ -0,0 +1,211 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static org.apache.ignite.internal.sql.engine.util.QueryChecker.containsIndexScan;
+import static org.apache.ignite.internal.sql.engine.util.QueryChecker.containsSubPlan;
+import static org.apache.ignite.internal.sql.engine.util.QueryChecker.containsTableScan;
+
+import com.google.common.collect.Streams;
+import java.util.stream.Stream;
+import org.apache.ignite.internal.sql.BaseSqlIntegrationTest;
+import org.hamcrest.Matcher;
+import org.hamcrest.Matchers;
+import org.jetbrains.annotations.NotNull;
+import org.jetbrains.annotations.Nullable;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.Arguments;
+import org.junit.jupiter.params.provider.MethodSource;
+
+/**
+ * Set of tests to validation conversion of prefix LIKE predicate to range scan.
+ */
+public class ItPrefixLikeToRangeScanConversionTest extends BaseSqlIntegrationTest {
+ private static final String QUERY_BASE = "SELECT val FROM t WHERE int_val = 1 AND val LIKE ?";
+ private static String[] indexNames;
+
+ @BeforeAll
+ static void setupSchema() {
+ sqlScript("CREATE TABLE t(id INT PRIMARY KEY, int_val INT, val VARCHAR);"
+ + "CREATE INDEX t_val_asc_nulls_first_idx ON t (val ASC NULLS FIRST);"
+ + "CREATE INDEX t_val_asc_nulls_last_idx ON t (val ASC NULLS LAST);"
+ + "CREATE INDEX t_val_desc_nulls_first_idx ON t (val DESC NULLS FIRST);"
+ + "CREATE INDEX t_val_desc_nulls_last_idx ON t (val DESC NULLS LAST);"
+ + "CREATE INDEX t_int_val_asc_val_asc_nulls_first_idx ON t (int_val ASC, val ASC NULLS FIRST);"
+ + "CREATE INDEX t_int_val_asc_val_asc_nulls_last_idx ON t (int_val ASC, val ASC NULLS LAST);"
+ + "CREATE INDEX t_int_val_asc_val_desc_nulls_first_idx ON t (int_val ASC, val DESC NULLS FIRST);"
+ + "CREATE INDEX t_int_val_asc_val_desc_nulls_last_idx ON t (int_val ASC, val DESC NULLS LAST);"
+ );
+
+ indexNames = new String[] {
+ "t_val_asc_nulls_first_idx",
+ "t_val_asc_nulls_last_idx",
+ "t_val_desc_nulls_first_idx",
+ "t_val_desc_nulls_last_idx",
+ "t_int_val_asc_val_asc_nulls_first_idx",
+ "t_int_val_asc_val_asc_nulls_last_idx",
+ "t_int_val_asc_val_desc_nulls_first_idx",
+ "t_int_val_asc_val_desc_nulls_last_idx"
+ };
+
+ String[] values = {
+ "foo", "fooa", "food", "fooz", "fooaa", "fooda", "fooza", "foo_b", "foo_bar", "foo%b", "foo%bar",
+ "fop", "fopa",
+ null, null, null,
+ threeMaxCharString(), threeMaxCharString() + "a", threeMaxCharString() + "b", threeMaxCharString() + "aa"
+ };
+
+ for (int i = 0; i < values.length; i++) {
+ sql("INSERT INTO t VALUES (?, 1, ?)", i, values[i]);
+ }
+ }
+
+ private static Stream<Arguments> indexNames() {
+ return Streams.concat(
+ Stream.of((String) null),
+ Stream.of(indexNames)
+ ).map(Arguments::of);
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void simplePrefixMatchesAll(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam("foo%")
+ .matches(planMather)
+ .returns("foo")
+ .returns("fooa")
+ .returns("food")
+ .returns("fooz")
+ .returns("fooaa")
+ .returns("fooda")
+ .returns("fooza")
+ .returns("foo_b")
+ .returns("foo_bar")
+ .returns("foo%b")
+ .returns("foo%bar")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void simplePrefixMatchesOne(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam("foo_")
+ .matches(planMather)
+ .returns("fooa")
+ .returns("food")
+ .returns("fooz")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void simplePrefixMatchesAllEscaped(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE + " ESCAPE '^'", indexName))
+ .withParam("foo^%%")
+ .matches(planMather)
+ .returns("foo%b")
+ .returns("foo%bar")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void simplePrefixMatchesOneEscaped(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE + " ESCAPE '^'", indexName))
+ .withParam("foo^__")
+ .matches(planMather)
+ .returns("foo_b")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void nullPattern(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam(null)
+ .matches(planMather)
+ .returnNothing()
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void maxCharPrefixMatchesAll(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam(threeMaxCharString() + "%")
+ .matches(planMather)
+ .returns(threeMaxCharString())
+ .returns(threeMaxCharString() + "a")
+ .returns(threeMaxCharString() + "b")
+ .returns(threeMaxCharString() + "aa")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void maxCharPrefixMatchesOne(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam(threeMaxCharString() + "_")
+ .matches(planMather)
+ .returns(threeMaxCharString() + "a")
+ .returns(threeMaxCharString() + "b")
+ .check();
+ }
+
+ private static @NotNull Matcher<String> indexOrTableScanMather(@Nullable String indexName) {
+ return indexName == null
+ ? containsTableScan("PUBLIC", "T")
+ : Matchers.allOf(
+ containsIndexScan("PUBLIC", "T", indexName.toUpperCase()),
+ containsSubPlan("searchBounds")
+ );
+ }
+
+ private static String threeMaxCharString() {
+ return ("" + Character.MAX_VALUE).repeat(3);
+ }
+
+ private static String appendForceIndexHint(String query, @Nullable String indexName) {
+ if (indexName == null) {
+ query = query.replace("SELECT", "SELECT /*+ no_index */");
+ } else {
+ query = query.replace("SELECT", "SELECT /*+ force_index(" + indexName + ") */");
+ }
+
+ System.out.println(query);
Review Comment:
fixed, thanks
##########
modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/IgniteSqlFunctions.java:
##########
@@ -761,4 +761,196 @@ private static long divide(long p, long q, RoundingMode mode) {
return increment ? div + signum : div;
}
+
+ /**
+ * Computes the next lexicographically greater string by incrementing the rightmost character that is not at its maximum value.
+ *
+ * <p>This method is primarily used in conjunction with {@link #findPrefix(String, String)} to create upper bounds for efficient
+ * range scans. Given a prefix extracted from a LIKE pattern, this method produces the smallest string that is lexicographically greater
+ * than the prefix, enabling queries like {@code WHERE key >= prefix AND key < nextGreaterPrefix}.
+ *
+ * <p>If all characters are at maximum value ({@value Character#MAX_VALUE}), the method returns {@code null} because no greater string
+ * exists within the Unicode character space.
+ *
+ * <p>Basic examples:
+ * <pre>
+ * nextGreaterPrefix("abc") → "abd"
+ * nextGreaterPrefix("test") → "tesu"
+ * nextGreaterPrefix("user") → "uses"
+ * nextGreaterPrefix("a") → "b"
+ * nextGreaterPrefix("z") → "{"
+ * nextGreaterPrefix("9") → ":"
+ * </pre>
+ *
+ * <p>Examples with maximum values:
+ * <pre>
+ * nextGreaterPrefix("abc\uFFFF") → "abd" (skip max char, increment 'c')
+ * nextGreaterPrefix("a\uFFFF\uFFFF") → "b" (skip two max chars, increment 'a')
+ * nextGreaterPrefix("test\uFFFF") → "tesu" (skip max char, increment 't')
+ * nextGreaterPrefix("\uFFFF") → null (cannot increment)
+ * nextGreaterPrefix("\uFFFF\uFFFF") → null (all chars at max)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * nextGreaterPrefix(null) → null (null input)
+ * nextGreaterPrefix("") → null (empty string, no chars to increment)
+ * nextGreaterPrefix("abc\uFFFF\uFFFF") → "abd" (truncates all trailing max values)
+ * </pre>
+ *
+ * <p>Properties:
+ * <ul>
+ * <li>The result is always the <em>smallest</em> string greater than the input</li>
+ * <li>For any valid input {@code s}, {@code nextGreaterPrefix(s).compareTo(s) > 0}</li>
+ * <li>The result may be shorter than the input (trailing max-value chars are removed)</li>
+ * <li>Trailing {@code \uFFFF} characters are effectively truncated</li>
+ * </ul>
+ *
+ * @param prefix The string to increment. If {@code null}, returns {@code null}.
+ * @return A next lexicographically greater string, or {@code null} if the input is {@code null}, empty, or consists entirely of
+ * maximum-value characters ({@code \uFFFF}). The returned string is guaranteed to be the smallest string greater than the
+ * input.
+ */
+ public static @Nullable String nextGreaterPrefix(@Nullable String prefix) {
+ if (prefix == null) {
+ return null;
+ }
+
+ // Try to increment characters from right to left
+ for (int i = prefix.length() - 1; i >= 0; i--) {
+ char c = prefix.charAt(i);
+
+ // Check if we can increment this character
+ if (c < Character.MAX_VALUE) {
+ // Increment and return
+ return prefix.substring(0, i) + ((char) (c + 1));
+ }
+
+ // This character is already max, continue to previous character
+ }
+
+ // All characters are at maximum value
Review Comment:
added
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@ignite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] IGNITE-27077 Sql. Support prefix LIKE condition in search bounds [ignite-3]
Posted by "zstan (via GitHub)" <gi...@apache.org>.
zstan commented on PR #6996:
URL: https://github.com/apache/ignite-3/pull/6996#issuecomment-3552491444
despite it works perfectly well, it has low test coverage with prefixes, smth. like : .withParam("_%foo") and so on
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@ignite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] IGNITE-27077 Sql. Support prefix LIKE condition in search bounds [ignite-3]
Posted by "AMashenkov (via GitHub)" <gi...@apache.org>.
AMashenkov commented on code in PR #6996:
URL: https://github.com/apache/ignite-3/pull/6996#discussion_r2537820057
##########
modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/IgniteSqlFunctions.java:
##########
@@ -761,4 +761,196 @@ private static long divide(long p, long q, RoundingMode mode) {
return increment ? div + signum : div;
}
+
+ /**
+ * Computes the next lexicographically greater string by incrementing the rightmost character that is not at its maximum value.
+ *
+ * <p>This method is primarily used in conjunction with {@link #findPrefix(String, String)} to create upper bounds for efficient
+ * range scans. Given a prefix extracted from a LIKE pattern, this method produces the smallest string that is lexicographically greater
+ * than the prefix, enabling queries like {@code WHERE key >= prefix AND key < nextGreaterPrefix}.
+ *
+ * <p>If all characters are at maximum value ({@value Character#MAX_VALUE}), the method returns {@code null} because no greater string
+ * exists within the Unicode character space.
+ *
+ * <p>Basic examples:
+ * <pre>
+ * nextGreaterPrefix("abc") → "abd"
+ * nextGreaterPrefix("test") → "tesu"
+ * nextGreaterPrefix("user") → "uses"
+ * nextGreaterPrefix("a") → "b"
+ * nextGreaterPrefix("z") → "{"
+ * nextGreaterPrefix("9") → ":"
+ * </pre>
+ *
+ * <p>Examples with maximum values:
+ * <pre>
+ * nextGreaterPrefix("abc\uFFFF") → "abd" (skip max char, increment 'c')
+ * nextGreaterPrefix("a\uFFFF\uFFFF") → "b" (skip two max chars, increment 'a')
+ * nextGreaterPrefix("test\uFFFF") → "tesu" (skip max char, increment 't')
+ * nextGreaterPrefix("\uFFFF") → null (cannot increment)
+ * nextGreaterPrefix("\uFFFF\uFFFF") → null (all chars at max)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * nextGreaterPrefix(null) → null (null input)
+ * nextGreaterPrefix("") → null (empty string, no chars to increment)
+ * nextGreaterPrefix("abc\uFFFF\uFFFF") → "abd" (truncates all trailing max values)
+ * </pre>
+ *
+ * <p>Properties:
+ * <ul>
+ * <li>The result is always the <em>smallest</em> string greater than the input</li>
+ * <li>For any valid input {@code s}, {@code nextGreaterPrefix(s).compareTo(s) > 0}</li>
+ * <li>The result may be shorter than the input (trailing max-value chars are removed)</li>
+ * <li>Trailing {@code \uFFFF} characters are effectively truncated</li>
+ * </ul>
+ *
+ * @param prefix The string to increment. If {@code null}, returns {@code null}.
+ * @return A next lexicographically greater string, or {@code null} if the input is {@code null}, empty, or consists entirely of
+ * maximum-value characters ({@code \uFFFF}). The returned string is guaranteed to be the smallest string greater than the
+ * input.
+ */
+ public static @Nullable String nextGreaterPrefix(@Nullable String prefix) {
+ if (prefix == null) {
+ return null;
+ }
+
+ // Try to increment characters from right to left
+ for (int i = prefix.length() - 1; i >= 0; i--) {
+ char c = prefix.charAt(i);
+
+ // Check if we can increment this character
+ if (c < Character.MAX_VALUE) {
+ // Increment and return
+ return prefix.substring(0, i) + ((char) (c + 1));
+ }
+
+ // This character is already max, continue to previous character
+ }
+
+ // All characters are at maximum value
+ return null; // Given prefix is the greatest.
+ }
+
+ /**
+ * Extracts the literal prefix from a SQL LIKE pattern by identifying all characters before the first unescaped wildcard.
+ *
+ * <p>This method processes SQL LIKE patterns containing wildcards ({@code %} for any sequence, {@code _} for single character) and
+ * returns the constant prefix that can be used for optimized range scans.
+ *
+ * <p>When an escape character is provided, it allows wildcards to be treated as literal characters. The escape character itself can
+ * also be escaped to include it literally in the prefix.
+ *
+ * <p>Examples without escape character:
+ * <pre>
+ * findPrefix("user%", null) → "user"
+ * findPrefix("admin_123", null) → "admin"
+ * findPrefix("test", null) → "test"
+ * findPrefix("%anything", null) → ""
+ * findPrefix("_anything", null) → ""
+ * </pre>
+ *
+ * <p>Examples with escape character:
+ * <pre>
+ * findPrefix("user\\%", "\\") → "user%" (% is literal)
+ * findPrefix("admin\\_", "\\") → "admin_" (_ is literal)
+ * findPrefix("path\\\\dir", "\\") → "path\\dir" (\\ escapes to \)
+ * findPrefix("test\\%end%", "\\") → "test%end" (first % escaped, second is wildcard)
+ * findPrefix("a\\%b\\%c", "\\") → "a%b%c" (both % are literal)
+ * findPrefix("value^%", "^") → "value%" (different escape char)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * findPrefix(null, null) → null (null pattern)
+ * findPrefix("test", "") → null (invalid escape length)
+ * findPrefix("test", "ab") → null (invalid escape length)
+ * findPrefix("", null) → "" (empty pattern)
+ * findPrefix("test\\", "\\") → "test\\" (escape at end treated as literal)
+ * </pre>
+ *
+ * @param pattern The SQL LIKE pattern to analyze; may contain wildcards {@code %} and {@code _}. If {@code null}, returns
+ * {@code null}.
+ * @param escape The escape character used to treat wildcards as literals; must be exactly one character long if provided. Use
+ * {@code null} if no escape character is defined.
+ * @return A literal prefix of the pattern before the first unescaped wildcard, with all escape sequences resolved to their literal
+ * characters. Returns an empty string if the pattern starts with a wildcard. Returns {@code null} if the pattern is
+ * {@code null} or if the escape parameter is invalid (not exactly 1 character).
+ */
+ public static @Nullable String findPrefix(@Nullable String pattern, @Nullable String escape) {
+ if (pattern == null || (escape != null && escape.length() != 1)) {
Review Comment:
Can `escape` parameter be of Character type?
```suggestion
if (escape != null && escape.length() != 1) {
throw new IllegalArgumentException("Invalid escape character.");
}
if (pattern == null) {
```
##########
modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/IgniteSqlFunctions.java:
##########
@@ -761,4 +761,196 @@ private static long divide(long p, long q, RoundingMode mode) {
return increment ? div + signum : div;
}
+
+ /**
+ * Computes the next lexicographically greater string by incrementing the rightmost character that is not at its maximum value.
+ *
+ * <p>This method is primarily used in conjunction with {@link #findPrefix(String, String)} to create upper bounds for efficient
+ * range scans. Given a prefix extracted from a LIKE pattern, this method produces the smallest string that is lexicographically greater
+ * than the prefix, enabling queries like {@code WHERE key >= prefix AND key < nextGreaterPrefix}.
+ *
+ * <p>If all characters are at maximum value ({@value Character#MAX_VALUE}), the method returns {@code null} because no greater string
+ * exists within the Unicode character space.
+ *
+ * <p>Basic examples:
+ * <pre>
+ * nextGreaterPrefix("abc") → "abd"
+ * nextGreaterPrefix("test") → "tesu"
+ * nextGreaterPrefix("user") → "uses"
+ * nextGreaterPrefix("a") → "b"
+ * nextGreaterPrefix("z") → "{"
+ * nextGreaterPrefix("9") → ":"
+ * </pre>
+ *
+ * <p>Examples with maximum values:
+ * <pre>
+ * nextGreaterPrefix("abc\uFFFF") → "abd" (skip max char, increment 'c')
+ * nextGreaterPrefix("a\uFFFF\uFFFF") → "b" (skip two max chars, increment 'a')
+ * nextGreaterPrefix("test\uFFFF") → "tesu" (skip max char, increment 't')
+ * nextGreaterPrefix("\uFFFF") → null (cannot increment)
+ * nextGreaterPrefix("\uFFFF\uFFFF") → null (all chars at max)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * nextGreaterPrefix(null) → null (null input)
+ * nextGreaterPrefix("") → null (empty string, no chars to increment)
+ * nextGreaterPrefix("abc\uFFFF\uFFFF") → "abd" (truncates all trailing max values)
+ * </pre>
+ *
+ * <p>Properties:
+ * <ul>
+ * <li>The result is always the <em>smallest</em> string greater than the input</li>
+ * <li>For any valid input {@code s}, {@code nextGreaterPrefix(s).compareTo(s) > 0}</li>
+ * <li>The result may be shorter than the input (trailing max-value chars are removed)</li>
+ * <li>Trailing {@code \uFFFF} characters are effectively truncated</li>
+ * </ul>
+ *
+ * @param prefix The string to increment. If {@code null}, returns {@code null}.
+ * @return A next lexicographically greater string, or {@code null} if the input is {@code null}, empty, or consists entirely of
+ * maximum-value characters ({@code \uFFFF}). The returned string is guaranteed to be the smallest string greater than the
+ * input.
+ */
+ public static @Nullable String nextGreaterPrefix(@Nullable String prefix) {
+ if (prefix == null) {
+ return null;
+ }
+
+ // Try to increment characters from right to left
+ for (int i = prefix.length() - 1; i >= 0; i--) {
+ char c = prefix.charAt(i);
+
+ // Check if we can increment this character
+ if (c < Character.MAX_VALUE) {
+ // Increment and return
+ return prefix.substring(0, i) + ((char) (c + 1));
+ }
+
+ // This character is already max, continue to previous character
+ }
+
+ // All characters are at maximum value
Review Comment:
```suggestion
// All characters are at maximum value, or prefix is empty
```
##########
modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/IgniteSqlFunctions.java:
##########
@@ -761,4 +761,196 @@ private static long divide(long p, long q, RoundingMode mode) {
return increment ? div + signum : div;
}
+
+ /**
+ * Computes the next lexicographically greater string by incrementing the rightmost character that is not at its maximum value.
+ *
+ * <p>This method is primarily used in conjunction with {@link #findPrefix(String, String)} to create upper bounds for efficient
+ * range scans. Given a prefix extracted from a LIKE pattern, this method produces the smallest string that is lexicographically greater
+ * than the prefix, enabling queries like {@code WHERE key >= prefix AND key < nextGreaterPrefix}.
+ *
+ * <p>If all characters are at maximum value ({@value Character#MAX_VALUE}), the method returns {@code null} because no greater string
+ * exists within the Unicode character space.
+ *
+ * <p>Basic examples:
+ * <pre>
+ * nextGreaterPrefix("abc") → "abd"
+ * nextGreaterPrefix("test") → "tesu"
+ * nextGreaterPrefix("user") → "uses"
+ * nextGreaterPrefix("a") → "b"
+ * nextGreaterPrefix("z") → "{"
+ * nextGreaterPrefix("9") → ":"
+ * </pre>
+ *
+ * <p>Examples with maximum values:
+ * <pre>
+ * nextGreaterPrefix("abc\uFFFF") → "abd" (skip max char, increment 'c')
+ * nextGreaterPrefix("a\uFFFF\uFFFF") → "b" (skip two max chars, increment 'a')
+ * nextGreaterPrefix("test\uFFFF") → "tesu" (skip max char, increment 't')
+ * nextGreaterPrefix("\uFFFF") → null (cannot increment)
+ * nextGreaterPrefix("\uFFFF\uFFFF") → null (all chars at max)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * nextGreaterPrefix(null) → null (null input)
+ * nextGreaterPrefix("") → null (empty string, no chars to increment)
+ * nextGreaterPrefix("abc\uFFFF\uFFFF") → "abd" (truncates all trailing max values)
+ * </pre>
+ *
+ * <p>Properties:
+ * <ul>
+ * <li>The result is always the <em>smallest</em> string greater than the input</li>
+ * <li>For any valid input {@code s}, {@code nextGreaterPrefix(s).compareTo(s) > 0}</li>
+ * <li>The result may be shorter than the input (trailing max-value chars are removed)</li>
+ * <li>Trailing {@code \uFFFF} characters are effectively truncated</li>
+ * </ul>
+ *
+ * @param prefix The string to increment. If {@code null}, returns {@code null}.
+ * @return A next lexicographically greater string, or {@code null} if the input is {@code null}, empty, or consists entirely of
+ * maximum-value characters ({@code \uFFFF}). The returned string is guaranteed to be the smallest string greater than the
+ * input.
+ */
+ public static @Nullable String nextGreaterPrefix(@Nullable String prefix) {
+ if (prefix == null) {
+ return null;
+ }
+
+ // Try to increment characters from right to left
+ for (int i = prefix.length() - 1; i >= 0; i--) {
+ char c = prefix.charAt(i);
+
+ // Check if we can increment this character
+ if (c < Character.MAX_VALUE) {
+ // Increment and return
+ return prefix.substring(0, i) + ((char) (c + 1));
+ }
+
+ // This character is already max, continue to previous character
+ }
+
+ // All characters are at maximum value
+ return null; // Given prefix is the greatest.
+ }
+
+ /**
+ * Extracts the literal prefix from a SQL LIKE pattern by identifying all characters before the first unescaped wildcard.
+ *
+ * <p>This method processes SQL LIKE patterns containing wildcards ({@code %} for any sequence, {@code _} for single character) and
+ * returns the constant prefix that can be used for optimized range scans.
+ *
+ * <p>When an escape character is provided, it allows wildcards to be treated as literal characters. The escape character itself can
+ * also be escaped to include it literally in the prefix.
+ *
+ * <p>Examples without escape character:
+ * <pre>
+ * findPrefix("user%", null) → "user"
+ * findPrefix("admin_123", null) → "admin"
+ * findPrefix("test", null) → "test"
+ * findPrefix("%anything", null) → ""
+ * findPrefix("_anything", null) → ""
+ * </pre>
+ *
+ * <p>Examples with escape character:
+ * <pre>
+ * findPrefix("user\\%", "\\") → "user%" (% is literal)
+ * findPrefix("admin\\_", "\\") → "admin_" (_ is literal)
+ * findPrefix("path\\\\dir", "\\") → "path\\dir" (\\ escapes to \)
+ * findPrefix("test\\%end%", "\\") → "test%end" (first % escaped, second is wildcard)
+ * findPrefix("a\\%b\\%c", "\\") → "a%b%c" (both % are literal)
+ * findPrefix("value^%", "^") → "value%" (different escape char)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * findPrefix(null, null) → null (null pattern)
+ * findPrefix("test", "") → null (invalid escape length)
+ * findPrefix("test", "ab") → null (invalid escape length)
+ * findPrefix("", null) → "" (empty pattern)
+ * findPrefix("test\\", "\\") → "test\\" (escape at end treated as literal)
+ * </pre>
+ *
+ * @param pattern The SQL LIKE pattern to analyze; may contain wildcards {@code %} and {@code _}. If {@code null}, returns
+ * {@code null}.
+ * @param escape The escape character used to treat wildcards as literals; must be exactly one character long if provided. Use
+ * {@code null} if no escape character is defined.
+ * @return A literal prefix of the pattern before the first unescaped wildcard, with all escape sequences resolved to their literal
+ * characters. Returns an empty string if the pattern starts with a wildcard. Returns {@code null} if the pattern is
+ * {@code null} or if the escape parameter is invalid (not exactly 1 character).
+ */
+ public static @Nullable String findPrefix(@Nullable String pattern, @Nullable String escape) {
+ if (pattern == null || (escape != null && escape.length() != 1)) {
+ return null;
+ }
+
+ if (escape == null) {
+ int cutPoint = findCutPoint(pattern);
+
+ if (cutPoint == 0) {
+ return "";
+ }
+
+ return pattern.substring(0, cutPoint);
+ }
+
+ return findPrefix(pattern, escape.charAt(0));
+ }
+
+ private static String findPrefix(String pattern, char escape) {
+ StringBuilder prefix = new StringBuilder(pattern.length());
+ int lastAppendEnd = 0;
+
+ for (int i = 0; i < pattern.length(); i++) {
+ char current = pattern.charAt(i);
+
+ if (current == escape) {
+ int nextCharIdx = i + 1;
+ if (nextCharIdx < pattern.length()) {
+ char nextChar = pattern.charAt(nextCharIdx);
+
+ if (nextChar == escape || nextChar == '%' || nextChar == '_') {
+ // Append everything from lastAppendEnd to current position (excluding escape char)
+ if (lastAppendEnd < i) {
+ prefix.append(pattern, lastAppendEnd, i);
+ }
+
+ // Append the escaped character
+ prefix.append(pattern.charAt(i + 1));
Review Comment:
Will it be the same?
```suggestion
prefix.append(nextChar);
```
##########
modules/sql-engine/src/integrationTest/java/org/apache/ignite/internal/sql/engine/ItPrefixLikeToRangeScanConversionTest.java:
##########
@@ -0,0 +1,211 @@
+/*
+ * 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.
+ */
+
+package org.apache.ignite.internal.sql.engine;
+
+import static org.apache.ignite.internal.sql.engine.util.QueryChecker.containsIndexScan;
+import static org.apache.ignite.internal.sql.engine.util.QueryChecker.containsSubPlan;
+import static org.apache.ignite.internal.sql.engine.util.QueryChecker.containsTableScan;
+
+import com.google.common.collect.Streams;
+import java.util.stream.Stream;
+import org.apache.ignite.internal.sql.BaseSqlIntegrationTest;
+import org.hamcrest.Matcher;
+import org.hamcrest.Matchers;
+import org.jetbrains.annotations.NotNull;
+import org.jetbrains.annotations.Nullable;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.Arguments;
+import org.junit.jupiter.params.provider.MethodSource;
+
+/**
+ * Set of tests to validation conversion of prefix LIKE predicate to range scan.
+ */
+public class ItPrefixLikeToRangeScanConversionTest extends BaseSqlIntegrationTest {
+ private static final String QUERY_BASE = "SELECT val FROM t WHERE int_val = 1 AND val LIKE ?";
+ private static String[] indexNames;
+
+ @BeforeAll
+ static void setupSchema() {
+ sqlScript("CREATE TABLE t(id INT PRIMARY KEY, int_val INT, val VARCHAR);"
+ + "CREATE INDEX t_val_asc_nulls_first_idx ON t (val ASC NULLS FIRST);"
+ + "CREATE INDEX t_val_asc_nulls_last_idx ON t (val ASC NULLS LAST);"
+ + "CREATE INDEX t_val_desc_nulls_first_idx ON t (val DESC NULLS FIRST);"
+ + "CREATE INDEX t_val_desc_nulls_last_idx ON t (val DESC NULLS LAST);"
+ + "CREATE INDEX t_int_val_asc_val_asc_nulls_first_idx ON t (int_val ASC, val ASC NULLS FIRST);"
+ + "CREATE INDEX t_int_val_asc_val_asc_nulls_last_idx ON t (int_val ASC, val ASC NULLS LAST);"
+ + "CREATE INDEX t_int_val_asc_val_desc_nulls_first_idx ON t (int_val ASC, val DESC NULLS FIRST);"
+ + "CREATE INDEX t_int_val_asc_val_desc_nulls_last_idx ON t (int_val ASC, val DESC NULLS LAST);"
+ );
+
+ indexNames = new String[] {
+ "t_val_asc_nulls_first_idx",
+ "t_val_asc_nulls_last_idx",
+ "t_val_desc_nulls_first_idx",
+ "t_val_desc_nulls_last_idx",
+ "t_int_val_asc_val_asc_nulls_first_idx",
+ "t_int_val_asc_val_asc_nulls_last_idx",
+ "t_int_val_asc_val_desc_nulls_first_idx",
+ "t_int_val_asc_val_desc_nulls_last_idx"
+ };
+
+ String[] values = {
+ "foo", "fooa", "food", "fooz", "fooaa", "fooda", "fooza", "foo_b", "foo_bar", "foo%b", "foo%bar",
+ "fop", "fopa",
+ null, null, null,
+ threeMaxCharString(), threeMaxCharString() + "a", threeMaxCharString() + "b", threeMaxCharString() + "aa"
+ };
+
+ for (int i = 0; i < values.length; i++) {
+ sql("INSERT INTO t VALUES (?, 1, ?)", i, values[i]);
+ }
+ }
+
+ private static Stream<Arguments> indexNames() {
+ return Streams.concat(
+ Stream.of((String) null),
+ Stream.of(indexNames)
+ ).map(Arguments::of);
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void simplePrefixMatchesAll(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam("foo%")
+ .matches(planMather)
+ .returns("foo")
+ .returns("fooa")
+ .returns("food")
+ .returns("fooz")
+ .returns("fooaa")
+ .returns("fooda")
+ .returns("fooza")
+ .returns("foo_b")
+ .returns("foo_bar")
+ .returns("foo%b")
+ .returns("foo%bar")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void simplePrefixMatchesOne(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam("foo_")
+ .matches(planMather)
+ .returns("fooa")
+ .returns("food")
+ .returns("fooz")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void simplePrefixMatchesAllEscaped(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE + " ESCAPE '^'", indexName))
+ .withParam("foo^%%")
+ .matches(planMather)
+ .returns("foo%b")
+ .returns("foo%bar")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void simplePrefixMatchesOneEscaped(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE + " ESCAPE '^'", indexName))
+ .withParam("foo^__")
+ .matches(planMather)
+ .returns("foo_b")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void nullPattern(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam(null)
+ .matches(planMather)
+ .returnNothing()
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void maxCharPrefixMatchesAll(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam(threeMaxCharString() + "%")
+ .matches(planMather)
+ .returns(threeMaxCharString())
+ .returns(threeMaxCharString() + "a")
+ .returns(threeMaxCharString() + "b")
+ .returns(threeMaxCharString() + "aa")
+ .check();
+ }
+
+ @ParameterizedTest
+ @MethodSource("indexNames")
+ void maxCharPrefixMatchesOne(@Nullable String indexName) {
+ Matcher<String> planMather = indexOrTableScanMather(indexName);
+
+ assertQuery(appendForceIndexHint(QUERY_BASE, indexName))
+ .withParam(threeMaxCharString() + "_")
+ .matches(planMather)
+ .returns(threeMaxCharString() + "a")
+ .returns(threeMaxCharString() + "b")
+ .check();
+ }
+
+ private static @NotNull Matcher<String> indexOrTableScanMather(@Nullable String indexName) {
+ return indexName == null
+ ? containsTableScan("PUBLIC", "T")
+ : Matchers.allOf(
+ containsIndexScan("PUBLIC", "T", indexName.toUpperCase()),
+ containsSubPlan("searchBounds")
+ );
+ }
+
+ private static String threeMaxCharString() {
+ return ("" + Character.MAX_VALUE).repeat(3);
+ }
+
+ private static String appendForceIndexHint(String query, @Nullable String indexName) {
+ if (indexName == null) {
+ query = query.replace("SELECT", "SELECT /*+ no_index */");
+ } else {
+ query = query.replace("SELECT", "SELECT /*+ force_index(" + indexName + ") */");
+ }
+
+ System.out.println(query);
Review Comment:
```suggestion
```
##########
modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/IgniteSqlFunctions.java:
##########
@@ -761,4 +761,196 @@ private static long divide(long p, long q, RoundingMode mode) {
return increment ? div + signum : div;
}
+
+ /**
+ * Computes the next lexicographically greater string by incrementing the rightmost character that is not at its maximum value.
+ *
+ * <p>This method is primarily used in conjunction with {@link #findPrefix(String, String)} to create upper bounds for efficient
+ * range scans. Given a prefix extracted from a LIKE pattern, this method produces the smallest string that is lexicographically greater
+ * than the prefix, enabling queries like {@code WHERE key >= prefix AND key < nextGreaterPrefix}.
+ *
+ * <p>If all characters are at maximum value ({@value Character#MAX_VALUE}), the method returns {@code null} because no greater string
+ * exists within the Unicode character space.
+ *
+ * <p>Basic examples:
+ * <pre>
+ * nextGreaterPrefix("abc") → "abd"
+ * nextGreaterPrefix("test") → "tesu"
+ * nextGreaterPrefix("user") → "uses"
+ * nextGreaterPrefix("a") → "b"
+ * nextGreaterPrefix("z") → "{"
+ * nextGreaterPrefix("9") → ":"
+ * </pre>
+ *
+ * <p>Examples with maximum values:
+ * <pre>
+ * nextGreaterPrefix("abc\uFFFF") → "abd" (skip max char, increment 'c')
+ * nextGreaterPrefix("a\uFFFF\uFFFF") → "b" (skip two max chars, increment 'a')
+ * nextGreaterPrefix("test\uFFFF") → "tesu" (skip max char, increment 't')
+ * nextGreaterPrefix("\uFFFF") → null (cannot increment)
+ * nextGreaterPrefix("\uFFFF\uFFFF") → null (all chars at max)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * nextGreaterPrefix(null) → null (null input)
+ * nextGreaterPrefix("") → null (empty string, no chars to increment)
+ * nextGreaterPrefix("abc\uFFFF\uFFFF") → "abd" (truncates all trailing max values)
+ * </pre>
+ *
+ * <p>Properties:
+ * <ul>
+ * <li>The result is always the <em>smallest</em> string greater than the input</li>
+ * <li>For any valid input {@code s}, {@code nextGreaterPrefix(s).compareTo(s) > 0}</li>
+ * <li>The result may be shorter than the input (trailing max-value chars are removed)</li>
+ * <li>Trailing {@code \uFFFF} characters are effectively truncated</li>
+ * </ul>
+ *
+ * @param prefix The string to increment. If {@code null}, returns {@code null}.
+ * @return A next lexicographically greater string, or {@code null} if the input is {@code null}, empty, or consists entirely of
+ * maximum-value characters ({@code \uFFFF}). The returned string is guaranteed to be the smallest string greater than the
+ * input.
+ */
+ public static @Nullable String nextGreaterPrefix(@Nullable String prefix) {
+ if (prefix == null) {
+ return null;
+ }
+
+ // Try to increment characters from right to left
+ for (int i = prefix.length() - 1; i >= 0; i--) {
+ char c = prefix.charAt(i);
+
+ // Check if we can increment this character
+ if (c < Character.MAX_VALUE) {
+ // Increment and return
+ return prefix.substring(0, i) + ((char) (c + 1));
+ }
+
+ // This character is already max, continue to previous character
+ }
+
+ // All characters are at maximum value
+ return null; // Given prefix is the greatest.
+ }
+
+ /**
+ * Extracts the literal prefix from a SQL LIKE pattern by identifying all characters before the first unescaped wildcard.
+ *
+ * <p>This method processes SQL LIKE patterns containing wildcards ({@code %} for any sequence, {@code _} for single character) and
+ * returns the constant prefix that can be used for optimized range scans.
+ *
+ * <p>When an escape character is provided, it allows wildcards to be treated as literal characters. The escape character itself can
+ * also be escaped to include it literally in the prefix.
+ *
+ * <p>Examples without escape character:
+ * <pre>
+ * findPrefix("user%", null) → "user"
+ * findPrefix("admin_123", null) → "admin"
+ * findPrefix("test", null) → "test"
+ * findPrefix("%anything", null) → ""
Review Comment:
Do we support the next case?
```suggestion
* findPrefix(any%thing", null) → "any"
* findPrefix("%anything", null) → ""
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@ignite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] IGNITE-27077 Sql. Support prefix LIKE condition in search bounds [ignite-3]
Posted by "korlov42 (via GitHub)" <gi...@apache.org>.
korlov42 commented on code in PR #6996:
URL: https://github.com/apache/ignite-3/pull/6996#discussion_r2541556649
##########
modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/IgniteSqlFunctions.java:
##########
@@ -761,4 +761,196 @@ private static long divide(long p, long q, RoundingMode mode) {
return increment ? div + signum : div;
}
+
+ /**
+ * Computes the next lexicographically greater string by incrementing the rightmost character that is not at its maximum value.
+ *
+ * <p>This method is primarily used in conjunction with {@link #findPrefix(String, String)} to create upper bounds for efficient
+ * range scans. Given a prefix extracted from a LIKE pattern, this method produces the smallest string that is lexicographically greater
+ * than the prefix, enabling queries like {@code WHERE key >= prefix AND key < nextGreaterPrefix}.
+ *
+ * <p>If all characters are at maximum value ({@value Character#MAX_VALUE}), the method returns {@code null} because no greater string
+ * exists within the Unicode character space.
+ *
+ * <p>Basic examples:
+ * <pre>
+ * nextGreaterPrefix("abc") → "abd"
+ * nextGreaterPrefix("test") → "tesu"
+ * nextGreaterPrefix("user") → "uses"
+ * nextGreaterPrefix("a") → "b"
+ * nextGreaterPrefix("z") → "{"
+ * nextGreaterPrefix("9") → ":"
+ * </pre>
+ *
+ * <p>Examples with maximum values:
+ * <pre>
+ * nextGreaterPrefix("abc\uFFFF") → "abd" (skip max char, increment 'c')
+ * nextGreaterPrefix("a\uFFFF\uFFFF") → "b" (skip two max chars, increment 'a')
+ * nextGreaterPrefix("test\uFFFF") → "tesu" (skip max char, increment 't')
+ * nextGreaterPrefix("\uFFFF") → null (cannot increment)
+ * nextGreaterPrefix("\uFFFF\uFFFF") → null (all chars at max)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * nextGreaterPrefix(null) → null (null input)
+ * nextGreaterPrefix("") → null (empty string, no chars to increment)
+ * nextGreaterPrefix("abc\uFFFF\uFFFF") → "abd" (truncates all trailing max values)
+ * </pre>
+ *
+ * <p>Properties:
+ * <ul>
+ * <li>The result is always the <em>smallest</em> string greater than the input</li>
+ * <li>For any valid input {@code s}, {@code nextGreaterPrefix(s).compareTo(s) > 0}</li>
+ * <li>The result may be shorter than the input (trailing max-value chars are removed)</li>
+ * <li>Trailing {@code \uFFFF} characters are effectively truncated</li>
+ * </ul>
+ *
+ * @param prefix The string to increment. If {@code null}, returns {@code null}.
+ * @return A next lexicographically greater string, or {@code null} if the input is {@code null}, empty, or consists entirely of
+ * maximum-value characters ({@code \uFFFF}). The returned string is guaranteed to be the smallest string greater than the
+ * input.
+ */
+ public static @Nullable String nextGreaterPrefix(@Nullable String prefix) {
+ if (prefix == null) {
+ return null;
+ }
+
+ // Try to increment characters from right to left
+ for (int i = prefix.length() - 1; i >= 0; i--) {
+ char c = prefix.charAt(i);
+
+ // Check if we can increment this character
+ if (c < Character.MAX_VALUE) {
+ // Increment and return
+ return prefix.substring(0, i) + ((char) (c + 1));
+ }
+
+ // This character is already max, continue to previous character
+ }
+
+ // All characters are at maximum value
+ return null; // Given prefix is the greatest.
+ }
+
+ /**
+ * Extracts the literal prefix from a SQL LIKE pattern by identifying all characters before the first unescaped wildcard.
+ *
+ * <p>This method processes SQL LIKE patterns containing wildcards ({@code %} for any sequence, {@code _} for single character) and
+ * returns the constant prefix that can be used for optimized range scans.
+ *
+ * <p>When an escape character is provided, it allows wildcards to be treated as literal characters. The escape character itself can
+ * also be escaped to include it literally in the prefix.
+ *
+ * <p>Examples without escape character:
+ * <pre>
+ * findPrefix("user%", null) → "user"
+ * findPrefix("admin_123", null) → "admin"
+ * findPrefix("test", null) → "test"
+ * findPrefix("%anything", null) → ""
Review Comment:
this case is similar to this one: `* findPrefix("admin_123", null) → "admin"`. Updated the javadoc
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@ignite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] IGNITE-27077 Sql. Support prefix LIKE condition in search bounds [ignite-3]
Posted by "korlov42 (via GitHub)" <gi...@apache.org>.
korlov42 merged PR #6996:
URL: https://github.com/apache/ignite-3/pull/6996
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@ignite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] IGNITE-27077 Sql. Support prefix LIKE condition in search bounds [ignite-3]
Posted by "korlov42 (via GitHub)" <gi...@apache.org>.
korlov42 commented on code in PR #6996:
URL: https://github.com/apache/ignite-3/pull/6996#discussion_r2541561766
##########
modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/exec/exp/IgniteSqlFunctions.java:
##########
@@ -761,4 +761,196 @@ private static long divide(long p, long q, RoundingMode mode) {
return increment ? div + signum : div;
}
+
+ /**
+ * Computes the next lexicographically greater string by incrementing the rightmost character that is not at its maximum value.
+ *
+ * <p>This method is primarily used in conjunction with {@link #findPrefix(String, String)} to create upper bounds for efficient
+ * range scans. Given a prefix extracted from a LIKE pattern, this method produces the smallest string that is lexicographically greater
+ * than the prefix, enabling queries like {@code WHERE key >= prefix AND key < nextGreaterPrefix}.
+ *
+ * <p>If all characters are at maximum value ({@value Character#MAX_VALUE}), the method returns {@code null} because no greater string
+ * exists within the Unicode character space.
+ *
+ * <p>Basic examples:
+ * <pre>
+ * nextGreaterPrefix("abc") → "abd"
+ * nextGreaterPrefix("test") → "tesu"
+ * nextGreaterPrefix("user") → "uses"
+ * nextGreaterPrefix("a") → "b"
+ * nextGreaterPrefix("z") → "{"
+ * nextGreaterPrefix("9") → ":"
+ * </pre>
+ *
+ * <p>Examples with maximum values:
+ * <pre>
+ * nextGreaterPrefix("abc\uFFFF") → "abd" (skip max char, increment 'c')
+ * nextGreaterPrefix("a\uFFFF\uFFFF") → "b" (skip two max chars, increment 'a')
+ * nextGreaterPrefix("test\uFFFF") → "tesu" (skip max char, increment 't')
+ * nextGreaterPrefix("\uFFFF") → null (cannot increment)
+ * nextGreaterPrefix("\uFFFF\uFFFF") → null (all chars at max)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * nextGreaterPrefix(null) → null (null input)
+ * nextGreaterPrefix("") → null (empty string, no chars to increment)
+ * nextGreaterPrefix("abc\uFFFF\uFFFF") → "abd" (truncates all trailing max values)
+ * </pre>
+ *
+ * <p>Properties:
+ * <ul>
+ * <li>The result is always the <em>smallest</em> string greater than the input</li>
+ * <li>For any valid input {@code s}, {@code nextGreaterPrefix(s).compareTo(s) > 0}</li>
+ * <li>The result may be shorter than the input (trailing max-value chars are removed)</li>
+ * <li>Trailing {@code \uFFFF} characters are effectively truncated</li>
+ * </ul>
+ *
+ * @param prefix The string to increment. If {@code null}, returns {@code null}.
+ * @return A next lexicographically greater string, or {@code null} if the input is {@code null}, empty, or consists entirely of
+ * maximum-value characters ({@code \uFFFF}). The returned string is guaranteed to be the smallest string greater than the
+ * input.
+ */
+ public static @Nullable String nextGreaterPrefix(@Nullable String prefix) {
+ if (prefix == null) {
+ return null;
+ }
+
+ // Try to increment characters from right to left
+ for (int i = prefix.length() - 1; i >= 0; i--) {
+ char c = prefix.charAt(i);
+
+ // Check if we can increment this character
+ if (c < Character.MAX_VALUE) {
+ // Increment and return
+ return prefix.substring(0, i) + ((char) (c + 1));
+ }
+
+ // This character is already max, continue to previous character
+ }
+
+ // All characters are at maximum value
+ return null; // Given prefix is the greatest.
+ }
+
+ /**
+ * Extracts the literal prefix from a SQL LIKE pattern by identifying all characters before the first unescaped wildcard.
+ *
+ * <p>This method processes SQL LIKE patterns containing wildcards ({@code %} for any sequence, {@code _} for single character) and
+ * returns the constant prefix that can be used for optimized range scans.
+ *
+ * <p>When an escape character is provided, it allows wildcards to be treated as literal characters. The escape character itself can
+ * also be escaped to include it literally in the prefix.
+ *
+ * <p>Examples without escape character:
+ * <pre>
+ * findPrefix("user%", null) → "user"
+ * findPrefix("admin_123", null) → "admin"
+ * findPrefix("test", null) → "test"
+ * findPrefix("%anything", null) → ""
+ * findPrefix("_anything", null) → ""
+ * </pre>
+ *
+ * <p>Examples with escape character:
+ * <pre>
+ * findPrefix("user\\%", "\\") → "user%" (% is literal)
+ * findPrefix("admin\\_", "\\") → "admin_" (_ is literal)
+ * findPrefix("path\\\\dir", "\\") → "path\\dir" (\\ escapes to \)
+ * findPrefix("test\\%end%", "\\") → "test%end" (first % escaped, second is wildcard)
+ * findPrefix("a\\%b\\%c", "\\") → "a%b%c" (both % are literal)
+ * findPrefix("value^%", "^") → "value%" (different escape char)
+ * </pre>
+ *
+ * <p>Edge cases:
+ * <pre>
+ * findPrefix(null, null) → null (null pattern)
+ * findPrefix("test", "") → null (invalid escape length)
+ * findPrefix("test", "ab") → null (invalid escape length)
+ * findPrefix("", null) → "" (empty pattern)
+ * findPrefix("test\\", "\\") → "test\\" (escape at end treated as literal)
+ * </pre>
+ *
+ * @param pattern The SQL LIKE pattern to analyze; may contain wildcards {@code %} and {@code _}. If {@code null}, returns
+ * {@code null}.
+ * @param escape The escape character used to treat wildcards as literals; must be exactly one character long if provided. Use
+ * {@code null} if no escape character is defined.
+ * @return A literal prefix of the pattern before the first unescaped wildcard, with all escape sequences resolved to their literal
+ * characters. Returns an empty string if the pattern starts with a wildcard. Returns {@code null} if the pattern is
+ * {@code null} or if the escape parameter is invalid (not exactly 1 character).
+ */
+ public static @Nullable String findPrefix(@Nullable String pattern, @Nullable String escape) {
+ if (pattern == null || (escape != null && escape.length() != 1)) {
+ return null;
+ }
+
+ if (escape == null) {
+ int cutPoint = findCutPoint(pattern);
+
+ if (cutPoint == 0) {
+ return "";
+ }
+
+ return pattern.substring(0, cutPoint);
+ }
+
+ return findPrefix(pattern, escape.charAt(0));
+ }
+
+ private static String findPrefix(String pattern, char escape) {
+ StringBuilder prefix = new StringBuilder(pattern.length());
+ int lastAppendEnd = 0;
+
+ for (int i = 0; i < pattern.length(); i++) {
+ char current = pattern.charAt(i);
+
+ if (current == escape) {
+ int nextCharIdx = i + 1;
+ if (nextCharIdx < pattern.length()) {
+ char nextChar = pattern.charAt(nextCharIdx);
+
+ if (nextChar == escape || nextChar == '%' || nextChar == '_') {
+ // Append everything from lastAppendEnd to current position (excluding escape char)
+ if (lastAppendEnd < i) {
+ prefix.append(pattern, lastAppendEnd, i);
+ }
+
+ // Append the escaped character
+ prefix.append(pattern.charAt(i + 1));
Review Comment:
yes, it will. Code updated according to suggestion
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@ignite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org