agavra commented on code in PR #9821:
URL: https://github.com/apache/pinot/pull/9821#discussion_r1025887664
##########
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:
👍 added the boolean =/<> in the other PR, adding failure tests here and
where clauses as well
--
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-unsubscr...@pinot.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
