MySQL Bugs: #110310: BETWEEN query with wrong result

Bug #110310	BETWEEN query with wrong result
Submitted:	9 Mar 2023 8:41	Modified:	9 Mar 2023 12:26
Reporter:	Pedro Ferreira	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	between, case, time

Description:
Run the following queries:

CREATE TABLE t0 (c0 INT);
INSERT INTO t0(c0) VALUES (2030666);

Then run these two:

SELECT 1 FROM t0 WHERE c0 BETWEEN 0 AND CASE WHEN 1 THEN TIME '571:0:0' ELSE 2 END;
SELECT CAST(sum(c0) AS SIGNED) FROM (SELECT CAST(c0 BETWEEN 0 AND CASE WHEN 1 THEN TIME '571:0:0' ELSE 2 END AS SIGNED) FROM t0) t0 (c0);

Although they are equivalent, the first query returns 0 rows, while the global aggregate on the second query returns 1. The number of rows of the first should be the same as the sum result. TIME '571:0:0' converted as int gives 5710000, so the between predicate is true for 2030666 and the query should return 1 row instead of 0.

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.

Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh

Hello,

I'm a beginner contributor and I'm trying to fix this bug. So feel free to correct me if I'm wrong.
I first tried to look for the error, I think it comes either from CASE or from BETWEEN.
So I found the corresponding class in sql/item.cc for CASE and sql/item_cmpfunc.cc for BETWEEN.

However, I wonder if the error really comes from there, and if these class are really those which manage CASE and BETWEEN.
Does anyone have an idea where the error could be coming from and guide me on the guidelines to follow to solve it please?

I am of course in version 8.0.32, I followed all the information of the report.

Thank you in advance for your answers.

These classes indeed implement the CASE and BETWEEN clauses.
However, the bug case also contains other operations, like an aggregation,
a derived table definition, and several implicit type conversions,
so it is quite hard to give a advice for where to start debugging.

You might try to isolate individual functions to see if the problem occurs with
or without those functions.

Hello,

Thank you for your answer, I will try to find the problem as best as I can.
I'm listening if other people have found the bug or have an idea I'm still interested.

Good morning,

Unfortunately, I did not manage to solve this bug, I allow myself to ask for help and share my tests with you.

So I checked the CASE (Item_case_expr) and BETWEEN (Item_func_between) codes but I didn't find any problem. SUM (item_sum.cc) works fine. So I only had to test the aggregations, AS SIGNED (Item_typecast_signed) has several ASSERTs so the conversion must be correct.

I am a taker for any type of answers, if I was wrong on a part, a code that does not correspond to his request or if you have suggestions or resolutions.

Thank you in advance for your help.