Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-533

Performance: Fix lower/f_unaccent usage by checking all 5 index types

    XMLWordPrintable

Details

    • CP: sprint 78
    • 3
    • Core: Platform

    Description

      unit tests fail due to to lack of detection for indexes. The cql2pgjson queryByLike only checks ginIndex but it should check btree as well. It falls back to use sql = wrapInLowerUnaccent(indexText, schemaIndex); when it cannot find ginIndex (schemaIndex is null).

      The following unit tests fail

        @Test
  public void multiFieldNamesNonUniqueIndex() throws Exception {
    CQL2PgJSON cql2pgJson = new CQL2PgJSON("tablea");
    cql2pgJson.setDbSchemaPath("templates/db_scripts/compoundIndex.json");
    String sql = cql2pgJson.toSql("keys == x").toString();
    String expected = "WHERE CASE WHEN length('x') <= 600 "
        + "THEN left(lower(f_unaccent(concat_space_sql(tablea.jsonb->>'key1' , tablea.jsonb->>'key2'))),600) LIKE lower(f_unaccent('x')) "
        + "ELSE left(lower(f_unaccent(concat_space_sql(tablea.jsonb->>'key1' , tablea.jsonb->>'key2'))),600)  LIKE  left(lower(f_unaccent('x')),600) "
        + "AND lower(f_unaccent(concat_space_sql(tablea.jsonb->>'key1' , tablea.jsonb->>'key2')))  LIKE  lower(f_unaccent('x')) END";
    assertEquals(expected, sql);
  }

  @Test
  public void multiFieldNamesUniqueIndex() throws Exception {
    CQL2PgJSON cql2pgJson = new CQL2PgJSON("tablea");
    cql2pgJson.setDbSchemaPath("templates/db_scripts/compoundIndex.json");
    String sql = cql2pgJson.toSql("barcode == y").toString();
    String expected = "WHERE CASE WHEN length('y') <= 600 "
      + "THEN left(lower(f_unaccent(concat_space_sql(tablea.jsonb->>'department' , tablea.jsonb->>'staffnumber'))),600) LIKE lower(f_unaccent('y')) "
      + "ELSE left(lower(f_unaccent(concat_space_sql(tablea.jsonb->>'department' , tablea.jsonb->>'staffnumber'))),600)  LIKE  left(lower(f_unaccent('y')),600) "
      + "AND lower(f_unaccent(concat_space_sql(tablea.jsonb->>'department' , tablea.jsonb->>'staffnumber')))  LIKE  lower(f_unaccent('y')) END";
    assertEquals(expected, sql);
  }

      TestRail: Results

        Attachments

          Issue Links

            blocks

            Bug - A problem which impairs or prevents the functions of the product. RMB-498 Truncate b-tree string for 2712 index row size

            • P2 - normal priority level, must be included in the current development cycle
            • Closed

            New Feature - New feature requests RMB-499 Add "normalizeDigits" function

            • P2 - normal priority level, must be included in the current development cycle
            • Closed
            relates to

            New Feature - New feature requests RMB-534 Reject CQL queries that match no index in schema.json

            • P3 - low priority level, item will be considered for inclusion in the next dev cycle
            • Open

            Bug - A problem which impairs or prevents the functions of the product. RMB-549 Mismatch lower() for compound index

            • P1 - highest priority item, drop everything else before this is resolved, reserved for critical bugfixes
            • Closed

            Task - A task that needs to be done. MODINVSTOR-407 GET instance-storage_instances?query=(item.effectiveLocationId==${effectiveLocationVal}) is slow

            • P2 - normal priority level, must be included in the current development cycle
            • Closed

            Activity

              People

                julianladisch Julian Ladisch
                evaluk Eric Valuk
                Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases