You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by GitBox <gi...@apache.org> on 2022/11/18 00:58:27 UTC
[GitHub] [pinot] 61yao commented on a diff in pull request #9821: [multistage] add comparison tests
61yao commented on code in PR #9821:
URL: https://github.com/apache/pinot/pull/9821#discussion_r1025886153
##########
pinot-query-runtime/src/test/resources/queries/Comparisons.json:
##########
@@ -0,0 +1,481 @@
+{
+ "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}" }
+ ]
+ },
+ "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}" }
+ ]
+ },
+ "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}" }
+ ]
+ },
+ "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}" }
+ ]
+ },
+ "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}" }
+ ]
+ },
+ "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}" }
+ ]
+ },
+ "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}" }
+ ]
+ }
+}
Review Comment:
Can we add boolean comparison? Can we also test them in where clause?
--
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: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org