[MDEV-32770] Mariadb-dump improper view creation Created: 2023-11-10  Updated: 2023-11-13

Status: Confirmed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 11.1.2
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Tadas Balaišis Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Win64


 Description   

Script, created by mariadb-dump, got execution errors due to improper view creation sequence. The reason is initial view creation by such type commands:

CREATE VIEW `...` AS SELECT
 
1 AS `...`,
 
1 AS `...`,

After that view columns defined as numeric despite original column types.
Then comes second view creation that uses function on underlying view columns and it hits an error of wrong type argument. This could be fixed if initial view would use corresponding type constant (for example, now() for datetime columns, '' for char columns), not only 1 for columns of all types.

 Comments   
Comment by Daniel Black [ 2023-11-12 ]

Can you please elaborate to make this a full set of steps and SQL structures.

Comment by Tadas Balaišis [ 2023-11-13 ]

Here is simplified sample:

CREATE VIEW v2 AS 
SELECT NOW() d1;
 
 
 
CREATE VIEW v1 AS 
SELECT to_char(d1,'MM') c1 FROM v2; 
-- sql_mode=oracle

mariadb-dump will create sql script with:

CREATE VIEW v1 AS 
SELECT 1 c1;
 
 
 
CREATE VIEW v2 AS 
SELECT 1 d1;
 
 
 
CREATE VIEW v1 AS 
SELECT to_char(d1,'MM') c1 FROM v2; 
-- will hit an error about wrong type

Comment by Sergei Golubchik [ 2023-11-13 ]

geomfromtext() is another example of a function that fails with incorrect types

Comment by Alexander Barkov [ 2023-11-13 ]

New data types like INET4, INET6, UUID have stricter rules than old data types.
So in fact there are many functions that raise an error during fix_fields():

Examples:

MariaDB [test]> select uuid()+1;
 
ERROR 4078 (HY000): Illegal parameter data types uuid and int for operation '+'


MariaDB [test]> select -uuid();
 
ERROR 4079 (HY000): Illegal parameter data type uuid for operation '-'


MariaDB [test]> select timestamp(uuid());
 
ERROR 4079 (HY000): Illegal parameter data type uuid for operation 'cast_as_datetime'

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