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

[Ramesh Reddy (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2988?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12973823#comment-12973823 ] 

Ramesh Reddy commented on TEIID-2988:
-------------------------------------

skip and starting index would be same, what is missing is third the length parameter. Since Teiid can use both SUBSTRING(X,Y) or SUBSTRING(X,Y,Z) in the first case MongoDB translator would need to set length. Since length function not available, I will be using MAX_STRING_STRING system property to define the largest string Teiid can allow.

> 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
>             Fix For: 8.7.1, 8.8
>
>
> 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.2.3#6260)