Repository navigation
CREATE VIEW doesn't respect column naming #10741
Description
CREATE VIEW, similar to CREATE TABLE takes a list of column names as its argument (CREATE VIEW(col1, col2, ...)). This is documented behavior in MySQL. Dolt also seems to take the column names and store then somewhat (see the DESCRIBE Ass result) but in its query execution it seems they are ignored.
CREATE TABLE Foo(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255)
);
INSERT INTO Foo(name) VALUES ('Brian', 'Odin', 'Lana');
CREATE TABLE Bar(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
type VARCHAR(64)
);
INSERT INTO Bar(name, type) VALUES ('Kyle', 'MECH'), ('Elenore', 'HEAD');
-- Note the specified column names of the view
CREATE VIEW Ass(id, name, type) AS
SELECT id, name, 'STAFF' FROM Foo
UNION ALL
SELECT id, name, type FROM Bar;
DESCRIBE Ass;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(255) | YES | | NULL | |
| type | longtext | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
SELECT * FROM Ass WHERE type = 'MECH';
-- unable to find field with index 3 in row of 3 columns.
-- This is a bug. Please file an issue here: https://github.com/dolthub/dolt/issuesHowever, when instead declaring the column names in the first SELECT of the view:
DROP VIEW Ass;
CREATE VIEW Ass AS
SELECT id, name, 'STAFF' AS type FROM Foo
UNION ALL
SELECT id, name, type FROM Bar;
SELECT * FROM Ass WHERE type = 'MECH';
+----+------+------+
| id | name | type |
+----+------+------+
| 1 | Kyle | MECH |
+----+------+------+
1 row in set (0.00 sec)
DoltHub aschnappauf/view-column-names
(Sorry, i would have saved the respective queries but it seems DoltHub doesn't like saved queries containing views(?))
Activity
elianddb commentedon Mar 26, 2026
Got a PR for this dolthub/go-mysql-server#3488, waiting for review