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-la...