[JBoss JIRA] (TEIID-3227) Add support for sub-expression pushing of must pushdown functions
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3227?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3227:
---------------------------------------
If the function is deterministic, then it's valid to create the pushdown form but we would need to add an aggregate such as first/last or add the function to the grouping (although that wouldn't quite be correct for rollups).
So more than likely this would look like:
SELECT AAA, SUM(first(x)) AS BBB_ALIAS FROM (select AAA, custom_function(BBB) as x from some_table WHERE some_table.CCC = 'VAL') as v GROUP BY AAA ORDER BY AAA
Detecting this is also a little different than the existing must pushdown logic in RuleAssignOutputElements in that we're actually dealing with the grouping node and not a project node when this determination needs to be made. Although we could rationalize this by inserting an explicit project node, rather than the current logic in the grouping processing node that performs the necessary projection.
> Add support for sub-expression pushing of must pushdown functions
> -----------------------------------------------------------------
>
> Key: TEIID-3227
> URL: https://issues.jboss.org/browse/TEIID-3227
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Affects Versions: 8.9
> Reporter: Michał Warecki
> Assignee: Steven Hawkins
> Priority: Minor
>
> As a user I'd like to have a possibility to push down sub-expressions of must pushdown functions.
> Example:
> Input query with not supported group by:
> SELECT AAA, SUM(custom_function(BBB)) AS BBB_ALIAS FROM some_table WHERE some_table.CCC = 'VAL' GROUP BY AAA ORDER BY AAA
>
> Output query (processed by teiid):
> SELECT AAA, BBB AS BBB_ALIAS FROM some_table.CCC = 'VAL'
>
> What I think it should looks like:
> SELECT AAA, custom_function(BBB) AS BBB_ALIAS FROM some_table.CCC = 'VAL'
> https://developer.jboss.org/message/910926#910926
--
This message was sent by Atlassian JIRA
(v6.3.8#6338)
9 years, 7 months
[JBoss JIRA] (TEIID-3231) Timezone of DV server has influence on Date and Timestamp columns in some data sources
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3231?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3231:
---------------------------------------
>From the BZ it says JDG, MongoDB and Solr are affected.
At least for JDG and MongoDB there is no remote notion of a Calendar based timestamp. That is to say it's all UTC so you would expect date/time/timestamp values to display differently in different local timezones vs. the remote. I assume that Solr is the same. This is not entirely undesirable behavior if you want to keep the original UTC value.
The approaches would be to have the respective translators updated with a server timezone setting (similar to JDBC) and have the translator adjust the utc value to keep the calendar fields consistent. Or don't work an enhancement and let the user manually use the modifytimezone system function.
> Timezone of DV server has influence on Date and Timestamp columns in some data sources
> --------------------------------------------------------------------------------------
>
> Key: TEIID-3231
> URL: https://issues.jboss.org/browse/TEIID-3231
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1
> Reporter: Van Halbert
> Assignee: Steven Hawkins
>
> Timezone of DV server has influence on Date and Timestamp columns in some data sources
--
This message was sent by Atlassian JIRA
(v6.3.8#6338)
9 years, 7 months
[JBoss JIRA] (TEIID-2988) MongoDB: Function substring - starting index and number of arguments
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-2988?page=com.atlassian.jira.plugin... ]
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)
9 years, 7 months