Unexpected Results when Using '%' operator

[suyZhong]

Considering the test case below:

CREATE TABLE t0(c1 INT);
INSERT INTO t0(c1) VALUES (1);

SELECT * FROM t0;
SELECT (c1 NOT IN (1%true)) IS NULL FROM t0;  -- 1
SELECT * FROM t0 WHERE (c1 NOT IN (1%true)) IS NULL; 
-- Expected: 1
-- Actual: empty

The third SELECT returns an empty result, which is surprising: If the result of second query is 1 (TRUE), the value of the NOT IN expression should be true, and thus the third query should return the row in t0.

This test case works well in MySQL 8.0.33, however not works in dolt 1.26.1

Besides, I also find a discrepancy between dolt and MySQL for this query:

SELECT 1 % true;
-- MySQL: 0
-- dolt: NULL

I originally find this when using the latest docker image dolt-sql-server 1.26.1

[timsehn]

Thanks for the issue. We will look at this today. I'm pretty sure because of the IN expression this is analyzer related so @max-hoffman or @jcor11599 will dig in.

[max-hoffman]

It looks like bad type conversion is at least one issue here, we convert the true to a decimal type of zero and error for division by zero. Will get a fix for that shortly

[max-hoffman]

This fixes the bug in my testing dolthub/go-mysql-server#2147. The original query you posted SELECT * FROM t0 WHERE (c1 NOT IN (1%true)) IS NULL; returns an empty result set when I test in MySQL and Dolt; (c1 NOT IN (1%true)) => c1 not in {0} => 1 not in {0} will be true, and so (c1 NOT IN (1%true)) IS NULL will be false. Let me know if there are other bugs here I am missing, our type coercion is still patchy in some places.

[bpf120]

@suyZhong , Thanks for filing. We'd love to learn about how you are using Dolt too. If you want to share, email me or swing by our Discord.