[JBoss JIRA] (TEIID-5440) Couchbase translator - LET usage prevents index scan
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5440?page=com.atlassian.jira.plugin... ]
Work on TEIID-5440 started by Steven Hawkins.
---------------------------------------------
> Couchbase translator - LET usage prevents index scan
> ----------------------------------------------------
>
> Key: TEIID-5440
> URL: https://issues.jboss.org/browse/TEIID-5440
> Project: Teiid
> Issue Type: Quality Risk
> Components: Misc. Connectors
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
>
> Couchbase translator excesively uses LET clause(1) just for purpose of naming columns.
> That's not correct usage of LET clause and affects query execution in couchbase.
> Simple query
> {code:sql}
> SELECT IntKey FROM SmallA;
> {code}
> is being pushed as
> {code:sql}
> SELECT `$cb_c1_IntKey` c1 FROM `dvqe` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`intkey` WHERE `$cb_t1`.`type` = 'smalla'
> {code}
> Which is fine and index on `type` column is scanned on couchbase side.
> But when I issue query
> {code:sql}
> SELECT * FROM SmallA;
> {code}
> it is pushed as
> {code:sql}
> SELECT
> `$cb_c1_documentID` c1,
> `$cb_c2_FloatNum` c2,
> `$cb_c3_IntKey` c3,
> `$cb_c4_BigIntegerValue` c4,
> `$cb_c5_StringKey` c5,
> `$cb_c6_CharValue` c6,
> `$cb_c7_LongNum` c7,
> `$cb_c8_type` c8,
> `$cb_c9_DoubleNum` c9,
> `$cb_c10_ObjectValue` c10,
> `$cb_c11_ShortValue` c11,
> `$cb_c12_BigDecimalValue` c12,
> `$cb_c13_DateValue` c13,
> `$cb_c14_BooleanValue` c14,
> `$cb_c15_TimestampValue` c15,
> `$cb_c16_ByteNum` c16,
> `$cb_c17_StringNum` c17,
> `$cb_c18_TimeValue` c18,
> `$cb_c19_IntNum` c19
> FROM `dvqe` `$cb_t1`
> LET `$cb_c1_documentID` = META(`$cb_t1`).id,
> `$cb_c2_FloatNum` = `$cb_t1`.`floatnum`,
> `$cb_c3_IntKey` = `$cb_t1`.`intkey`,
> `$cb_c4_BigIntegerValue` = `$cb_t1`.`bigintegervalue`,
> `$cb_c5_StringKey` = `$cb_t1`.`stringkey`,
> `$cb_c6_CharValue` = `$cb_t1`.`charvalue`,
> `$cb_c7_LongNum` = `$cb_t1`.`longnum`,
> `$cb_c8_type` = `$cb_t1`.`type`,
> `$cb_c9_DoubleNum` = `$cb_t1`.`doublenum`,
> `$cb_c10_ObjectValue` = `$cb_t1`.`objectvalue`,
> `$cb_c11_ShortValue` = `$cb_t1`.`shortvalue`,
> `$cb_c12_BigDecimalValue` = `$cb_t1`.`bigdecimalvalue`,
> `$cb_c13_DateValue` = `$cb_t1`.`datevalue`,
> `$cb_c14_BooleanValue` = `$cb_t1`.`booleanvalue`,
> `$cb_c15_TimestampValue` = `$cb_t1`.`timestampvalue`,
> `$cb_c16_ByteNum` = `$cb_t1`.`bytenum`,
> `$cb_c17_StringNum` = `$cb_t1`.`stringnum`,
> `$cb_c18_TimeValue` = `$cb_t1`.`timevalue`,
> `$cb_c19_IntNum` = `$cb_t1`.`intnum`
> WHERE `$cb_c8_type` = 'smalla'
> {code}
> The way how WHERE criteria is being pushed (either column or LET variable reference) affects execution plan and prevents index scan in the latter case. (WHERE `$cb_t1`.`type` = 'smalla' vs. WHERE `$cb_c8_type` = 'smalla').
> (1) https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-la...
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 4 months
[JBoss JIRA] (TEIID-5426) For Each processing for instead of triggers should materialize change set before processing
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5426?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5426:
---------------------------------
Fix Version/s: 8.12.15.6_4
> For Each processing for instead of triggers should materialize change set before processing
> -------------------------------------------------------------------------------------------
>
> Key: TEIID-5426
> URL: https://issues.jboss.org/browse/TEIID-5426
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 11.0
> Reporter: Ramesh Reddy
> Assignee: Steven Hawkins
> Fix For: 11.1, 11.0.1, 10.3.3, 8.12.15.6_4
>
>
> Currently when a view is defined with Inserts, Updates and Deletes with Instead of Triggers with "FOR EACH" row processing, the change set (the rows that are changing for the statement) typically is like
> {code}
> loop on (select ids from (view) where predicate)
> begin
> procedure body
> end
> {code}
> where there is a possibility that "procedure body" will change underlying tables such that the "loop on" query results could vary during the processing. Like concurrent updates. This could be avoided by materializing the ids in #temp table before processing any of the rows.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 4 months
[JBoss JIRA] (TEIID-5440) Couchbase translator - LET usage prevents index scan
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-5440?page=com.atlassian.jira.plugin... ]
Jan Stastny commented on TEIID-5440:
------------------------------------
Probably depends on whether the column in where criteria is also projected. That's why the first query is pushed differently.
> Couchbase translator - LET usage prevents index scan
> ----------------------------------------------------
>
> Key: TEIID-5440
> URL: https://issues.jboss.org/browse/TEIID-5440
> Project: Teiid
> Issue Type: Quality Risk
> Components: Misc. Connectors
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
>
> Couchbase translator excesively uses LET clause(1) just for purpose of naming columns.
> That's not correct usage of LET clause and affects query execution in couchbase.
> Simple query
> {code:sql}
> SELECT IntKey FROM SmallA;
> {code}
> is being pushed as
> {code:sql}
> SELECT `$cb_c1_IntKey` c1 FROM `dvqe` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`intkey` WHERE `$cb_t1`.`type` = 'smalla'
> {code}
> Which is fine and index on `type` column is scanned on couchbase side.
> But when I issue query
> {code:sql}
> SELECT * FROM SmallA;
> {code}
> it is pushed as
> {code:sql}
> SELECT
> `$cb_c1_documentID` c1,
> `$cb_c2_FloatNum` c2,
> `$cb_c3_IntKey` c3,
> `$cb_c4_BigIntegerValue` c4,
> `$cb_c5_StringKey` c5,
> `$cb_c6_CharValue` c6,
> `$cb_c7_LongNum` c7,
> `$cb_c8_type` c8,
> `$cb_c9_DoubleNum` c9,
> `$cb_c10_ObjectValue` c10,
> `$cb_c11_ShortValue` c11,
> `$cb_c12_BigDecimalValue` c12,
> `$cb_c13_DateValue` c13,
> `$cb_c14_BooleanValue` c14,
> `$cb_c15_TimestampValue` c15,
> `$cb_c16_ByteNum` c16,
> `$cb_c17_StringNum` c17,
> `$cb_c18_TimeValue` c18,
> `$cb_c19_IntNum` c19
> FROM `dvqe` `$cb_t1`
> LET `$cb_c1_documentID` = META(`$cb_t1`).id,
> `$cb_c2_FloatNum` = `$cb_t1`.`floatnum`,
> `$cb_c3_IntKey` = `$cb_t1`.`intkey`,
> `$cb_c4_BigIntegerValue` = `$cb_t1`.`bigintegervalue`,
> `$cb_c5_StringKey` = `$cb_t1`.`stringkey`,
> `$cb_c6_CharValue` = `$cb_t1`.`charvalue`,
> `$cb_c7_LongNum` = `$cb_t1`.`longnum`,
> `$cb_c8_type` = `$cb_t1`.`type`,
> `$cb_c9_DoubleNum` = `$cb_t1`.`doublenum`,
> `$cb_c10_ObjectValue` = `$cb_t1`.`objectvalue`,
> `$cb_c11_ShortValue` = `$cb_t1`.`shortvalue`,
> `$cb_c12_BigDecimalValue` = `$cb_t1`.`bigdecimalvalue`,
> `$cb_c13_DateValue` = `$cb_t1`.`datevalue`,
> `$cb_c14_BooleanValue` = `$cb_t1`.`booleanvalue`,
> `$cb_c15_TimestampValue` = `$cb_t1`.`timestampvalue`,
> `$cb_c16_ByteNum` = `$cb_t1`.`bytenum`,
> `$cb_c17_StringNum` = `$cb_t1`.`stringnum`,
> `$cb_c18_TimeValue` = `$cb_t1`.`timevalue`,
> `$cb_c19_IntNum` = `$cb_t1`.`intnum`
> WHERE `$cb_c8_type` = 'smalla'
> {code}
> The way how WHERE criteria is being pushed (either column or LET variable reference) affects execution plan and prevents index scan in the latter case. (WHERE `$cb_t1`.`type` = 'smalla' vs. WHERE `$cb_c8_type` = 'smalla').
> (1) https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-la...
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 4 months
[JBoss JIRA] (TEIID-5440) Couchbase translator - LET usage prevents index scan
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-5440?page=com.atlassian.jira.plugin... ]
Jan Stastny commented on TEIID-5440:
------------------------------------
The version of couchbase is 4.5.1.
> Couchbase translator - LET usage prevents index scan
> ----------------------------------------------------
>
> Key: TEIID-5440
> URL: https://issues.jboss.org/browse/TEIID-5440
> Project: Teiid
> Issue Type: Quality Risk
> Components: Misc. Connectors
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
>
> Couchbase translator excesively uses LET clause(1) just for purpose of naming columns.
> That's not correct usage of LET clause and affects query execution in couchbase.
> Simple query
> {code:sql}
> SELECT IntKey FROM SmallA;
> {code}
> is being pushed as
> {code:sql}
> SELECT `$cb_c1_IntKey` c1 FROM `dvqe` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`intkey` WHERE `$cb_t1`.`type` = 'smalla'
> {code}
> Which is fine and index on `type` column is scanned on couchbase side.
> But when I issue query
> {code:sql}
> SELECT * FROM SmallA;
> {code}
> it is pushed as
> {code:sql}
> SELECT
> `$cb_c1_documentID` c1,
> `$cb_c2_FloatNum` c2,
> `$cb_c3_IntKey` c3,
> `$cb_c4_BigIntegerValue` c4,
> `$cb_c5_StringKey` c5,
> `$cb_c6_CharValue` c6,
> `$cb_c7_LongNum` c7,
> `$cb_c8_type` c8,
> `$cb_c9_DoubleNum` c9,
> `$cb_c10_ObjectValue` c10,
> `$cb_c11_ShortValue` c11,
> `$cb_c12_BigDecimalValue` c12,
> `$cb_c13_DateValue` c13,
> `$cb_c14_BooleanValue` c14,
> `$cb_c15_TimestampValue` c15,
> `$cb_c16_ByteNum` c16,
> `$cb_c17_StringNum` c17,
> `$cb_c18_TimeValue` c18,
> `$cb_c19_IntNum` c19
> FROM `dvqe` `$cb_t1`
> LET `$cb_c1_documentID` = META(`$cb_t1`).id,
> `$cb_c2_FloatNum` = `$cb_t1`.`floatnum`,
> `$cb_c3_IntKey` = `$cb_t1`.`intkey`,
> `$cb_c4_BigIntegerValue` = `$cb_t1`.`bigintegervalue`,
> `$cb_c5_StringKey` = `$cb_t1`.`stringkey`,
> `$cb_c6_CharValue` = `$cb_t1`.`charvalue`,
> `$cb_c7_LongNum` = `$cb_t1`.`longnum`,
> `$cb_c8_type` = `$cb_t1`.`type`,
> `$cb_c9_DoubleNum` = `$cb_t1`.`doublenum`,
> `$cb_c10_ObjectValue` = `$cb_t1`.`objectvalue`,
> `$cb_c11_ShortValue` = `$cb_t1`.`shortvalue`,
> `$cb_c12_BigDecimalValue` = `$cb_t1`.`bigdecimalvalue`,
> `$cb_c13_DateValue` = `$cb_t1`.`datevalue`,
> `$cb_c14_BooleanValue` = `$cb_t1`.`booleanvalue`,
> `$cb_c15_TimestampValue` = `$cb_t1`.`timestampvalue`,
> `$cb_c16_ByteNum` = `$cb_t1`.`bytenum`,
> `$cb_c17_StringNum` = `$cb_t1`.`stringnum`,
> `$cb_c18_TimeValue` = `$cb_t1`.`timevalue`,
> `$cb_c19_IntNum` = `$cb_t1`.`intnum`
> WHERE `$cb_c8_type` = 'smalla'
> {code}
> The way how WHERE criteria is being pushed (either column or LET variable reference) affects execution plan and prevents index scan in the latter case. (WHERE `$cb_t1`.`type` = 'smalla' vs. WHERE `$cb_c8_type` = 'smalla').
> (1) https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-la...
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 4 months
[JBoss JIRA] (TEIID-5440) Couchbase translator - LET usage prevents index scan
by Jan Stastny (JIRA)
Jan Stastny created TEIID-5440:
----------------------------------
Summary: Couchbase translator - LET usage prevents index scan
Key: TEIID-5440
URL: https://issues.jboss.org/browse/TEIID-5440
Project: Teiid
Issue Type: Quality Risk
Components: Misc. Connectors
Affects Versions: 8.12.14.6_4
Reporter: Jan Stastny
Assignee: Steven Hawkins
Priority: Critical
Couchbase translator excesively uses LET clause(1) just for purpose of naming columns.
That's not correct usage of LET clause and affects query execution in couchbase.
Simple query
{code:sql}
SELECT IntKey FROM SmallA;
{code}
is being pushed as
{code:sql}
SELECT `$cb_c1_IntKey` c1 FROM `dvqe` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`intkey` WHERE `$cb_t1`.`type` = 'smalla'
{code}
Which is fine and index on `type` column is scanned on couchbase side.
But when I issue query
{code:sql}
SELECT * FROM SmallA;
{code}
it is pushed as
{code:sql}
SELECT
`$cb_c1_documentID` c1,
`$cb_c2_FloatNum` c2,
`$cb_c3_IntKey` c3,
`$cb_c4_BigIntegerValue` c4,
`$cb_c5_StringKey` c5,
`$cb_c6_CharValue` c6,
`$cb_c7_LongNum` c7,
`$cb_c8_type` c8,
`$cb_c9_DoubleNum` c9,
`$cb_c10_ObjectValue` c10,
`$cb_c11_ShortValue` c11,
`$cb_c12_BigDecimalValue` c12,
`$cb_c13_DateValue` c13,
`$cb_c14_BooleanValue` c14,
`$cb_c15_TimestampValue` c15,
`$cb_c16_ByteNum` c16,
`$cb_c17_StringNum` c17,
`$cb_c18_TimeValue` c18,
`$cb_c19_IntNum` c19
FROM `dvqe` `$cb_t1`
LET `$cb_c1_documentID` = META(`$cb_t1`).id,
`$cb_c2_FloatNum` = `$cb_t1`.`floatnum`,
`$cb_c3_IntKey` = `$cb_t1`.`intkey`,
`$cb_c4_BigIntegerValue` = `$cb_t1`.`bigintegervalue`,
`$cb_c5_StringKey` = `$cb_t1`.`stringkey`,
`$cb_c6_CharValue` = `$cb_t1`.`charvalue`,
`$cb_c7_LongNum` = `$cb_t1`.`longnum`,
`$cb_c8_type` = `$cb_t1`.`type`,
`$cb_c9_DoubleNum` = `$cb_t1`.`doublenum`,
`$cb_c10_ObjectValue` = `$cb_t1`.`objectvalue`,
`$cb_c11_ShortValue` = `$cb_t1`.`shortvalue`,
`$cb_c12_BigDecimalValue` = `$cb_t1`.`bigdecimalvalue`,
`$cb_c13_DateValue` = `$cb_t1`.`datevalue`,
`$cb_c14_BooleanValue` = `$cb_t1`.`booleanvalue`,
`$cb_c15_TimestampValue` = `$cb_t1`.`timestampvalue`,
`$cb_c16_ByteNum` = `$cb_t1`.`bytenum`,
`$cb_c17_StringNum` = `$cb_t1`.`stringnum`,
`$cb_c18_TimeValue` = `$cb_t1`.`timevalue`,
`$cb_c19_IntNum` = `$cb_t1`.`intnum`
WHERE `$cb_c8_type` = 'smalla'
{code}
The way how WHERE criteria is being pushed (either column or LET variable reference) affects execution plan and prevents index scan in the latter case. (WHERE `$cb_t1`.`type` = 'smalla' vs. WHERE `$cb_c8_type` = 'smalla').
(1) https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-la...
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 4 months
[JBoss JIRA] (TEIID-5394) Define how to utilize multiple vdbs
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5394?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5394:
-------------------------------------
>The drawback to the build time approach is that it's not straight-forward to flatten vdb.xml nor vdb.ddl contents. It's only straight-forward to flatten on a per schema >basis when those are captured as separate ddl files.
I was thinking to load each VDB file then, using MetadataFactory write back a single VDB out. Even otherwise, I think we can/should impose rules how vdb-import works, like it must have separate ddl files etc, as it is an advanced feature.
>From the teiid thorntail examples the project yaml is treated as a resource that is copied into the target/classes, which is then picked up by the vdb maven >plugin for inclusion in the .vdb. Is that not what you had in mind?
The yaml files are not part of the VDB artifact. There are two maven plugins at play there, one is VDB maven plugin that takes the VDB files and builds an archive artifact, another is Thorntail deployment/Uber jar plugin which picks up the main artifact of the project along with yaml files and builds an Uber Jar that is executable. If this Uber Jar needs to deploy into OpenShift, then third FMP plugin which does the docker wrapping and OpenShift specific configuration which is separate from Thorntail configuration.
> Define how to utilize multiple vdbs
> -----------------------------------
>
> Key: TEIID-5394
> URL: https://issues.jboss.org/browse/TEIID-5394
> Project: Teiid
> Issue Type: Sub-task
> Components: Build/Kits
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 11.1
>
>
> Perhaps to support vdb imports, we should define how a build can incorporate multiple vdbs.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 4 months
[JBoss JIRA] (TEIID-5434) count(*) returns different nr of rows compared to normal select
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5434?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5434:
---------------------------------
Fix Version/s: 8.12.15.6_4
> count(*) returns different nr of rows compared to normal select
> ---------------------------------------------------------------
>
> Key: TEIID-5434
> URL: https://issues.jboss.org/browse/TEIID-5434
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.1, 10.3.4, 11.0.2, 8.12.15.6_4
>
> Attachments: count_plan.txt, count_plan_debug_log.txt, count_plan_text.txt, select_plan.txt, select_plan_debug_log.txt, select_plan_text.txt
>
>
> Normal select return 162175 rows:
> {code:sql}
> select pa.ICUSessionID,11074, obr.DateTime, obr.varvalue, true, 15001866
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> count query return a count of 11573
> {code:sql}
> select count(*)
> from tv_retrieve_observrec obr
> join kd_matric.ExtrPatICUAdmissions epa on epa.PatientID = obr.admissionid
> join kd_matric.PatICUAdmissions pa on pa.ICUSessionID = epa.ICUSessionID and pa.BatchID < 1152
> where obr.VariableID = 15001866;
> {code}
> This was detected on a snapshot release of 10.3.3
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 4 months