[MDEV-18605] Loss of column aliases by using view and group Created: 2019-02-16  Updated: 2020-08-25  Resolved: 2019-02-26

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.3.11, 10.3.12, 10.3, 10.4
Fix Version/s: 10.3.14, 10.4.4

Type: Bug Priority: Critical
Reporter: Friedemann Schmidt Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 4
Labels: alias, group, views
Environment:

windows 10


 Description   

hello,

upgrade from 10.2 to 10.3 due to a crash in some of my stored procedures.

Since the upgrade from 10.2 to 10.3, the column name is output incorrectly if the column is formed in the view using a function and is used WHERE- and GROUP BY-statements in the query.

I tested it on a Server with 10.2: in all cases the output is "bar" as column-name
on 10.3 the output is "foo" in the last case

CREATE TABLE t1 (id int, foo int);
 
CREATE VIEW v1 AS  SELECT id, IFNULL(foo,'') AS foo FROM t1;
 
 
 
INSERT INTO t1 (id, foo) VALUES (1,1),(2,2);
 
 
 
SELECT v.id, v.foo AS bar  FROM v1 v
 
  WHERE id = 2;
 
//normal output as "bar"
 
-- id , bar
 
-- 2  , 2
 
 
 
SELECT v.id, v.foo AS bar  FROM v1 v
 
  GROUP BY v.id;
 
//normal output as "bar"
 
-- id , bar
 
-- 1  , 1
 
-- 2  , 2
 
 
 
SELECT v.id, v.foo AS bar  FROM v1 v
 
  WHERE id = 2
 
  GROUP BY v.id;
 
//unexpected output as "foo"!! only in 10.3
 
-- id , foo
 
-- 2  , 2
 
 
 
//Cleanup
 
Drop View v1;
 
Drop table t1;



 Comments   
Comment by Alice Sherepa [ 2019-02-18 ]

Thanks for the report!
Reproducible as described, on MariaDB 10.3-10.4

Comment by Alex Boag-Munroe [ 2019-02-22 ]

Added a vote for this issue as we've just seen the same thing when aliasing a column from a VIEW.

Starts in 10.3.1 (was unable to install 10.3.0) but is fine in all other versions up to 10.2 latest.

Comment by Alex Boag-Munroe [ 2019-02-22 ]

In our tests, simply using GROUP BY breaks the alias (but if you GROUP BY on the alias, it works again).

Comment by Oleksandr Byelkin [ 2019-02-25 ]

revision-id: dee4b76f607ca4ce6bec6338f1a29f742dd64601 (mariadb-10.3.12-66-gdee4b76f607)
parent(s): 09bd2138522787a4e0b015695c462903f4a9e728
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2019-02-25 15:57:08 +0100
message:

MDEV-18605: Loss of column aliases by using view and group

Preserv column name with copy fields even if it is function and Co.

—

Comment by Alexander Barkov [ 2019-02-26 ]

The patch dee4b76f607ca4ce6bec6338f1a29f742dd64601 is OK to push.

Generated at Thu Feb 08 08:45:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.