TMyQuery fails with UNION statement

Pete B · Post by **Pete B** » Thu 23 Dec 2010 15:47

Hi
Given a standard data manipulation statement including a UNION eg.

(SELECT A, B, C FROM table1)
UNION
(SELECT A, B, C FROM table2)
ORDER BY B, A;

... I am finding that this causes an error in the Delphi debugger, in this case it appears to be with the interpretation of tinyint fields as boolean v. smallint eg.

EDatabaseError MyQuery: Type mismatch for field 'C', expecting: Boolean actual: Smallint

I don't understand this because ...

1. Each SELECT when run as an individual query with TMyQuery works fine.
2. The required, complete SELECT UNION SELECT works fine when run within dbForge for MySQL.
3. TMyQuery Option, EnableBoolean is True

I'm using Delphi 2009 / MyDAC 5.90.0.60

Can you suggest where this maybe going wrong please ?

thanks

Pete B

Justmade · Post by **Justmade** » Mon 27 Dec 2010 01:11

I am a user and not the developer so below are just based on my own experience.

For MyDAC, boolean field is defined as tinyint(1)

If C in table1 is defined as tinyint(1), mysql will define the field type of C as boolean.

However if your field C in table2 is, say smallint(6), then Delphi cannot process the smallint value to boolean value and cause error.

Pete B · Post by **Pete B** » Mon 27 Dec 2010 10:29

Justmade: thanks for the reply, it's appreciated.
I'm familiar with the current MySQL handling of boolean fields and I understand what you are saying, which would be correct. However I'm not, as far as I can see, mixing any boolean fields with smallint fields in this SQL statement and as I mentioned in item 2. of the original post, this query works fine when run in dbForge for MySQL. If MySQL server was 'unhappy' with the query, it should raise an exception too.
It seems as though the problem maybe with TMyQuery but I'm no guru

... it may be something really simple that I just can't see.

cheers

Justmade · Post by **Justmade** » Mon 27 Dec 2010 10:58

I am not taking about how mysql deal with boolean.

Taking tinyint(1) as boolean field (i.e. TBooleanField) is MyDAC's assumption (when EnableBoolean=true), not MySQL's.

dbForge do not make this assumption and will not matter if you mix tinyint(1) with other int-type fields.

However, MyDAC (including TMyQuery) will take tinyint(1) field as boolean field when EnableBoolean is true.

I think if you do not need boolean field, you can set enablboolean to false, else you can cast the tinyint(1) field to int / select table 2 first?

Pete B · Post by **Pete B** » Mon 27 Dec 2010 12:30

I do understand what you are saying and the role of the "EnableBoolean" option. Where you said ...

However, MyDAC (including TMyQuery) will take tinyint(1) field as boolean field when EnableBoolean is true.

... is exactly as I would want TMyQuery to behave. The field which causes the error is declared as TBooleanField as are all the tinyint(1) fields I use (I would not use tinyint(1) for anything other than boolean representation).

To set EnableBoolean to False given that I already have several boolean fields in the tables SELECTed causes major disruption and surely the whole point of having the EnableBoolean option is to prevent the need to cast tinyint(1) to something else.

Neither of the 2 tables queried in the SELECT-UNION-SELECT statement has any SmallInt fields in them and I just don't understand why this EDatabase exception is returned. Perhaps it is something like the field length is being incorrectly returned ?

I also tried selecting table 2 first, unfortunately it didn't help but definitely was worth a try.

thanks

AndreyZ · Post by **AndreyZ** » Mon 27 Dec 2010 13:03

Hello,

For TINYINT(1) fields MySQL server returns boolean types for simple queries and smallint types for queries with UNION. In the second case we cannot determine whether the field is boolean or not. It seems that at first you used the simple query and created fields by Fields Editor, and after this you changed the SQL property to a query with UNION. To solve the problem remove all these fields from the TMyQuery component. If you want to work with constant fields (created by Fields Editor), you should add them after assigning the TMyQuery.SQL property.

Pete B · Post by **Pete B** » Mon 27 Dec 2010 13:16

For TINYINT(1) fields MySQL server returns boolean types for simple queries and smallint types for queries with UNION.

... ahhh, I did not know this, I will now do as you suggest by removing these boolean / tinyint(1) fields from the fields editor and add them after assigning the SQL. Thanks Andrey.