[MDEV-19913] Query with virtual columns are stored in query cache even when in expression of virtual columns exists CURRENT_TIMESTAMP() Created: 2019-07-01  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Query Cache, Virtual Columns
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Peter Stöcker Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: querycache, virtual_columns
Environment:

with 10.3 and 10.4 system on Windows and Linux


Issue Links:
Duplicate
duplicates MDEV-14043 Virtual columns depending on system f... Open

 Description   

With the following procedure you can reproduce the bug:

1. Query cache needs to be ON
2. Create the Table

CREATE TABLE `test`.`virtualtest` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`aDateTime` DATETIME,
`isGreater` TINYINT(1) GENERATED ALWAYS AS (IF(CURRENT_TIMESTAMP()>`aDateTime`,TRUE,FALSE)) VIRTUAL,
PRIMARY KEY (`id`)
);

3. Insert data

INSERT INTO `virtualtest` (`aDateTime`) VALUES (TIMESTAMPADD(SECOND,30,CURRENT_TIMESTAMP()));

4. Select the data

SELECT * FROM `virtualtest` a;

Expected behaviour:
When current timestamp ist graeater than the timestamp in the table the field isGreater whould become true.
But when the query is executed before (isGreater=false) this time the result will keep FALSE even when current timestamp is greater.

Conclusion:
Obviously the result set is stored in query cache. I guess the check for query cache doesn't check the expressen of virtual columns.


Generated at Thu Feb 08 08:55:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.