[JBoss JIRA] (TEIID-4245) Add support for fetch syntax "FETCH cursorname"
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4245?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4245.
-----------------------------------
Resolution: Done
Added support for just fetch cursor name and added better handling for exceptions in the odbc layer (such as parsing errors with the regex). Will work the unquoted cursor names as part of TEIID-4238
> Add support for fetch syntax "FETCH cursorname"
> -----------------------------------------------
>
> Key: TEIID-4245
> URL: https://issues.jboss.org/browse/TEIID-4245
> Project: Teiid
> Issue Type: Feature Request
> Components: ODBC
> Reporter: Marc Shirley
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> Executing syntax "FETCH cursorname" results in parsing error like [1]. Instead, syntax like "FETCH 1 IN cursorname" has been used as a workaround.
> [1]
> org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]FETCH[*] mycursor" at line 1, column 1.
> Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months
[JBoss JIRA] (TEIID-4238) Remove need for quotes around cursor name declarations/fetches
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4238?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4238:
---------------------------------------
> working as expected when executing directly against PostgreSQL servers through the same PostgreSQL ODBC driver
This is expected as they are using the compiled postgres sql and not the declare/fetch feature of the odbc driver.
> Remove need for quotes around cursor name declarations/fetches
> --------------------------------------------------------------
>
> Key: TEIID-4238
> URL: https://issues.jboss.org/browse/TEIID-4238
> Project: Teiid
> Issue Type: Enhancement
> Components: ODBC
> Reporter: Marc Shirley
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> Attempting to declare a cursor without quotes results in an error similar to[1]. The quotes should be optional except in special cases, as this seems to match later standards (SQL2003). Performing fetch operations against the cursor have the same issue, where if the cursorname is not quoted, parsing errors occur.
> [1] Caused by: org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]DECLARE[*] cursorname CURSOR" at line 1, column 1.
> Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months
[JBoss JIRA] (TEIID-4238) Remove need for quotes around cursor name declarations/fetches
by Marc Shirley (JIRA)
[ https://issues.jboss.org/browse/TEIID-4238?page=com.atlassian.jira.plugin... ]
Marc Shirley commented on TEIID-4238:
-------------------------------------
To expand, these are applications coded in 4GL and compiled to C that are reported working as expected when executing directly against PostgreSQL servers through the same PostgreSQL ODBC driver..
> Remove need for quotes around cursor name declarations/fetches
> --------------------------------------------------------------
>
> Key: TEIID-4238
> URL: https://issues.jboss.org/browse/TEIID-4238
> Project: Teiid
> Issue Type: Enhancement
> Components: ODBC
> Reporter: Marc Shirley
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> Attempting to declare a cursor without quotes results in an error similar to[1]. The quotes should be optional except in special cases, as this seems to match later standards (SQL2003). Performing fetch operations against the cursor have the same issue, where if the cursorname is not quoted, parsing errors occur.
> [1] Caused by: org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]DECLARE[*] cursorname CURSOR" at line 1, column 1.
> Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months
[JBoss JIRA] (TEIID-4090) Issue with entitysets/properties ending in auth or token
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4090?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4090:
---------------------------------------
That would be good, but we may need to also exclude things like pom.xml as well.
> Issue with entitysets/properties ending in auth or token
> --------------------------------------------------------
>
> Key: TEIID-4090
> URL: https://issues.jboss.org/browse/TEIID-4090
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.12
> Reporter: Steven Hawkins
> Assignee: Ramesh Reddy
> Fix For: 9.0, 8.12.5
>
>
> To handle oauth, we are check for uri.endsWith("auth") || uri.endsWith("token") - however valid uris can end with those characters as well. An ioexception will be thrown instead of seeing the expected results - we also need to not simply throw an ioexception in ODataServlet as there is no server log of the exception and the client sees a 500 error.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months
[JBoss JIRA] (TEIID-4259) "TEIID30259 Cannot introduce new expressions" when using formattimestamp
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4259?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4259.
-----------------------------------
Resolution: Out of Date
I can reproduce something similar on 8.11.3, but it has been corrected already on later branches - TEIID-3681
> "TEIID30259 Cannot introduce new expressions" when using formattimestamp
> ------------------------------------------------------------------------
>
> Key: TEIID-4259
> URL: https://issues.jboss.org/browse/TEIID-4259
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.11.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
>
> A certain query executes fine when using the normal timestamp column (w.valuetime). When this is changed to formattimestamp(w.valuetime, 'dd/MM/yyyy HH:mm') we get an exception complaining about a case statement that is part of the select a few columns after the particular valuetime column.
> 14:00:00,055 WARN [org.teiid.PROCESSOR] (Worker17615_QueryProcessorQueue95374) 7OLMH/JCvk3W TEIID30020 Processing exception for request 7OLMH/JCvk3W.48 'TEIID30259 Cannot introduce new expressions [CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END] in duplicate removal.'. Originally QueryPlannerException RuleAssignOutputElements.java:407. Enable more detailed logging to see the entire stacktrace.
> This was the query that caused the problem:
> select w.patientid as Patientid,
> formattimestamp(w.valuetime,'yyyy-MM-dd HH:mm') as t2,
> w.valuetime as Tijdstip,
> max(w.bed) as Bed, max(w.Naam) as Naam,
> case when convert(max(w.opname), date) = timestampadd(SQL_TSI_DAY, -1, convert(now() , date)) then 'Gisteren'
> else
> case when convert(max(w.opname), date) = convert(now(), date) then 'Vannacht' else
> '' end end as Opname,
> case when count(w.MondZorgAantal) is null then 'Niet gemeten' else 'OK' end as Comfort,
> case when count(w.EvaluatieMond) > 0 then 'OK' else 'Ontbreekt' end as NRS
> from
> (
> select pi.patientid as Patientid, null as ValueTime, pi.bedabbr as Bed, concat(pi.PatientLastName, concat(', ',pi.PatientFirstName)) as Naam, pi.AdmTime as opname, null as MondZorgAantal, null as EvaluatieMond, patientStatusID
> from prod_PV_PatientInfo pi
> where pi.PatientStatusID = 1 and PatGroupAbbr like 'PICU%' and cast(pi.admTime as date) <> cast(now() as date)
> union
> select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname,
> v.c as MondZorgAantal, null as EvaluatieMond, v.PatientStatusID from
> (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID
> from prod_PV_DerivedVariables obs1
> join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) <> cast(now() as date) and PatGroupAbbr like 'PICU%'
> where obs1.variableid = 30010003
> and cast(valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date))
> group by obs1.patientid, obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v
> union
> select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname, null as MondZorgAantal, v.c as EvaluatieMond, v.PatientStatusID from
> (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID
> from prod_PV_ObservationVariables obs1
> join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) < cast(now() as date) and PatGroupAbbr like 'PICU%'
> where obs1.variableid = 15005534
> and cast(obs1.valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date))
> group by obs1.patientid, obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v
> ) w
> group by w.patientid, w.ValueTime
> order by max(w.bed)
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months
[JBoss JIRA] (TEIID-4090) Issue with entitysets/properties ending in auth or token
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4090?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4090:
-------------------------------------
Yes, but these are kind of fluid as some of them come from OData spec. Would prefer to keep generic to avoid forgetting to add something. May be just restrict to *.xml files?
> Issue with entitysets/properties ending in auth or token
> --------------------------------------------------------
>
> Key: TEIID-4090
> URL: https://issues.jboss.org/browse/TEIID-4090
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.12
> Reporter: Steven Hawkins
> Assignee: Ramesh Reddy
> Fix For: 9.0, 8.12.5
>
>
> To handle oauth, we are check for uri.endsWith("auth") || uri.endsWith("token") - however valid uris can end with those characters as well. An ioexception will be thrown instead of seeing the expected results - we also need to not simply throw an ioexception in ODataServlet as there is no server log of the exception and the client sees a 500 error.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months
[JBoss JIRA] (TEIID-4090) Issue with entitysets/properties ending in auth or token
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4090?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4090:
---------------------------------------
So you don't know in advance what resources could be accessed? Is there at least a subset that is known and then require the web.xml to define others?
> Issue with entitysets/properties ending in auth or token
> --------------------------------------------------------
>
> Key: TEIID-4090
> URL: https://issues.jboss.org/browse/TEIID-4090
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.12
> Reporter: Steven Hawkins
> Assignee: Ramesh Reddy
> Fix For: 9.0, 8.12.5
>
>
> To handle oauth, we are check for uri.endsWith("auth") || uri.endsWith("token") - however valid uris can end with those characters as well. An ioexception will be thrown instead of seeing the expected results - we also need to not simply throw an ioexception in ODataServlet as there is no server log of the exception and the client sees a 500 error.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months
[JBoss JIRA] (TEIID-4227) Impala Translator - Support Multiple Count Distinct with Group By
by Scott Wallace (JIRA)
[ https://issues.jboss.org/browse/TEIID-4227?page=com.atlassian.jira.plugin... ]
Scott Wallace edited comment on TEIID-4227 at 6/10/16 9:22 AM:
---------------------------------------------------------------
[~shawkins] my question wasn't phrased correctly. What I meant was will pushdown be ignored only in cases where multiple count distincts are being requested? I'm just confirming that pushdown occurs for all other cases currently handled by the translator and this is a one off to let Teiid handle the multiple count distincts. If that is the case, then I think it would be a great solution until Impala solves the multiple count distinct issue. If you're able to modify the translator, we could definitely test it out. Thanks again!
was (Author: walla2sl):
[~shawkins] my question wasn't phrased correctly. What I meant was will pushdown be ignored only in cases where multiple count distincts are being requested. I'm just confirming that pushdown occurs for all other cases currently handled by the translator and this is a one off to let Teiid handle the multiple count distincts. If that is the case, then I think it would be a great solution until Impala solves the multiple count distinct issue. If you're able to modify the translator, we could definitely test it out. Thanks again!
> Impala Translator - Support Multiple Count Distinct with Group By
> -----------------------------------------------------------------
>
> Key: TEIID-4227
> URL: https://issues.jboss.org/browse/TEIID-4227
> Project: Teiid
> Issue Type: Feature Request
> Components: Misc. Connectors
> Affects Versions: 8.13.3
> Reporter: Scott Wallace
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> This is somewhat related to TEIID-3743, which added support of multiple count distinct metrics. We have a requirement to allow multiple count distinct metrics grouped by common attributes.
> Currently, we get a failure as follows:
> {noformat}org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 vw_impression_click_transaction_process_date_detail: 500051 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.process_date_key, SUM(g_0.num_clicks), SUM(g_0.num_impressions), COUNT(DISTINCT g_0.orderid), COUNT(DISTINCT g_0.`hash_tid`), SUM(g_0.sales) FROM detail.vw_impression_click_transaction_process_date_detail g_0 WHERE g_0.process_date_key >= '2016-05-22' AND g_0.process_date_key < '2016-05-23' AND g_0.advertiser_key = 12345 GROUP BY g_0.process_date_key]
> {noformat}
> To allow this request to succeed, we could have translator write that Impala query as:
> {noformat}
> select nvl(v1.c1,v2.c1), nvl(v1.c2,v1.c2), nvl(v1.c3,v1.c3), v1.c4, v2.c5, nvl(v1.c6,v1.c6) from (
> SELECT g_0.process_date_key as c1,
> SUM(g_0.num_clicks) as c2,
> SUM(g_0.num_impressions) as c3,
> COUNT(DISTINCT g_0.orderid) as c4,
> SUM(g_0.sales) as c6
> FROM detail.vw_impression_click_transaction_process_date_detail g_0
> WHERE g_0.process_date_key >= '2016-05-22'
> AND g_0.process_date_key < '2016-05-23'
> AND g_0.advertiser_key = 12345
> GROUP BY g_0.process_date_key
> ) v1 inner join
> (
> SELECT g_0.process_date_key as c1,
> SUM(g_0.num_clicks) as c2,
> SUM(g_0.num_impressions) as c3,
> COUNT(DISTINCT g_0.`hash_tid`) as c5,
> SUM(g_0.sales) as c6
> FROM detail.vw_impression_click_transaction_process_date_detail g_0
> WHERE g_0.process_date_key >= '2016-05-22'
> AND g_0.process_date_key < '2016-05-23'
> AND g_0.advertiser_key = 12345
> GROUP BY g_0.process_date_key
> ) v2 on v1.c1 = v2.c1;
> {noformat}
> FYI [~shawkins]
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months
[JBoss JIRA] (TEIID-4227) Impala Translator - Support Multiple Count Distinct with Group By
by Scott Wallace (JIRA)
[ https://issues.jboss.org/browse/TEIID-4227?page=com.atlassian.jira.plugin... ]
Scott Wallace commented on TEIID-4227:
--------------------------------------
[~shawkins] my question wasn't phrased correctly. What I meant was will pushdown be ignored only in cases where multiple count distincts are being requested. I'm just confirming that pushdown occurs for all other cases currently handled by the translator and this is a one off to let Teiid handle the multiple count distincts. If that is the case, then I think it would be a great solution until Impala solves the multiple count distinct issue. If you're able to modify the translator, we could definitely test it out. Thanks again!
> Impala Translator - Support Multiple Count Distinct with Group By
> -----------------------------------------------------------------
>
> Key: TEIID-4227
> URL: https://issues.jboss.org/browse/TEIID-4227
> Project: Teiid
> Issue Type: Feature Request
> Components: Misc. Connectors
> Affects Versions: 8.13.3
> Reporter: Scott Wallace
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> This is somewhat related to TEIID-3743, which added support of multiple count distinct metrics. We have a requirement to allow multiple count distinct metrics grouped by common attributes.
> Currently, we get a failure as follows:
> {noformat}org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 vw_impression_click_transaction_process_date_detail: 500051 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.process_date_key, SUM(g_0.num_clicks), SUM(g_0.num_impressions), COUNT(DISTINCT g_0.orderid), COUNT(DISTINCT g_0.`hash_tid`), SUM(g_0.sales) FROM detail.vw_impression_click_transaction_process_date_detail g_0 WHERE g_0.process_date_key >= '2016-05-22' AND g_0.process_date_key < '2016-05-23' AND g_0.advertiser_key = 12345 GROUP BY g_0.process_date_key]
> {noformat}
> To allow this request to succeed, we could have translator write that Impala query as:
> {noformat}
> select nvl(v1.c1,v2.c1), nvl(v1.c2,v1.c2), nvl(v1.c3,v1.c3), v1.c4, v2.c5, nvl(v1.c6,v1.c6) from (
> SELECT g_0.process_date_key as c1,
> SUM(g_0.num_clicks) as c2,
> SUM(g_0.num_impressions) as c3,
> COUNT(DISTINCT g_0.orderid) as c4,
> SUM(g_0.sales) as c6
> FROM detail.vw_impression_click_transaction_process_date_detail g_0
> WHERE g_0.process_date_key >= '2016-05-22'
> AND g_0.process_date_key < '2016-05-23'
> AND g_0.advertiser_key = 12345
> GROUP BY g_0.process_date_key
> ) v1 inner join
> (
> SELECT g_0.process_date_key as c1,
> SUM(g_0.num_clicks) as c2,
> SUM(g_0.num_impressions) as c3,
> COUNT(DISTINCT g_0.`hash_tid`) as c5,
> SUM(g_0.sales) as c6
> FROM detail.vw_impression_click_transaction_process_date_detail g_0
> WHERE g_0.process_date_key >= '2016-05-22'
> AND g_0.process_date_key < '2016-05-23'
> AND g_0.advertiser_key = 12345
> GROUP BY g_0.process_date_key
> ) v2 on v1.c1 = v2.c1;
> {noformat}
> FYI [~shawkins]
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months