[JBoss JIRA] (TEIID-3998) EDS 5.3 jdbc driver virtual procedure call against JDV 6.2.3
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3998?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3998:
------------------------------------------------
David Le Sage <dlesage(a)redhat.com> changed the Status of [bug 1311213|https://bugzilla.redhat.com/show_bug.cgi?id=1311213] from VERIFIED to CLOSED
> EDS 5.3 jdbc driver virtual procedure call against JDV 6.2.3
> ------------------------------------------------------------
>
> Key: TEIID-3998
> URL: https://issues.jboss.org/browse/TEIID-3998
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.12
> Environment: eds-5.3.1_1_2015-jdbc
> Reporter: Filip Elias
> Assignee: Steven Hawkins
> Attachments: server1.log
>
>
> We identified an issue when trying to connect with EDS 5.3 jdbc driver to JDV 6.2.3 instance.
> The problem is caused by virtual procedure execution.
> Query performed:
> EXEC View.hiddenUpdate(456,'changed')
> View definition:
> {code}
> <model name="View" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE VIRTUAL PROCEDURE hiddenUpdate(ID integer,CUSTOMERNAME string) AS BEGIN
> MERGE INTO Customers(id,customername) VALUES(ID,CUSTOMERNAME);
> END
> ]]> </metadata>
> </model>
> {code}
> NPE excepton:
> {code}
> Exception in thread "main" java.lang.NullPointerException
> at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:342)
> at jdbcdriver.JDBCClient.executeUpdate(JDBCClient.java:59)
> at jdbcdriver.JDBCClient.main(JDBCClient.java:52)
> {code}
> Client code:
> {code}
> @Test
> public void procedureInvalidateCacheTest(){
> String sqlSelectAll = "/*+ cache */SELECT * FROM Customers;";
> try{
>
> Utils.executeUpdate(con,"INSERT INTO Customers(id,customername) VALUES (456, 'Testing 1');");
>
> ResultSet empty = Utils.executeQuery(con, sqlSelectAll);
> Assert.assertTrue(empty.next(), "ResultSet should be empty");
>
> try{
> Thread.sleep(3000);
> }catch(InterruptedException ie){
>
> }
>
> Utils.executeUpdate(con,"EXEC View.hiddenUpdate(456,'changed')");
>
> ResultSet full = Utils.executeQuery(con, sqlSelectAll);
> Assert.assertTrue(full.next(), "ResultSet shouldn't be empty");
>
> int custId = full.getInt(1);
> String custName = full.getString(2);
> Assert.assertEquals(custId, 456,"Returned row with wrong customer id");
> Assert.assertEquals(custName,"changed","PROCEDURE with UPDATE query didn't invalidate the cache:returned row with wrong customer name");
>
>
> }catch(SQLException se){
> LOG.error("Error during performing sql query on teiid",se);
> Assert.fail("Error during performing sql query on teiid", se);
> }
> }
> static Connection getTeiidConnectionUser1() throws SQLException {
> LOG.info("Getting Teiid connection for user1.");
> return TeiidDriver.getInstance().connect(TEIID_URL_USER1, null);
> }
> static int executeUpdate(Connection con, String sql) throws SQLException{
> LOG.info("Executing update [connection: " + con + "]: " + sql + ".");
> Statement s = con.createStatement();
> return s.executeUpdate(sql);
> }
> {code}
> Server log is in the attachment.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-3816) Informix translator - <> ALL is translated as NOT IN - this statement seem to not work in Infromix
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3816?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3816:
------------------------------------------------
David Le Sage <dlesage(a)redhat.com> changed the Status of [bug 1279473|https://bugzilla.redhat.com/show_bug.cgi?id=1279473] from ASSIGNED to ON_QA
> Informix translator - <> ALL is translated as NOT IN - this statement seem to not work in Infromix
> --------------------------------------------------------------------------------------------------
>
> Key: TEIID-3816
> URL: https://issues.jboss.org/browse/TEIID-3816
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.2.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> Query:
> {code:sql}
> SELECT INTKEY, BOOLEANVALUE FROM BQT1.SMALLA WHERE BOOLEANVALUE <> ALL (SELECT BOOLEANVALUE FROM BQT1.SMALLA WHERE INTKEY = 40)
> {code}
> Source-specific command:
> {code:sql}
> SELECT g_0.IntKey, g_0.BooleanValue FROM Source.SmallA AS g_0 WHERE g_0.BooleanValue NOT IN (SELECT g_1.BooleanValue FROM Source.SmallA AS g_1 WHERE g_1.IntKey = 40)
> {code}
> *Note: boolean value for row where Intkey = 40 is false.*
> Expected result:
> |intkey|booleanvalue|
> |1|true|
> |...|...|
> Actual result:
> |intkey|booleanvalue|
> |0|false|
> |...|...|
> This seems to be an Informix issue as neither '<> ALL' nor 'NOT IN' works.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-2578) add/remove schema elements
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2578?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-2578:
-------------------------------------
ALTER Support will be like
{code}
ALTER <VIRTUAL> SCHEMA <name> OPTIONS ( ADD|SET|DROP <key-value>)
ALTER DATABASE <name> OPTIONS ( ADD|SET|DROP <key-value>)
ALTER SERVER <name> OPTIONS ( ADD|SET|DROP <key-value>)
ALTER (DATA WRAPPER|TRANSLATOR) <name> OPTIONS ( ADD|SET|DROP <key-value>)
{code}
ALTER TABLE, Procedure to follow later..
> add/remove schema elements
> --------------------------
>
> Key: TEIID-2578
> URL: https://issues.jboss.org/browse/TEIID-2578
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Fix For: 9.1
>
>
> Schemas are currently static after load. Modifications can only happen with restarts or new versions. We should allow add/drop at runtime.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-2578) add/remove schema elements
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2578?page=com.atlassian.jira.plugin... ]
Ramesh Reddy edited comment on TEIID-2578 at 8/10/16 3:20 PM:
--------------------------------------------------------------
I will be modifying the schema creation as
{code}
CREATE [VIRTUAL] SCHEMA <name> [SERVER <server-name> (<COMMA> <server-name>)*] OPTIONS (....)
{code}
This will establish two things.
1. Defines the SCHEMA aka model is PHYSICAL or VIRTUAL.
2. Associates the Data Source name(s) with a SCHEMA. This being plural, will also handle the Multi Source models.
was (Author: rareddy):
I will be modifying the schema creation as
{code}
CREATE (VIRTUAL|FOREIGN) SCHEMA <name> [SERVER <server-name> (<COMMA> <server-name>)*] OPTIONS (....)
{code}
This will establish two things.
1. Defines the SCHEMA aka model is PHYSICAL or VIRTUAL.
2. Associates the Data Source name(s) with a SCHEMA. This being plural, will also handle the Multi Source models.
> add/remove schema elements
> --------------------------
>
> Key: TEIID-2578
> URL: https://issues.jboss.org/browse/TEIID-2578
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Fix For: 9.1
>
>
> Schemas are currently static after load. Modifications can only happen with restarts or new versions. We should allow add/drop at runtime.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-3511) usage of BITAND function in exists statement results in duplicate rows
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3511?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3511:
---------------------------------------
Have you been able to test this in later versions?
> usage of BITAND function in exists statement results in duplicate rows
> ----------------------------------------------------------------------
>
> Key: TEIID-3511
> URL: https://issues.jboss.org/browse/TEIID-3511
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.9.1
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Attachments: debugplan_bitand.txt, debugplan_bitand_correct.txt, debugplan_nobitand.txt, debugplan_nobitand_correct.txt
>
>
> I've added the debug plan for the query without the BITAND function and with the BITAND function as an attachment.
> The version without the BITAND function returns 438 rows and the version with BITAND function returns 833 rows. I can see however that rows are duplicated in this second result. I've checked this by executing the version with BITAND statement with select *.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4294) External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4294?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4294:
---------------------------------------
To summarize this -
For sources that do not treat ddl transactionally (for example non-XA), or have an isolation level that allows the affect to be seen immediately, then there can be an issue with the documented/default swap strategy such that the table temporarily does not exist.
Possible remedies:
1. With TEIID-4240 for sources that support it, it's possible to send a set of commands to the source rather than as individual native queries.
2. If on error action is wait, then we can temporarily mark the table as invalid during the after/swap - but that would only narrow the possibility of an invalid read and not prevent it.
3. Don't promote/rely on rename - add a column to the status table to indicate whether the primary or the staging table should be accessed (similar to how JDG staging logic functions) so that the swap is atomic. For this to work out-of-the box would require several logic and doc changes.
4. Promote an alternative strategy based upon maintaining just a single table - for sources that support merge this would be straightforward but can require adding an additional column to the materialized table for purging out of date entries. This can be just documented.
> External Materialization MATVIEW_AFTER_LOAD_SCRIPT is not atomic operation
> --------------------------------------------------------------------------
>
> Key: TEIID-4294
> URL: https://issues.jboss.org/browse/TEIID-4294
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: dv-2016-06-23-mat-view.log, teiid-command.log
>
>
> Firstly, this issue appears even after *TEIID-4283* fix, and is db independent.
> When during materialized view's loading there is a query on the view performed, timing issues appear. In MATVIEW_AFTER_LOAD_SCRIPT there might be more commands separated by semicolon, but these commands are not performed as a single operation. In specific timing a query on such view might fail when you have a MATVIEW_AFTER_LOAD_SCRIPT like this:
> {code:sql}
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');
> {code}
> user might end up querying the view right in the moment, when 'primary' materialized table (dv_matviews_mat_view) was renamed (dv_matviews_mat_view_stage) and thus there's no such table like defined in the materialized view.
> {code:xml}
> CREATE VIEW external_long_ttl (
> customer_id integer NOT NULL,
> total_amount integer
> ) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'Source.JSTASTNY.dv_matviews_mat_view',
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'Source.JSTASTNY.dv_matviews_statustable',
> "teiid_rel:ON_VDB_START_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_create) SELECT id, vdb_create+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:ON_VDB_DROP_SCRIPT" 'MERGE INTO dv_matviews_check_table(id,vdb_drop) SELECT id, vdb_drop+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_LOAD_SCRIPT" 'INSERT INTO dv_matviews_mat_view_stage(customer_id,total_amount) SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;',
> "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'exec Source.native(''truncate table dv_matviews_mat_view_stage'');MERGE INTO dv_matviews_check_table(id,before_load) SELECT id, before_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'exec Source.native(''RENAME TABLE dv_matviews_mat_view_stage TO dv_matviews_mat_view_temp'');exec Source.native(''RENAME TABLE dv_matviews_mat_view TO dv_matviews_mat_view_stage'');exec Source.native(''RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view'');MERGE INTO dv_matviews_check_table(id,after_load) SELECT id, after_load+1 FROM dv_matviews_check_table WHERE id=''external_long_ttl'';',
> "teiid_rel:MATVIEW_ONERROR_ACTION" 'IGNORE',
> "teiid_rel:MATVIEW_TTL" 20000)
> AS SELECT CONVERT(c.id,integer) AS customer_id, CONVERT(SUM(o.amount),integer) AS total_amount FROM dv_matviews_customers c INNER JOIN dv_matviews_orders o ON c.id = o.customer_id GROUP BY c.id;
> {code}
> Then for some queries I get:
> {code:plain}
> 10:40:19,252 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue130) Connector worker process failed for atomic-request=+k/MyS3sUhQX.14.7.113: org.teiid.translator.jdbc.JDBCExecutionException: 259 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."CUSTOMER_ID", g_0."TOTAL_AMOUNT" FROM "JSTASTNY".dvqe_X_XjdkY_YDV_MATVIEWS_MAT_VIEW AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.GeneratedMethodAccessor158.invoke(Unknown Source) [:1.7.0_79]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_79]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy80.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_79]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_79]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_79]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_79]
> Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [259]: invalid table name: Could not find table/view DVQE_X_XJDKY_YDV_MATVIEWS_MAT_VIEW in schema JSTASTNY: line 1 col 62 (at pos 61)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:185)
> at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:100)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1130)
> at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:877)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:410)
> at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:266)
> at com.sap.db.jdbc.CallableStatementSapDB.executeQuery(CallableStatementSapDB.java:756)
> at com.sap.db.jdbc.trace.PreparedStatement.executeQuery(PreparedStatement.java:161)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.5.redhat-5.jar:8.12.5.redhat-5]
> ... 17 more
> {code}
> From the logs it seems that the after load script has completed, but probably not in the source db. In logs just before the exception I see:
> {code:plain}
> 12:34:59,247 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) Source-specific command: RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Executed command
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing MORE request
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Getting results from connector
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Obtained last batch, total row count: 1
> 12:34:59,663 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Remove State
> 12:34:59,664 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue79) ORTEkGreciAL.0.85.84 Processing Close : EXEC Source.native('RENAME TABLE dv_matviews_mat_view_temp TO dv_matviews_mat_view')
> {code}
> I attached the logs to this issue.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4378) Support clob concat
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4378?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4378.
-----------------------------------
Resolution: Done
Added similar logic as SQL/XML to support clob concat. There are no new checks for pushdown as most sources that have clob types will support concat, but that won't be the case in narrow circumstance that we can address later.
> Support clob concat
> -------------------
>
> Key: TEIID-4378
> URL: https://issues.jboss.org/browse/TEIID-4378
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> Clob concat is the most important of the clob operations to support.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-1248) Clob usability
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-1248?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-1248:
---------------------------------------
The plan here is to address the subtasks and for 9.1 and defer the other clob logic until later releases.
> Clob usability
> --------------
>
> Key: TEIID-1248
> URL: https://issues.jboss.org/browse/TEIID-1248
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Affects Versions: 8.0
> Reporter: Steven Hawkins
> Priority: Minor
>
> In many circumstances, clobs should be treated as charsequences to allow for use in string functions (concat returning clob, insert, left, etc.).
> There have also been customer requests to treat clobs as comparable, but that there are a lot of places including at design time where we validate against clobs being comparable that it may not be a good idea.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4378) Support clob concat
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-4378:
-------------------------------------
Summary: Support clob concat
Key: TEIID-4378
URL: https://issues.jboss.org/browse/TEIID-4378
Project: Teiid
Issue Type: Sub-task
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 9.1
Clob concat is the most important of the clob operations to support.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months