You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by ro...@apache.org on 2022/11/18 03:36:49 UTC
[pinot] branch master updated: [multistage] add comparison tests (#9821)
This is an automated email from the ASF dual-hosted git repository.
rongr 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 e98fa6e47f [multistage] add comparison tests (#9821)
e98fa6e47f is described below
commit e98fa6e47f7534cd8ce6242f59247814f4dd2814
Author: Almog Gavra <al...@gmail.com>
AuthorDate: Thu Nov 17 19:36:43 2022 -0800
[multistage] add comparison tests (#9821)
---
.../pinot/query/runtime/QueryRunnerTestBase.java | 3 +
.../runtime/queries/ResourceBasedQueriesTest.java | 3 +-
.../testutils/MockInstanceDataManagerFactory.java | 4 +-
.../src/test/resources/queries/Comparisons.json | 516 +++++++++++++++++++++
4 files changed, 522 insertions(+), 4 deletions(-)
diff --git a/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/QueryRunnerTestBase.java b/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/QueryRunnerTestBase.java
index 80fd4a98cb..7e6e783c3c 100644
--- a/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/QueryRunnerTestBase.java
+++ b/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/QueryRunnerTestBase.java
@@ -271,6 +271,9 @@ public abstract class QueryRunnerTestBase extends QueryTestSet {
case BIG_DECIMAL:
fieldType = "NUMERIC";
break;
+ case BOOLEAN:
+ fieldType = "BOOLEAN";
+ break;
default:
throw new UnsupportedOperationException("Unsupported type conversion to h2 type: " + dataType);
}
diff --git a/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/queries/ResourceBasedQueriesTest.java b/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/queries/ResourceBasedQueriesTest.java
index 690406025b..1d1d932796 100644
--- a/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/queries/ResourceBasedQueriesTest.java
+++ b/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/queries/ResourceBasedQueriesTest.java
@@ -64,7 +64,8 @@ public class ResourceBasedQueriesTest extends QueryRunnerTestBase {
"LexicalStructure.json",
"SelectExpressions.json",
"ValueExpressions.json",
- "NumericTypes.json"
+ "NumericTypes.json",
+ "Comparisons.json"
);
@BeforeClass
diff --git a/pinot-query-runtime/src/test/java/org/apache/pinot/query/testutils/MockInstanceDataManagerFactory.java b/pinot-query-runtime/src/test/java/org/apache/pinot/query/testutils/MockInstanceDataManagerFactory.java
index 396b2e65ab..1b27d24c53 100644
--- a/pinot-query-runtime/src/test/java/org/apache/pinot/query/testutils/MockInstanceDataManagerFactory.java
+++ b/pinot-query-runtime/src/test/java/org/apache/pinot/query/testutils/MockInstanceDataManagerFactory.java
@@ -45,7 +45,6 @@ import org.apache.pinot.spi.utils.builder.TableConfigBuilder;
import org.apache.pinot.spi.utils.builder.TableNameBuilder;
import static org.mockito.ArgumentMatchers.any;
-import static org.mockito.ArgumentMatchers.matches;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.when;
@@ -108,8 +107,7 @@ public class MockInstanceDataManagerFactory {
tableDataManagers.put(e.getKey(), tableDataManager);
}
for (Map.Entry<String, TableDataManager> e : tableDataManagers.entrySet()) {
- when(instanceDataManager.getTableDataManager(matches(String.format("%s.*", e.getKey())))).thenReturn(
- e.getValue());
+ when(instanceDataManager.getTableDataManager(e.getKey())).thenAnswer(inv -> e.getValue());
}
return instanceDataManager;
}
diff --git a/pinot-query-runtime/src/test/resources/queries/Comparisons.json b/pinot-query-runtime/src/test/resources/queries/Comparisons.json
new file mode 100644
index 0000000000..d87ed6a634
--- /dev/null
+++ b/pinot-query-runtime/src/test/resources/queries/Comparisons.json
@@ -0,0 +1,516 @@
+{
+ "ints_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "INT"},
+ {"name": "i2", "type": "INT"}
+ ],
+ "inputs": [
+ [1, 2], [1, 1], [-1, -2], [0, 0], [2147483647, -2147483648]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" },
+ { "sql": "SELECT * FROM {tbl} WHERE i1 >= i2" }
+ ]
+ },
+ "boolean_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "BOOLEAN"},
+ {"name": "i2", "type": "BOOLEAN"}
+ ],
+ "inputs": [
+ [true, true], [true, false], [false, true], [false, false]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" },
+ { "sql": "SELECT * FROM {tbl} WHERE i1 >= i2" }
+ ]
+ },
+ "floats_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "FLOAT"},
+ {"name": "i2", "type": "FLOAT"}
+ ],
+ "inputs": [
+ [1.1, 1.2], [1.01, 1.1], [1.1, 1.1], [-1.1, -1.2], [0.0, 0.0], [1E-37, 1E+37]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" },
+ { "sql": "SELECT * FROM {tbl} WHERE i1 >= i2" }
+ ]
+ },
+ "doubles_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "DOUBLE"},
+ {"name": "i2", "type": "DOUBLE"}
+ ],
+ "inputs": [
+ [1.1, 1.2], [1.01, 1.1], [1.1, 1.1], [-1.1, -1.2], [0.0, 0.0], [1E-307, 1E+307]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" },
+ { "sql": "SELECT * FROM {tbl} WHERE i1 >= i2" }
+ ]
+ },
+ "numerics_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "BIG_DECIMAL"},
+ {"name": "i2", "type": "BIG_DECIMAL"}
+ ],
+ "inputs": [
+ ["1.1", "1.2"], ["1.01", "1.1"], ["1.1", "1.1"], ["-1.1", "-1.2"], ["0.0", "0.0"], ["1E-307", "1E+307"]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" },
+ {
+ "ignored": true,
+ "comment": "fails with IllegalStateException... not sure why!",
+ "sql": "SELECT * FROM {tbl} WHERE i1 >= i2"
+ }
+ ]
+ },
+ "strings_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "STRING"},
+ {"name": "i2", "type": "STRING"}
+ ],
+ "inputs": [
+ ["foo", "foo"], ["foo", "bar"],
+ ["123", "123"], ["123", "321"],
+ ["alpha123", "alpha321"],
+ ["beta123", "gamma123"],
+ ["Οὐχὶ (greek)", "แสน (thai)"],
+ ["", ""],
+ ["", "foo"]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" },
+ { "sql": "SELECT * FROM {tbl} WHERE i1 >= i2" }
+ ]
+ },
+ "timestamps_comp": {
+ "ignored": true,
+ "comment": "we don't support timestamp comparisons",
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "TIMESTAMP"},
+ {"name": "i2", "type": "TIMESTAMP"}
+ ],
+ "inputs": [
+ ["2020-01-01 03:32:12", "2020-01-01 03:32:12"],
+ ["2020-01-01 03:32:12", "2020-02-01 03:32:12"]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" },
+ { "sql": "SELECT * FROM {tbl} WHERE i1 >= i2" }
+ ]
+ },
+ "int_x_bigint_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "INT"},
+ {"name": "i2", "type": "LONG"}
+ ],
+ "inputs": [
+ [1, 2], [1, 1], [-1, -2], [0, 0], [2147483647, -2147483648]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" }
+ ]
+ },
+ "int_x_float_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "INT"},
+ {"name": "i2", "type": "FLOAT"}
+ ],
+ "inputs": [
+ [1, 2.0], [1, 0.9], [-1, -2.0], [0, 0.0], [2147483647, -2147483648.001]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" }
+ ]
+ },
+ "int_x_double_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "INT"},
+ {"name": "i2", "type": "DOUBLE"}
+ ],
+ "inputs": [
+ [1, 2.0], [1, 0.9], [-1, -2.0], [0, 0.0], [2147483647, -2147483648.001]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" }
+ ]
+ },
+ "int_x_numeric_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "INT"},
+ {"name": "i2", "type": "BIG_DECIMAL"}
+ ],
+ "inputs": [
+ [1, "2.0"], [1, "0.9"], [-1, "-2.0"], [0, "0.0"], [2147483647, "-2147483648"]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" }
+ ]
+ },
+ "float_x_bigint_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "FLOAT"},
+ {"name": "i2", "type": "LONG"}
+ ],
+ "inputs": [
+ [1.1, 1], [0.01, 1], [1.1, 1], [-1.1, -1], [0.0, 0], [1E-37, 137]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" }
+ ]
+ },
+ "float_x_double_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "FLOAT"},
+ {"name": "i2", "type": "DOUBLE"}
+ ],
+ "inputs": [
+ [1.1, 1.2],
+ [1.01, 1.1],
+ [1.1, 1.1],
+ [-1.1, -1.2],
+ [0.0, 0.0],
+ [1E-37, 1E+37]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" },
+ {
+ "ignored": true,
+ "comment": "we don't handle equality checks correctly for float/double",
+ "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}"
+ },
+ {
+ "ignored": true,
+ "comment": "we don't handle equality checks correctly for float/double",
+ "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}"
+ },
+ {
+ "ignored": true,
+ "comment": "we don't handle equality checks correctly for float/double",
+ "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}"
+ }
+ ]
+ },
+ "float_x_numeric_comp": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "i1", "type": "FLOAT"},
+ {"name": "i2", "type": "BIG_DECIMAL"}
+ ],
+ "inputs": [
+ [1.1, "2.0"], [1.1, "0.9"], [-1.1, "-2.0"], [0.0, "0.0"], [2147.483647, "-2147483648"]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 < i2, i2 < i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <= i2, i2 <= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 > i2, i2 > i1 FROM {tbl}" },
+ { "sql": "SELECT i1 >= i2, i2 >= i1 FROM {tbl}" },
+ { "sql": "SELECT i1 != i2, i2 != i1 FROM {tbl}" },
+ { "sql": "SELECT i1 <> i2, i2 <> i1 FROM {tbl}" },
+ { "sql": "SELECT i1 = i2, i2 = i1 FROM {tbl}" },
+ { "sql": "SELECT i1 IS DISTINCT FROM i2, i2 IS DISTINCT FROM i1 FROM {tbl}" }
+ ]
+ },
+ "between_ints": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "val", "type": "INT"},
+ {"name": "small", "type": "INT"},
+ {"name": "big", "type": "INT"}
+ ],
+ "inputs": [
+ [1, 2, 3], [1, 1, 1], [-1, -3, -2], [0, -1, 1], [0, 2147483647, -2147483648]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT val BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val BETWEEN big AND small FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN big AND small FROM {tbl}" },
+ {
+ "ignored": true,
+ "comment": "SYMMETRIC not supported",
+ "sql": "SELECT val BETWEEN SYMMETRIC small AND big FROM {tbl}"
+ },
+ {
+ "ignored": true,
+ "comment": "SYMMETRIC not supported",
+ "sql": "SELECT val BETWEEN SYMMETRIC big AND small FROM {tbl}"
+ }
+ ]
+ },
+ "between_bigints": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "val", "type": "LONG"},
+ {"name": "small", "type": "LONG"},
+ {"name": "big", "type": "LONG"}
+ ],
+ "inputs": [
+ [1, 2, 3], [1, 1, 1], [-1, -3, -2], [0, -1, 1], [0, 2147483647, -2147483648]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT * FROM {tbl}"},
+ { "sql": "SELECT val BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val BETWEEN big AND small FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN big AND small FROM {tbl}" }
+ ]
+ },
+ "between_floats": {
+ "psql": "9.2",
+ "comment": "floats silently fail (don't return any rows)",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "val", "type": "FLOAT"},
+ {"name": "small", "type": "FLOAT"},
+ {"name": "big", "type": "FLOAT"}
+ ],
+ "inputs": [
+ [1.5, 1.1, 2.1], [1.1, 1.1, 1.1], [-1.1, -3.1, -2.1], [0.0, -1.0, 1.0], [0.0, 2147483647.0, -2147483648.0]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT val BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val BETWEEN big AND small FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN big AND small FROM {tbl}" }
+ ]
+ },
+ "between_doubles": {
+ "comment": "doubles silently fail (don't return any rows)",
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "val", "type": "DOUBLE"},
+ {"name": "small", "type": "DOUBLE"},
+ {"name": "big", "type": "DOUBLE"}
+ ],
+ "inputs": [
+ [1.5, 1.1, 2.1], [1.1, 1.1, 1.1], [-1.1, -3.1, -2.1], [0.0, -1.0, 1.0], [0.0, 2147483647.0, -2147483648.0]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT val BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val BETWEEN big AND small FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN big AND small FROM {tbl}" }
+ ]
+ },
+ "between_numerics": {
+ "comment": "big decimal silently fails (doesn't throw exception, but returns no rows)",
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "val", "type": "BIG_DECIMAL"},
+ {"name": "small", "type": "BIG_DECIMAL"},
+ {"name": "big", "type": "BIG_DECIMAL"}
+ ],
+ "inputs": [
+ ["1.5", "1.1", "2.1"], ["1.1", "1.1", "1.1"], ["-1.1", "-3.1", "-2.1"],
+ ["0.0", "-1.0", "1.0"], ["0.0", "2147483647.0", "-2147483648.0"]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT val BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val BETWEEN big AND small FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN small AND big FROM {tbl}" },
+ { "sql": "SELECT val NOT BETWEEN big AND small FROM {tbl}" }
+ ]
+ },
+ "between_different_types": {
+ "psql": "9.2",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "a", "type": "INT"},
+ {"name": "b", "type": "FLOAT"},
+ {"name": "c", "type": "DOUBLE"}
+ ],
+ "inputs": [
+ [1, 2.0, 3.0], [2, 3.0, 1.0], [3, 1.0, 2]
+ ]
+ }
+ },
+ "queries": [
+ { "sql": "SELECT a BETWEEN b AND c FROM {tbl}" },
+ { "sql": "SELECT b BETWEEN c AND a FROM {tbl}" },
+ { "sql": "SELECT c BETWEEN a AND b FROM {tbl}" }
+ ]
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org