[JBoss JIRA] (TEIID-5285) Add high-level feature for redirection of updates
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5285?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5285:
-------------------------------------
I wanted to check at least some basic unique, constraint checking checks, as we are trying to represent the source tables. Trying to mimic more than no constraints at all, as that may come as surprise to the user and may leave inconsistent data otherwise. I will correct other issues. I purposefully omitted the "account" view from the example for clarity of text, yes it will be there.
> Add high-level feature for redirection of updates
> -------------------------------------------------
>
> Key: TEIID-5285
> URL: https://issues.jboss.org/browse/TEIID-5285
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine, Teiid Spring Boot
> Reporter: Steven Hawkins
> Assignee: Ramesh Reddy
> Fix For: 11.x
>
> Attachments: redirection-vdb.xml
>
>
> In microservices testing it is desirable to test against production/live data but not commit any updates. We should offer a simple solution that can defined by extension metadata and enabled/disabled by a feature flag. We may need to make simplifying assumptions about the scope (per session, per application, etc.) and durability of the updates.
> Under the covers this will be achieved by using views, update triggers, and a store for the updates and when not enabled the expectation is that all operations should pass through. However the application will be limited to using Teiid SQL and will be required to use the Teiid or pg driver, or Teiid spring boot.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 7 months
[JBoss JIRA] (TEIID-5380) Disallow the default option for a function parameter
by Jan Stastny (JIRA)
Jan Stastny created TEIID-5380:
----------------------------------
Summary: Disallow the default option for a function parameter
Key: TEIID-5380
URL: https://issues.jboss.org/browse/TEIID-5380
Project: Teiid
Issue Type: Bug
Reporter: Jan Stastny
Assignee: Steven Hawkins
Currently there's no support in Teiid for default values for parameters of a Function.
But [Function element in BNF for SQL Grammar|https://teiid.gitbooks.io/documents/content/reference/BNF_for_SQL_Grammar.html#createDDLProcedure] shares the definition with Procedure, which permits to deploy such a VDB, where there are DEFAULT values defined on a Function parameter.
So a VDB with following DDL shouldn't deploy successfully:
{code:sql}
CREATE VIRTUAL FUNCTION f1(e2 string NOT NULL DEFAULT 'default') RETURNS string AS
BEGIN
RETURN e2;
END;
{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 7 months
[JBoss JIRA] (TEIID-5375) limit pushing should be considered prior to dependent join planning
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5375?page=com.atlassian.jira.plugin... ]
Steven Hawkins reassigned TEIID-5375:
-------------------------------------
Fix Version/s: 11.x
(was: 11.0)
Priority: Minor (was: Major)
Assignee: (was: Steven Hawkins)
An example query:
select pm1.g1.e1, pm2.g1.e2 from pm1.g1 left outer join pm2.g1 on pm1.g1.e1 = pm2.g1.e1 limit 10
There isn't a great way to do this with the current logic as rule push limit will move the limit nodes into locations in the plan where they are not expected by other rules. The other approach is to attempt to adjust the costing logic to look up in the tree, but that would largely need to duplicate the checks in rule push limit. Moving out of 11.0.
> limit pushing should be considered prior to dependent join planning
> -------------------------------------------------------------------
>
> Key: TEIID-5375
> URL: https://issues.jboss.org/browse/TEIID-5375
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Reporter: Steven Hawkins
> Priority: Minor
> Fix For: 11.x
>
>
> In some situations such as a limit over a left outer join, the effect of the limit should be considered in the costing of the left hand side of the join - but is currently not since rule push limits is fired after rule choose dependent.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 7 months
[JBoss JIRA] (TEIID-5285) Add high-level feature for redirection of updates
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5285?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5285:
---------------------------------------
In general implementing the constraint handling will get tricky. You have to consider how far it makes sense to go with it - enforce unique, check constraints, cascading of deletes, etc.
> CREATE TRIGGER ON customer INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC DECLARE string VARIABLES.X = (SELECT SSN FROM master.CUSTOMER WHERE SSN = NEW.SSN); IF (VARIABLES.X IS NULL) BEGIN INSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (NEW.SSN, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PHONE, 1); END END;
There would be an else case that throws an exception if the key already exists.
> CREATE TRIGGER ON customer INSTEAD OF DELETE AS FOR EACH ROW BEGIN ATOMIC DECLARE boolean VARIABLES.X = (SELECT x.return FROM (EXECUTE CHECK_ACCOUNTS_CUSTOMER_DELETE(OLD.SSN)) AS x); IF (VARIABLES.X) BEGIN UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (OLD.SSN, OLD.FIRSTNAME, OLD.LASTNAME, OLD.PHONE, 3); END ELSE BEGIN RAISE SQLEXCEPTION 'constaint violation with Account table'; END END;
> CREATE VIRTUAL PROCEDURE CHECK_ACCOUNTS_CUSTOMER_DELETE(ssn_value string) RETURNS boolean AS BEGIN DECLARE string VARIABLES.X = (SELECT SSN FROM master.ACCOUNT_MODIFIED WHERE SSN = ssn_value AND STATUS <> 3); DECLARE string VARIABLES.Y = (SELECT SSN FROM master.ACCOUNT WHERE SSN = ssn_value); IF (VARIABLES.X IS NULL AND VARIABLES.Y IS NULL) BEGIN RETURN cast('TRUE'AS boolean); END RETURN cast('FALSE'AS boolean);
You should probably have a view for account as well, and just query it here. Issuing these separate queries won't quite be correct as an account could already have been deleted and will still be detected in the second query.
> RETURN cast('TRUE'AS boolean); END RETURN cast('FALSE'AS boolean);
You can just use TRUE and FALSE keywords :)
> Add high-level feature for redirection of updates
> -------------------------------------------------
>
> Key: TEIID-5285
> URL: https://issues.jboss.org/browse/TEIID-5285
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine, Teiid Spring Boot
> Reporter: Steven Hawkins
> Assignee: Ramesh Reddy
> Fix For: 11.x
>
> Attachments: redirection-vdb.xml
>
>
> In microservices testing it is desirable to test against production/live data but not commit any updates. We should offer a simple solution that can defined by extension metadata and enabled/disabled by a feature flag. We may need to make simplifying assumptions about the scope (per session, per application, etc.) and durability of the updates.
> Under the covers this will be achieved by using views, update triggers, and a store for the updates and when not enabled the expectation is that all operations should pass through. However the application will be limited to using Teiid SQL and will be required to use the Teiid or pg driver, or Teiid spring boot.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 7 months
[JBoss JIRA] (TEIID-5376) Timezone handling discrepancies in MongoDB
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5376?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5376.
-----------------------------------
Resolution: Rejected
Since Teiid only supports timestamp without timezone this is the expected behavior. If UTC values are expected through-out, the server timezone must be set to UTC.
> Timezone handling discrepancies in MongoDB
> ------------------------------------------
>
> Key: TEIID-5376
> URL: https://issues.jboss.org/browse/TEIID-5376
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Reporter: Jan Martiska
> Assignee: Steven Hawkins
>
> Suppose this query against a MongoDB-backed VDB and you're running it in timezone UTC+1:
> {noformat}
> SELECT timestampvalue, HOUR(timestampvalue) AS TheHour FROM BQT1.SmallA WHERE (HOUR(timestampvalue) = 16);
> {noformat}
> If the database contains a timestamp '2000-01-01 16:00:00', you will get this row as result:
> ||timestampvalue||TheHour||
> |2000-01-01 17:00:00|16|
> Looks like this is because the {{timestampvalue}} column is fully evaluated by Teiid and converted into the user's timezone, but the {{hour()}} call is pushed and evaluated by MongoDB and therefore evaluated as UTC and is not converted by Teiid to user's timezone.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 7 months
[JBoss JIRA] (TEIID-3750) Allow the parsing of an expression
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3750?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3750:
---------------------------------------
> (2). So only BNF is more relaxed, than the current support is. That's alright I guess. (Even though it would be probably better for the VDB not to deploy at all.)
Yes the BNF is effectively shared with only a few checks to differentiate. Please log an issue to disallow the default option for a function parameter.
> Allow the parsing of an expression
> ----------------------------------
>
> Key: TEIID-3750
> URL: https://issues.jboss.org/browse/TEIID-3750
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.14.6_4
>
>
> Now that we have an extension property, we could allow an expression to be used in parsing rather than just a string.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 7 months
[JBoss JIRA] (TEIID-5379) LIMIT a OFFSET b
by Lukas Eder (JIRA)
[ https://issues.jboss.org/browse/TEIID-5379?page=com.atlassian.jira.plugin... ]
Lukas Eder commented on TEIID-5379:
-----------------------------------
Do note that some databases support both LIMIT a OFFSET b and OFFSET b LIMIT a
> LIMIT a OFFSET b
> ----------------
>
> Key: TEIID-5379
> URL: https://issues.jboss.org/browse/TEIID-5379
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Lukáš Svačina
> Assignee: Steven Hawkins
> Priority: Minor
> Fix For: 11.0
>
>
> Tradition syntax for limiting result set in teiid sql dialect is not supported.
> *select * from X limit A offset B;*
> It is a problem for SQL builders like JOOQ to work properly with TEIID syntax. I need to build SQL queries by some builder, but i didn't find anything suitable for TEIID SQL syntax.
> JOOQ works quite good with DEFAULT dialect except for limit/offset syntax.
> Are there any other ways how to build syntax correct TEIID SQL queries?
> Please add this syntax support. More info here: https://groups.google.com/forum/#!topic/jooq-user/M1X6_I2JLpg
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 7 months
[JBoss JIRA] (TEIID-5378) NPE when inserting into Global Temporary Table from a function or procedure
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5378?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5378.
-----------------------------------
Fix Version/s: 11.0
10.3.2
10.2.3
Resolution: Done
The logic to get the temp table was only using the session store for session level queries - and instead accidentally using the procedure scoped temp store otherwise. Now we'll use the correct temp store.
> NPE when inserting into Global Temporary Table from a function or procedure
> ---------------------------------------------------------------------------
>
> Key: TEIID-5378
> URL: https://issues.jboss.org/browse/TEIID-5378
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0, 10.3.2, 10.2.3
>
>
> For following vdb:
> {code:xml}
> <vdb name="a" version="1">
> <model name="vm1" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE GLOBAL TEMPORARY TABLE teiidtemp(val integer) OPTIONS (UPDATABLE 'TRUE');
> CREATE VIRTUAL FUNCTION f1() RETURNS string AS
> BEGIN
> INSERT INTO teiidtemp(val) VALUES (1);
> DECLARE string v1 = SELECT 'default'||COUNT(val) FROM teiidtemp;
> RETURN v1;
> END;
> CREATE VIRTUAL PROCEDURE p1() RETURNS (v1 string) AS
> BEGIN
> INSERT INTO teiidtemp(val) VALUES (1);
> SELECT 'default'||COUNT(val) FROM teiidtemp;
> END;
> ]]>
> </metadata>
> </model>
> </vdb>
> {code}
> The following errors occur when invoking procedure or function:
> # {code:sql}SELECT f1(){code}
> #* 15:49:17,608 WARN [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue14) TEIID30020 Processing exception for request 3rGTFUcDmmRP.0 'TEIID30328 Unable to evaluate f1(): TEIID30167 java.lang.NullPointerException'. Originally ExpressionEvaluationException TempTableDataManager.java:227. Enable more detailed logging to see the entire stacktrace.
> # {code:sql}SELECT a.v1 FROM (CALL p1()) a{code}
> #* 15:49:59,581 WARN [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue15) TEIID30020 Processing exception for request 3rGTFUcDmmRP.1 'TEIID30167 java.lang.NullPointerException'. Originally ProcedureErrorInstructionException TempTableDataManager.java:227. Enable more detailed logging to see the entire stacktrace.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 7 months
[JBoss JIRA] (TEIID-5379) LIMIT a OFFSET b
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5379?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5379:
---------------------------------------
Teiid supports both:
LIMIT a, b
OFFSET a ROWS FETCH NEXT b ROWS ONLY
The latter form is the ANSI sql standard.
It should be possible though to add support for an explicit offset:
LIMIT b OFFSET a
> LIMIT a OFFSET b
> ----------------
>
> Key: TEIID-5379
> URL: https://issues.jboss.org/browse/TEIID-5379
> Project: Teiid
> Issue Type: Bug
> Reporter: Lukáš Svačina
> Assignee: Steven Hawkins
> Priority: Minor
> Fix For: 11.0
>
>
> Tradition syntax for limiting result set in teiid sql dialect is not supported.
> *select * from X limit A offset B;*
> It is a problem for SQL builders like JOOQ to work properly with TEIID syntax. I need to build SQL queries by some builder, but i didn't find anything suitable for TEIID SQL syntax.
> JOOQ works quite good with DEFAULT dialect except for limit/offset syntax.
> Are there any other ways how to build syntax correct TEIID SQL queries?
> Please add this syntax support. More info here: https://groups.google.com/forum/#!topic/jooq-user/M1X6_I2JLpg
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 7 months