CMDBuild Forum

CQL filter works incorrectly

Old Versions
#1

Hello!

Recently I've upgraded my installation of CMDBuild from 1.5 to version 2.1.4 and found that some CQL syntax seems to have changed.

To filter some references in my DB I was using (in 1.5) CQL filters like that:

from HardwareModel where Vendor = {client:Vendor} & [ModelType2HardwareModel].objects(Code BEGIN 'Server-')

It was working perfectly but after migration to 2.1.4 I constantly get the error while opening any card with this filter for edit:

Call: services/json/management/modcard/getcardlistshort?_dc=1377233775853
--------------------------------------------------------------------------
Error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar

The error message is followed by huge SQL query and another message (translated from Russian):

nested exception is org.postgresql.util.PSQLException: ERROR: column ModelType.Code not exists.

When I tried to replace Code by Id it started to work fine. But I need explicitly the Code field to filter on.

I assume the Code field exists in any CMDBuild class derived from "Class" class. So I think this is a bug. Please, check this problem as it really degrades the usability of software.

Thank you!

#2

Hello again!

 

I've just tried to analyse that huge autoconstucted SQL query from the error message. It seems the query contains two parts. I took only first part and removed all unnesessary details from it. As a result I've got a much simpler query:

-------------------

SELECT DISTINCT ON ("_HardwareModel_Id")

"HardwareModel"."IdClass"::oid AS "_HardwareModel_IdClass",

"HardwareModel"."Id" AS "_HardwareModel_Id"

FROM "HardwareModel" AS "HardwareModel"

LEFT JOIN "ModelType" AS "ModelType#HardwareModel#ModelType" ON "ModelType#HardwareModel#ModelType"."Id" = "HardwareModel"."ModelType" 

JOIN (SELECT "Id", "IdDomain", "IdObj2" AS "IdObj1", "IdObj1" AS "IdObj2", "User", "BeginDate", NULL AS "EndDate" FROM ONLY "Map_ModelType2HardwareModel"

      WHERE "Map_ModelType2HardwareModel"."Status" = 'A') AS "Map_ModelType2HardwareModel" ON "HardwareModel"."Id"="Map_ModelType2HardwareModel"."IdObj1"

JOIN (SELECT "Id", "IdClass", "User", "BeginDate",NULL AS "EndDate" FROM ONLY "ModelType"

      WHERE (TRUE  AND   "ModelType"."Status" = 'A' )) AS "ModelType" ON "Map_ModelType2HardwareModel"."IdObj2"="ModelType"."Id"

WHERE "ModelType"."Code" = 'Server' AND "HardwareModel"."Status" = 'A'

-------------------

The error is generated because of the last string in this simplified query:

WHERE "ModelType"."Code" = 'Server' AND ...

 

The "ModelType"."Code" column is really does not exists within context of the query.

As I can understand, this is because of bug in the query construction procedure, which affects one of the two positions in it:

1) if I replace the "ModelType"."Code" by "ModelType#HardwareModel#ModelType" alias taken from the 5th row of the query the query starts to work fine

OR

 

2) if I add the "Code" column into the

JOIN (SELECT "Id", "IdClass", "User", "BeginDate",NULL AS "EndDate" FROM ONLY "ModelType"

row, then again the query starts to work fine.

Both variants of the query return the same data.

 

Please, check the source for this bug. It is almost all of my CQL filters have failed because of it. I can not revert the DB back to 1.5 as there is a lot of new data already imported into the DB.

 

P.S. The initial query is in the attached file

 


cmdbuild-error-query-txt (12.7 KB)
#3

Hello!

 
Yesterday I've posted another comment with SQL query analysis. There were some problems while sending it. Have you received it or I need to repost it?
 
Thank you!
#4

Hi Andrey,

 

as a workaraoud i suggest you to use the SQL injection into the

CQL query, for example:

 

from HardwareModel where Id in (/(select hw."Id" from "HardwareModel" as hw join "ModelType" model on model."Id"=hw."ModelType" and model."Status"='A' and model."Code" ilike 'Server-%' where hw."Vendor"={client:Vendor} and hw."Status"='A')/)

 

or something similar.

The SQL query can be injected using the (/(...)/) syntax. The SQL query must return the set of "Id" of the correct cards. You can send to the query all values {client:...} or {server:...}.

 

Best regards

     Alberto

 

 

Previously Andrey wrote:

Hello!

Recently I've upgraded my installation of CMDBuild from 1.5 to version 2.1.4 and found that some CQL syntax seems to have changed.

To filter some references in my DB I was using (in 1.5) CQL filters like that:

from HardwareModel where Vendor = {client:Vendor} & [ModelType2HardwareModel].objects(Code BEGIN 'Server-')

It was working perfectly but after migration to 2.1.4 I constantly get the error while opening any card with this filter for edit:

Call: services/json/management/modcard/getcardlistshort?_dc=1377233775853
--------------------------------------------------------------------------
Error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar

The error message is followed by huge SQL query and another message (translated from Russian):

nested exception is org.postgresql.util.PSQLException: ERROR: column ModelType.Code not exists.

When I tried to replace Code by Id it started to work fine. But I need explicitly the Code field to filter on.

I assume the Code field exists in any CMDBuild class derived from "Class" class. So I think this is a bug. Please, check this problem as it really degrades the usability of software.

Thank you!