test: fuzzing test failed at Bind error: types Boolean and Varchar cannot be matched

[BugenZhao]

-- Query
CREATE MATERIALIZED VIEW stream_query AS WITH with_0 AS (SELECT to_char(TIMESTAMP '2022-01-02 01:06:05', 'Vl7W9H8w8N') AS col_0, (2147483647) AS col_1, 'UpqmmLgdbj' AS col_2, (INT '408799106') AS col_3 FROM m1 AS t_1 WHERE CASE WHEN false THEN (TIME '02:06:04' - t_1.col_0) <= (INTERVAL '60') WHEN false THEN true WHEN CAST((INT '1') AS BOOLEAN) THEN CASE WHEN true THEN '39RNmIYUB9' LIKE 'MOUV1t2UwY' WHEN false THEN true WHEN NULL IN (NULL, NULL, NULL, NULL) THEN true WHEN true THEN (REAL '-1421243392') >= (INT '1306403215') WHEN true THEN EXISTS (SELECT (0) AS col_0, ((((0) / (-2036932247)) + min((INT '0')) FILTER(WHERE true)) + (9006918883005200442)) * (4717953741197367586) AS col_1 FROM hop(person, person.date_time, INTERVAL '86400', INTERVAL '5184000') AS hop_2 WHERE true GROUP BY hop_2.state) WHEN true THEN false WHEN false THEN true ELSE t_1.col_2 <> (693222585) END WHEN false THEN false >= 'LN1O0QP1yi' NOT IN (md5('4SeUPZhUbH')) WHEN true THEN (0) < (REAL '530279106') WHEN DATE '2022-01-01' >= TIMESTAMP '2022-01-01 02:06:05' THEN coalesce(NULL, NULL, NULL, false, NULL, NULL, NULL, NULL, NULL, NULL) WHEN coalesce(NULL, NULL, true, NULL, NULL, NULL, NULL, NULL, NULL, NULL) THEN false WHEN true THEN true ELSE false END GROUP BY t_1.col_1) SELECT (REAL '2147483647') + (FLOAT '1') AS col_0, DATE '2021-12-26' AS col_1, (-9223372036854775808) / (INT '-817442038') AS col_2, ARRAY[TIME '02:05:05', TIME '02:06:04', TIME '02:06:04', TIME '02:06:05', TIME '02:05:05', TIME '02:06:04', TIME '02:06:04', TIME '02:06:05', TIME '02:05:05', TIME '02:06:05', TIME '01:06:05', TIME '02:06:05', TIME '02:06:05', TIME '01:06:05', TIME '02:05:05', TIME '07:07:35', TIME '01:27:55', TIME '02:05:05', TIME '02:06:04', TIME '02:06:05', TIME '02:06:04', TIME '02:05:05', TIME '01:06:05', TIME '01:06:05', TIME '01:06:05', TIME '02:05:05', TIME '01:06:05', TIME '01:06:05', TIME '06:11:27', TIME '02:06:05', TIME '11:31:03', TIME '02:06:05', TIME '09:38:23', TIME '02:05:05', TIME '02:05:05', TIME '02:06:04', TIME '07:29:34', TIME '02:05:05', TIME '05:07:58', TIME '02:06:05', TIME '02:06:05', TIME '02:06:04', TIME '02:05:05', TIME '02:06:05', TIME '02:06:04', TIME '01:06:05', TIME '02:06:05', TIME '02:06:04', TIME '02:06:05', TIME '23:31:02', TIME '06:19:19', TIME '02:05:05', TIME '02:05:05', TIME '01:06:05', TIME '09:59:47', TIME '02:06:04', TIME '02:06:05', TIME '02:06:05', TIME '02:06:05', TIME '02:05:05', TIME '02:06:05', TIME '01:06:05', TIME '02:06:05', TIME '02:06:04', TIME '01:06:05', TIME '02:06:05', TIME '01:06:05', TIME '02:06:04', TIME '02:05:05', TIME '01:06:05', TIME '16:45:56', TIME '02:06:05', TIME '02:05:05', TIME '14:05:20', TIME '02:06:05', TIME '02:06:04', TIME '01:06:05', TIME '02:06:05', TIME '02:06:05'] AS col_3 FROM with_0 WHERE EXISTS (SELECT (SMALLINT '8848') / (((4612983059751132871) / (INT '-844083123')) - CASE WHEN true THEN (SMALLINT '26634') WHEN true THEN (SMALLINT '-32768') WHEN EXISTS (SELECT (0) AS col_0, (SMALLINT '-32768') AS col_1, (9223372036854775807) AS col_2, (REAL '0') + (REAL '781747395') AS col_3 FROM customer AS t_4 GROUP BY t_4.c_acctbal, t_4.c_custkey HAVING false) THEN (SMALLINT '3904') WHEN true THEN (SMALLINT '18651') ELSE (SMALLINT '31330') END) AS col_0, 'oLU25HSrBf' AS col_1, (1580193718486657888) AS col_2 FROM m2 AS t_3 GROUP BY t_3.col_3, t_3.col_2, t_3.col_1 HAVING false);
---- END

Reason:
db error: ERROR: ExecuteError: Bind error: types Boolean and Varchar cannot be matched
', /risingwave/src/tests/sqlsmith/src/runner.rs:197:13
stack backtrace:
   0: rust_begin_unwind
             at /rustc/3984bc5833db8bfb0acc522c9775383e4171f3de/library/std/src/panicking.rs:575:5
   1: core::panicking::panic_fmt
             at /rustc/3984bc5833db8bfb0acc522c9775383e4171f3de/library/core/src/panicking.rs:64:14
   2: risingwave_sqlsmith::runner::validate_response
             at ./src/tests/sqlsmith/src/runner.rs:197:13
   3: risingwave_sqlsmith::runner::test_stream_queries::{{closure}}
             at ./src/tests/sqlsmith/src/runner.rs:105:20
   4: risingwave_sqlsmith::runner::test_sqlsmith::{{closure}}
             at ./src/tests/sqlsmith/src/runner.rs:59:81
   5: risingwave_sqlsmith::runner::run::{{closure}}
             at ./src/tests/sqlsmith/src/runner.rs:28:64
   6: risingwave_simulation::main::{{closure}}::{{closure}}::{{closure}}
             at ./src/tests/simulation/src/main.rs:175:85
   7: <core::pin::Pin<P> as core::future::future::Future>::poll
             at /rustc/3984bc5833db8bfb0acc522c9775383e4171f3de/library/core/src/future/future.rs:125:9
   8: async_task::raw::RawTask<F,T,S>::run
             at ./.cargo/registry/src/github.jparrowsec.cn-1ecc6299db9ec823/async-task-4.3.0/src/raw.rs:511:20
   9: madsim::sim::task::Executor::run_all_ready
             at ./.cargo/registry/src/github.jparrowsec.cn-1ecc6299db9ec823/madsim-0.2.14/src/sim/task.rs:208:17
  10: madsim::sim::task::Executor::block_on
             at ./.cargo/registry/src/github.jparrowsec.cn-1ecc6299db9ec823/madsim-0.2.14/src/sim/task.rs:159:13
  11: madsim::sim::runtime::Runtime::block_on
             at ./.cargo/registry/src/github.jparrowsec.cn-1ecc6299db9ec823/madsim-0.2.14/src/sim/runtime/mod.rs:124:9
  12: madsim::sim::runtime::builder::Builder::run::{{closure}}::{{closure}}::{{closure}}
             at ./.cargo/registry/src/github.jparrowsec.cn-1ecc6299db9ec823/madsim-0.2.14/src/sim/runtime/builder.rs:128:35

Full logs here.

[kwannoel]

Shrunk

select false >= 'LN1O0QP1yi' NOT IN (
    md5('4SeUPZhUbH'));

dev=> select false >= 'LN1O0QP1yi' NOT IN (
    md5('4SeUPZhUbH'));
ERROR:  QueryError: Bind error: types Boolean and Varchar cannot be matched

postgres>  select false >= 'LN1O0QP1yi' NOT IN (
    md5('4SeUPZhUbH'));
 ?column?
----------
 f
(1 row)

Parsing precedence issue?

[ioperations]

could fix this issue , but care should be taken

[fuyufjh]

@ioperations Looks good! Would you mind opening a PR and assign @xiangjinwu and @TennyZhuang as reviewers?

[xiangjinwu]

https://www.postgresql.org/docs/15/sql-syntax-lexical.html#SQL-PRECEDENCE

The upstream sqlparser-rs also treats >= (on left) with higher priority than (NOT) IN for this query, maybe because of MySQL.

[xxchan]

and assign ... as reviewers?

@fuyufjh Only people inside org can request review. Do you want to introduce some "request review bot"? 🥵