Invalid SQL: ProcessedFileChecksumUpdate compares BLOB with INTEGER column
The following statement is performed during the 7.4-dev Upgrade Wizard:
SELECT COUNT(*) FROM "sys_file_processedfile" LEFT JOIN "sys_registry" ON "entry_key"="sys_file_processedfile"."uid" AND "entry_namespace"='ProcessedFileChecksumUpdate' WHERE ("entry_key" IS NULL AND "sys_file_processedfile"."identifier" <> '') OR "sys_file_processedfile"."width" IS NULL
This query compares a BLOB field with an INTEGER field without doing an explicit cast. This is fine on MySQL but fails on DBAL Installations, i.e. running PostgreSQL.
Updated by Herbert Sojnik over 5 years ago
I have to disagree, that the query works fine on MySQL and this is only a problem using DBAL:
In our installation there are about 170.000 entries in sys_file_processedfile and 28.000 in sys_registry.
The original query lasts more than 2000 seconds.
Using the statement with CAST it needs a few seconds.
I would suggest to include the patch also in 6.2