[MDEV-28651] quote(NULL) returns incorrect result in view ('NU' instead of 'NULL') Created: 2022-05-24  Updated: 2024-01-23  Resolved: 2024-01-23

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.4.1

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: view-protocol

Issue Links:
PartOf
is part of MDEV-27691 make working view-protocol Open
Relates
relates to MDEV-27945 Select from view with multi-byte char... Open
relates to MDEV-28585 Wrong result for view defined as SELE... Open
relates to MDEV-28649 json_array(false) in the definition o... Stalled
relates to MDEV-28652 SUBSTRING(str,pos,len) returns incorr... Open
relates to MDEV-28659 View returns wrong value for big digi... Open
relates to MDEV-28660 TRUNCATE(X,D) returns incorrect resul... Open
relates to MDEV-28661 MIN(X) returns incorrect result in view Open
relates to MDEV-28673 View returns different value for min... Open
relates to MDEV-28677 UDF returns wrong string value in view Open
relates to MDEV-29524 "-0000000000000001" changes to "-1" i... Open
relates to MDEV-29525 CAST in view definition returns strin... Open
relates to MDEV-29526 ROUND(X,D) returns incorrect result ... Open
relates to MDEV-29534 In view FROM_UNIXTIME adds .000000 in... Open
relates to MDEV-29647 ROW_NUMBER is not 0 for warnings in c... Open
relates to MDEV-29552 LEFT and RIGHT with big value for par... Open
relates to MDEV-31587 quote(NULL) returns incorrect result ... Open

 Description   

Test:

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

Actual result:

NU

Expected result:

NULL



 Comments   
Comment by Oleksandr Byelkin [ 2022-12-01 ]

It looks like some type processing mismatch:

select quote(NULL);
 
quote(NULL)
 
NULL
 
explain extended select quote(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 quote(NULL) AS `quote(NULL)`
 
create view v1 as select quote(NULL);
 
select * from v1;
 
quote(NULL)
 
NU
 
show create view v1;
 
View	Create View	character_set_client	collation_connection
 
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select quote(NULL) AS `quote(NULL)`	latin1	latin1_swedish_ci
 
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 'NU' AS `quote(NULL)` from dual
 
drop view v1;


select quote(NULL);
 
explain extended select quote(NULL);
 
create view v1 as select quote(NULL);
 
select * from v1;
 
show create view v1;
 
explain extended select * from v1;
 
drop view v1;

Comment by Oleksandr Byelkin [ 2022-12-01 ]

I looks like metainformation is wrong, so save in field saves only NU from NULL

Comment by Oleksandr Byelkin [ 2022-12-01 ]

Probably duplicate of MDEV-29552 (but versions mismatch)

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