Icon View Incident Report

Serious Serious
Reported By: Hendrik Gruetzmacher
Reported On: 8/17/2011
For: Version 2.05 Build 10
# 3496 Using a DISTINCT Query with a UNION ALL Operation Can Cause Improper BLOB Column Error

I get this error message:

ElevateDB Error #100 There is an error in the metadata for the index column in the index __Distinct (BLOB columns cannot be included in a non-text index)

when trying to execute the following query.

SELECT DISTINCT
   P.TABLE_NAME + #13 + #10 + T.NAME + #13 + #10 + '(' + P.NAME + ')' "TABLE_NAME", 
   P.TABLE_ID, 
   P.ADDED, 
   P.ADDED_TIME, 
   P.ADDED_BY, '' 
FROM 
   T_PROTOCOLS P, 
   V_ALL_REPORTPARAMETERS T 
WHERE 
   P.TABLE_NAME='T_REPORTPARAMETERS' AND 
   P.TABLE_ID=T.ID AND 
   T.REPORT__ID=2 AND 
   P.NAME='DELETED' AND 
   NOT EXISTS (SELECT ID FROM T_REPORTPARAMETERS WHERE ID=P.TABLE_ID)
UNION ALL 
SELECT 
   P.TABLE_NAME + #13 + #10 + T.NAME + #13 + #10 + '(' + P.NAME + ')' "TABLE_NAME",
   P.TABLE_ID, 
   P.ADDED, 
   P.ADDED_TIME, 
   P.ADDED_BY, 
   P.REMARK
FROM 
   T_PROTOCOLS P, 
   T_REPORTDATASOURCES T 
WHERE 
   P.TABLE_NAME='T_REPORTDATASOURCES' AND 
   P.TABLE_ID=T.ID AND 
   T.REPORT__ID=2



Comments Comments and Workarounds
The bug is an improper error message. What happens is that the UNION ALL operation forces the last '' expression in the first query to be a CLOB column due to the fact that it is being UNIONed with the P.REMARK column, which is a CLOB column. EDB performs type promotion as necessary to accommodate the differing types when using UNION, INTERSECT, etc. Normally this isn't a problem, but because you are also using DISTINCT, it causes an issue with the result set creation, which is trying to create an index for the DISTINCT operation on a BLOB column. Now, there *is* a bug with the error message - the error should be a compilation error that occurs during the Prepare, not a vague runtime error that is hard
to understand.

To get around this issue, be more specific about the types, and CAST() the CLOB columns to a VARCHAR to match the first query.


Resolution Resolution
Fixed Problem on 8/17/2011 in version 2.05 build 11


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image