I've found this BUG with1.0.65, later I checked with 1.0.63, but it performs the same.
When creating tables using CREATE TABLE x AS SELECT * FROM y WHERE ... the created table does not inherit the data types of the original table!
for example:
The original 'BinaryAttachment' is:
CREATE TABLE "BinaryAttachment"
(
binaryAttachment_ID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
binaryAttachmentType_ID INTEGER(1) NOT NULL,
name TEXT(50) NOT NULL,
description TEXT(400),
rowStatus INTEGER(1) NOT NULL,
binaryData BLOB NOT NULL,
dataSize INTEGER(4) NOT NULL,
creationTime DATETIME NOT NULL
);
After filtering some rows into a TEMP table using
CREATE TABLE tmp_BinaryAttachment_15_5e6c86 AS SELECT * FROM BinaryAttachment WHERE ...
the created temp table has this declaration:
CREATE TABLE tmp_BinaryAttachment_15_5e6c86(
binaryAttachment_ID NUM,
binaryAttachmentType_ID INT,
name TEXT,
description TEXT,
rowStatus INT,
binaryData,
dataSize INT,
creationTime NUM
);
The first problem is that (i think) the created table should have the same types as the original table.
The second problem is (as you can see), the type of the 'binaryData' column is not declared!