740 words

Issue #115 - Different behavior for two equal queries

BugVersion: 1.04Priority: NormalStatus: ClosedReplies: 4
#1
2011-12-09 08:57

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

quasado
quasado's picture
Joined on 2011-04-30
User Post #43
#2
2011-12-09 08:59

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.
quasado
quasado's picture
Joined on 2011-04-30
User Post #44
#3
2011-12-09 09:02

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

quasado
quasado's picture
Joined on 2011-04-30
User Post #45
#4
2011-12-12 21:14

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?

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #796
#5
2012-01-11 03:43

This issue will be closed as non reproducible.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #846

Post Reply

Please read carefully the posting instructions - before posting to the ObjectDB website.

  • You may have to disable pop up blocking in order to use the toolbar (e.g. in Chrome).
  • Use ctrl + right click to open the browser context menu in the editing area (e.g. for using a browser spell checker).
  • To insert formatted lines (e.g. Java code, stack trace) - select a style in the toolbar and then insert the text in the new created block.
  • Avoid overflow of published source code examples by breaking long lines.
  • You may mark in paragraph code words (e.g. class names) with the code style (can be applied by ctrl + D).
  • Long stack traces (> 50 lines) and complex source examples (> 100 lines) should be posted as attachments.
Attachments:
Maximum file size: 32 MB
Cancel