Couchbase substring function is 0-based --------------------------------------- Key: TEIID-5033 URL: https://issues.jboss.org/browse/TEIID-5033 Project: Teiid Issue Type: Bug Components: Misc. Connectors Affects Versions: 8.12.x-6.4 Reporter: Jan Stastny Assignee: Steven Hawkins Fix For: 10.0, 8.12.x-6.4, 9.3.3 There is difference in SUBSTRING function handling in Teiid and Couchbase. Teiid handling: {code:sql|title=query1} SELECT SUBSTRING('49',1) {code} || expr1 || || 49 || Couchbase pushdown: {code:sql|title=query2} SELECT SUBSTRING(IntKey,1) FROM BQT1.SmallA WHERE IntKey=49 {code} || expr1 || || 9 || {code:title=teiid-command.log} 16:04:32,385 INFO [org.teiid.COMMAND_LOG] (New I/O worker #2) QE2ZpDa/Btt5 START USER COMMAND: startTime=2017-08-24 16:04:32.385 requestID=QE2ZpDa/Btt5.116 txID=null sessionID=QE2ZpDa/Btt5 applicationName=JDBC principal=user@teiid-security vdbName=couchbase vdbVersion=1 sql=SELECT SUBSTRING(IntKey,1) FROM BQT1.SmallA WHERE IntKey=49 16:04:32,390 DEBUG [org.teiid.COMMAND_LOG] (Worker35_QueryProcessorQueue325) QE2ZpDa/Btt5 START DATA SRC COMMAND: startTime=2017-08-24 16:04:32.39 requestID=QE2ZpDa/Btt5.116 sourceCommandID=0 executionID=79 txID=null modelName=Couchbase_small translatorName=couchbase sessionID=QE2ZpDa/Btt5 principal=user@teiid-security sql=SELECT SUBSTRING(convert(Couchbase_small.SmallA.IntKey, string), 1) FROM Couchbase_small.SmallA WHERE Couchbase_small.SmallA.IntKey = 49 16:04:32,391 DEBUG [org.teiid.COMMAND_LOG] (Worker35_QueryProcessorQueue325) QE2ZpDa/Btt5 SOURCE SRC COMMAND: endTime=2017-08-24 16:04:32.391 requestID=QE2ZpDa/Btt5.116 sourceCommandID=0 executionID=79 txID=null modelName=Couchbase_small translatorName=couchbase sessionID=QE2ZpDa/Btt5 principal=user@teiid-security sourceCommand=[SELECT SUBSTR(TOSTRING(`$cb_c1_IntKey`), 1) FROM `dvqe_small` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`IntKey` WHERE `$cb_c1_IntKey` = 49 AND `$cb_t1`.`type` = 'SmallA'] 16:04:32,607 DEBUG [org.teiid.COMMAND_LOG] (Worker34_QueryProcessorQueue326) QE2ZpDa/Btt5 END SRC COMMAND: endTime=2017-08-24 16:04:32.607 requestID=QE2ZpDa/Btt5.116 sourceCommandID=0 executionID=79 txID=null modelName=Couchbase_small translatorName=couchbase sessionID=QE2ZpDa/Btt5 principal=user@teiid-security finalRowCount=1 cpuTime(ns)=1689816 16:04:32,610 INFO [org.teiid.COMMAND_LOG] (Worker34_QueryProcessorQueue327) QE2ZpDa/Btt5 END USER COMMAND: endTime=2017-08-24 16:04:32.61 requestID=QE2ZpDa/Btt5.116 txID=null sessionID=QE2ZpDa/Btt5 principal=user@teiid-security vdbName=couchbase vdbVersion=1 finalRowCount=1 {code}