[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)
10 years, 7 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)
10 years, 7 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)
10 years, 7 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)
10 years, 7 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)
10 years, 7 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)
10 years, 7 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)
10 years, 7 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)
10 years, 7 months
[JBoss JIRA] (TEIID-2896) Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2896?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-2896:
-------------------------------------
IMO, we should support simple case of fix length where all the lines are same width with Designer, so that we can keep the UI simple. The SELECTOR and varying width can be user exercise as I do not think they will be used as much, and making intent show up in UI could be challenging.
> Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2896
> URL: https://issues.jboss.org/browse/TEIID-2896
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.7
>
> Attachments: sample, TEIID-2896.patch
>
>
> Current behavior is that data lines too short to fit a fixed width data interpretation produces an error such as "Error: Text parse error: Fixed width line width 35 is smaller than the expected 60 on text line 1 in file:/tmp/file.csv.". The customer's use case is interpreting data files from another application which bundles multiple types of data along with additional descriptive info/metadata about the data contained within the file. As the error is thrown if any lines after the first interpreted line are shorter than the summed value of the fixed width columns, they are unable to query the file. The customer wishes for the ERROR to be suppressed and the query to return the data independent of whether it fits the fixed width criteria and potentially to be able to filter the data being retrieved.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 7 months