Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

  • Blama
    replied
    Hi Isomorphic,

    thanks for the heads up and all the best to your colleague.

    Best regards
    Blama

    Leave a comment:


  • Isomorphic
    replied
    Just an FYI - the developer handling this ticket is assigned to multiple high priority tasks and has just been admitted to hospital, so this will be a pretty long wait, just to set expectations.

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    to clarify: I was only speaking about the bug.
    IMHO it does not matter if it is fixed by going back to the old way (like with sql.postgresql.useILike=false) of
    Code:
    lower(field) = 'value' (where value is the lowercased version of the search string)
    or by using
    Code:
    field ILIKE 'value' (where value is the lowercased version of the search string or the unmodified search string)
    , but...
    • I agree that using ILIKE could have performance benefits (I did not test it)
    • At the same this would already be solveable with an index on lower(field) (see "Examples" here). This then is doable with a normal btree index, no need for Trigram extension for GIN indexes.
    • This is not true for the iContains, which needs ILIKE to be performant.
    • The issue with using the ILIKE approach for iEquals is that you need to escape percent sign ( %) and underscore sign (_) in the search string.

    So IMHO it seems more straightforward to use ILIKE only for iContains/iStartsWith/iEndsWith and leave iEquals as it is.

    Does this make sense to you?

    Best regards
    Blama

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    thanks for looking into this. I’m on vacation now and will test and answer in 1-2 weeks, as I don’t know the exact problem we had with it anymore.

    Best regards
    Blama

    Leave a comment:


  • Isomorphic
    replied
    We've reproduced behavior you are reporting, but this needs some clarity.

    First, why do you say effect on iEquals operator is not intended? For case insensitive comparisons iEquals using iLike approach would get same benefits of using indexes as other case insensitive operators, like iContains for example. Can you explain this please?

    Second, BatchUploader lookup by ID has an issue that needs to be fixed. When sql.postgresql.useILike=false we use lower(field)="value" approach. Then, if sql.postgresql.useILike=true, we could use either same lower(field)="value" approach or switch to using field ilike "value" as well. This is related to the first question, since this lookup is using case insensitive comparison (like iEquals does) and we think using iLike here would make perfect sense. Do you agree?

    Leave a comment:


  • Isomorphic
    replied
    Apologies for the delay, this was lost in a queue of assigned tasks. We are working on this and will follow up soon.

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    can you reproduce this issue?

    Best regards
    Blama

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    can you reproduce this issue?

    Best regards
    Blama

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    can you reproduce this issue?

    Best regards
    Blama

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    this is the generated query with sql.postgresql.useILike: true in the server.properties:
    Code:
    SELECT longfieldlist FROM tablename JOIN alltheneededjoinsforallthefields [B]WHERE (T_COUNTRY.ISO_3166_1_ALPHA_2='DE')[/B]
    .

    This is the query without the new setting:
    Code:
    SELECT longfieldlist FROM tablename JOIN alltheneededjoinsforallthefields [B]WHERE (LOWER(T_COUNTRY.ISO_3166_1_ALPHA_2)='de')[/B]
    My suspicion is that the new setting should affect the "i" versions of operators contains/startsWith/endsWith and their respective not-versions. This seems to be working fine.
    It somehow also seems to have an effect on iEquals, which is not intended.

    Best regards
    Blama

    Leave a comment:


  • Isomorphic
    replied
    Could you clarify - if ILIKE is equivalent to = plus lower() then how could this change the importDisplay feature?

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    I tried this feature using v12.0p_2020-03-11. It seems to work in general, but broke importStrategy="display"-fields in BatchUploader, as now the query is with a normal "=" instead with ILIKE. It was a "=" with LOWER() before.
    Code:
            <field name="CUST_ADDRESS_COUNTRY_ID" [B]importStrategy="display"[/B] uploadFieldName="ADDRESS_COUNTRY_ID" displayField="CUST_ADDRESS_COUNTRY_NAME"
                foreignKey="T_COUNTRY.ID">
                <title><fmt:message key="country" /></title>
                <validators>
                    <validator type="hasRelatedRecord">
                        <errorMessage><fmt:message key="validatorSelectCountry" /></errorMessage>
                    </validator>
                </validators>
            </field>
    Best regards
    Blama

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    thank you. I'm pretty sure this setting will help other users as well. It's also great that you made this configurable, as it might be possible that other users like ANSI SQL here better for some reason.

    Best regards
    Blama

    Leave a comment:


  • Isomorphic
    replied
    We have now implemented optional support for ILIKE in our PostgreSQL driver. This will be present in 12.1 and 12.0 builds as of tomorrow - ie, builds dated December 21 or later. Note, the use of ILIKE depends on a server.properties flag, which is set to true in 12.1 builds, but false in 12.0 builds (for safety, because 12.0 is really a bugfix-only branch). So to see a difference with a 12.0 build, add the following line to your server.properties file:

    Code:
    sql.postgresql.useILike: true

    Leave a comment:


  • Isomorphic
    replied
    We agree that this would be a worthwhile enhancement to our Postgres support. A developer is scheduled to make this change, and we will post back on this thread soon.

    Leave a comment:

Working...
X