[teiid-issues] [JBoss JIRA] (TEIID-5440) Couchbase translator - LET usage prevents index scan

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-5440?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-5440.
-----------------------------------
    Fix Version/s: 11.1
       Resolution: Done


Reworked the logic so that let is only used for array position expressions as it seems like all column references and meta usage do not require a let.

> Couchbase translator - LET usage prevents index scan
> ----------------------------------------------------
>
>                 Key: TEIID-5440
>                 URL: https://issues.jboss.org/browse/TEIID-5440
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: Misc. Connectors
>    Affects Versions: 8.12.14.6_4
>            Reporter: Jan Stastny
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 11.1
>
>
> 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
> {code:sql}
> SELECT IntKey FROM SmallA;
> {code}
> is being pushed as
> {code:sql}
> SELECT `$cb_c1_IntKey` c1 FROM `dvqe` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`intkey` WHERE `$cb_t1`.`type` = 'smalla'
> {code}
> Which is fine and index on `type` column is scanned on couchbase side.
> But when I issue query
> {code:sql}
> SELECT * FROM SmallA;
> {code}
> it is pushed as
> {code:sql}
> 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'
> {code}
> 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



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)