MySQL Bugs: #110678: oct query with wrong result

Bug #110678	oct query with wrong result
Submitted:	13 Apr 2023 11:26	Modified:	14 Apr 2023 8:25
Reporter:	Pedro Ferreira	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	oct

Description:
Run these queries:

CREATE TABLE t0 (c0 TIME, c1 TINYBLOB AS (t0.c0) STORED);
INSERT INTO t0(c0) VALUES (TIME '5:26:10'),(NULL),(NULL),(TIME '63:46:49'),(TIME '18:59:49'),(TIME '76:43:4'),(TIME '49:2:52'),(NULL),(TIME '15:9:0'),(TIME '64:0:35'),(TIME '19:11:21');

Then this pair:

SELECT 1 FROM t0 WHERE oct(DATE '1118-9-2') > t0.c1;
SELECT CAST(sum(c1) AS SIGNED) FROM (SELECT CAST(oct(DATE '1118-9-2') > t0.c1 AS SIGNED) FROM t0) t1 (c1);

Although they are equivalent, the first query returns 4 rows, while the global aggregate on the second query returns 3. The number of rows of the first should be the same as the sum result. By looking more carefully it seems the first query is wrong.

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.

HI Mr. Ferreira,

Thank you for your bug report.

However, we can not repeat the behaviour with our 8.0.32 binary.

Both queries return zero rows in the result set, which is expected behaviour.

For the first query, the expression is always FALSE, since you are comparing octal numbers with TIME, which is of the different type.

For the second query, a derived table returns only NULLs, zeros and three times it returns 1, which makes the entire second query returning a single row with a value of 3. This is because your second query is an aggregated one ......

Not a bug.

On my setup, I get different results. Have you tried using a schema other than the default one?

Hi,

We created a new schema for this bug .....

So, the answer is YES.

Can you try with the same server parameters as me?

mysqld --no-defaults --user=mysql --basedir=... --datadir=... --plugin-dir=... --log-error=... --log-error-verbosity=3 --pid-file=... --port=3306 --mysqlx-port=3307 --slave-parallel-type=LOGICAL_CLOCK --slave-parallel-workers=4 --slave-preserve-commit-order=ON --enforce-gtid-consistency --gtid-mode=ON --binlog-format=ROW --server-id=1 --core-file --default-authentication-plugin=mysql_native_password

Hi,

It comes to the same result.