CTE Issue with table name

I am evaluating the current version of mariadb (10.6.11). Currently we are using 10.5.10 and planning to upgrade to 10.6.

One of our queries uses CTE and mariadb throws an error `SQL Error (1932): Table 'xxxx' doesn't exist in engine`.

`lower_case_table_names` is off so that table names are handled as case-sensitive.

We have a table `ScheduledTaskJournal` we use CTE to retrieve the last entry per `ScheduledTaskId`.

here is the SQL Code to reproduce the problem.


CREATE TABLE IF NOT EXISTS `ScheduledTaskJournal` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `CreatedAt` datetime(6) NOT NULL, `UpdatedAt` datetime(6) NOT NULL, `ScheduledTaskId` bigint(20) NOT NULL, `Result` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `ResultDetail` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`Id`), KEY `IX_ScheduledTaskJournal_CreatedAt` (`CreatedAt`), KEY `IX_ScheduledTaskJournal_ScheduledTaskId` (`ScheduledTaskId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_nopad_ci;

WITH latest_journal AS ( SELECT stj.ScheduledTaskId ,MAX(stj.CreatedAt) CreatedAt FROM `ScheduledTaskJournal` stj WHERE stj.ScheduledTaskId IN (1,2,3) GROUP BY stj.ScheduledTaskId ), last_items AS ( SELECT stj2.* ,ROW_NUMBER() OVER ( PARTITION BY stj2.ScheduledTaskId ORDER BY stj2.CreatedAt DESC ) AS RowNumber FROM `ScheduledTaskJournal` stj2 JOIN latest_journal jl ON jl.ScheduledTaskId = stj2.ScheduledTaskId AND jl.CreatedAt = stj2.CreatedAt ) SELECT * FROM last_items WHERE RowNumber = 1

Could you tell me what I did wrong?


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.