[MDEV-28585] Wrong result for view defined as SELECT ROW(1,2,3) = (SELECT 1,2,NULL) - Jira

XML

Word

Printable

Type: Bug
Status: Open (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
Fix Version/s: 10.11
Component/s: None
Labels:
- view-protocol

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)

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

is part of

MDEV-27691 make working view-protocol

relates to

MDEV-28651 quote(NULL) returns incorrect result in view ('NU' instead of 'NULL')

MDEV-31728 Cursor protocol returns wrong result for SELECT ROW(1,2,3) = (SELECT 1,2,NULL)

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