Issue #632: Issue with UPPER ?

Hi,

Assuming those queries which are all the same except the UPPER calls:

# Case (1)
SELECT COUNT($1) FROM com.lexware.vereinsverwaltung.verein.mitglied.Mitglied $1 LEFT JOIN $1.mitgliedschaft $2 LEFT JOIN $1.profile $3 LEFT JOIN $3.organisation $4 LEFT JOIN $1.postalAddresses $5 LEFT JOIN $1.communication $6 WHERE (((NOT (($2 IS NOT NULL) AND ($2.austritt IS NOT NULL) AND ($2.austritt<=CURRENT_DATE)))) AND (((($3 IS NOT NULL) AND ($4 IS NOT NULL) AND ($4.name IS NOT NULL) AND ($4.name LIKE '%81%')) OR (($5 IS NOT NULL) AND ($5.postalCode IS NOT NULL) AND ($5.postalCode LIKE '%81%')) OR (($5 IS NOT NULL) AND ($5.streetNumber IS NOT NULL) AND ($5.streetNumber LIKE '%81%')) OR (($6 IS NOT NULL) AND ($6.cellphone IS NOT NULL) AND ($6.cellphone LIKE '%81%')) OR (($5 IS NOT NULL) AND ($5.street IS NOT NULL) AND ($5.street LIKE '%81%')) OR (($5 IS NOT NULL) AND ($5.city IS NOT NULL) AND ($5.city LIKE '%81%')) OR (($3 IS NOT NULL) AND ($3.lastName IS NOT NULL) AND ($3.lastName LIKE '%81%')) OR (($6 IS NOT NULL) AND ($6.email IS NOT NULL) AND ($6.email LIKE '%81%')) OR (($3 IS NOT NULL) AND ($3.firstName IS NOT NULL) AND ($3.firstName LIKE '%81%')) OR (($6 IS NOT NULL) AND ($6.fax IS NOT NULL) AND ($6.fax LIKE '%81%')) OR (($6 IS NOT NULL) AND ($6.phone IS NOT NULL) AND ($6.phone LIKE '%81%')))))

# Case (2)
SELECT COUNT($1) FROM com.lexware.vereinsverwaltung.verein.mitglied.Mitglied $1 LEFT JOIN $1.mitgliedschaft $2 LEFT JOIN $1.profile $3 LEFT JOIN $3.organisation $4 LEFT JOIN $1.postalAddresses $5 LEFT JOIN $1.communication $6 WHERE (((NOT (($2 IS NOT NULL) AND ($2.austritt IS NOT NULL) AND ($2.austritt<=CURRENT_DATE)))) AND (((($3 IS NOT NULL) AND ($4 IS NOT NULL) AND ($4.name IS NOT NULL) AND (UPPER($4.name) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.postalCode IS NOT NULL) AND (UPPER($5.postalCode) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.streetNumber IS NOT NULL) AND (UPPER($5.streetNumber) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.cellphone IS NOT NULL) AND (UPPER($6.cellphone) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.street IS NOT NULL) AND (UPPER($5.street) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.city IS NOT NULL) AND (UPPER($5.city) LIKE UPPER('%81%'))) OR (($3 IS NOT NULL) AND ($3.lastName IS NOT NULL) AND (UPPER($3.lastName) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.email IS NOT NULL) AND (UPPER($6.email) LIKE UPPER('%81%'))) OR (($3 IS NOT NULL) AND ($3.firstName IS NOT NULL) AND (UPPER($3.firstName) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.fax IS NOT NULL) AND (UPPER($6.fax) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.phone IS NOT NULL) AND (UPPER($6.phone) LIKE UPPER('%81%'))))))


# Case (3)
SELECT COUNT($1) FROM com.lexware.vereinsverwaltung.verein.mitglied.Mitglied $1 LEFT JOIN $1.mitgliedschaft $2 LEFT JOIN $1.profile $3 LEFT JOIN $3.organisation $4 LEFT JOIN $1.postalAddresses $5 LEFT JOIN $1.communication $6 WHERE (((NOT (($2 IS NOT NULL) AND ($2.austritt IS NOT NULL) AND ($2.austritt<=CURRENT_DATE)))) AND (((($3 IS NOT NULL) AND ($4 IS NOT NULL) AND ($4.name IS NOT NULL) AND ($4.name LIKE '%81%')) OR (($5 IS NOT NULL) AND ($5.postalCode IS NOT NULL) AND ($5.postalCode LIKE '%81%')) OR (($5 IS NOT NULL) AND ($5.streetNumber IS NOT NULL) AND ($5.streetNumber LIKE '%81%')) OR (($6 IS NOT NULL) AND ($6.cellphone IS NOT NULL) AND (UPPER($6.cellphone) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.street IS NOT NULL) AND (UPPER($5.street) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.city IS NOT NULL) AND (UPPER($5.city) LIKE UPPER('%81%'))) OR (($3 IS NOT NULL) AND ($3.lastName IS NOT NULL) AND (UPPER($3.lastName) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.email IS NOT NULL) AND (UPPER($6.email) LIKE UPPER('%81%'))) OR (($3 IS NOT NULL) AND ($3.firstName IS NOT NULL) AND (UPPER($3.firstName) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.fax IS NOT NULL) AND (UPPER($6.fax) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.phone IS NOT NULL) AND (UPPER($6.phone) LIKE UPPER('%81%'))))))

What happens is this:

Case (1) -> all is ok, it returns a count of one which is correct, the query is equal to Case (2) & (3) except the missing UPPER() calls everywhere

Case (2) -> fails badly and suddenly returns doubled results and weird counts (the total count of ALL available Rows is 100 though this returns 149!!!). The difference between this and Case (1) which works is the use of UPPER everywhere

Case (3) -> is a copy+paste of Case(2) with all UPPER removed from the beginning of the query until it started suddenly working again correctly as it should

Please help out, this looks very weird.. especially.. for this case, using or not using UPPER shouldn't make ANY difference as we're testing for a number as well...

All fields that are tested are strings

Here're the query stack traces from the explorer for each case. Attached you can also find my database for testing hope that helps, we'd need an eager fix to this issue to be able to do ignore-case queries..

# Case (1)

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

Step 1: Process Mitglied ($1) instances
---------------------------------------
[Step 1a]
Scan type com.lexware.vereinsverwaltung.verein.api.model.mitglied.Mitglied
locating all the Mitglied ($1) instances.

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

Step 2: Process IndividualProfile ($3) instances
  (for every result of step 1)
------------------------------------------------
Iterate over all the instances ($3) in $1.profile.

Step 3: Extract field values
----------------------------
Retrieve fields in IndividualProfile ($3) instances.

Step 4: Process OrganisationContact ($4) instances
  (for every result tuple of the steps above)
--------------------------------------------------
Iterate over all the instances ($4) in $3.organisation.

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

Step 6: Extract field values
----------------------------
Retrieve fields in IndividualPostalAddress ($5) instances.

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

Step 8: Process IndividualCommunicationInfo ($6) instances
  (for every result tuple of the steps above)
----------------------------------------------------------
[Step 8a]
Iterate over all the instances ($6) in $1.communication.

[Step 8b]
Retrieve fields in IndividualCommunicationInfo ($6) instances.

[Step 8c]
Filter the results of step 8b
retaining only results that satisfy:
  or(and(like($6.phone,'%81%'),($6.phone!=null),($6!=null)),and(like($6.fax,'%81%'),($6.fax!=null),($6!=null)),and(like($3.firstName,'%81%'),($3.firstName!=null),($3!=null)),and(like($6.email,'%81%'),($6.email!=null),($6!=null)),and(like($3.lastName,'%81%'),($3.lastName!=null),($3!=null)),and(like($5.city,'%81%'),($5.city!=null),($5!=null)),and(like($5.street,'%81%'),($5.street!=null),($5!=null)),and(like($6.cellphone,'%81%'),($6.cellphone!=null),($6!=null)),and(like($5.streetNumber,'%81%'),($5.streetNumber!=null),($5!=null)),and(like($5.postalCode,'%81%'),($5.postalCode!=null),($5!=null)),and(like($4.name,'%81%'),($4.name!=null),($4!=null),($3!=null))).

Step 9: Process Mitgliedschaft ($2) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 9a]
Iterate over all the instances ($2) in $1.mitgliedschaft.

[Step 9b]
Retrieve fields in Mitgliedschaft ($2) instances.

[Step 9c]
Filter the results of step 9b
retaining only results that satisfy:
  not(and(($2.austritt<=current-java.sql.Date),($2.austritt!=null),($2!=null))).

Step 10: Group results
---------------------
Evaluate aggregate expressions:
  count($1).

Step 11: Apply selection
-----------------------
Apply selection and prepare final results.

# Case (2)
Query Plan Description
======================

Step 1: Process Mitglied ($1) instances
---------------------------------------
[Step 1a]
Scan type com.lexware.vereinsverwaltung.verein.api.model.mitglied.Mitglied
locating all the Mitglied ($1) instances.

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

Step 2: Process IndividualProfile ($3) instances
  (for every result of step 1)
------------------------------------------------
Iterate over all the instances ($3) in $1.profile.

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

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

Step 5: Extract field values
----------------------------
Retrieve fields in IndividualCommunicationInfo ($6) instances.

Step 6: Extract field values
----------------------------
Retrieve fields in IndividualPostalAddress ($5) instances.

Step 7: Extract field values
----------------------------
Retrieve fields in IndividualProfile ($3) instances.

Step 8: Process OrganisationContact ($4) instances
  (for every result tuple of the steps above)
--------------------------------------------------
Step 8a: Handle a constant expression
------------------------------------
[Step 8a1]
If or(or(or(or(or(or(or(or(or(and(like(toUpper($5.postalCode),toUpper('%81%')),($5.postalCode!=null),($5!=null)),and(like(toUpper($5.streetNumber),toUpper('%81%')),($5.streetNumber!=null),($5!=null))),and(like(toUpper($6.cellphone),toUpper('%81%')),($6.cellphone!=null),($6!=null))),and(like(toUpper($5.street),toUpper('%81%')),($5.street!=null),($5!=null))),and(like(toUpper($5.city),toUpper('%81%')),($5.city!=null),($5!=null))),and(like(toUpper($3.lastName),toUpper('%81%')),($3.lastName!=null),($3!=null))),and(like(toUpper($6.email),toUpper('%81%')),($6.email!=null),($6!=null))),and(like(toUpper($3.firstName),toUpper('%81%')),($3.firstName!=null),($3!=null))),and(like(toUpper($6.fax),toUpper('%81%')),($6.fax!=null),($6!=null))),and(like(toUpper($6.phone),toUpper('%81%')),($6.phone!=null),($6!=null))) is true - execute only step 8a2.
If or(or(or(or(or(or(or(or(or(and(like(toUpper($5.postalCode),toUpper('%81%')),($5.postalCode!=null),($5!=null)),and(like(toUpper($5.streetNumber),toUpper('%81%')),($5.streetNumber!=null),($5!=null))),and(like(toUpper($6.cellphone),toUpper('%81%')),($6.cellphone!=null),($6!=null))),and(like(toUpper($5.street),toUpper('%81%')),($5.street!=null),($5!=null))),and(like(toUpper($5.city),toUpper('%81%')),($5.city!=null),($5!=null))),and(like(toUpper($3.lastName),toUpper('%81%')),($3.lastName!=null),($3!=null))),and(like(toUpper($6.email),toUpper('%81%')),($6.email!=null),($6!=null))),and(like(toUpper($3.firstName),toUpper('%81%')),($3.firstName!=null),($3!=null))),and(like(toUpper($6.fax),toUpper('%81%')),($6.fax!=null),($6!=null))),and(like(toUpper($6.phone),toUpper('%81%')),($6.phone!=null),($6!=null))) is false - execute only step 8a3.

[Step 8a2]
Scan type com.quasado.foundation.contact.api.model.organisation.OrganisationContact
locating all the OrganisationContact ($4) instances.

Step 8a3: Handle a constant expression
------------------------------------
[Step 8a3a]
If ($3!=null) is true - execute only step 8a3e.
If ($3!=null) is false - execute only step 8a3f.

[Step 8a3b]
Scan type com.quasado.foundation.contact.api.model.organisation.OrganisationContact
locating OrganisationContact ($4) instances that satisfy:
  ($4!=null).

[Step 8a3c]
Evaluate fields in OrganisationContact ($4) instances.

[Step 8a3d]
Filter the results of step 8a3c
retaining only results that satisfy:
  like(toUpper($4.name),toUpper('%81%')).

[Step 8a3e]
Filter the results of step 8a3d
retaining only results that satisfy:
  ($4.name!=null).

[Step 8a3f]
Prepare an empty result set.

[Step 8b]
Filter the results of step 8a
retaining only results that satisfy:
  ($3.organisation left-join $4).

Step 9: Process Mitgliedschaft ($2) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 9a]
Iterate over all the instances ($2) in $1.mitgliedschaft.

[Step 9b]
Retrieve fields in Mitgliedschaft ($2) instances.

[Step 9c]
Filter the results of step 9b
retaining only results that satisfy:
  not(and(($2.austritt<=current-java.sql.Date),($2.austritt!=null),($2!=null))).

Step 10: Group results
---------------------
Evaluate aggregate expressions:
  count($1).

Step 11: Apply selection
-----------------------
Apply selection and prepare final results.

# Case (3)
Query Plan Description
======================

Step 1: Process Mitglied ($1) instances
---------------------------------------
[Step 1a]
Scan type com.lexware.vereinsverwaltung.verein.api.model.mitglied.Mitglied
locating all the Mitglied ($1) instances.

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

Step 2: Process IndividualProfile ($3) instances
  (for every result of step 1)
------------------------------------------------
Iterate over all the instances ($3) in $1.profile.

Step 3: Extract field values
----------------------------
Retrieve fields in IndividualProfile ($3) instances.

Step 4: Process OrganisationContact ($4) instances
  (for every result tuple of the steps above)
--------------------------------------------------
Iterate over all the instances ($4) in $3.organisation.

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

Step 6: Extract field values
----------------------------
Retrieve fields in IndividualPostalAddress ($5) instances.

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

Step 8: Process IndividualCommunicationInfo ($6) instances
  (for every result tuple of the steps above)
----------------------------------------------------------
[Step 8a]
Iterate over all the instances ($6) in $1.communication.

[Step 8b]
Retrieve fields in IndividualCommunicationInfo ($6) instances.

[Step 8c]
Filter the results of step 8b
retaining only results that satisfy:
  or(and(like(toUpper($6.phone),toUpper('%81%')),($6.phone!=null),($6!=null)),and(like(toUpper($6.fax),toUpper('%81%')),($6.fax!=null),($6!=null)),and(like(toUpper($3.firstName),toUpper('%81%')),($3.firstName!=null),($3!=null)),and(like(toUpper($6.email),toUpper('%81%')),($6.email!=null),($6!=null)),and(like(toUpper($3.lastName),toUpper('%81%')),($3.lastName!=null),($3!=null)),and(like(toUpper($5.city),toUpper('%81%')),($5.city!=null),($5!=null)),and(like(toUpper($5.street),toUpper('%81%')),($5.street!=null),($5!=null)),and(like(toUpper($6.cellphone),toUpper('%81%')),($6.cellphone!=null),($6!=null)),and(like($5.streetNumber,'%81%'),($5.streetNumber!=null),($5!=null)),and(like($5.postalCode,'%81%'),($5.postalCode!=null),($5!=null)),and(like($4.name,'%81%'),($4.name!=null),($4!=null),($3!=null))).

Step 9: Process Mitgliedschaft ($2) instances
  (for every result tuple of the steps above)
---------------------------------------------
[Step 9a]
Iterate over all the instances ($2) in $1.mitgliedschaft.

[Step 9b]
Retrieve fields in Mitgliedschaft ($2) instances.

[Step 9c]
Filter the results of step 9b
retaining only results that satisfy:
  not(and(($2.austritt<=current-java.sql.Date),($2.austritt!=null),($2!=null))).

Step 10: Group results
---------------------
Evaluate aggregate expressions:
  count($1).

Step 11: Apply selection
-----------------------
Apply selection and prepare final results.

It is possible that there is an issue with UPPER, maybe in combinations with LIKE, but in that case you can probably demonstrate it with simpler queries. Please try to demonstrate this issue with the simplest query (and with minimal number of JOIN variables).

The number 149 is possible since JOIN may generate duplicates. The second query with a small change, COUNT(distinct $1), returns 100.

hi,

I've already tried to simplify it, it has been bigger before ;)

Hmm in this case no join should result in duplicates because I am also checking for not null and the values with like which can only return ONE item in my current database for the given query so a return of 100 is still wrong though...

Any idea?

Alex

Sorry, forgot to mention that if for Case (2) you do only remove the last OR (..) it works correctly again so I cannot simplify it anymore.. compare this to Case (2) which here works:

SELECT COUNT($1) FROM com.lexware.vereinsverwaltung.verein.mitglied.Mitglied $1 LEFT JOIN $1.mitgliedschaft $2 LEFT JOIN $1.profile $3 LEFT JOIN $3.organisation $4 LEFT JOIN $1.postalAddresses $5 LEFT JOIN $1.communication $6 WHERE (((NOT (($2 IS NOT NULL) AND ($2.austritt IS NOT NULL) AND ($2.austritt<=CURRENT_DATE)))) AND (((($3 IS NOT NULL) AND ($4 IS NOT NULL) AND ($4.name IS NOT NULL) AND (UPPER($4.name) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.postalCode IS NOT NULL) AND (UPPER($5.postalCode) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.streetNumber IS NOT NULL) AND (UPPER($5.streetNumber) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.cellphone IS NOT NULL) AND (UPPER($6.cellphone) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.street IS NOT NULL) AND (UPPER($5.street) LIKE UPPER('%81%'))) OR (($5 IS NOT NULL) AND ($5.city IS NOT NULL) AND (UPPER($5.city) LIKE UPPER('%81%'))) OR (($3 IS NOT NULL) AND ($3.lastName IS NOT NULL) AND (UPPER($3.lastName) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.email IS NOT NULL) AND (UPPER($6.email) LIKE UPPER('%81%'))) OR (($3 IS NOT NULL) AND ($3.firstName IS NOT NULL) AND (UPPER($3.firstName) LIKE UPPER('%81%'))) OR (($6 IS NOT NULL) AND ($6.fax IS NOT NULL) AND (UPPER($6.fax) LIKE UPPER('%81%'))))))

Alex

OK. The problem is not UPPER or LIKE but some query optimization that goes wrong.

Please try build 2.3.6_03, in which this optimization is disabled.

hi,

Will this be fixed or kept in future versions? We've been forced to switch to major (aka no *_x) versions only..

Btw -> it'd be great if you could extend the description in your change list of the various versions, i.e. writing what effects a fix has so that we can see whether that might fix some other issues one might have as well..

thanks

Alex

This optimization will remain disabled in version 2.3.7 that will be released soon, so using 2.3.6_x is temporary. In future versions the optimization may be enabled again, but only after considering your example.

Unfortunately extending the description in the change log cannot help - even in this case it would be very difficult to explain the bug in a way that you (that experienced it) can recognize it. ObjectDB 2.x is still in a process of becoming more stable, so using the most recent version is recommended in the general case.