Unexpected Results about Floating-point Type Casting

[suyZhong]

Considering the test case below:

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

SELECT * FROM t1 WHERE (1 AND true); -- 1
SELECT * FROM t1 WHERE (1/1 AND true);
-- Expected: 1
-- Actual: empty

The second SELECT returns an empty result, which is surprising: since the only difference between two queries is the integer 1 and float 1/1. I suppose there's some type coercion issue in floating-point, as mentioned in issue #7006

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

I originally find this by building dolt from source version (76c2f46). It could also be reproduced in 1.26.1

[timsehn]

Thanks again for the great correctness bugs! @max-hoffman will fix this today.

[max-hoffman]

@suyZhong How are you generating these queries? If you have a list of queries or a fuzz tester we can fix a batch of bugs faster than one-off tickets

[max-hoffman]

@suyZhong @mrigger If you are using sqlance with the mariaDB harness to generate these, would you mind sharing the parameters you are using?

[jycor]

Hey @suyZhong, the fix for this has been merged to gms, and will eventually make its way to dolt.

Type coercion is a spotty area for us, but we have plans to improve it in the near future.
It'd be a great help if you could provide some of your testing methods or create batches of issues.

[suyZhong]

@max-hoffman @jcor11599 Thanks for your feedback and fix of the issue!

Yes, I am indeed working on a new fuzz tester, but it's likely not useful to share multiple tests at once, since typically, many test cases are generated that repeatedly hit the same bug. I'll try to report multiple likely unrelated bugs (if possible) at once next time.

I recently started on the project. I plan to further develop the approach and then share the fuzzer, open-source it, and also willing to help with integrating it into your platform. Indeed, I am developing an improved version of SQLancer, and the current version of SQLancer lacks a "harness" of Dolt.

[max-hoffman]

@suyZhong We appreciate the interest in our project! Dolt should be compatible with any MySQL wire layer interfaces you have, if you find discrepancies we are also happy to fix those bugs.