[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4510?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-4510:
------------------------------------
Also, I didn't notice how you configured the "id" columns on the child tables. That column should not be selectable on the child. In order to get the ID, you must join with the parent object. If you use the import metadata option, that's the way it will be exposed. And from the unit test it has it defined as the following on the child tables:
id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE FALSE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
This needs to be clear in the doc's if the metadata is to be manually created.
> JDG Complex POJO 1-n relationship wrong results when joining parent and child table
> -----------------------------------------------------------------------------------
>
> Key: TEIID-4510
> URL: https://issues.jboss.org/browse/TEIID-4510
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.6.6_3
> Reporter: Jan Stastny
> Assignee: Van Halbert
> Priority: Blocker
>
> When using infinispan-cache-dsl translator to access JDG cache incorrect results are returned for joins between the parent and child table.
> POJO proto schema:
> {code:plain}
> package org.jboss.qe.jdg.remote.protobuf.complex;
> /* @Indexed */
> message Person {
>
> /* @IndexedField(index=true, store=false) */
> required int32 id = 1;
> /* @IndexedField */
> required string name = 2;
>
> /* @IndexedField */
> optional string email = 3;
>
> /* @IndexedField(index=true, store=false) */
> repeated PhoneNumber phones = 4;
> /* @Indexed */
> message Address {
> /* @IndexedField */
> required string Address = 1;
>
> /* @IndexedField(index=true, store=false) */
> required string City = 2;
>
> /* @IndexedField(index=true, store=false) */
> required string State = 3;
> }
> /* @IndexedField(index=true, store=false) */
> optional Address address = 5;
> }
> /* @Indexed */
> message PhoneNumber {
> /* @IndexedField */
> required string number = 1;
> }
> {code}
> Source metadata:
> {code:sql}
> CREATE FOREIGN TABLE Address (
> Address string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'address.Address', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> City string NOT NULL OPTIONS (NAMEINSOURCE 'address.City', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> State string NOT NULL OPTIONS (NAMEINSOURCE 'address.State', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'address')
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE Person (
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> name string NOT NULL OPTIONS (NAMEINSOURCE 'name', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> email string OPTIONS (NAMEINSOURCE 'email', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> CONSTRAINT PK_ID PRIMARY KEY(id)
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE PhoneNumber (
> number string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'phones.number', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'phones')
> ) OPTIONS (UPDATABLE TRUE);
> {code}
> Tested scenario:
> {code:sql}
> SELECT COUNT(*) FROM PhoneNumber WHERE id=1
> {code}
> returns 2.
> {code:sql}
> SELECT COUNT(*) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
> {code}
> or
> {code:sql}
> SELECT COUNT(*) FROM Person p INNER JOIN PhoneNumber pn ON p.id=pn.id WHERE p.id=1
> {code}
> both return only 1.
> NOTE: issue appears also when using only nested descriptors in the proto schema.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 2 months
[JBoss JIRA] (TEIID-4511) st_Intersection not implemented
by Andrej Šmigala (JIRA)
Andrej Šmigala created TEIID-4511:
-------------------------------------
Summary: st_Intersection not implemented
Key: TEIID-4511
URL: https://issues.jboss.org/browse/TEIID-4511
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 9.x
Reporter: Andrej Šmigala
Assignee: Steven Hawkins
Priority: Blocker
The st_Intersection function was supposed to be implemented as part of TEIID-4393, but was omitted
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 2 months
[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4510?page=com.atlassian.jira.plugin... ]
Steven Hawkins reassigned TEIID-4510:
-------------------------------------
Assignee: Van Halbert (was: Steven Hawkins)
> JDG Complex POJO 1-n relationship wrong results when joining parent and child table
> -----------------------------------------------------------------------------------
>
> Key: TEIID-4510
> URL: https://issues.jboss.org/browse/TEIID-4510
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.6.6_3
> Reporter: Jan Stastny
> Assignee: Van Halbert
> Priority: Blocker
>
> When using infinispan-cache-dsl translator to access JDG cache incorrect results are returned for joins between the parent and child table.
> POJO proto schema:
> {code:plain}
> package org.jboss.qe.jdg.remote.protobuf.complex;
> /* @Indexed */
> message Person {
>
> /* @IndexedField(index=true, store=false) */
> required int32 id = 1;
> /* @IndexedField */
> required string name = 2;
>
> /* @IndexedField */
> optional string email = 3;
>
> /* @IndexedField(index=true, store=false) */
> repeated PhoneNumber phones = 4;
> /* @Indexed */
> message Address {
> /* @IndexedField */
> required string Address = 1;
>
> /* @IndexedField(index=true, store=false) */
> required string City = 2;
>
> /* @IndexedField(index=true, store=false) */
> required string State = 3;
> }
> /* @IndexedField(index=true, store=false) */
> optional Address address = 5;
> }
> /* @Indexed */
> message PhoneNumber {
> /* @IndexedField */
> required string number = 1;
> }
> {code}
> Source metadata:
> {code:sql}
> CREATE FOREIGN TABLE Address (
> Address string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'address.Address', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> City string NOT NULL OPTIONS (NAMEINSOURCE 'address.City', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> State string NOT NULL OPTIONS (NAMEINSOURCE 'address.State', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'address')
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE Person (
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> name string NOT NULL OPTIONS (NAMEINSOURCE 'name', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> email string OPTIONS (NAMEINSOURCE 'email', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> CONSTRAINT PK_ID PRIMARY KEY(id)
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE PhoneNumber (
> number string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'phones.number', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'phones')
> ) OPTIONS (UPDATABLE TRUE);
> {code}
> Tested scenario:
> {code:sql}
> SELECT COUNT(*) FROM PhoneNumber WHERE id=1
> {code}
> returns 2.
> {code:sql}
> SELECT COUNT(*) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
> {code}
> or
> {code:sql}
> SELECT COUNT(*) FROM Person p INNER JOIN PhoneNumber pn ON p.id=pn.id WHERE p.id=1
> {code}
> both return only 1.
> NOTE: issue appears also when using only nested descriptors in the proto schema.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 2 months
[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4510?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-4510:
------------------------------------
I'm glad you didn't use metadata import in this case. But, unfortunately, on the surface (until I can dig in) this would appear to be a JDG issue, because the only difference in the testing is the JDG descriptor that is used to configure the JDG schema. And that's provided directly (as is) to JDG when the cache container is configured in the connector.
> JDG Complex POJO 1-n relationship wrong results when joining parent and child table
> -----------------------------------------------------------------------------------
>
> Key: TEIID-4510
> URL: https://issues.jboss.org/browse/TEIID-4510
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.6.6_3
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Blocker
>
> When using infinispan-cache-dsl translator to access JDG cache incorrect results are returned for joins between the parent and child table.
> POJO proto schema:
> {code:plain}
> package org.jboss.qe.jdg.remote.protobuf.complex;
> /* @Indexed */
> message Person {
>
> /* @IndexedField(index=true, store=false) */
> required int32 id = 1;
> /* @IndexedField */
> required string name = 2;
>
> /* @IndexedField */
> optional string email = 3;
>
> /* @IndexedField(index=true, store=false) */
> repeated PhoneNumber phones = 4;
> /* @Indexed */
> message Address {
> /* @IndexedField */
> required string Address = 1;
>
> /* @IndexedField(index=true, store=false) */
> required string City = 2;
>
> /* @IndexedField(index=true, store=false) */
> required string State = 3;
> }
> /* @IndexedField(index=true, store=false) */
> optional Address address = 5;
> }
> /* @Indexed */
> message PhoneNumber {
> /* @IndexedField */
> required string number = 1;
> }
> {code}
> Source metadata:
> {code:sql}
> CREATE FOREIGN TABLE Address (
> Address string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'address.Address', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> City string NOT NULL OPTIONS (NAMEINSOURCE 'address.City', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> State string NOT NULL OPTIONS (NAMEINSOURCE 'address.State', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'address')
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE Person (
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> name string NOT NULL OPTIONS (NAMEINSOURCE 'name', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> email string OPTIONS (NAMEINSOURCE 'email', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> CONSTRAINT PK_ID PRIMARY KEY(id)
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE PhoneNumber (
> number string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'phones.number', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'phones')
> ) OPTIONS (UPDATABLE TRUE);
> {code}
> Tested scenario:
> {code:sql}
> SELECT COUNT(*) FROM PhoneNumber WHERE id=1
> {code}
> returns 2.
> {code:sql}
> SELECT COUNT(*) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
> {code}
> or
> {code:sql}
> SELECT COUNT(*) FROM Person p INNER JOIN PhoneNumber pn ON p.id=pn.id WHERE p.id=1
> {code}
> both return only 1.
> NOTE: issue appears also when using only nested descriptors in the proto schema.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 2 months
[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-4510?page=com.atlassian.jira.plugin... ]
Jan Stastny commented on TEIID-4510:
------------------------------------
Hi [~vhalbert2],
I tried with Address message both nested and non-nested in the descriptor, with exactly same result.
I should probably note, that I am not using metadata import, in this scenario, but it also has no influence on the outcome (I've just checked). I don't think this issue is connected with nested/non-nested variants of the descriptor structure. The metadata are correct, but result returns only one row instead of many (2 in my case).
> JDG Complex POJO 1-n relationship wrong results when joining parent and child table
> -----------------------------------------------------------------------------------
>
> Key: TEIID-4510
> URL: https://issues.jboss.org/browse/TEIID-4510
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.6.6_3
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Blocker
>
> When using infinispan-cache-dsl translator to access JDG cache incorrect results are returned for joins between the parent and child table.
> POJO proto schema:
> {code:plain}
> package org.jboss.qe.jdg.remote.protobuf.complex;
> /* @Indexed */
> message Person {
>
> /* @IndexedField(index=true, store=false) */
> required int32 id = 1;
> /* @IndexedField */
> required string name = 2;
>
> /* @IndexedField */
> optional string email = 3;
>
> /* @IndexedField(index=true, store=false) */
> repeated PhoneNumber phones = 4;
> /* @Indexed */
> message Address {
> /* @IndexedField */
> required string Address = 1;
>
> /* @IndexedField(index=true, store=false) */
> required string City = 2;
>
> /* @IndexedField(index=true, store=false) */
> required string State = 3;
> }
> /* @IndexedField(index=true, store=false) */
> optional Address address = 5;
> }
> /* @Indexed */
> message PhoneNumber {
> /* @IndexedField */
> required string number = 1;
> }
> {code}
> Source metadata:
> {code:sql}
> CREATE FOREIGN TABLE Address (
> Address string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'address.Address', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> City string NOT NULL OPTIONS (NAMEINSOURCE 'address.City', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> State string NOT NULL OPTIONS (NAMEINSOURCE 'address.State', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'address')
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE Person (
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> name string NOT NULL OPTIONS (NAMEINSOURCE 'name', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> email string OPTIONS (NAMEINSOURCE 'email', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> CONSTRAINT PK_ID PRIMARY KEY(id)
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE PhoneNumber (
> number string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'phones.number', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'phones')
> ) OPTIONS (UPDATABLE TRUE);
> {code}
> Tested scenario:
> {code:sql}
> SELECT COUNT(*) FROM PhoneNumber WHERE id=1
> {code}
> returns 2.
> {code:sql}
> SELECT COUNT(*) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
> {code}
> or
> {code:sql}
> SELECT COUNT(*) FROM Person p INNER JOIN PhoneNumber pn ON p.id=pn.id WHERE p.id=1
> {code}
> both return only 1.
> NOTE: issue appears also when using only nested descriptors in the proto schema.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 2 months
[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-4510?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-4510:
------------------------------------
Based on the descriptor you posted above, the PhoneNumber message isn't nested, its defined outside of the Person message. But is the issue only occuring when Address message is nested?
> JDG Complex POJO 1-n relationship wrong results when joining parent and child table
> -----------------------------------------------------------------------------------
>
> Key: TEIID-4510
> URL: https://issues.jboss.org/browse/TEIID-4510
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.6.6_3
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Blocker
>
> When using infinispan-cache-dsl translator to access JDG cache incorrect results are returned for joins between the parent and child table.
> POJO proto schema:
> {code:plain}
> package org.jboss.qe.jdg.remote.protobuf.complex;
> /* @Indexed */
> message Person {
>
> /* @IndexedField(index=true, store=false) */
> required int32 id = 1;
> /* @IndexedField */
> required string name = 2;
>
> /* @IndexedField */
> optional string email = 3;
>
> /* @IndexedField(index=true, store=false) */
> repeated PhoneNumber phones = 4;
> /* @Indexed */
> message Address {
> /* @IndexedField */
> required string Address = 1;
>
> /* @IndexedField(index=true, store=false) */
> required string City = 2;
>
> /* @IndexedField(index=true, store=false) */
> required string State = 3;
> }
> /* @IndexedField(index=true, store=false) */
> optional Address address = 5;
> }
> /* @Indexed */
> message PhoneNumber {
> /* @IndexedField */
> required string number = 1;
> }
> {code}
> Source metadata:
> {code:sql}
> CREATE FOREIGN TABLE Address (
> Address string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'address.Address', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> City string NOT NULL OPTIONS (NAMEINSOURCE 'address.City', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> State string NOT NULL OPTIONS (NAMEINSOURCE 'address.State', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'address')
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE Person (
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> name string NOT NULL OPTIONS (NAMEINSOURCE 'name', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> email string OPTIONS (NAMEINSOURCE 'email', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> CONSTRAINT PK_ID PRIMARY KEY(id)
> ) OPTIONS (UPDATABLE TRUE);
> CREATE FOREIGN TABLE PhoneNumber (
> number string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'phones.number', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
> id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
> CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'phones')
> ) OPTIONS (UPDATABLE TRUE);
> {code}
> Tested scenario:
> {code:sql}
> SELECT COUNT(*) FROM PhoneNumber WHERE id=1
> {code}
> returns 2.
> {code:sql}
> SELECT COUNT(*) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
> {code}
> or
> {code:sql}
> SELECT COUNT(*) FROM Person p INNER JOIN PhoneNumber pn ON p.id=pn.id WHERE p.id=1
> {code}
> both return only 1.
> NOTE: issue appears also when using only nested descriptors in the proto schema.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 2 months
[JBoss JIRA] (TEIID-4284) Implement Salesforce Bulk API for SELECTS to Salesforce Connector
by sameer P (JIRA)
[ https://issues.jboss.org/browse/TEIID-4284?page=com.atlassian.jira.plugin... ]
sameer P edited comment on TEIID-4284 at 10/13/16 6:28 AM:
-----------------------------------------------------------
[~shawkins], I checked the batch status after the second execution of the same query and it had mainly two batches: one with the *Not Processed* state (original batch) and the other with *COMPLETED* status (subsequent batch). And from salesforce, the status *Not Processed* means:
{code:java}
The batch won't be processed. This state is assigned when a job is aborted while the batch is queued. For bulk queries, if the job has PK chunking enabled, this state is assigned to the original batch that contains the query when the subsequent batches are created. After the original batch is changed to this state, you can monitor the subsequent batches and retrieve each batch's results when it's completed.
{code}
So, may be instead of waiting for just *COMPLETED* status, we should also check the status of subsequent batches in the Job?
was (Author: sameerp):
[~shawkins], I checked the bulk status after the second execution of the same query and it had mainly two bulks : one with the *Not Processed* state (original batch) and the other with *COMPLETED* status (subsequent batch). And from salesforce, the status *Not Processed* means:
{code:java}
The batch won't be processed. This state is assigned when a job is aborted while the batch is queued. For bulk queries, if the job has PK chunking enabled, this state is assigned to the original batch that contains the query when the subsequent batches are created. After the original batch is changed to this state, you can monitor the subsequent batches and retrieve each batch's results when it's completed.
{code}
So, may be instead of waiting for just *COMPLETED* status, we should also check the status of subsequent bulks in the Job?
> Implement Salesforce Bulk API for SELECTS to Salesforce Connector
> -----------------------------------------------------------------
>
> Key: TEIID-4284
> URL: https://issues.jboss.org/browse/TEIID-4284
> Project: Teiid
> Issue Type: Feature Request
> Components: Salesforce Connector
> Affects Versions: 8.13.5
> Environment: With Salesforce datasource
> Reporter: sameer P
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> There is some huge data (many GBs) in the Salesforce which has around 1.5 million rows and doing some simple select * on it fails with QUERY_TIMEOUT.
> The salesforce guys suggested to try Bulk API for select with PK chunking as stated in https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asy... .
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 2 months
[JBoss JIRA] (TEIID-4510) JDG Complex POJO 1-n relationship wrong results when joining parent and child table
by Jan Stastny (JIRA)
Jan Stastny created TEIID-4510:
----------------------------------
Summary: JDG Complex POJO 1-n relationship wrong results when joining parent and child table
Key: TEIID-4510
URL: https://issues.jboss.org/browse/TEIID-4510
Project: Teiid
Issue Type: Bug
Components: Misc. Connectors
Affects Versions: 8.12.6.6_3
Reporter: Jan Stastny
Assignee: Steven Hawkins
Priority: Blocker
When using infinispan-cache-dsl translator to access JDG cache incorrect results are returned for joins between the parent and child table.
POJO proto schema:
{code:plain}
package org.jboss.qe.jdg.remote.protobuf.complex;
/* @Indexed */
message Person {
/* @IndexedField(index=true, store=false) */
required int32 id = 1;
/* @IndexedField */
required string name = 2;
/* @IndexedField */
optional string email = 3;
/* @IndexedField(index=true, store=false) */
repeated PhoneNumber phones = 4;
/* @Indexed */
message Address {
/* @IndexedField */
required string Address = 1;
/* @IndexedField(index=true, store=false) */
required string City = 2;
/* @IndexedField(index=true, store=false) */
required string State = 3;
}
/* @IndexedField(index=true, store=false) */
optional Address address = 5;
}
/* @Indexed */
message PhoneNumber {
/* @IndexedField */
required string number = 1;
}
{code}
Source metadata:
{code:sql}
CREATE FOREIGN TABLE Address (
Address string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'address.Address', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
City string NOT NULL OPTIONS (NAMEINSOURCE 'address.City', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
State string NOT NULL OPTIONS (NAMEINSOURCE 'address.State', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'address')
) OPTIONS (UPDATABLE TRUE);
CREATE FOREIGN TABLE Person (
id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
name string NOT NULL OPTIONS (NAMEINSOURCE 'name', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
email string OPTIONS (NAMEINSOURCE 'email', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
CONSTRAINT PK_ID PRIMARY KEY(id)
) OPTIONS (UPDATABLE TRUE);
CREATE FOREIGN TABLE PhoneNumber (
number string NOT NULL PRIMARY KEY OPTIONS (NAMEINSOURCE 'phones.number', SEARCHABLE 'Searchable', NATIVE_TYPE 'java.lang.String'),
id integer NOT NULL OPTIONS (NAMEINSOURCE 'id', SELECTABLE TRUE, SEARCHABLE 'Searchable', NATIVE_TYPE 'int'),
CONSTRAINT FK_PERSON FOREIGN KEY(id) REFERENCES Person (id) OPTIONS (NAMEINSOURCE 'phones')
) OPTIONS (UPDATABLE TRUE);
{code}
Tested scenario:
{code:sql}
SELECT COUNT(*) FROM PhoneNumber WHERE id=1
{code}
returns 2.
{code:sql}
SELECT COUNT(*) FROM Person p, PhoneNumber pn WHERE p.id=pn.id AND p.id=1
{code}
or
{code:sql}
SELECT COUNT(*) FROM Person p INNER JOIN PhoneNumber pn ON p.id=pn.id WHERE p.id=1
{code}
both return only 1.
NOTE: issue appears also when using only nested descriptors in the proto schema.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 2 months
[JBoss JIRA] (TEIID-4509) Implement transaction detection for procedures
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-4509:
-------------------------------------
Summary: Implement transaction detection for procedures
Key: TEIID-4509
URL: https://issues.jboss.org/browse/TEIID-4509
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 9.2
Building on TEIID-4076 we should also detect when transactions are needed with virtual procedures.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 2 months