[MySQL] Datatype mismatch in objects causing issues

[shubham-yb]

Due to the different datatype mapping, example INT is parsed as BIGINT from MYSQL, it has created an issue where certain functions are concerned since their body is passed as is and a type mismatch occurs.

Please consider this example below for better understanding.

drop table if exists bar;
create table bar(
      id int, 
      p_name varchar(10)
);

drop function if exists foo;
delimiter //

create function foo (p_id int)
returns varchar(20)
reads sql data
begin
return (
select p_name from bar where p_id=id
);
end//

delimiter ;

CREATE OR REPLACE VIEW v1 
as 
select foo(id) as p_name 
from bar;

This schema when exported, is exported as:

CREATE TABLE bar (
        id bigint,
        p_name varchar(10)
) ;

CREATE OR REPLACE FUNCTION foo (p_id integer) RETURNS varchar AS $body$
BEGIN
return(select p_name from bar where p_id=id);
end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

CREATE OR REPLACE VIEW v1 AS select foo(bar.id) AS p_name FROM bar;

The table and the function get created fine. But the view creation errors out with:

ERROR:  function foo(bigint) does not exist
LINE 1: CREATE OR REPLACE VIEW v1 AS select foo(bar.id) AS p_name F...
                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

As you can see, since the table column is of type BIGINT but the parameter is INT, it errors out.

The same view definition if run as:

CREATE OR REPLACE VIEW v1 AS select foo(bar.id::integer) AS p_name FROM bar;

Will work fine.