[JBoss JIRA] (TEIID-4656) parseTimestamp issue with Oracle
by Juraj Duráni (JIRA)
[ https://issues.jboss.org/browse/TEIID-4656?page=com.atlassian.jira.plugin... ]
Juraj Duráni reopened TEIID-4656:
---------------------------------
Function parseTime is still being pushed down.
>From my test:
{code:sql|title=Oracle data source}
CREATE TABLE teiid4656 (id number(1) PRIMARY KEY, col varchar(23))
INSERT INTO teiid4656 (id, col) VALUES (1, '2016-24-12 20:00:00.111')
INSERT INTO teiid4656 (id, col) VALUES (2, '20:00:00.111 2016-24-12')
{code}
*Query 1:*
{code:sql}
SELECT CAST(PARSEDATE(col, 'yyyy-dd-MM') AS string) FROM teiid4656 WHERE id = 1
{code}
*Result 1 (OK):* 2016-12-24
*Query 2:*
{code:sql}
SELECT CAST(PARSETIME(col, 'hh:mm:ss') AS string) FROM teiid4656 WHERE id = 2
{code}
*Result 2 (Exception):*
{code:plain|title=Server log}
07:27:48,165 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue4) NHKd8fOn5GvJ.0 executing SELECT CAST(PARSETIME(col, 'hh:mm:ss') AS string) FROM teiid4656 WHERE id = 2
07:27:48,169 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue4) ProcessTree for NHKd8fOn5GvJ.0 AccessNode(0) output=[convert(convert(Source.teiid4656.col, TIME), string)] SELECT convert(convert(g_0.col, TIME), string) FROM Source.teiid4656 AS g_0 WHERE g_0.id = 2
07:27:48,169 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue4) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@cd77a59(NHKd8fOn5GvJ)
07:27:48,169 DEBUG [org.teiid.TXN_LOG] (Worker0_QueryProcessorQueue4) after getOrCreateTransactionContext : NHKd8fOn5GvJ NONE ID:NONE
07:27:48,170 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue4) Creating TupleBuffer: 1 [convert(convert(Source.teiid4656.col, TIME), string)] [class java.lang.String] batch size 1024 of type PROCESSOR
07:27:48,170 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue4) NHKd8fOn5GvJ.0.0.1 Create State
07:27:48,171 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue5) Running task for parent thread Worker0_QueryProcessorQueue4
07:27:48,171 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue5) NHKd8fOn5GvJ.0.0.1 Processing NEW request: SELECT convert(convert(g_0.col, TIME), string) FROM Source.teiid4656 AS g_0 WHERE g_0.id = 2
07:27:48,172 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue5) NHKd8fOn5GvJ.0.0.1 Obtained execution
07:27:48,173 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue5) Source-specific command: SELECT to_char(to_date(g_0.col, 'HH24:MI:SS'), 'HH24:MI:SS') FROM teiid4656 g_0 WHERE g_0.id = 2
07:27:48,174 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue4) NHKd8fOn5GvJ.0.0.1 Blocking on source query NHKd8fOn5GvJ.0.0.1
07:27:48,174 DEBUG [org.teiid.BUFFER_MGR] (Worker0_QueryProcessorQueue4) NHKd8fOn5GvJ.0 Blocking on source request(s).
07:27:48,174 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue4) Request Thread NHKd8fOn5GvJ.0 - processor blocked
07:27:48,486 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue5) Connector worker process failed for atomic-request=NHKd8fOn5GvJ.0.0.1: org.teiid.translator.jdbc.JDBCExecutionException: 1830 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT to_char(to_date(g_0.col, 'HH24:MI:SS'), 'HH24:MI:SS') FROM teiid4656 g_0 WHERE g_0.id = 2]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:337) [teiid-engine-8.7.11.6_2-redhat-1.jar:8.7.11.6_2-redhat-1]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.11.6_2-redhat-1.jar:8.7.11.6_2-redhat-1]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.11.6_2-redhat-1.jar:8.7.11.6_2-redhat-1]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.11.6_2-redhat-1.jar:8.7.11.6_2-redhat-1]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0-internal]
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.11.6_2-redhat-1.jar:8.7.11.6_2-redhat-1]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.11.6_2-redhat-1.jar:8.7.11.6_2-redhat-1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.11.6_2-redhat-1.jar:8.7.11.6_2-redhat-1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.11.6_2-redhat-1.jar:8.7.11.6_2-redhat-1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0-internal]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0-internal]
at java.lang.Thread.run(Thread.java:744) [rt.jar:1.8.0-internal]
Caused by: java.sql.SQLDataException: ORA-01830: date format picture ends before converting entire input string
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 12 more
{code}
*Query 3:*
{code:sql}
SELECT CAST(PARSETIMESTAMP(col, 'yyyy-dd-MM hh:mm') AS string) FROM teiid4656 WHERE id = 1
{code}
*Result 3 (OK):* 2016-12-24 20:00:00.0
> parseTimestamp issue with Oracle
> --------------------------------
>
> Key: TEIID-4656
> URL: https://issues.jboss.org/browse/TEIID-4656
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector, Query Engine
> Affects Versions: 8.12
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.7.10.6_2, 9.2
>
>
> parseTimestamp is pushed as to_timestamp. However TO_TIMESTAMP is not generally valid unless the format string covers the entire string input value otherwise there is an exception about the picture ending.
> This means in general that we cannot pushdown the parseDate/Time/Timestamp operations to oracle as that is behavioral different with the java format routines, which will process just to the end of the format string.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 1 month
[JBoss JIRA] (TEIID-4639) Mongo translator - embeddable tables - NULL primary key of embedded table in result
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4639?page=com.atlassian.jira.plugin... ]
Work on TEIID-4639 started by Ramesh Reddy.
-------------------------------------------
> Mongo translator - embeddable tables - NULL primary key of embedded table in result
> -----------------------------------------------------------------------------------
>
> Key: TEIID-4639
> URL: https://issues.jboss.org/browse/TEIID-4639
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Fix For: 9.2
>
>
> Issuing query like \[1\] on embeddable tables scenario \[2\], in the result, primary key of embedded table is NULL \[3\].
> {code:sql|title=\[1\] Sample query}
> SELECT a.id AS a_id, a.company_id AS a_cid, b.id AS b_id FROM Person a INNER JOIN Company b IN a.company_id = b.id
> {code}
> {code:sql|title=\[2\] DDL}
> CREATE FOREIGN TABLE Person (
> id integer PRIMARY KEY,
> name varchar(25),
> company_id integer,
> FOREIGN KEY (company_id) REFERENCES Company (id)
> ) OPTIONS(UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE Company (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> {code}
> *\[3\] Result*
> ||a_id||a_cid||b_id||
> |11|11|<null>|
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 1 month
[JBoss JIRA] (TEIID-4619) left join returns wrong results
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4619?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4619:
---------------------------------------
Thanks Bram, yes this helps complete the picture, but the logs seem to have been produced before the final batch, so I'm not quite able to compare row counts directly.
If you take query1 and add the predicate l.infectionid = 880, do you get the expected result? If not, that may help narrow in a bit.
Otherwise, having the full row counts will be helpful - or even better a small sanitized reproduction.
> left join returns wrong results
> -------------------------------
>
> Key: TEIID-4619
> URL: https://issues.jboss.org/browse/TEIID-4619
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.0.4, 9.0.5
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, enclosed_queryplan.txt, query1_enclosed_plan.txt, query1_plan.txt, query2_plan.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this first query there are 66290 results. However if I only look at the lines for infectionid 880 then there are only 16 lines.
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from #tmp_admissions adm
> join cos2_links l on l.admissionid = cast(adm.patientid as string)
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> {code}
> This query does almost the same but returns 30 rows (and is correct).
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from cos2_links l
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> where l.infectionid = 880
> {code}
> cos2_link_culture contains 2 rows for this infectionid. The left join statements should result in 15 rows for both rows. However the left join results in the first query for the first row are null and to my understanding ignored. I'll attach the query plans for both queries.
> I should note that there is a one to many relation between infection and admission so therefore infectionid is for the same admission.
> Strangely enough if you enclode the first query in a group by query and count the rows it does indeed return 2 times 15 for the specific groups (see enclosed_queryplan.txt).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 1 month
[JBoss JIRA] (TEIID-4658) Data scramble feature
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4658?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4658:
---------------------------------------
This is one form of anonymizing/pseudonymised data. For this to be performant it would have to be based upon a materialization based upon appropriate shuffling or not consistent - that is each query could return different random values.
> Data scramble feature
> ---------------------
>
> Key: TEIID-4658
> URL: https://issues.jboss.org/browse/TEIID-4658
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Rodrigo Silva
> Assignee: Steven Hawkins
>
> I have a customer asking for a feature to do data scramble.
> The need is change the production data to be used in another environments without the user know the original data.
> e.g:
> Give the person table:
> {code:java}
> CREATE TABLE person (
> id INT PRIMARY KEY,
> name VARCHAR(256) NOT NULL,
> age INT,
> height INT,
> weight DOUBLE
> );
> {code}
> {code:java}
> SELECT * FROM TABLE PERSON WHERE ID=1;
> {code}
> Result:
> The *name* is from row with *ID#15*, the *age* is from row with *ID#3*, the *height* is from row with *ID#5*.
> On this way the customer can work with consistent and natural data, but unreal.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 1 month
[JBoss JIRA] (TEIID-4659) High-level event handling
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-4659:
-------------------------------------
Summary: High-level event handling
Key: TEIID-4659
URL: https://issues.jboss.org/browse/TEIID-4659
Project: Teiid
Issue Type: Enhancement
Components: Grammar, Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 9.2
We should look into high-level plugability of event handling at a row level. This could even be handled at the language level with new trigger semantics for source tables: create trigger on tbl on cdc (insert|update...) - which could potentially even specify the topic mapping other details. There is complexity for multi-source models here though.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 1 month
[JBoss JIRA] (TEIID-4658) Data scramble feature
by Rodrigo Silva (JIRA)
[ https://issues.jboss.org/browse/TEIID-4658?page=com.atlassian.jira.plugin... ]
Rodrigo Silva updated TEIID-4658:
---------------------------------
Description:
I have a customer asking for a feature to do data scramble.
The need is change the production data to be used in another environments without the user know the original data.
e.g:
Give the person table:
{code:java}
CREATE TABLE person (
id INT PRIMARY KEY,
name VARCHAR(256) NOT NULL,
age INT,
height INT,
weight DOUBLE
);
{code}
{code:java}
SELECT * FROM TABLE PERSON WHERE ID=1;
{code}
Result:
The *name* is from row with *ID#15*, the *age* is from row with *ID#3*, the *height* is from row with *ID#5*.
On this way the customer can work with consistent and natural data, but unreal.
was:
I have a customer asking for a feature to do data scramble.
The need is change the production data to be used in another environments without the user know the original data.
e.g:
SELECT * FROM TABLE PERSON WHERE ID=1;
Result:
The *name* is from row with *ID#15*, the *age* is from row with *ID#3*, the *height* is from row with *ID#5*.
On this way the customer can work with consistent and natural data, but unreal.
> Data scramble feature
> ---------------------
>
> Key: TEIID-4658
> URL: https://issues.jboss.org/browse/TEIID-4658
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Rodrigo Silva
> Assignee: Steven Hawkins
>
> I have a customer asking for a feature to do data scramble.
> The need is change the production data to be used in another environments without the user know the original data.
> e.g:
> Give the person table:
> {code:java}
> CREATE TABLE person (
> id INT PRIMARY KEY,
> name VARCHAR(256) NOT NULL,
> age INT,
> height INT,
> weight DOUBLE
> );
> {code}
> {code:java}
> SELECT * FROM TABLE PERSON WHERE ID=1;
> {code}
> Result:
> The *name* is from row with *ID#15*, the *age* is from row with *ID#3*, the *height* is from row with *ID#5*.
> On this way the customer can work with consistent and natural data, but unreal.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 1 month
[JBoss JIRA] (TEIID-4658) Data scramble feature
by Rodrigo Silva (JIRA)
Rodrigo Silva created TEIID-4658:
------------------------------------
Summary: Data scramble feature
Key: TEIID-4658
URL: https://issues.jboss.org/browse/TEIID-4658
Project: Teiid
Issue Type: Feature Request
Reporter: Rodrigo Silva
Assignee: Steven Hawkins
I have a customer asking for a feature to do data scramble.
The need is change the production data to be used in another environments without the user know the original data.
e.g:
SELECT * FROM TABLE PERSON WHERE ID=1;
Result:
The *name* is from row with *ID#15*, the *age* is from row with *ID#3*, the *height* is from row with *ID#5*.
On this way the customer can work with consistent and natural data, but unreal.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 1 month
[JBoss JIRA] (TEIID-4640) Mongo translator - embeddable tables - creation of collection fails
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4640?page=com.atlassian.jira.plugin... ]
Work on TEIID-4640 started by Ramesh Reddy.
-------------------------------------------
> Mongo translator - embeddable tables - creation of collection fails
> -------------------------------------------------------------------
>
> Key: TEIID-4640
> URL: https://issues.jboss.org/browse/TEIID-4640
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Ramesh Reddy
> Fix For: 9.2
>
>
> I use Teiid to create collection in MongoDB (with first INSERT).
> This scenario works for simple tables. However, if I have embeddable tables \[1\], parent table creation fails \[2, 3\].
> {code:sql|title=\[1\] DDL}
> CREATE FOREIGN TABLE Person (
> id integer PRIMARY KEY,
> name varchar(25),
> company_id integer,
> FOREIGN KEY (company_id) REFERENCES Company (id)
> ) OPTIONS(UPDATABLE 'TRUE');
> CREATE FOREIGN TABLE Company (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'TRUE');
> {code}
> {code:sql|title=\[2\] Insert queries}
> INSERT INTO Company (id, name) VALUES (11, 'comp11');
> INSERT INTO Person(id, name, company_id) VALUES (11, 'p11', 11);
> {code}
> {code:plain|title=\[3\] Exception}
> 09:23:19,306 WARN [org.teiid.PROCESSOR] (Worker33_QueryProcessorQueue527) TEIID30020 Processing exception for request 7MaF5Ey1GjEO.32 'TEIID30504 local: { "serverUsed" : "dvqe03.mw.lab.eng.bos.redhat.com:27017" , "spec" : { "name" : "FK0" , "ns" : "Person" , "key" : { "company_id" : 1}} , "ok" : 0.0 , "errmsg" : "namespace mismatch"}'. Originally TeiidProcessingException CommandResult.java:76.: org.teiid.core.TeiidProcessingException: TEIID30504 local: { "serverUsed" : "dvqe03.mw.lab.eng.bos.redhat.com:27017" , "spec" : { "name" : "FK0" , "ns" : "Person" , "key" : { "company_id" : 1}} , "ok" : 0.0 , "errmsg" : "namespace mismatch"}
> at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:391) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0-internal]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0-internal]
> at java.lang.Thread.run(Thread.java:744) [rt.jar:1.8.0-internal]
> Caused by: org.teiid.translator.TranslatorException: { "serverUsed" : "dvqe03.mw.lab.eng.bos.redhat.com:27017" , "spec" : { "name" : "FK0" , "ns" : "Person" , "key" : { "company_id" : 1}} , "ok" : 0.0 , "errmsg" : "namespace mismatch"}
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.execute(MongoDBUpdateExecution.java:79)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:402) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at sun.reflect.GeneratedMethodAccessor91.invoke(Unknown Source) [:1.8.0-internal]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0-internal]
> at java.lang.reflect.Method.invoke(Method.java:483) [rt.jar:1.8.0-internal]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0-internal]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> ... 6 more
> Caused by: com.mongodb.CommandFailureException: { "serverUsed" : "dvqe03.mw.lab.eng.bos.redhat.com:27017" , "spec" : { "name" : "FK0" , "ns" : "Person" , "key" : { "company_id" : 1}} , "ok" : 0.0 , "errmsg" : "namespace mismatch"}
> at com.mongodb.CommandResult.getException(CommandResult.java:76)
> at com.mongodb.CommandResult.throwOnError(CommandResult.java:140)
> at com.mongodb.DBCollectionImpl.createIndex(DBCollectionImpl.java:399)
> at com.mongodb.DBCollection.createIndex(DBCollection.java:597)
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.createIndex(MongoDBUpdateExecution.java:608)
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.getCollection(MongoDBUpdateExecution.java:578)
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.executeInternal(MongoDBUpdateExecution.java:85)
> at org.teiid.translator.mongodb.MongoDBUpdateExecution.execute(MongoDBUpdateExecution.java:77)
> ... 18 more
> {code}
> If collection in Mongo exists, no exception is thrown and all data are properly inserted.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 1 month
[JBoss JIRA] (TEIID-4619) left join returns wrong results
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-4619?page=com.atlassian.jira.plugin... ]
Bram Gadeyne edited comment on TEIID-4619 at 12/20/16 10:59 AM:
----------------------------------------------------------------
Hi Steven,
I'm sorry for the delay. I've recently updates our systems to 9.0.5 and the problem is also present in this version.
I've attached 2 new query plans. Are these the ones you're looking for?
I've also attached a 3th file that contains the same query 1 but then enclosed in a group by statement.
So query 1 returns 16 lines for infectionid 880 while there should be 30. The group by query however shows a total count of 30 as expected.
was (Author: gadeyne.bram):
Hi Steven,
I'm sorry for the delay. I've recently updates our systems to 9.0.5 and the problem is also present in this version.
I've attached 2 new query plans. Are these the ones you're looking for?
> left join returns wrong results
> -------------------------------
>
> Key: TEIID-4619
> URL: https://issues.jboss.org/browse/TEIID-4619
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.0.4, 9.0.5
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Attachments: correct_result.txt, enclosed_queryplan.txt, query1_enclosed_plan.txt, query1_plan.txt, query2_plan.txt, wrong_result.txt
>
>
> I have the following situation.
> I have a temporary table #tmp_admissions that contains 8047 rows.
> In this first query there are 66290 results. However if I only look at the lines for infectionid 880 then there are only 16 lines.
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from #tmp_admissions adm
> join cos2_links l on l.admissionid = cast(adm.patientid as string)
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> {code}
> This query does almost the same but returns 30 rows (and is correct).
> {code:sql}
> select l.infectionid, l.id as linkid, lc.linkcultureid, lc.responsibleculture, lc.culturealternative, cl.sampleinsertts, cl.specimennumber,
> cl.culturenumber, cl.culturename, cl.quotation, ls.material, ls.sampletime,
> abr.culturenumber as abgram_culturenumber,abr.antibiogrampart, abr.resisttype,
> lc.antibiogramculturenr,lc.antibiogramspecimennr,lc.antibiogramsampleinsertts
> from cos2_links l
> join cos2_link_culture lc on lc.linkid = l.id
> left join cos2_lab_culture cl on cl.culturenumber = lc.culturenr and cl.specimennumber = lc.culturespecimennr and cl.sampleinsertts = lc.culturesampleinsertts
> left join cos2_lab_sample ls on ls.inserttime = cl.sampleinsertts and ls.specimennumber = cl.specimennumber
> left join cos2_antibiogramresistences abr on abr.specimennumber = cl.specimennumber and abr.culturenumber = cl.culturenumber and abr.sampleinsertts = cl.sampleinsertts
> where l.infectionid = 880
> {code}
> cos2_link_culture contains 2 rows for this infectionid. The left join statements should result in 15 rows for both rows. However the left join results in the first query for the first row are null and to my understanding ignored. I'll attach the query plans for both queries.
> I should note that there is a one to many relation between infection and admission so therefore infectionid is for the same admission.
> Strangely enough if you enclode the first query in a group by query and count the rows it does indeed return 2 times 15 for the specific groups (see enclosed_queryplan.txt).
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
8 years, 1 month