Issue #608: Different behavior for two equal queries

Hi,

I've got two equal queries though they behave differently:

SELECT FROM com.quasado.foundation.communication.contact.individual.IndividualContact $1
WHERE (($1.organisation.name LIKE '%Q%'))

This correctly returns one entry from my database

SELECT FROM com.quasado.foundation.communication.contact.individual.IndividualContact $1
WHERE (($1.organisation.name LIKE '%Q%') OR
 ($1.communication.classification.name LIKE '%Q%') OR
 ($1.postalAddresses.deliveryPoint.deliveryPointLocation.thoroughfare.name LIKE '%Q%') OR
 ($1.communication.cellphone LIKE '%Q%') OR
 ($1.position.name LIKE '%Q%') OR
 ($1.job LIKE '%Q%') OR
 ($1.communication.www LIKE '%Q%') OR
 ($1.communication.email LIKE '%Q%') OR
 ($1.profile.firstName LIKE '%Q%') OR
 ($1.communication.phone LIKE '%Q%') OR
 ($1.postalAddresses.classification.name LIKE '%Q%') OR
 ($1.postalAddresses.deliveryPoint.deliveryPointLocation.streetNumberOrPlot LIKE '%Q%') OR
 ($1.postalAddresses.deliveryPoint.postalCode LIKE '%Q%') OR
 ($1.profile.title.name LIKE '%Q%') OR
 ($1.postalAddresses.deliveryPoint.locality.town LIKE '%Q%') OR
 ($1.profile.lastName LIKE '%Q%') OR
 ($1.communication.instantMessaging LIKE '%Q%') OR
 ($1.communication.fax LIKE '%Q%') OR
 ($1.profile.salutation.name LIKE '%Q%'))

This returns no results...

Alex

Here're also the execution plans from the explorer if that helps:

First query (which works):

Query Plan Description
======================

Step 1: Process IndividualContact ($1) instances
------------------------------------------------
[Step 1a]
Scan type com.quasado.foundation.communication.api.model.contact.individual.IndividualContact
locating all the IndividualContact ($1) instances.

[Step 1b]
Evaluate fields in IndividualContact ($1) instances.

Step 2: Process OrganisationContact (v$1) instances
  (for every result of step 1)
---------------------------------------------------
[Step 2a]
Iterate over all the instances (v$1) in $1.organisation.

[Step 2b]
Retrieve fields in OrganisationContact (v$1) instances.

[Step 2c]
Filter the results of step 2b
retaining only results that satisfy:
  like(v$1.name,'%Q%').

Step 3: Group results
---------------------
Group the results of step 2by:
  $1.

[Step 4]
Remove duplicates from the groups of step 3.

[Step 5]
Apply selection and prepare final results.

Second query (which fails):

Query Plan Description
======================

Step 1: Process IndividualContact ($1) instances
------------------------------------------------
[Step 1a]
Scan type com.quasado.foundation.communication.api.model.contact.individual.IndividualContact
locating all the IndividualContact ($1) instances.

[Step 1b]
Evaluate fields in IndividualContact ($1) instances.

Step 2: Process OrganisationContact (v$1) instances
  (for every result of step 1)
---------------------------------------------------
Iterate over all the instances (v$1) in $1.organisation.

Step 3: Process IndividualCommunicationInfo (v$2) instances
  (for every result tuple of the steps above)
-----------------------------------------------------------
Iterate over all the instances (v$2) in $1.communication.

Step 4: Extract field values
----------------------------
Retrieve fields in IndividualCommunicationInfo (v$2) instances.

Step 5: Process IndividualCommunicationClassification (v$3) instances
  (for every result tuple of the steps above)
---------------------------------------------------------------------
Iterate over all the instances (v$3) in v$2.classification.

Step 6: Process IndividualPostalAddress (v$4) instances
  (for every result tuple of the steps above)
-------------------------------------------------------
Iterate over all the instances (v$4) in $1.postalAddresses.

Step 7: Extract field values
----------------------------
Retrieve fields in IndividualPostalAddress (v$4) instances.

Step 8: Process DeliveryPointSpecification (v$5) instances
  (for every result tuple of the steps above)
----------------------------------------------------------
Iterate over all the instances (v$5) in v$4.deliveryPoint.

Step 9: Extract field values
----------------------------
Retrieve fields in DeliveryPointSpecification (v$5) instances.

Step 10: Process DeliveryPointLocation (v$6) instances
  (for every result tuple of the steps above)
-----------------------------------------------------
Iterate over all the instances (v$6) in v$5.deliveryPointLocation.

Step 11: Extract field values
----------------------------
Retrieve fields in DeliveryPointLocation (v$6) instances.

Step 12: Process Thoroughfare (v$7) instances
  (for every result tuple of the steps above)
--------------------------------------------
Iterate over all the instances (v$7) in v$6.thoroughfare.

Step 13: Process IndividualOrganisationPosition (v$8) instances
  (for every result tuple of the steps above)
--------------------------------------------------------------
Iterate over all the instances (v$8) in $1.position.

Step 14: Process IndividualProfile (v$9) instances
  (for every result tuple of the steps above)
-------------------------------------------------
Iterate over all the instances (v$9) in $1.profile.

Step 15: Process IndividualCommunicationClassification (v$10) instances
  (for every result tuple of the steps above)
----------------------------------------------------------------------
Iterate over all the instances (v$10) in v$4.classification.

Step 16: Extract field values
----------------------------
Retrieve fields in IndividualProfile (v$9) instances.

Step 17: Process IndividualTitle (v$11) instances
  (for every result tuple of the steps above)
------------------------------------------------
Iterate over all the instances (v$11) in v$9.title.

Step 18: Process Locality (v$12) instances
  (for every result tuple of the steps above)
-----------------------------------------
Iterate over all the instances (v$12) in v$5.locality.

Step 19: Extract field values
----------------------------
Retrieve fields in Locality (v$12) instances.

Step 20: Extract field values
----------------------------
Retrieve fields in IndividualTitle (v$11) instances.

Step 21: Extract field values
----------------------------
Retrieve fields in IndividualCommunicationClassification (v$10) instances.

Step 22: Extract field values
----------------------------
Retrieve fields in IndividualOrganisationPosition (v$8) instances.

Step 23: Extract field values
----------------------------
Retrieve fields in Thoroughfare (v$7) instances.

Step 24: Extract field values
----------------------------
Retrieve fields in IndividualCommunicationClassification (v$3) instances.

Step 25: Extract field values
----------------------------
Retrieve fields in OrganisationContact (v$1) instances.

Step 26: Process IndividualSalutation (v$13) instances
  (for every result tuple of the steps above)
-----------------------------------------------------
[Step 26a]
Iterate over all the instances (v$13) in v$9.salutation.

[Step 26b]
Retrieve fields in IndividualSalutation (v$13) instances.

[Step 26c]
Filter the results of step 26b
retaining only results that satisfy:
  or(like(v$13.name,'%Q%'),like(v$2.fax,'%Q%'),like(v$2.instantMessaging,'%Q%'),like(v$9.lastName,'%Q%'),like(v$12.town,'%Q%'),like(v$11.name,'%Q%'),like(v$5.postalCode,'%Q%'),like(v$6.streetNumberOrPlot,'%Q%'),like(v$10.name,'%Q%'),like(v$2.phone,'%Q%'),like(v$9.firstName,'%Q%'),like(v$2.email,'%Q%'),like(v$2.www,'%Q%'),like($1.job,'%Q%'),like(v$8.name,'%Q%'),like(v$2.cellphone,'%Q%'),like(v$7.name,'%Q%'),like(v$3.name,'%Q%'),like(v$1.name,'%Q%')).

Step 27: Group results
---------------------
Group the results of step 26by:
  $1.

[Step 28]
Remove duplicates from the groups of step 27.

[Step 29]
Apply selection and prepare final results.

By the way, the second query also has way too many executions in it.. because already the FIRST where clause should be a hit and the query execution be finished as the rest are just ORs, no??

Alex

Maybe this is the result of navigation through null in one of the OR components. Navigation should be compiled as LEFT OUTER JOIN to avoid losing results in such cases, but maybe an ordinary JOIN was used in this query.

Could you isolate the OR component that causes this query execution failure? Does it have navigation through null? Could you upload the database in which this query fails?

This issue will be closed as non reproducible.