[MDEV-28585] Wrong result for view defined as SELECT ROW(1,2,3) = (SELECT 1,2,NULL) Created: 2022-05-17  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.11

Type: Bug Priority: Critical
Reporter: Lena Startseva Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: view-protocol

Issue Links:
PartOf
is part of MDEV-27691 make working view-protocol Open
Relates
relates to MDEV-28651 quote(NULL) returns incorrect result ... Closed
relates to MDEV-31728 Cursor protocol returns wrong result... Open

 Description   

Query 

SELECT ROW(1,2,3) = (SELECT 1,2,NULL);

return as result

NULL

But the view with such definition returns 0:

Test:

CREATE VIEW v1 as SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
 
SELECT * FROM v1;
 
 
 
DROP VIEW v1;

Actual result:

ROW(1,2,3) = (SELECT 1,2,NULL)
 
0

Query plans are also very different:
select

EXPLAIN EXTENDED SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
Warnings:
 
Note	1003	/* select#1 */ select (1,2,3) = (/* select#2 */ select 1,2,NULL) AS `ROW(1,2,3) = (SELECT 1,2,NULL)`

view

CREATE VIEW v1 as SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
 
EXPLAIN EXTENDED SELECT * FROM v1;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
Warnings:
 
Note	1003	/* select#1 */ select 0 AS `ROW(1,2,3) = (SELECT 1,2,NULL)` from dual



 Comments   
Comment by Lena Startseva [ 2022-05-24 ]

The same problem with function BENCHMARK(count,expr)
Test:

create view v1 as select benchmark(NULL, NULL);
 
select * from v1;
 
drop view v1;

Actual result:

0

Expected result:

NULL

Query plans:
select

explain extended select benchmark(NULL, NULL);
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
Warnings:
 
Note	1003	select benchmark(NULL,NULL) AS `benchmark(NULL, NULL)`

view

create view v1 as select benchmark(NULL, NULL);
 
explain extended  select * from v1;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 
Warnings:
 
Note	1003	/* select#1 */ select 0 AS `benchmark(NULL, NULL)` from dual

Comment by Oleksandr Byelkin [ 2022-12-01 ]

It looks like the same problem with types, but now it just loose NULL

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