[MDEV-28551] Full text query returning NULL generated columns Created: 2022-05-12  Updated: 2022-11-23  Resolved: 2022-11-23

Status: Closed
Project: MariaDB Server
Component/s: Full-text Search, Virtual Columns
Affects Version/s: 10.3.32, 10.6.7
Fix Version/s: 10.11.2, 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Major
Reporter: Wilson Medeiros dos Santos Neto Assignee: Nikita Malyavin
Resolution: Duplicate Votes: 1
Labels: None

Issue Links:
Duplicate
duplicates MDEV-29169 Using MATCH returns NULL for Virtual ... Closed

 Description   

When querying a table that has virtual generated columns using full text search, these columns are not generated and are always NULL in the results.

It can be seen here: https://dbfiddle.uk/?rdbms=mariadb_10.6&rdbms2=mysql_8.0&fiddle=524b4b7e77d159f0ce8740b639aaa9fd

 Comments   
Comment by Daniel Black [ 2022-05-13 ]

Nice use of dbfiddle's comparison.

Copying here for preservation's sake:

CREATE TABLE entry (
 
  `id` int unsigned PRIMARY KEY AUTO_INCREMENT,
 
  `field1` varchar(200),
 
  `field2` varchar(200),
 
  `gen_aux1` tinyint unsigned NOT NULL,
 
  `gen_aux2` tinyint unsigned NOT NULL,
 
  `gen` tinyint unsigned GENERATED ALWAYS AS (`gen_aux1` AND `gen_aux2`) VIRTUAL,
 
  FULLTEXT INDEX `idx_text` (`field1`, `field2`)
 
);
 
 
 
INSERT INTO entry(`field1`, `field2`, `gen_aux1`, `gen_aux2`)
 
VALUES ('hello', 'world', 0, 1),
 
	   ('hello lorem', 'hello ipsum', 1, 1);
 
 
 
SELECT * FROM `entry`;
 
 
 
# gen is NULL in MariaDB
 
SELECT * FROM `entry`
 
WHERE MATCH(`field1`, `field2`) AGAINST ('hello' IN NATURAL LANGUAGE MODE);

actual results

 
id 	field1 		field2 		gen_aux1 	gen_aux2 	gen
 
2 	hello l..	hello ip.	1 		1 		NULL
 
1 	hello 		world 		0 		1 		NULL

expected results (MySQL 5.7.35, 8.0.27)

 
id 	field1 		field2 		gen_aux1 	gen_aux2 	gen
 
2 	hello l.	hello ip..	1 		1 		1
 
1 	hello 		world 		0 		1 		0

Comment by Marko Mäkelä [ 2022-05-13 ]

If I remember correctly, the virtual column implementation in MySQL 5.7 allows virtual columns to be declared NOT NULL, while MariaDB does not. I suppose that the motivation of that design decision is to allow evaluation errors to be mapped to NULL values. (INSERT in MariaDB does not evaluate virtual columns, unless they are indexed. Hence, a virtual column expression like 1/x does not work as a substitute for a constraint CHECK x!=0.)

Without looking at the code, I would guess that here the problem is that the values of virtual columns are simply not computed in the relevant code path, and a ‘default’ value of NULL is being reported.

Generated at Thu Feb 08 10:01:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.