This is an automated email from the ASF dual-hosted git repository.
saurabhd336 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/pinot.git
The following commit(s) were added to refs/heads/master by this push:
new ecf7c7af42 Change substring to comply with standard sql definition
(#11502)
ecf7c7af42 is described below
commit ecf7c7af42fbae6db6e781cab5addb13a7b90b9a
Author: Saurabh Dubey <[email protected]>
AuthorDate: Tue Sep 5 17:07:14 2023 +0530
Change substring to comply with standard sql definition (#11502)
* Change substring to comply with standard sql definition
---------
Co-authored-by: Saurabh Dubey <[email protected]>
---
.../common/function/scalar/StringFunctions.java | 30 ++++++++++++++++++++--
.../tests/BaseClusterIntegrationTestSet.java | 12 ++++-----
.../tests/tpch/TPCHQueryIntegrationTest.java | 4 +--
.../src/test/resources/tpch/7.sql | 2 +-
.../src/test/resources/tpch/8.sql | 2 +-
.../src/test/resources/tpch/9.sql | 2 +-
.../test/resources/queries/StringFunctions.json | 2 +-
7 files changed, 40 insertions(+), 14 deletions(-)
diff --git
a/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/StringFunctions.java
b/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/StringFunctions.java
index 31d0cc7f01..52442590e8 100644
---
a/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/StringFunctions.java
+++
b/pinot-common/src/main/java/org/apache/pinot/common/function/scalar/StringFunctions.java
@@ -82,7 +82,7 @@ public class StringFunctions {
* @param beginIndex index from which substring should be created
* @return substring from beginIndex to end of the parent string
*/
- @ScalarFunction(names = {"substr", "substring"})
+ @ScalarFunction
public static String substr(String input, int beginIndex) {
return StringUtils.substring(input, beginIndex);
}
@@ -97,7 +97,7 @@ public class StringFunctions {
* @param endIndex index at which substring should be terminated
* @return substring from beginIndex to endIndex
*/
- @ScalarFunction(names = {"substr", "substring"})
+ @ScalarFunction
public static String substr(String input, int beginIndex, int endIndex) {
if (endIndex == -1) {
return substr(input, beginIndex);
@@ -105,6 +105,32 @@ public class StringFunctions {
return StringUtils.substring(input, beginIndex, endIndex);
}
+ /**
+ * @param input Parent string
+ * @param beginIndex 1 based index from which substring should be created
+ * @return substring from beginIndex to end of the parent string
+ */
+ @ScalarFunction
+ public static String substring(String input, int beginIndex) {
+ return StringUtils.substring(input, beginIndex - 1);
+ }
+
+ /**
+ * Returns the substring of the main string from beginIndex of length.
+ *
+ * @param input Parent string
+ * @param beginIndex 1 based index from which substring should be created
+ * @param length length of substring to be created
+ * @return a substirng of input string from beginIndex of length 'length'
+ */
+ @ScalarFunction
+ public static String substring(String input, int beginIndex, int length) {
+ // index is always 1 based
+ beginIndex = beginIndex - 1;
+ int endIndex = beginIndex + length;
+ return StringUtils.substring(input, beginIndex, endIndex);
+ }
+
/**
* Join two input string with seperator in between
* @param input1
diff --git
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/BaseClusterIntegrationTestSet.java
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/BaseClusterIntegrationTestSet.java
index e850b4ba2b..5b07d1cc37 100644
---
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/BaseClusterIntegrationTestSet.java
+++
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/BaseClusterIntegrationTestSet.java
@@ -130,8 +130,10 @@ public abstract class BaseClusterIntegrationTestSet
extends BaseClusterIntegrati
testQuery(query);
query = "SELECT COUNT(*) FROM mytable WHERE CarrierDelay=15 AND ArrDelay >
CarrierDelay LIMIT 1";
testQuery(query);
- query = "SELECT ArrDelay, CarrierDelay, (ArrDelay - CarrierDelay) AS diff
FROM mytable WHERE CarrierDelay=15 AND "
- + "ArrDelay > CarrierDelay ORDER BY diff, ArrDelay, CarrierDelay LIMIT
100000";
+ query =
+ "SELECT ArrDelay, CarrierDelay, (ArrDelay - CarrierDelay) AS diff,
substring(DestStateName, 4, 8) as "
+ + "stateSubStr FROM mytable WHERE CarrierDelay=15 AND "
+ + "ArrDelay > CarrierDelay ORDER BY diff, ArrDelay, CarrierDelay
LIMIT 100000";
testQuery(query);
query = "SELECT COUNT(*) FROM mytable WHERE ArrDelay > CarrierDelay LIMIT
1";
testQuery(query);
@@ -255,11 +257,9 @@ public abstract class BaseClusterIntegrationTestSet
extends BaseClusterIntegrati
testQuery(query, h2Query);
// test arithmetic operations on date time columns
- query = "SELECT sub(DaysSinceEpoch,25), COUNT(*) FROM mytable "
- + "GROUP BY sub(DaysSinceEpoch,25) "
+ query = "SELECT sub(DaysSinceEpoch,25), COUNT(*) FROM mytable " + "GROUP
BY sub(DaysSinceEpoch,25) "
+ "ORDER BY COUNT(*),sub(DaysSinceEpoch,25) DESC";
- h2Query = "SELECT DaysSinceEpoch - 25, COUNT(*) FROM mytable "
- + "GROUP BY DaysSinceEpoch "
+ h2Query = "SELECT DaysSinceEpoch - 25, COUNT(*) FROM mytable " + "GROUP BY
DaysSinceEpoch "
+ "ORDER BY COUNT(*), DaysSinceEpoch DESC";
testQuery(query, h2Query);
}
diff --git
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/tpch/TPCHQueryIntegrationTest.java
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/tpch/TPCHQueryIntegrationTest.java
index e4e3181aa6..020ff6581c 100644
---
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/tpch/TPCHQueryIntegrationTest.java
+++
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/tpch/TPCHQueryIntegrationTest.java
@@ -57,8 +57,8 @@ public class TPCHQueryIntegrationTest extends
BaseClusterIntegrationTest {
// Pinot query 6 fails due to mismatch results.
// Pinot queries 15, 16, 17 fail due to lack of support for views.
- // Pinot queries 23, 24 fail due to java heap space problem or timeout.
- private static final Set<Integer> EXEMPT_QUERIES = ImmutableSet.of(6, 15,
16, 17, 23, 24);
+ // Pinot queries 23 fail due to java heap space problem or timeout.
+ private static final Set<Integer> EXEMPT_QUERIES = ImmutableSet.of(6, 15,
16, 17, 23);
@BeforeClass
public void setUp()
diff --git a/pinot-integration-tests/src/test/resources/tpch/7.sql
b/pinot-integration-tests/src/test/resources/tpch/7.sql
index 512d76ef06..5727766fa2 100644
--- a/pinot-integration-tests/src/test/resources/tpch/7.sql
+++ b/pinot-integration-tests/src/test/resources/tpch/7.sql
@@ -8,7 +8,7 @@ from
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
- substring(l_shipdate, 0, 4) as l_year,
+ substring(l_shipdate, 1, 4) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,
diff --git a/pinot-integration-tests/src/test/resources/tpch/8.sql
b/pinot-integration-tests/src/test/resources/tpch/8.sql
index 7a039ad3d1..64cc10818c 100644
--- a/pinot-integration-tests/src/test/resources/tpch/8.sql
+++ b/pinot-integration-tests/src/test/resources/tpch/8.sql
@@ -6,7 +6,7 @@ select
from
(
select
- substring(o_orderdate, 0, 4) as o_year,
+ substring(o_orderdate, 1, 4) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
diff --git a/pinot-integration-tests/src/test/resources/tpch/9.sql
b/pinot-integration-tests/src/test/resources/tpch/9.sql
index d7541ae695..e1fbfe6e8b 100644
--- a/pinot-integration-tests/src/test/resources/tpch/9.sql
+++ b/pinot-integration-tests/src/test/resources/tpch/9.sql
@@ -6,7 +6,7 @@ from
(
select
n_name as nation,
- substring(o_orderdate, 0, 4) as o_year,
+ substring(o_orderdate, 1, 4) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
diff --git
a/pinot-query-runtime/src/test/resources/queries/StringFunctions.json
b/pinot-query-runtime/src/test/resources/queries/StringFunctions.json
index 815d69e680..157c70739a 100644
--- a/pinot-query-runtime/src/test/resources/queries/StringFunctions.json
+++ b/pinot-query-runtime/src/test/resources/queries/StringFunctions.json
@@ -123,7 +123,7 @@
},
{
"comment": "h2 only supports SUBSTRING(input FROM x TO y) syntax,
which we don't support",
- "sql": "SELECT substring(strCol, 0) FROM {stringTbl}",
+ "sql": "SELECT substring(strCol, 1) FROM {stringTbl}",
"outputs": [
[ "Hello" ],
[ "hello" ],
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
