[JBoss JIRA] (TEIID-3056) MongoDB: Put nested JSON documents in one single table
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3056?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-3056.
---------------------------------
Resolution: Rejected
To explain further, in the above example create two tables Customer and Address, where Address table is marked as "MERGED" into the Customer so that it will defined with document structure as defined.
Then create a view called "Customer" with required attributes from source Customer and Address to full fill the request. You can hide the source model in VDB, if you do not want expose that level.
> MongoDB: Put nested JSON documents in one single table
> -------------------------------------------------------
>
> Key: TEIID-3056
> URL: https://issues.jboss.org/browse/TEIID-3056
> Project: Teiid
> Issue Type: Enhancement
> Components: Misc. Connectors
> Affects Versions: 8.7
> Environment: MongoDB with Teiid
> Reporter: Ivan Chan
> Assignee: Ramesh Reddy
> Labels: teiid
>
> Currently, it seems like ID needs to be appear in parent and child for nested documents. But it is unlikely a case for MongoDB customers. It would be nice if Teiid can flatten out data in one single table. Therefore, users would not need to insert parent id in the child document in order for Teiid to work.
> I am thinking the following case. For example I have a JSON object like this:
> {code}
> Customer
> {
> _id: 1374932,
> FirstName: "John",
> LastName: "Doe",
> Address: {
> _id: 43839430,
> Street: "123 Lane",
> City: "New York",
> State: "NY",
> Zipcode: "12345"
> }
> }
> {code}
>
> In this case, customer._id doesn't exist inside Address. There is no way to do join in regular SQL fashion. But it would be nice if teiid can put the column from parent and child into a single table. Maybe something similar to the following schema:
> {code}
> CREATE FOREIGN TABLE Customer (
> CustomerId integer PRIMARY KEY,
> FirstName varchar(25),
> LastName varchar(25),
> address__id integer,
> address_Street varchar(50),
> address_City varchar(25),
> address_State varchar(25),
> address_Zipcode varchar(6),
> ) OPTIONS(UPDATABLE 'TRUE');
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3275) remaining work should always be canceled
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3275?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3275.
-----------------------------------
Resolution: Done
generalized the cancellation logic to always cancel source work that was not completed.
> remaining work should always be canceled
> ----------------------------------------
>
> Key: TEIID-3275
> URL: https://issues.jboss.org/browse/TEIID-3275
> Project: Teiid
> Issue Type: Quality Risk
> Components: JDBC Connector, Query Engine
> Affects Versions: 7.0
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> When a result is partially read we typically send a cancel to source queries that remain open for faster cleanup. Depending upon the plan and the usage of max rows this may not happen in all circumstances. We should ensure that cancel is always called on partially read results.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3276) Data result not correct when joinning subquery over a date field
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3276?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3276:
---------------------------------------
That does not look like the 8.9 plan. Is the issue occurring 8.9 and older versions? If it's specific to 8.9, can you confirm if it is the same as https://issues.jboss.org/browse/TEIID-3263 by using 8.9.1?
> Data result not correct when joinning subquery over a date field
> ----------------------------------------------------------------
>
> Key: TEIID-3276
> URL: https://issues.jboss.org/browse/TEIID-3276
> Project: Teiid
> Issue Type: Bug
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> Hello,
> I'm using Teiid 8.9.0 To execute the below query and the execution is done without the problem; the issue is that I'm getting different results when executing the same query over mysql
> The original query is much bigger but i tried to simplify it to isolate the problem.
> SELECT DISTINCT
> WEEK(contactcalldetail.startdatetime) AS contactcalldetail_startdatet,
> sessionid_Sub.contactcalldetail_sessionid
> AS contactcalldetail_sessionid
> FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail
> LEFT JOIN /* optional */
> (SELECT COUNT(DISTINCT contactcalldetail_sub.sessionid)
> AS contactcalldetail_sessionid,
> WEEK(contactcalldetail_sub.startdatetime)
> AS contactcalldetail_startdatet
> FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail_sub
> WHERE (((contactcalldetail_sub.contacttype IN (1))))
> AND ((((contactcalldetail_sub.connecttime >= 300))))
> GROUP BY WEEK(contactcalldetail_sub.startdatetime)) sessionid_Sub
> ON (WEEK(contactcalldetail.startdatetime) =
> sessionid_Sub.contactcalldetail_startdatet)
> WHERE (((contactcalldetail.contacttype IN (1))))
> LIMIT 0, 10
> Here is Teiid execution plan :
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 1</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Child 0"><node name="SortNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 1</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 13</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="JoinNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 11</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>expr (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 7</value><value>Node Blocks: 5</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.startdatetime AS c_0, WEEK(g_0.startdatetime) AS c_1 FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0 WHERE g_0.contacttype = 1 ORDER BY c_1</value></property><property name="Model Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 8</value><value>Node Cumulative Process Time: 15</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="GroupingNode"><property name="Output Columns"><value>gcol0 (integer)</value><value>agg0 (integer)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 14</value><value>Node Cumulative Next Batch Process Time: 8</value><value>Node Cumulative Process Time: 15</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>sessionid (long)</value></property><property name="Statistics"><value>Node Output Rows: 772</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 9</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_0.startdatetime, g_0.sessionid FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0 WHERE (g_0.contacttype = 1) AND (g_0.connecttime >= 300)</value></property><property name="Model Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property name="Grouping Columns"><value>WEEK(contactcalldetail_sub.startdatetime)</value></property><property name="Sort Mode"><value>false</value></property></node></property><property name="Select Columns"><value>anon_grp0.gcol0 AS contactcalldetail_startdatet</value><value>anon_grp0.agg0 AS contactcalldetail_sessionid</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>WEEK(contactcalldetail.startdatetime)=sessionid_Sub.contactcalldetail_startdatet</value></property></node></property><property name="Select Columns"><value>WEEK(contactcalldetail.startdatetime) AS contactcalldetail_startdatet</value><value>sessionid_Sub.contactcalldetail_sessionid AS contactcalldetail_sessionid</value></property></node></property><property name="Sort Mode"><value>DUP_REMOVE</value></property></node></property><property name="Row Offset"><value>null</value></property><property name="Row Limit"><value>10</value></property></node>
> Here are the 2 JDBC queries executed by Teiid:
> SELECT DISTINCT g_0.`startdatetime` AS c_0, week(g_0.`startdatetime`) AS c_1 FROM `db_cra`.`contactcalldetail` AS g_0 WHERE g_0.`contacttype` = 1 ORDER BY c_1
> SELECT g_0.`startdatetime`, g_0.`sessionid` FROM `db_cra`.`contactcalldetail` AS g_0 WHERE g_0.`contacttype` = 1 AND g_0.`connecttime` >= 300
> Teiid query execution result is :
> week countsession
> 36 null
> 37 328
> 38 384
> mysql execution result:
> week countsession
> 35 328
> 36 384
> 37 60
> You can notice that the week is incremented by one and the count result is scrambled
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3277) Setting SupportsLucenceSearch to false doesn't disable
by Van Halbert (JIRA)
Van Halbert created TEIID-3277:
----------------------------------
Summary: Setting SupportsLucenceSearch to false doesn't disable
Key: TEIID-3277
URL: https://issues.jboss.org/browse/TEIID-3277
Project: Teiid
Issue Type: Bug
Components: Misc. Connectors
Affects Versions: 8.10
Reporter: Van Halbert
Assignee: Van Halbert
Priority: Trivial
The negative action wasn't added to the execution factory to not enable the supports option, only true option was considered. Simple fix to add check for false and not enable option.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3223) An issue with time in WHERE clause - SQLServer
by Juraj Duráni (JIRA)
[ https://issues.jboss.org/browse/TEIID-3223?page=com.atlassian.jira.plugin... ]
Juraj Duráni reopened TEIID-3223:
---------------------------------
There is a problem with older SQL Server 2005. I want to cast the source (SQLS) data type 'datetime' to the teiid data type 'time' (dynamic VDB):
<metadata type="DDL"><![CDATA[
CREATE VIEW smalla_view(
DateValue date,
TimeValue time,
TimestampValue timestamp
) AS SELECT
convert(SmallA.DateValue, date) AS DateValue,
convert(SmallA.TimeValue, time) AS TimeValue,
SmallA.TimestampValue
FROM SmallA;
CREATE FOREIGN TABLE SmallA (
INTNUM integer,
BIGDECIMALVALUE bigdecimal,
BIGINTEGERVALUE biginteger,
BOOLEANVALUE boolean,
BYTENUM byte,
CHARVALUE char,
DATEVALUE timestamp,
DOUBLENUM double,
FLOATNUM float,
INTKEY integer,
LONGNUM long,
OBJECTVALUE object,
SHORTVALUE short,
STRINGKEY string,
STRINGNUM string,
TIMESTAMPVALUE timestamp,
TIMEVALUE timestamp
) OPTIONS(UPDATABLE 'TRUE');
]]> </metadata>
But the SQL query results in an exception:
SELECT TimeValue FROM BQT1.smalla_view WHERE TimeValue <= {t '21:00:00'} ==> Error executing statement(s): [Prepared Values: [] SQL: SELECT TOP 100 cast('1970-01-01 ' + convert(varchar, g_0.TIMEVALUE, 8) AS datetime) AS c_0 FROM SmallA g_0 WHERE cast('1970-01-01 ' + convert(varchar, g_0.TIMEVALUE, 8) AS datetime) <= cast('21:00:00' as time)]'. Originally TeiidProcessingException 'Type time is not a defined system type.' SQLServerException.java:196.
> An issue with time in WHERE clause - SQLServer
> ----------------------------------------------
>
> Key: TEIID-3223
> URL: https://issues.jboss.org/browse/TEIID-3223
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.1
> Environment: OS: fedora20
> arch: x86_64
> java: oracle 1.7
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 8.7.1, 8.10
>
>
> Teiid is not able to manage WHERE clause with time value.
> translator: sqlserver
> base DB: SQL Server 2012/2008/2005
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3276) Data result not correct when joinning subquery over a date field
by Mark Tawk (JIRA)
Mark Tawk created TEIID-3276:
--------------------------------
Summary: Data result not correct when joinning subquery over a date field
Key: TEIID-3276
URL: https://issues.jboss.org/browse/TEIID-3276
Project: Teiid
Issue Type: Bug
Reporter: Mark Tawk
Assignee: Steven Hawkins
Priority: Critical
Hello,
I'm using Teiid 8.9.0 To execute the below query and the execution is done without the problem; the issue is that I'm getting different results when executing the same query over mysql
The original query is much bigger but i tried to simplify it to isolate the problem.
SELECT DISTINCT
WEEK(contactcalldetail.startdatetime) AS contactcalldetail_startdatet,
sessionid_Sub.contactcalldetail_sessionid
AS contactcalldetail_sessionid
FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail
LEFT JOIN /* optional */
(SELECT COUNT(DISTINCT contactcalldetail_sub.sessionid)
AS contactcalldetail_sessionid,
WEEK(contactcalldetail_sub.startdatetime)
AS contactcalldetail_startdatet
FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail_sub
WHERE (((contactcalldetail_sub.contacttype IN (1))))
AND ((((contactcalldetail_sub.connecttime >= 300))))
GROUP BY WEEK(contactcalldetail_sub.startdatetime)) sessionid_Sub
ON (WEEK(contactcalldetail.startdatetime) =
sessionid_Sub.contactcalldetail_startdatet)
WHERE (((contactcalldetail.contacttype IN (1))))
LIMIT 0, 10
Here is Teiid execution plan :
<?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 1</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Child 0"><node name="SortNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 1</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 13</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="JoinNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 11</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>expr (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 7</value><value>Node Blocks: 5</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.startdatetime AS c_0, WEEK(g_0.startdatetime) AS c_1 FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0 WHERE g_0.contacttype = 1 ORDER BY c_1</value></property><property name="Model Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 8</value><value>Node Cumulative Process Time: 15</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="GroupingNode"><property name="Output Columns"><value>gcol0 (integer)</value><value>agg0 (integer)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 14</value><value>Node Cumulative Next Batch Process Time: 8</value><value>Node Cumulative Process Time: 15</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>sessionid (long)</value></property><property name="Statistics"><value>Node Output Rows: 772</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 9</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_0.startdatetime, g_0.sessionid FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0 WHERE (g_0.contacttype = 1) AND (g_0.connecttime >= 300)</value></property><property name="Model Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property name="Grouping Columns"><value>WEEK(contactcalldetail_sub.startdatetime)</value></property><property name="Sort Mode"><value>false</value></property></node></property><property name="Select Columns"><value>anon_grp0.gcol0 AS contactcalldetail_startdatet</value><value>anon_grp0.agg0 AS contactcalldetail_sessionid</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>WEEK(contactcalldetail.startdatetime)=sessionid_Sub.contactcalldetail_startdatet</value></property></node></property><property name="Select Columns"><value>WEEK(contactcalldetail.startdatetime) AS contactcalldetail_startdatet</value><value>sessionid_Sub.contactcalldetail_sessionid AS contactcalldetail_sessionid</value></property></node></property><property name="Sort Mode"><value>DUP_REMOVE</value></property></node></property><property name="Row Offset"><value>null</value></property><property name="Row Limit"><value>10</value></property></node>
Here are the 2 JDBC queries executed by Teiid:
SELECT DISTINCT g_0.`startdatetime` AS c_0, week(g_0.`startdatetime`) AS c_1 FROM `db_cra`.`contactcalldetail` AS g_0 WHERE g_0.`contacttype` = 1 ORDER BY c_1
SELECT g_0.`startdatetime`, g_0.`sessionid` FROM `db_cra`.`contactcalldetail` AS g_0 WHERE g_0.`contacttype` = 1 AND g_0.`connecttime` >= 300
Teiid query execution result is :
week countsession
36 null
37 328
38 384
mysql execution result:
week countsession
35 328
36 384
37 60
You can notice that the week is incremented by one and the count result is scrambled
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months