Details
-
Quality Risk
-
Resolution: Done
-
Critical
-
8.12.14.6_4
-
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
