Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5440

Couchbase translator - LET usage prevents index scan

    Details

    • Type: Quality Risk
    • Status: Resolved (View Workflow)
    • Priority: Critical
    • Resolution: Done
    • Affects Version/s: 8.12.14.6_4
    • Fix Version/s: 11.1
    • Component/s: Misc. Connectors
    • Labels:
      None

      Description

      Couchbase translator excesively uses LET clause(1) just for purpose of naming columns.

      That's not correct usage of LET clause and affects query execution in couchbase.

      Simple query

      SELECT IntKey FROM SmallA;
      

      is being pushed as

      SELECT `$cb_c1_IntKey` c1 FROM `dvqe` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`intkey` WHERE `$cb_t1`.`type` = 'smalla'
      

      Which is fine and index on `type` column is scanned on couchbase side.

      But when I issue query

      SELECT * FROM SmallA;
      

      it is pushed as

      SELECT 
      `$cb_c1_documentID` c1,
       `$cb_c2_FloatNum` c2, 
      `$cb_c3_IntKey` c3, 
      `$cb_c4_BigIntegerValue` c4, 
      `$cb_c5_StringKey` c5, 
      `$cb_c6_CharValue` c6, 
      `$cb_c7_LongNum` c7, 
      `$cb_c8_type` c8, 
      `$cb_c9_DoubleNum` c9, 
      `$cb_c10_ObjectValue` c10, 
      `$cb_c11_ShortValue` c11, 
      `$cb_c12_BigDecimalValue` c12, 
      `$cb_c13_DateValue` c13, 
      `$cb_c14_BooleanValue` c14, 
      `$cb_c15_TimestampValue` c15, 
      `$cb_c16_ByteNum` c16, 
      `$cb_c17_StringNum` c17, 
      `$cb_c18_TimeValue` c18, 
      `$cb_c19_IntNum` c19 
      FROM `dvqe` `$cb_t1` 
      LET `$cb_c1_documentID` = META(`$cb_t1`).id, 
      `$cb_c2_FloatNum` = `$cb_t1`.`floatnum`, 
      `$cb_c3_IntKey` = `$cb_t1`.`intkey`, 
      `$cb_c4_BigIntegerValue` = `$cb_t1`.`bigintegervalue`, 
      `$cb_c5_StringKey` = `$cb_t1`.`stringkey`, 
      `$cb_c6_CharValue` = `$cb_t1`.`charvalue`, 
      `$cb_c7_LongNum` = `$cb_t1`.`longnum`, 
      `$cb_c8_type` = `$cb_t1`.`type`, 
      `$cb_c9_DoubleNum` = `$cb_t1`.`doublenum`, 
      `$cb_c10_ObjectValue` = `$cb_t1`.`objectvalue`, 
      `$cb_c11_ShortValue` = `$cb_t1`.`shortvalue`, 
      `$cb_c12_BigDecimalValue` = `$cb_t1`.`bigdecimalvalue`, 
      `$cb_c13_DateValue` = `$cb_t1`.`datevalue`, 
      `$cb_c14_BooleanValue` = `$cb_t1`.`booleanvalue`, 
      `$cb_c15_TimestampValue` = `$cb_t1`.`timestampvalue`, 
      `$cb_c16_ByteNum` = `$cb_t1`.`bytenum`, 
      `$cb_c17_StringNum` = `$cb_t1`.`stringnum`, 
      `$cb_c18_TimeValue` = `$cb_t1`.`timevalue`, 
      `$cb_c19_IntNum` = `$cb_t1`.`intnum` 
      WHERE `$cb_c8_type` = 'smalla'
      

      The way how WHERE criteria is being pushed (either column or LET variable reference) affects execution plan and prevents index scan in the latter case. (WHERE `$cb_t1`.`type` = 'smalla' vs. WHERE `$cb_c8_type` = 'smalla').

      (1) https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/let.html

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jstastny Jan Stastny
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: