[MDEV-28535] View is created with wrong column name if there are more than one same column name or column name is empty Created: 2022-05-11  Updated: 2023-03-03  Resolved: 2022-12-01

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Oleksandr Byelkin
Resolution: Not a Bug Votes: 0
Labels: view-protocol

Issue Links:
PartOf
is part of MDEV-27691 make working view-protocol Open

 Description   

According standard "If a <view column list> is" not "specified ... the <column name>s of the view are the <column name>s of the table specified by the <query expression>"

In simple select is everything good:

select null, null, null;
 
NULL	NULL	NULL
 
NULL	NULL	NULL

But if column name is NULL and there are more than one such column name in select for creating view, than columns have names like "My_exp_NULL", "My_exp_1_NULL" and etc.

Test:

create view v1 as select null, null, null;
 
select * from v1;
 
drop view v1;

Actual result:

create view v1 as select null, null, null;
 
select * from v1;
 
NULL	My_exp_NULL	My_exp_1_NULL
 
NULL	NULL	NULL
 
drop view v1;

Expected result:

create view v1 as select null, null, null;
 
select * from v1;
 
NULL	NULL	NULL
 
NULL	NULL	NULL
 
drop view v1;

=============================================================================================================================

The same problem is with aggregate functions:

Test 2:

CREATE TABLE t1 (a int(11));
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
select a, SUM(a), SUM(a)+1, SUM(a) from t1;
 
 
 
create view v1 as select a, SUM(a), SUM(a)+1, SUM(a) from t1;
 
select * from v1;
 
 
 
drop view v1;
 
drop tables t1;

Actual result 2:

select a, SUM(a), SUM(a)+1, SUM(a) from t1;
 
a	SUM(a)	SUM(a)+1	SUM(a)
 
1	3	4	3
 
 
 
create view v1 as select a, SUM(a), SUM(a)+1, SUM(a) from t1;
 
select * from v1;
 
a	SUM(a)	SUM(a)+1	My_exp_SUM(a)
 
1	3	4	3



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

Test 3:

CREATE VIEW v1 as select N'', length(N'');
 
SELECT * FROM v1;
 
DROP VIEW v1;

Actual result:

Name_exp_1	length(N'')
 
	0

Expected result:

	length(N'')
 
	0

Comment by Oleksandr Byelkin [ 2022-12-01 ]

It is well known problem, sending results allows the same name of columns, view (as any table) do not allow columns with the same name, so it uses generated names.

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