Bug #110988 VIEW call error

• Submitted: 11 May 2023 8:06
• Modified: 10 Jul 2023 13:11
• Reporter: Pedro Ferreira
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Ubuntu (22.04)
• CPU Architecture: x86 (x86_64)
• Tags: VIEW, year

[11 May 2023 8:06] Pedro Ferreira

Description:
This may be related to Bug #110577. Create the table and the view as follows:

CREATE SCHEMA test;
USE test;
CREATE TABLE t0 (c0 YEAR);
CREATE VIEW v1 (c0) AS (SELECT 1 FROM t0 WHERE (SELECT 1 WHERE CAST(2000 AS YEAR) BETWEEN t0.c0 AND CAST(2048 AS YEAR)));

Then run:
SELECT 1 FROM v1;
--ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Strangely if I run the SELECT query outside the view, it goes fine.

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 statements above.

[11 May 2023 9:14] MySQL Verification Team

Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh

[15 Jun 2023 2:59] Pedro Ferreira

Reproduced again today with:

CREATE VIEW v1(c0) AS (SELECT '' LIKE CAST(X'' AS CHAR));
SELECT c0 FROM v1; -- View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

[10 Jul 2023 13:11] Pedro Ferreira

Reproduced again with:

CREATE TABLE t0(c0 BOOLEAN);
CREATE VIEW v0(c0) AS (SELECT 1 FROM (SELECT 1 FROM t0) x(x) GROUP BY x.x HAVING x.x);
SELECT 1 FROM v0; --ERROR 1356 (HY000): View 'sys.v0' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them