[JBoss JIRA] (TEIID-3756) Order By clause specified in STRING_AGG function is skipped when pushed down to PostgreSQL
by Salvatore R (JIRA)
[ https://issues.jboss.org/browse/TEIID-3756?page=com.atlassian.jira.plugin... ]
Salvatore R commented on TEIID-3756:
------------------------------------
I created a new ticket here:
https://issues.jboss.org/browse/TEIID-3777
> Order By clause specified in STRING_AGG function is skipped when pushed down to PostgreSQL
> ------------------------------------------------------------------------------------------
>
> Key: TEIID-3756
> URL: https://issues.jboss.org/browse/TEIID-3756
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.4
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.12, 8.11.5
>
>
> I defined a small table in PostgreSQL as follows:
> {code:sql}
> create table test_string_agg (col1 varchar(10))
> insert into test_string_agg VALUES ('b');
> insert into test_string_agg VALUES ('a');
> insert into test_string_agg VALUES ('c');
> {code}
> When I run a query with a STRING_AGG function where an ORDER BY clause is specified, the strings are not concatenated in the expected order. For example, these three queries:
> {code:sql}
> select string_agg(col1, ' , ' order by col1) from pg.test_string_agg;
> select string_agg(col1, ' , ' order by col1 desc) from pg.test_string_agg;
> select string_agg(col1, ' , ' order by col1 asc) from pg.test_string_agg;
> {code}
> return all the same result "b , a , c".
> It seems that the ORDER BY clause is not pushed down to PostgreSQL, in fact all the three rewritten queries executed in PostgreSQL look like:
> {code:sql}
> SELECT STRING_AGG(g_0."col1", ' , ') FROM "public"."test_string_agg" AS g_0
> {code}
> If the STRING_AGG function is not pushed down (e.g. in MySQL), I get an expected result.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3777) ORDER BY DESC is ignored in STRING_AGG function when DISTINCT is also specified
by Salvatore R (JIRA)
Salvatore R created TEIID-3777:
----------------------------------
Summary: ORDER BY DESC is ignored in STRING_AGG function when DISTINCT is also specified
Key: TEIID-3777
URL: https://issues.jboss.org/browse/TEIID-3777
Project: Teiid
Issue Type: Bug
Reporter: Salvatore R
Assignee: Steven Hawkins
When both DISTINCT and ORDER BY clause are specified in a STRING_AGG function, the result of the aggregate function is not correctly sorted.
For example, running the following query:
{code:sql}
select
string_agg(col1, ',' ORDER BY col1 DESC) as orderByDesc,
string_agg(col1, ',' ORDER BY col1 ASC) as orderByAsc,
string_agg(DISTINCT col1, ',' ORDER BY col1 DESC) as distinctOrderByDesc,
string_agg(DISTINCT col1, ',' ORDER BY col1 ASC) as distinctOrderByAsc
from (
SELECT 'b' as col1
UNION ALL
SELECT 'c' as col1
UNION ALL
SELECT 'a' as col1
UNION ALL
SELECT 'c' as col1
) x
{code}
the result is:
||orderByDesc||orderByAsc||distinctOrderByDesc||distinctOrderByAsc||
|c,c,b,a|a,b,c,c|a,b,c|a,b,c|
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3767) HBase - Phoenix does not support LIKE ESCAPE
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3767?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3767.
-----------------------------------
Resolution: Done
Added a source property - getRequiredLikeEscape. If the like push down has no escape or matches the required escape, then it's fine. We'll reject the pushdown otherwise - and latter as needed can refine the logic to swap to the required escape.
> HBase - Phoenix does not support LIKE ESCAPE
> --------------------------------------------
>
> Key: TEIID-3767
> URL: https://issues.jboss.org/browse/TEIID-3767
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7.1.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 8.12.1, 8.13
>
>
> Phoenix does not support LIKE ESCAPE. There is hardcoded escape character \.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3759) Missing Apache Olingo dependency in Teiid 8.12.0.Final
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3759?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-3759:
-------------------------------------
It about dependency management in maven. Since I first started developing the server side, I bought in the dependencies though "teiid-olingo" module, but the translator is in "teiid-odata4" module. The issue is "teiid-olingo" module is not part of the embedded kit, thus we are missing the jars. I will fix it.
> Missing Apache Olingo dependency in Teiid 8.12.0.Final
> ------------------------------------------------------
>
> Key: TEIID-3759
> URL: https://issues.jboss.org/browse/TEIID-3759
> Project: Teiid
> Issue Type: Enhancement
> Components: Embedded, OData
> Affects Versions: 8.12
> Reporter: Gary Gregory
> Assignee: Ramesh Reddy
>
> I think Teiid might have a missing Apache Olingo dependency:
> {noformat}
> java.lang.NoClassDefFoundError: org/apache/olingo/commons/api/edm/provider/CsdlBindingTarget
> at org.teiid.translator.odata4.ODataExecutionFactory.getMetadataProcessor(ODataExecutionFactory.java:140)
> at org.teiid.deployers.TranslatorUtil.buildTranslatorProperties(TranslatorUtil.java:280)
> at org.teiid.deployers.TranslatorUtil.buildTranslatorMetadata(TranslatorUtil.java:252)
> ...
> Caused by: java.lang.ClassNotFoundException: org.apache.olingo.commons.api.edm.provider.CsdlBindingTarget
> at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
> at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
> at java.security.AccessController.doPrivileged(Native Method)
> at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
> at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
> ... 35 more
> {noformat}
> There is no Apache Olingo in {{optional/odata}} or {{lib/optional/odata4}}.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3756) Order By clause specified in STRING_AGG function is skipped when pushed down to PostgreSQL
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3756?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3756:
---------------------------------------
Can you open another issue to cover what you are seeing?
> Order By clause specified in STRING_AGG function is skipped when pushed down to PostgreSQL
> ------------------------------------------------------------------------------------------
>
> Key: TEIID-3756
> URL: https://issues.jboss.org/browse/TEIID-3756
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.4
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.12, 8.11.5
>
>
> I defined a small table in PostgreSQL as follows:
> {code:sql}
> create table test_string_agg (col1 varchar(10))
> insert into test_string_agg VALUES ('b');
> insert into test_string_agg VALUES ('a');
> insert into test_string_agg VALUES ('c');
> {code}
> When I run a query with a STRING_AGG function where an ORDER BY clause is specified, the strings are not concatenated in the expected order. For example, these three queries:
> {code:sql}
> select string_agg(col1, ' , ' order by col1) from pg.test_string_agg;
> select string_agg(col1, ' , ' order by col1 desc) from pg.test_string_agg;
> select string_agg(col1, ' , ' order by col1 asc) from pg.test_string_agg;
> {code}
> return all the same result "b , a , c".
> It seems that the ORDER BY clause is not pushed down to PostgreSQL, in fact all the three rewritten queries executed in PostgreSQL look like:
> {code:sql}
> SELECT STRING_AGG(g_0."col1", ' , ') FROM "public"."test_string_agg" AS g_0
> {code}
> If the STRING_AGG function is not pushed down (e.g. in MySQL), I get an expected result.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3773) teiid-oauth-util.sh not work for all rest oauth 2 authentication
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3773?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-3773:
-------------------------------------
It would be better if you mention what did not work? This tool is designed especially for "Access Code" based OAuth2 flow. This has been tested with SalesForce and Twitter. I will see if I can also validate with FaceBook and LinkedIn.
> teiid-oauth-util.sh not work for all rest oauth 2 authentication
> ----------------------------------------------------------------
>
> Key: TEIID-3773
> URL: https://issues.jboss.org/browse/TEIID-3773
> Project: Teiid
> Issue Type: Quality Risk
> Components: Misc. Connectors
> Affects Versions: 8.13
> Reporter: Kylin Soong
> Assignee: Steven Hawkins
>
> I have tested teiid-oauth-util.sh against one of China's social media(Weibo), it not works, probably this tool need more test before go project.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3756) Order By clause specified in STRING_AGG function is skipped when pushed down to PostgreSQL
by Salvatore R (JIRA)
[ https://issues.jboss.org/browse/TEIID-3756?page=com.atlassian.jira.plugin... ]
Salvatore R commented on TEIID-3756:
------------------------------------
Thanks Steven for fixing this issue.
Even if not strictly related to this issue because it happens only if the STRING_AGG function is not pushed down, I am experiencing a similar problem when DISTINCT and ORDER BY clauses are both specified in the aggregate function.
For example, the following queries:
{code:sql}
SELECT string_agg(DISTINCT col1, ',' ORDER BY col1 DESC) from my.test_string_agg;
SELECT string_agg(DISTINCT col1, ',' ORDER BY col1 ASC) from my.test_string_agg;
{code}
return the same result "a,b,c" if the function is not pushed down and locally processed in Teiid.
> Order By clause specified in STRING_AGG function is skipped when pushed down to PostgreSQL
> ------------------------------------------------------------------------------------------
>
> Key: TEIID-3756
> URL: https://issues.jboss.org/browse/TEIID-3756
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.4
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.12, 8.11.5
>
>
> I defined a small table in PostgreSQL as follows:
> {code:sql}
> create table test_string_agg (col1 varchar(10))
> insert into test_string_agg VALUES ('b');
> insert into test_string_agg VALUES ('a');
> insert into test_string_agg VALUES ('c');
> {code}
> When I run a query with a STRING_AGG function where an ORDER BY clause is specified, the strings are not concatenated in the expected order. For example, these three queries:
> {code:sql}
> select string_agg(col1, ' , ' order by col1) from pg.test_string_agg;
> select string_agg(col1, ' , ' order by col1 desc) from pg.test_string_agg;
> select string_agg(col1, ' , ' order by col1 asc) from pg.test_string_agg;
> {code}
> return all the same result "b , a , c".
> It seems that the ORDER BY clause is not pushed down to PostgreSQL, in fact all the three rewritten queries executed in PostgreSQL look like:
> {code:sql}
> SELECT STRING_AGG(g_0."col1", ' , ') FROM "public"."test_string_agg" AS g_0
> {code}
> If the STRING_AGG function is not pushed down (e.g. in MySQL), I get an expected result.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3776) Teiid throws NPE if query contains AVG function on varchar type
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3776?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3776.
-----------------------------------
Resolution: Done
Updating the resolving logic to account more for null type values and added a general validation to ensure all non-matching aggregate scenarios are caught.
> Teiid throws NPE if query contains AVG function on varchar type
> ---------------------------------------------------------------
>
> Key: TEIID-3776
> URL: https://issues.jboss.org/browse/TEIID-3776
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7.1.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 8.12.1, 8.13
>
>
> Execution of this query ends with NPE in Teiid:
> SELECT stringnum FROM bqt1.smalla GROUP BY stringnum HAVING avg(stringnum) = '1'
> Expected result:
> Exception similar to - org.teiid.api.exception.query.QueryValidatorException: TEIID30492 The aggregate function AVG cannot be used with non-numeric expressions: AVG(BQT1.SmallA.StringNum)
> Actual result:
> {code:text}
> 12:27:32,829 DEBUG [org.teiid.TRANSPORT] (New I/O worker #7) processing message:MessageHolder: key=619 contents=Invoke interface org.teiid.client.DQP.executeRequest
> 12:27:32,830 DEBUG [org.teiid.PROCESSOR] (Worker52_QueryProcessorQueue378) Request Thread r77C2X0C5Gwa.10 with state NEW
> 12:27:32,830 DEBUG [org.teiid.PROCESSOR] (Worker52_QueryProcessorQueue378) r77C2X0C5Gwa.10 Non-cachable command.
> 12:27:32,830 DEBUG [org.teiid.PROCESSOR] (Worker52_QueryProcessorQueue378) r77C2X0C5Gwa.10 executing SELECT stringnum FROM bqt1.smalla GROUP BY stringnum HAVING avg(stringnum) = '1'
> 12:27:32,831 ERROR [org.teiid.PROCESSOR] (Worker52_QueryProcessorQueue378) TEIID30019 Unexpected exception for request r77C2X0C5Gwa.10: java.lang.NullPointerException
> at org.teiid.query.resolver.util.ResolverVisitor.resolveCompareCriteria(ResolverVisitor.java:767) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.resolver.util.ResolverVisitor.visit(ResolverVisitor.java:248) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.sql.lang.CompareCriteria.acceptVisitor(CompareCriteria.java:126) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:54) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:61) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.resolver.command.SimpleQueryResolver$QueryResolverVisitor.postVisitVisitor(SimpleQueryResolver.java:197) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:135) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.sql.lang.CompareCriteria.acceptVisitor(CompareCriteria.java:126) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.resolver.command.SimpleQueryResolver$QueryResolverVisitor.visit(SimpleQueryResolver.java:214) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:69) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:271) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:369) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:435) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:613) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:315) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.1.6_2-redhat-6.jar:8.7.1.6_2-redhat-6]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_40]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_40]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_40]
> 12:27:32,832 DEBUG [org.teiid.PROCESSOR] (Worker52_QueryProcessorQueue378) java.lang.NullPointerException Sending error to client r77C2X0C5Gwa.10
> 12:27:32,833 DEBUG [org.teiid.TRANSPORT] (Worker52_QueryProcessorQueue378) send message: MessageHolder: key=619 contents=ResultsMessage rowCount=0 finalRow=-1
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months