[JBoss JIRA] (TEIID-4960) Problems when using External Materialized Views
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4960?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4960:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1467913|https://bugzilla.redhat.com/show_bug.cgi?id=1467913] from MODIFIED to ON_QA
> Problems when using External Materialized Views
> -----------------------------------------------
>
> Key: TEIID-4960
> URL: https://issues.jboss.org/browse/TEIID-4960
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.3
> Environment: * Teiid Server 9.3.0
> * Wildfly 10
> * Mysql 5.7.18
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Fix For: 10.0, 8.12.x-6.4, 9.3.1, 9.2.5, 8.12.12.6_3
>
> Attachments: server.log, teiid4960-server.log, teiid4960-vdb.xml
>
>
> The following problem occurs when, apparently, we have two views where one depend on another, and also we use External Materialization.
> The dependent view is never updated due to a "Transaction already associated with request." exception.
> Logs attached.
> The error:
> {panel:title=The exception in logs}
> 2017-06-14 13:51:51,753 WARN [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue365) Hlbq3jmDWXNl org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate mvstatus('NumberingPlan', 'numbering_plan'): TEIID30384 Error while evaluating function mvstatus
> 2017-06-14 13:52:51,803 INFO [org.teiid.MATVIEWS] (Worker14_QueryProcessorQueue368) XAtcLA8RBrLm Materialization of view NumberingPlanRaw.numbering_plan_raw started.
> 2017-06-14 13:52:51,867 ERROR [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue372) XAtcLA8RBrLm TEIID30019 Unexpected exception for request XAtcLA8RBrLm.-2514910280603581440: java.lang.AssertionError: Transaction already associated with request.
> {panel}
> The VDB:
> {code:xml}
> <model name="MnomMaterialized" type="PHYSICAL">
> <property name="importer.useFullSchemaName" value="false"/>
> <property name="query-timeout" value="600000"/>
> <source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
> </model>
>
> <model name="NumberingPlanCsvData">
> <source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
> </model>
> <model name="NumberingPlan" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE VIEW numbering_plan (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'TRUE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY cns) as id,
> cns
> FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
> TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
>
> ]]>
> </metadata>
> </model>
>
> <model name="NumberingPlanRaw" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[
> CREATE VIEW numbering_plan_raw (
> id integer PRIMARY KEY,
> global_title varchar(20)
> )
> OPTIONS(
> MATERIALIZED 'TRUE',
> UPDATABLE 'FALSE',
> MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
> "teiid_rel:MATVIEW_TTL" 86400000,
> "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
> "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
> "teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
> )
> AS
> SELECT ROW_NUMBER() OVER (ORDER BY network) as id,
> global_title
> FROM numbering_plan np;
>
> ]]>
> </metadata>
> </model>
> </model>
> {code}
> The Materialized table:
> {code:sql}
> CREATE TABLE status (
> VDBName VARCHAR(50) NOT NULL,
> VDBVersion VARCHAR(50) NOT NULL,
> SchemaName VARCHAR(50) NOT NULL,
> Name VARCHAR(256) NOT NULL,
> TargetSchemaName VARCHAR(50),
> TargetName VARCHAR(256) NOT NULL,
> Valid BOOLEAN NOT NULL,
> LoadState VARCHAR(25) NOT NULL,
> Cardinality BIGINT,
> Updated TIMESTAMP NOT NULL,
> LoadNumber BIGINT NOT NULL,
> NodeName varchar(25) not null,
> StaleCount BIGINT,
> PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
> );
> CREATE TABLE numbering_plan_cache (
> id integer,
> global_title varchar(20),
> LoadNumber BIGINT,
> PRIMARY KEY(id)
> );
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5109) Set operations and parenthesis
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-5109?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-5109:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1504707|https://bugzilla.redhat.com/show_bug.cgi?id=1504707] from NEW to ASSIGNED
> Set operations and parenthesis
> ------------------------------
>
> Key: TEIID-5109
> URL: https://issues.jboss.org/browse/TEIID-5109
> Project: Teiid
> Issue Type: Bug
> Components: Connector API, Query Engine
> Reporter: Mike Higgins
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 10.0, 8.12.x-6.4, 9.3.4, 9.2.7
>
>
> Using Teiid 9.2.2 with postgresql, I issue a query structured like this:
>
> (a
> UNION ALL
> b
> UNION ALL
> c)
> INTERSECT
> d
>
> All queries are from a single database.
> This query does not give back the expected result.
> In the plan, I see the query printed with no parenthesis around the unions:
> a UNION ALL b UNION ALL c INTERSECT d
> This gives the same result as the query submitted with no parens in it (the intersect of c and d is unioned with a union b.
> The expepeted result is to preserve the paren so that the intersect is performed against the union of all three tables.
> As a note, using UNION instead of UNION ALL did not preserve the parens, either.
>
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5118) Osisoft Translator - NPE when using ORDER BY
by Andrej Šmigala (JIRA)
Andrej Šmigala created TEIID-5118:
-------------------------------------
Summary: Osisoft Translator - NPE when using ORDER BY
Key: TEIID-5118
URL: https://issues.jboss.org/browse/TEIID-5118
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 8.12.x-6.4
Reporter: Andrej Šmigala
Assignee: Steven Hawkins
Priority: Critical
Any query with an ORDER BY clause run against Osisoft PI datasource fails with a NullPointerException
Stack trace:
{noformat}
10:17:09,235 ERROR [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue17) Connector worker process failed for atomic-request=mcBkcBzOiieD.5.0.5: java.lang.NullPointerException
at org.teiid.translator.jdbc.pi.PISQLConversionVisitor.visit(PISQLConversionVisitor.java:157) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
at org.teiid.language.ColumnReference.acceptVisitor(ColumnReference.java:72) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:91) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:130) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
at org.teiid.language.visitor.SQLStringVisitor.visit(SQLStringVisitor.java:737) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.SortSpecification.acceptVisitor(SortSpecification.java:53) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:91) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:130) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:102) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.visitor.SQLStringVisitor.visit(SQLStringVisitor.java:733) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.OrderBy.acceptVisitor(OrderBy.java:45) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:91) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:130) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
at org.teiid.language.visitor.SQLStringVisitor.visit(SQLStringVisitor.java:790) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.Select.acceptVisitor(Select.java:110) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:91) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:130) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
at org.teiid.translator.jdbc.TranslatedCommand.translateCommand(TranslatedCommand.java:76) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
at org.teiid.translator.jdbc.JDBCBaseExecution.translateCommand(JDBCBaseExecution.java:120) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:114) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:363)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_141]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_141]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_141]
at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_141]
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy79.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:266) [rt.jar:1.8.0_141]
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280)
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:1149) [rt.jar:1.8.0_141]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_141]
at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_141]
{noformat}
I tried remote debugging the server process and it appears that when running query such as
{code:sql}
SELECT FloatNum FROM BQT1.SmallA ORDER BY FloatNum ASC
{code}
the PISQLConversionVisitor.visit(ColumnReference obj) is called twice, the first time the ColumnReference object has value "g_0.FloatNum" and all fields set, the second time the object has only name set to "c_2" and nothing else (table, metadata object etc).
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5037) Couchbase UNION doesn't work correctly
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-5037?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-5037:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1485334|https://bugzilla.redhat.com/show_bug.cgi?id=1485334] from MODIFIED to ON_QA
> Couchbase UNION doesn't work correctly
> --------------------------------------
>
> Key: TEIID-5037
> URL: https://issues.jboss.org/browse/TEIID-5037
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.x-6.4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 10.0, 8.12.x-6.4, 9.3.3
>
>
> UNION operator is returning correct data.
> {code:sql|title=query_smalla}
> SELECT IntKey, ShortValue FROM BQT1.SmallA WHERE ShortValue = -32745
> {code}
> || IntKey || ShortValue ||
> | 23 | -32745 |
> {code:sql|title=query_smallb}
> SELECT IntKey, ShortValue FROM BQT2.SmallB WHERE ShortValue = -32750
> {code}
> || IntKey || ShortValue ||
> | 18 | -32750 |
> {code:sql|title=query_union}
> SELECT IntKey, ShortValue FROM BQT1.SmallA WHERE ShortValue = -32745 UNION SELECT IntKey, ShortValue FROM BQT2.SmallB WHERE ShortValue = -32750
> {code}
> || IntKey || ShortValue ||
> | <null> | -32750 |
> | 23 | -32745 |
> {code:title=teiid-command.log}
> 14:19:54,385 INFO [org.teiid.COMMAND_LOG] (New I/O worker #3) nxUTZWzvm0Te START USER COMMAND: startTime=2017-08-25 14:19:54.385 requestID=nxUTZWzvm0Te.9 txID=null sessionID=nxUTZWzvm0Te applicationName=JDBC principal=user@teiid-security vdbName=couchbase vdbVersion=1 sql=SELECT IntKey, ShortValue FROM BQT1.SmallA WHERE ShortValue = -32745 UNION SELECT IntKey, ShortValue FROM BQT2.SmallB WHERE ShortValue = -32750
> 14:19:54,389 DEBUG [org.teiid.COMMAND_LOG] (Worker78_QueryProcessorQueue660) nxUTZWzvm0Te START DATA SRC COMMAND: startTime=2017-08-25 14:19:54.389 requestID=nxUTZWzvm0Te.9 sourceCommandID=0 executionID=158 txID=null modelName=Couchbase_small translatorName=couchbase sessionID=nxUTZWzvm0Te principal=user@teiid-security sql=SELECT Couchbase_small.SmallA.IntKey AS c_0, convert(Couchbase_small.SmallA.ShortValue, short) AS c_1 FROM Couchbase_small.SmallA WHERE convert(Couchbase_small.SmallA.ShortValue, short) = -32745 UNION SELECT Couchbase_small.SmallB.IntKey AS c_0, convert(Couchbase_small.SmallB.ShortValue, short) AS c_1 FROM Couchbase_small.SmallB WHERE convert(Couchbase_small.SmallB.ShortValue, short) = -32750
> 14:19:54,390 DEBUG [org.teiid.COMMAND_LOG] (Worker78_QueryProcessorQueue660) nxUTZWzvm0Te SOURCE SRC COMMAND: endTime=2017-08-25 14:19:54.39 requestID=nxUTZWzvm0Te.9 sourceCommandID=0 executionID=158 txID=null modelName=Couchbase_small translatorName=couchbase sessionID=nxUTZWzvm0Te principal=user@teiid-security sourceCommand=[SELECT `$cb_c1_IntKey`, TONUMBER(`$cb_c2_ShortValue`) FROM `dvqe_small` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`IntKey`, `$cb_c2_ShortValue` = `$cb_t1`.`ShortValue` WHERE TONUMBER(`$cb_c2_ShortValue`) = -32745 AND `$cb_t1`.`type` = 'SmallA' UNION SELECT `$cb_c3_IntKey`, TONUMBER(`$cb_c2_ShortValue`) FROM `dvqe_small` `$cb_t2` LET `$cb_c1_IntKey` = `$cb_t2`.`IntKey`, `$cb_c2_ShortValue` = `$cb_t2`.`ShortValue`, `$cb_c3_IntKey` = `$cb_t2`.`IntKey` WHERE TONUMBER(`$cb_c2_ShortValue`) = -32750 AND `$cb_t2`.`type` = 'SmallA']
> 14:19:54,674 DEBUG [org.teiid.COMMAND_LOG] (Worker77_QueryProcessorQueue661) nxUTZWzvm0Te END SRC COMMAND: endTime=2017-08-25 14:19:54.674 requestID=nxUTZWzvm0Te.9 sourceCommandID=0 executionID=158 txID=null modelName=Couchbase_small translatorName=couchbase sessionID=nxUTZWzvm0Te principal=user@teiid-security finalRowCount=2 cpuTime(ns)=1197365
> 14:19:54,683 INFO [org.teiid.COMMAND_LOG] (Worker77_QueryProcessorQueue662) nxUTZWzvm0Te END USER COMMAND: endTime=2017-08-25 14:19:54.683 requestID=nxUTZWzvm0Te.9 txID=null sessionID=nxUTZWzvm0Te principal=user@teiid-security vdbName=couchbase vdbVersion=1 finalRowCount=2
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5117) Osisoft Translator - Casting to bigdecimal results in NULL value
by Andrej Šmigala (JIRA)
Andrej Šmigala created TEIID-5117:
-------------------------------------
Summary: Osisoft Translator - Casting to bigdecimal results in NULL value
Key: TEIID-5117
URL: https://issues.jboss.org/browse/TEIID-5117
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 8.12.x-6.4
Reporter: Andrej Šmigala
Assignee: Steven Hawkins
Casting any value obtained from an Osisoft PI datasource results in NULL, regardless of the original value.
E.g. a query such as
{code:sql}
select cast(stringnum as bigdecimal), cast(intnum as bigdecimal), cast(shortvalue as bigdecimal) from Source.smalla
{code}
returns result set full of NULLs.
The SOURCE SRC COMMAND in teiid-command.log for this query is however
{code:sql}
SELECT g_0.StringNum, g_0.IntNum, g_0.ShortValue FROM dvqe..SmallA AS g_0
{code}
i.e. the cast is not pushed down (as Osisoft doesn't support bigdecimal datatype).
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5116) Osisoft Translator - NULL values in numeric columns returned as zeroes
by Andrej Šmigala (JIRA)
Andrej Šmigala created TEIID-5116:
-------------------------------------
Summary: Osisoft Translator - NULL values in numeric columns returned as zeroes
Key: TEIID-5116
URL: https://issues.jboss.org/browse/TEIID-5116
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 8.12.x-6.4
Reporter: Andrej Šmigala
Assignee: Steven Hawkins
Priority: Critical
NULL values in columns with a numeric type (all of int8, int16 etc, single, double) are returned as 0.
This is due to a bug/quirk of the Osisoft PI JDBC driver, which is hard-coded to return false from the wasNull method.
On the Teiid side, the code in JDBCExecutionFactory.retrieveValue() assumes (completely reasonably) that the wasNull method is implemented correctly:
{code:java}
case DataTypeManager.DefaultTypeCodes.INTEGER: {
int value = results.getInt(columnIndex);
if(results.wasNull()) {
return null;
}
return Integer.valueOf(value);
}
{code}
I managed to workaround the bug in the PI JDBC driver by overriding the retrieveValue() in PIExecutionFactory and replacing the calls to wasNull like this:
{code:java}
case DataTypeManager.DefaultTypeCodes.INTEGER: {
int value = results.getInt(columnIndex);
if (results.getObject(columnIndex) == null) {
return null;
}
return Integer.valueOf(value);
}
{code}
but that probably isn't the best solution.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months