[JBoss JIRA] (TEIID-2992) MongoDB: Offset clause causes MongoDB to crash
by Filip Elias (JIRA)
Filip Elias created TEIID-2992:
----------------------------------
Summary: MongoDB: Offset clause causes MongoDB to crash
Key: TEIID-2992
URL: https://issues.jboss.org/browse/TEIID-2992
Project: Teiid
Issue Type: Bug
Components: Misc. Connectors
Affects Versions: 8.4.1
Environment: MongoDB < 2.5.2
Reporter: Filip Elias
Assignee: Ramesh Reddy
MongoDB(version < 2.5.2) will crash when offset clause is used in SQL query.
Althought the bug[1] is in MongoDB (Adding big number into $limit causes Mongodb to shutdown), it is also caused by teiid because it adds max integer into $limit which seems to be unnecessary.
Sample query:
{code}
SELECT INTKEY FROM bqt1.smalla ORDER BY INTKEY OFFSET 45 ROWS
{code}
Generated mongo command:
{code}
{ aggregate: "smalla", pipeline: [ { $project: { c_0: "$INTKEY" } }, { $sort: { c_0: 1 } }, { $skip: 45 }, { $limit: 2147483647 } ] }
{code}
[1] https://jira.mongodb.org/browse/SERVER-10136
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2985) odbc script reader does not handle extra statement delimiters
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2985?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-2985.
-----------------------------------
Resolution: Done
Updated the logic to handle extra statement delimiters.
> odbc script reader does not handle extra statement delimiters
> -------------------------------------------------------------
>
> Key: TEIID-2985
> URL: https://issues.jboss.org/browse/TEIID-2985
> Project: Teiid
> Issue Type: Bug
> Components: ODBC
> Affects Versions: 7.7
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.7.1, 8.8
>
>
> If sent the block of statements:
> BEGIN;declare "SQL_CUR0x1e4ba50" cursor with hold for select * from pg_proc;;fetch 1 in "SQL_CUR0x1e4ba50"
> We'll stop processing at the extra ; after the declare.
> In older branches such as 7.7 we'll attempt to process an empty sql statement, which will throw an exception.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2988) MongoDB: Function substring - starting index and number of arguments
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2988?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-2988.
---------------------------------
Labels: Beta1 (was: )
Resolution: Done
Done, updated the function modifier to act accordingly
> 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.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)
11 years, 10 months
[JBoss JIRA] (TEIID-2990) MongoDB: Usage of lookup() function throws an exception
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2990?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-2990.
---------------------------------
Labels: Beta1 (was: )
Resolution: Done
Upon investigation it looks like <2.6 version of the MongoDB do not support scalar values in the $projection. In version 2.6 they introduced $literal construct with which it is possible.
So, added the version class from jdbc translator, but parked the default server version as 2.4 in the translator. If translator sees this situation in the query, then it will error with proper message, however if user is working with 2.6 version and have properly set the database version in the translator override properties to 2.6 then it will use the $literal construct and return correct result.
> MongoDB: Usage of lookup() function throws an exception
> -------------------------------------------------------
>
> Key: TEIID-2990
> URL: https://issues.jboss.org/browse/TEIID-2990
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.4.1
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 8.7.1, 8.8
>
> Attachments: server.log
>
>
> Exception[1] is thrown when function lookup() is used in SQL query.
> Mongo translator doesn't support selection of constants only, which causes the exception(select 1, select 'hi' from smalla).
> {code}
> [1] 3:59:20,997 ERROR [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue6) Connector worker process failed for atomic-request=Ef2Z7E0246by.1.5.3: com.mongodb.CommandFailureException: { "serverUsed" : "localhost/127.0.0.1:27017" , "errmsg" : "exception: $projection requires at least one output field" , "code" : 16403 , "ok" : 0.0}
> at com.mongodb.CommandResult.getException(CommandResult.java:71)
> {code}
> Server log is in the attachment.
> Sample query:
> {code}
> SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT1.SmallB WHERE BQT1.SmallA.IntKey= lookup('BQT1.SmallB', 'IntKey', 'StringKey', BQT1.SmallA.StringKey) ORDER BY IntKey
> {code}
> Generated mongo commands:
> {code}
> { aggregate: "smalla", pipeline: [ { $project: { _m0: "$INTKEY", _m1: "$STRINGKEY" } } ] }
> {code}
> and
> {code}
> { aggregate: "smallb", pipeline: [ { $project: {} } ] }
> {code}
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2987) MongoDB: No rows are returned when a function is used in a where clause
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2987?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-2987.
---------------------------------
Labels: Beta1 (was: )
Resolution: Done
Functions and Aggregate functions will not be supported in the $match phase in the MongoDB. They need to be pushed as projected columns, then do a $match on the computed value.
What was occurring before was, the $match phase was being added correctly but implicit adding the functions to $project were not being added. Also note that since these are being placed on project then match the performance will not as good as others
> MongoDB: No rows are returned when a function is used in a where clause
> -----------------------------------------------------------------------
>
> Key: TEIID-2987
> URL: https://issues.jboss.org/browse/TEIID-2987
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.4.1
> Environment: Teiid 8.4.1
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 8.7.1, 8.8
>
>
> Teiid will return no rows when a function which is pushed down is used in a where clause.
> These queries should return few rows, but the result is empty:
> Query:
> {code}
> SELECT intkey, stringnum FROM BQT1.SmallA WHERE concat(stringkey, stringnum) = '7-17' ORDER BY intkey
> {code}
> Generated mongo command:
> {code}
> {aggregate: "smalla", pipeline: [ { $match: { _m0: "7-17" } }, { $project: { c_0: "$INTKEY", c_1: "$STRINGNUM" } }, { $sort: { c_0: 1 } } ] }
> {code}
> Query:
> {code}
> select intKey from bqt1.smalla where intKey+intKey >20
> {code}
> Generated mongo command:
> {code}
> {aggregate: "smalla", pipeline: [ { $match: { _m0: { $gt: 20 } } }, { $project: { _m1: "$INTKEY" } } ] }
> {code}
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2987) MongoDB: No rows are returned when a function is used in a where clause
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2987?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-2987:
--------------------------------
Description:
Teiid will return no rows when a function which is pushed down is used in a where clause.
These queries should return few rows, but the result is empty:
Query:
{code}
SELECT intkey, stringnum FROM BQT1.SmallA WHERE concat(stringkey, stringnum) = '7-17' ORDER BY intkey
{code}
Generated mongo command:
{code}
{aggregate: "smalla", pipeline: [ { $match: { _m0: "7-17" } }, { $project: { c_0: "$INTKEY", c_1: "$STRINGNUM" } }, { $sort: { c_0: 1 } } ] }
{code}
Query:
{code}
select intKey from bqt1.smalla where intKey+intKey >20
{code}
Generated mongo command:
{code}
{aggregate: "smalla", pipeline: [ { $match: { _m0: { $gt: 20 } } }, { $project: { _m1: "$INTKEY" } } ] }
{code}
was:
Teiid will return no rows when a function which is pushed down is used in a where clause.
These queries should return few rows, but the result is empty:
Query:
{code}
SELECT intkey, stringnum FROM BQT1.SmallA WHERE concat(stringkey, stringnum) = '7-17' ORDER BY intkey
{code}
Generated mongo command:
{code}
{aggregate: "smalla", pipeline: [ { $match: { _m0: "7-17" } }, { $project: { c_0: "$INTKEY", c_1: "$STRINGNUM" } }, { $sort: { c_0: 1 } } ] }
{code}
Query:
[code}
select intKey from bqt1.smalla where intKey+intKey >20
{code}
Generated mongo command:
{code}
{aggregate: "smalla", pipeline: [ { $match: { _m0: { $gt: 20 } } }, { $project: { _m1: "$INTKEY" } } ] }
{code}
> MongoDB: No rows are returned when a function is used in a where clause
> -----------------------------------------------------------------------
>
> Key: TEIID-2987
> URL: https://issues.jboss.org/browse/TEIID-2987
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.4.1
> Environment: Teiid 8.4.1
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
> Fix For: 8.7.1, 8.8
>
>
> Teiid will return no rows when a function which is pushed down is used in a where clause.
> These queries should return few rows, but the result is empty:
> Query:
> {code}
> SELECT intkey, stringnum FROM BQT1.SmallA WHERE concat(stringkey, stringnum) = '7-17' ORDER BY intkey
> {code}
> Generated mongo command:
> {code}
> {aggregate: "smalla", pipeline: [ { $match: { _m0: "7-17" } }, { $project: { c_0: "$INTKEY", c_1: "$STRINGNUM" } }, { $sort: { c_0: 1 } } ] }
> {code}
> Query:
> {code}
> select intKey from bqt1.smalla where intKey+intKey >20
> {code}
> Generated mongo command:
> {code}
> {aggregate: "smalla", pipeline: [ { $match: { _m0: { $gt: 20 } } }, { $project: { _m1: "$INTKEY" } } ] }
> {code}
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2987) MongoDB: No rows are returned when a function is used in a where clause
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2987?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-2987:
--------------------------------
Description:
Teiid will return no rows when a function which is pushed down is used in a where clause.
These queries should return few rows, but the result is empty:
Query:
{code}
SELECT intkey, stringnum FROM BQT1.SmallA WHERE concat(stringkey, stringnum) = '7-17' ORDER BY intkey
{code}
Generated mongo command:
{code}
{aggregate: "smalla", pipeline: [ { $match: { _m0: "7-17" } }, { $project: { c_0: "$INTKEY", c_1: "$STRINGNUM" } }, { $sort: { c_0: 1 } } ] }
{code}
Query:
[code}
select intKey from bqt1.smalla where intKey+intKey >20
{code}
Generated mongo command:
{code}
{aggregate: "smalla", pipeline: [ { $match: { _m0: { $gt: 20 } } }, { $project: { _m1: "$INTKEY" } } ] }
{code}
was:
Teiid will return no rows when a function which is pushed down is used in a where clause.
These queries should return few rows, but the result is empty:
Query:
SELECT intkey, stringnum FROM BQT1.SmallA WHERE concat(stringkey, stringnum) = '7-17' ORDER BY intkey
Generated mongo command:
{aggregate: "smalla", pipeline: [ { $match: { _m0: "7-17" } }, { $project: { c_0: "$INTKEY", c_1: "$STRINGNUM" } }, { $sort: { c_0: 1 } } ] }
Query:
select intKey from bqt1.smalla where intKey+intKey >20
Generated mongo command:
{aggregate: "smalla", pipeline: [ { $match: { _m0: { $gt: 20 } } }, { $project: { _m1: "$INTKEY" } } ] }
> MongoDB: No rows are returned when a function is used in a where clause
> -----------------------------------------------------------------------
>
> Key: TEIID-2987
> URL: https://issues.jboss.org/browse/TEIID-2987
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.4.1
> Environment: Teiid 8.4.1
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
> Fix For: 8.7.1, 8.8
>
>
> Teiid will return no rows when a function which is pushed down is used in a where clause.
> These queries should return few rows, but the result is empty:
> Query:
> {code}
> SELECT intkey, stringnum FROM BQT1.SmallA WHERE concat(stringkey, stringnum) = '7-17' ORDER BY intkey
> {code}
> Generated mongo command:
> {code}
> {aggregate: "smalla", pipeline: [ { $match: { _m0: "7-17" } }, { $project: { c_0: "$INTKEY", c_1: "$STRINGNUM" } }, { $sort: { c_0: 1 } } ] }
> {code}
> Query:
> [code}
> select intKey from bqt1.smalla where intKey+intKey >20
> {code}
> Generated mongo command:
> {code}
> {aggregate: "smalla", pipeline: [ { $match: { _m0: { $gt: 20 } } }, { $project: { _m1: "$INTKEY" } } ] }
> {code}
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2983) MongoDB: Function "count()" returns sum instead of number of rows
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2983?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-2983:
-------------------------------------
I do not know if that type of sum statement can be build, mongodb processing is a pipeline. In order for SUM function to return the number documents (instead of summation) you would need to give it an "_id" based grouping, before the summation task and there can be only one "_id" in the pipeline execution. So, now
{code}
select count(*), count(CustomerId) from customer
{code}
will fail saying that multiple count aggregates are not allowed
however, queries like these are allowed
{code}
select count(*), min(CustomerId) from customer
{code}
as these do not need the grouping construct.
> MongoDB: Function "count()" returns sum instead of number of rows
> -----------------------------------------------------------------
>
> Key: TEIID-2983
> URL: https://issues.jboss.org/browse/TEIID-2983
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1
> Reporter: Van Halbert
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 8.7.1, 8.8
>
>
> Function "count()" returns sum of rows instead of number of rows
> Query [1] returns sum of values of all rows of IntKey column.
> Query [2] correctly returns number of rows
>
> [1] SELECT COUNT(IntKey) FROM BQT1.SmallA
> [2] SELECT COUNT(*) FROM BQT1.SmallA
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months