MySQL views datatype mismatch

Question

Hi, 
 
 I'm facing issue in views due to this bug https://bugs.mysql.com/bug.php?id=61131 . 
 While publishing datastore I'm gettting error (The data source schema does not match the type mappings: Wrong column type in XXXXXXX_View for column XXXXX. Found: tinyint, expected: boolean (APNX-2-4056-000)). 
 I don't want to change Appian datatype as there're many dependents. Is there any solution from database perspective to change those columns back from tinyint(4) to tinyint(1). 
 
 Thanks in advance.

josep · Accepted Answer

In that case because of that bug i don't think you have too much options, well you have a lot but some are not that easy. 
1) Correct the error recompile the MySQL :) 
2) wait until they fix it 
3) change the database 
just kidding =D , but still an option 
 
you have to decide what do you prefer , extra proccesing? extra memory? extra space? update code? 
4) update the CDT's 
5) recreate all your current views with a preffix "_pre" or post fix "pre_" and create the view with calling the function. 
6) change your design, instead using 2 tables and a union put everything in the same table. 
 
7) create the union your send in another table 
7a) bulk both tables in 1 table (no union) and the new table will have a type to differentiate between the two tables 
7b) then add triggers on DELETE, UPDATE, INSERT and apply the changes to the new table (remember the type) 
7c) change the view to query the new table 
 
I can try to think about more options. But now i am getting curious why you are doing unions which affect you that much, maybe if you tell us your use case or the problem we can even suggest some other options. 
 
in your CDT you specified "tinyint(1)" as the type in the column annotation? 
 
Hope this helps

josep · Answer

Hello Riyazs, 
 So, I understand you still have a boolean value in that field it is just converted to a tinyint(4). 
 you cannot use the cast because tinyint is not allowed but can you create a new view and a function? 
 
 From the link you refer they are creating this: 
 - create or replace view la_luis_view2 as select * from la_luis_view1 union all select * as X from la_luis_view1; 
 I will rewrite it like this: 
 - create or replace view la_luis_view2 as select especie_de_boolean from la_luis_view1 union all select especie_de_boolean as X from la_luis_view1; 
 then create the following 
 DELIMITER $$
CREATE FUNCTION `IntToTinyInt1`(TheInt tinyint) 
RETURNS tinyint(1) -- here is the trick
DETERMINISTIC
BEGIN
RETURN TheInt;
END$$
DELIMITER ;

create or replace view la_luis_view3 as 
select IntToTinyInt1(especie_de_boolean) from la_luis_view2;
desc la_luis_view3; 
 (If you want to see the thread https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_convert ) 
 Jose