[MDEV-36991] FullText indexes in System Versioned tables - Jira

XML

Word

Printable

Hi,
we noticed that in MATCH AGAINST clauses on a versioned column only the last version is retrived.
That's the example:

CREATE TABLE `t` (

	`name` VARCHAR(45) NOT NULL WITH SYSTEM VERSIONING,

	FULLTEXT INDEX `ftx_name` (`name`),

        INDEX `idx_name` (`name`)

);

INSERT INTO `t` VALUE ('franco');

UPDATE `t` SET `name` = 'antonio';

This shows both 'franco' and 'antonio'.

SELECT `name` FROM `t` FOR SYSTEM_TIME ALL WHERE MATCH(`name`) AGAINST ('franco' IN BOOLEAN MODE);

This doesn't show anything.

SELECT `name` FROM `t` FOR SYSTEM_TIME ALL WHERE MATCH(`name`) AGAINST ('antonio' IN BOOLEAN MODE);

This shows 'antonio'.

On the other way,

SELECT `name` FROM `t` FOR SYSTEM_TIME ALL WHERE `name` = 'franco';

, which goes on the other index, shows up 'franco' correctly.

I tryied also to create a standard versioned table as follows, but with no luck.

CREATE TABLE t2 (

   `name` VARCHAR(45) NOT NULL,

   start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,

   end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,

   PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp),

   FULLTEXT INDEX `ftx_name` (`name`),

	INDEX `idx_name` (`name`)

) WITH SYSTEM VERSIONING;

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.