[teiid-issues] [JBoss JIRA] (TEIID-2988) MongoDB: Function substring - starting index and number of arguments

[Van Halbert (JIRA)]

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

Van Halbert reopened TEIID-2988:
--------------------------------


The second problem with SUBSTRING's argument is not fixed. Arguments/indexes in teiid substring function are 1-based while arguments in mongo $substr function are 0-based. Translator should compensate it.

How to reproduce:

SELECT SUBSTRING(BQT1.SmallA.stringkey, 1) FROM BQT1.SmallA ORDER BY intkey

Expected result: SUBSTRING function returns the whole stringkey value

Actual result: SUBSTRING function returns stringkey value without the first letter

> MongoDB: Function substring - starting index and number of arguments
> --------------------------------------------------------------------
>
>                 Key: TEIID-2988
>                 URL: https://issues.jboss.org/browse/TEIID-2988
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.4.1
>            Reporter: Filip Elias
>            Assignee: Ramesh Reddy
>              Labels: Beta1
>             Fix For: 8.8, 8.7.1
>
>
> Two minor bugs in function substring()
> Mongo's function $substr takes only 3 arguments so this query fails:
> {code}
> SELECT intkey, SUBSTRING(BQT1.SmallA.stringkey, 1) FROM BQT1.SmallA ORDER BY intkey
> {code}
> {code}
> Exception:13:18:48,220 ERROR [org.teiid.CONNECTOR] (Worker38_QueryProcessorQueue345) Connector worker process failed for atomic-request=omsmHP0o16Sa.40.0.86: com.mongodb.CommandFailureException: { "serverUsed" : "localhost/127.0.0.1:27017" , "errmsg" : "exception: the $substr operator requires 3 operand(s)" , "code" : 16020 , "ok" : 0.0}
> 	at com.mongodb.CommandResult.getException(CommandResult.java:71)
> 	at com.mongodb.CommandResult.throwOnError(CommandResult.java:110)
> 	at com.mongodb.DBCollection.aggregate(DBCollection.java:1308)
> {code}
> Generated mongo command: 
> {code}
> { aggregate: "smalla", pipeline: [ { $project: { c_0: "$INTKEY", c_1: { $substr: [ "$STRINGKEY", 1 ] } } }, { $sort: { c_0: 1 } } ] }
> {code}
> Mongo's $substr function second argument represents number of bytes to skip, not the starting index. The function substring(StringKey,1,1) returns the second letter, but it should return the first letter.



--
This message was sent by Atlassian JIRA
(v6.3.8#6338)