Cast from BOOLEAN to BOOLEAN not supported


#1

I’ve added a boolean column to a table. I’m now wanting to set to true if anything has been entered into 5 other string fields. I’ve tried several statements but all receive the error “Cast from BOOLEAN to BOOLEAN not supported”

2 Examples:
update December set HasDefect = true where Engineering1 is not null;
update December set HasDefect = true where Engineering1 >’’;


#2

@davidrubert sorry you’re having trouble. Can you clarify whether you’re using SQL Editor, MapDQL, a custom app, or ?


#3

this is SQL Editor in immerse.

I can login direct and try it if you think it will make a difference.


#4

Also, my apologies, this is version 4.4.1


#5

I just did it with mapdql and received the same message


#6

OK, good investigation so far. Let me do a bit of research on our capabilities.


#7

@davidrubert i’m guessing it’s the same issue raised in Query failed : Exception: Cast from BOOLEAN to BOOLEAN not supported which resulted in an open issue.


#8

hi @davidrubert,

try enclosing the true or false on dml with single quotes

update December set HasDefect = ‘true’ where Engineering1 is not null;

I guess the problem relies on differences between legacy parser and calcite one.

so you have to enclose the true or false on insert,updates deletes, but you have to use it without enclosing on select operations


#9

so a small clarification on bools - treat them like stings? even something like = ‘1’?

and thanks again


#10

On DMLs? I think you can, but ‘true’ and ‘false’ is more readable.

A little clarification on the issue; the software started using a custom parser/optimizer but after some time, for queries has been switched to Calcite, so there can be some differences between selects statements and DMLs/DDLs


#11

because it seems inconsistent with this expression
select count(*) from December where HasDefect = True;

If I were to put single quotes around ‘True’ it will fail with
Exception: Cannot compare between BOOLEAN and VARCHAR

My dev teams would react to such apparent inconsistancy.


#12

again - thanks
Have a good day


#13

This issue is annoying but if we are here is for Performance and it’s likely they won’t complain about that.