Disjoint by id

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Disjoint by id

Mark Stobbe
Hi,

I was wondering if this is correct:

I am using an fetch limit for some queries, though I also need to make sure
I add the prefetch rules to refresh the data. I read in the documentation
that i should use DISJOINT_BY_ID_PREFETCH_SEMANTICS, so I did. Now when I
look at the queries I am a little bit surprised because I see the same id
occur many times. Shouldn't this be purged before building the query?

Example query would be:

SELECT t0.*
FROM   djem_account t0
WHERE  ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 217 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 )
        OR ( t0.id = 219 );

Best regards,
Mark Stobbe
Reply | Threaded
Open this post in threaded view
|

Re: Disjoint by id

Andrus Adamchik
Hi Mark,

Yeah, doesn't look right. What version of Cayenne is this? And what is the relationship semantics? Is this a regular to-one?

Andrus

> On Nov 14, 2016, at 6:02 PM, Mark Stobbe <[hidden email]> wrote:
>
> Hi,
>
> I was wondering if this is correct:
>
> I am using an fetch limit for some queries, though I also need to make sure
> I add the prefetch rules to refresh the data. I read in the documentation
> that i should use DISJOINT_BY_ID_PREFETCH_SEMANTICS, so I did. Now when I
> look at the queries I am a little bit surprised because I see the same id
> occur many times. Shouldn't this be purged before building the query?
>
> Example query would be:
>
> SELECT t0.*
> FROM   djem_account t0
> WHERE  ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 217 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 )
>        OR ( t0.id = 219 );
>
> Best regards,
> Mark Stobbe

Reply | Threaded
Open this post in threaded view
|

Re: Disjoint by id

Mark Stobbe
Hi Andrus,

Version 3.2M1. Relationship would be a normal to-one yes. I am executing a
query for the first 25 records of "djem_user".
Configuration is the following:

<db-entity name="djem_account">
  <db-attribute name="id" type="BIGINT" isMandatory="true"
isPrimaryKey="true" />
</db-entity>

<db-entity name="djem_user">
  <db-attribute name="id" type="BIGINT" isMandatory="true"
isPrimaryKey="true" />
  <db-attribute name="fk_acco" type="BIGINT" />
</db-entity>

<db-relationship name="user_acco" source="djem_user" target="djem_account"
toMany="false">
  <db-attribute-pair source="fk_acco" target="id" />
</db-relationship>

<db-relationship name="acco_user" source="djem_account" target="djem_user"
toMany="true">
  <db-attribute-pair source="id" target="fk_acco" />
</db-relationship>

Mark

On Mon, Nov 14, 2016 at 6:17 PM, Andrus Adamchik <[hidden email]>
wrote:

> Hi Mark,
>
> Yeah, doesn't look right. What version of Cayenne is this? And what is the
> relationship semantics? Is this a regular to-one?
>
> Andrus
>
> > On Nov 14, 2016, at 6:02 PM, Mark Stobbe <[hidden email]> wrote:
> >
> > Hi,
> >
> > I was wondering if this is correct:
> >
> > I am using an fetch limit for some queries, though I also need to make
> sure
> > I add the prefetch rules to refresh the data. I read in the documentation
> > that i should use DISJOINT_BY_ID_PREFETCH_SEMANTICS, so I did. Now when
> I
> > look at the queries I am a little bit surprised because I see the same id
> > occur many times. Shouldn't this be purged before building the query?
> >
> > Example query would be:
> >
> > SELECT t0.*
> > FROM   djem_account t0
> > WHERE  ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 217 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 )
> >        OR ( t0.id = 219 );
> >
> > Best regards,
> > Mark Stobbe
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Disjoint by id

Andrus Adamchik
Could be a bug. I just opened a Jira [1] to investigate. From what I am seeing the current behavior is correct in respect to the returned result, but is certainly suboptimal.

Andrus

[1] https://issues.apache.org/jira/browse/CAY-2141

> On Nov 14, 2016, at 11:25 PM, Mark Stobbe <[hidden email]> wrote:
>
> Hi Andrus,
>
> Version 3.2M1. Relationship would be a normal to-one yes. I am executing a
> query for the first 25 records of "djem_user".
> Configuration is the following:
>
> <db-entity name="djem_account">
>  <db-attribute name="id" type="BIGINT" isMandatory="true"
> isPrimaryKey="true" />
> </db-entity>
>
> <db-entity name="djem_user">
>  <db-attribute name="id" type="BIGINT" isMandatory="true"
> isPrimaryKey="true" />
>  <db-attribute name="fk_acco" type="BIGINT" />
> </db-entity>
>
> <db-relationship name="user_acco" source="djem_user" target="djem_account"
> toMany="false">
>  <db-attribute-pair source="fk_acco" target="id" />
> </db-relationship>
>
> <db-relationship name="acco_user" source="djem_account" target="djem_user"
> toMany="true">
>  <db-attribute-pair source="id" target="fk_acco" />
> </db-relationship>
>
> Mark
>
> On Mon, Nov 14, 2016 at 6:17 PM, Andrus Adamchik <[hidden email]>
> wrote:
>
>> Hi Mark,
>>
>> Yeah, doesn't look right. What version of Cayenne is this? And what is the
>> relationship semantics? Is this a regular to-one?
>>
>> Andrus
>>
>>> On Nov 14, 2016, at 6:02 PM, Mark Stobbe <[hidden email]> wrote:
>>>
>>> Hi,
>>>
>>> I was wondering if this is correct:
>>>
>>> I am using an fetch limit for some queries, though I also need to make
>> sure
>>> I add the prefetch rules to refresh the data. I read in the documentation
>>> that i should use DISJOINT_BY_ID_PREFETCH_SEMANTICS, so I did. Now when
>> I
>>> look at the queries I am a little bit surprised because I see the same id
>>> occur many times. Shouldn't this be purged before building the query?
>>>
>>> Example query would be:
>>>
>>> SELECT t0.*
>>> FROM   djem_account t0
>>> WHERE  ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 217 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 )
>>>       OR ( t0.id = 219 );
>>>
>>> Best regards,
>>> Mark Stobbe
>>
>>

Reply | Threaded
Open this post in threaded view
|

Re: Disjoint by id

Mark Stobbe
I also noticed that it adds t0.id = NULL conditions. However, id is a
primary key so it doesn't really make sense to check for NULL.
Example query:

SELECT t0.*
FROM   djem_account t0
WHERE  ( t0.id IS NULL )
        OR ( t0.id = 208 )
        OR ( t0.id = 208 )
        OR ( t0.id = 208 )
        OR ( t0.id = 208 )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id = 208 )
        OR ( t0.id = 208 )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id = 208 )
        OR ( t0.id = 208 )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL )
        OR ( t0.id IS NULL );

On Tue, Nov 15, 2016 at 7:43 AM, Andrus Adamchik <[hidden email]>
wrote:

> Could be a bug. I just opened a Jira [1] to investigate. From what I am
> seeing the current behavior is correct in respect to the returned result,
> but is certainly suboptimal.
>
> Andrus
>
> [1] https://issues.apache.org/jira/browse/CAY-2141
>
> > On Nov 14, 2016, at 11:25 PM, Mark Stobbe <[hidden email]>
> wrote:
> >
> > Hi Andrus,
> >
> > Version 3.2M1. Relationship would be a normal to-one yes. I am executing
> a
> > query for the first 25 records of "djem_user".
> > Configuration is the following:
> >
> > <db-entity name="djem_account">
> >  <db-attribute name="id" type="BIGINT" isMandatory="true"
> > isPrimaryKey="true" />
> > </db-entity>
> >
> > <db-entity name="djem_user">
> >  <db-attribute name="id" type="BIGINT" isMandatory="true"
> > isPrimaryKey="true" />
> >  <db-attribute name="fk_acco" type="BIGINT" />
> > </db-entity>
> >
> > <db-relationship name="user_acco" source="djem_user"
> target="djem_account"
> > toMany="false">
> >  <db-attribute-pair source="fk_acco" target="id" />
> > </db-relationship>
> >
> > <db-relationship name="acco_user" source="djem_account"
> target="djem_user"
> > toMany="true">
> >  <db-attribute-pair source="id" target="fk_acco" />
> > </db-relationship>
> >
> > Mark
> >
> > On Mon, Nov 14, 2016 at 6:17 PM, Andrus Adamchik <[hidden email]
> >
> > wrote:
> >
> >> Hi Mark,
> >>
> >> Yeah, doesn't look right. What version of Cayenne is this? And what is
> the
> >> relationship semantics? Is this a regular to-one?
> >>
> >> Andrus
> >>
> >>> On Nov 14, 2016, at 6:02 PM, Mark Stobbe <[hidden email]>
> wrote:
> >>>
> >>> Hi,
> >>>
> >>> I was wondering if this is correct:
> >>>
> >>> I am using an fetch limit for some queries, though I also need to make
> >> sure
> >>> I add the prefetch rules to refresh the data. I read in the
> documentation
> >>> that i should use DISJOINT_BY_ID_PREFETCH_SEMANTICS, so I did. Now
> when
> >> I
> >>> look at the queries I am a little bit surprised because I see the same
> id
> >>> occur many times. Shouldn't this be purged before building the query?
> >>>
> >>> Example query would be:
> >>>
> >>> SELECT t0.*
> >>> FROM   djem_account t0
> >>> WHERE  ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 217 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 )
> >>>       OR ( t0.id = 219 );
> >>>
> >>> Best regards,
> >>> Mark Stobbe
> >>
> >>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Disjoint by id

Andrus Adamchik
Ugh

> On Nov 15, 2016, at 11:06 AM, Mark Stobbe <[hidden email]> wrote:
>
> I also noticed that it adds t0.id = NULL conditions. However, id is a
> primary key so it doesn't really make sense to check for NULL.
> Example query:
>
> SELECT t0.*
> FROM   djem_account t0
> WHERE  ( t0.id IS NULL )
>        OR ( t0.id = 208 )
>        OR ( t0.id = 208 )
>        OR ( t0.id = 208 )
>        OR ( t0.id = 208 )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id = 208 )
>        OR ( t0.id = 208 )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id = 208 )
>        OR ( t0.id = 208 )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL )
>        OR ( t0.id IS NULL );
>
> On Tue, Nov 15, 2016 at 7:43 AM, Andrus Adamchik <[hidden email]>
> wrote:
>
>> Could be a bug. I just opened a Jira [1] to investigate. From what I am
>> seeing the current behavior is correct in respect to the returned result,
>> but is certainly suboptimal.
>>
>> Andrus
>>
>> [1] https://issues.apache.org/jira/browse/CAY-2141
>>
>>> On Nov 14, 2016, at 11:25 PM, Mark Stobbe <[hidden email]>
>> wrote:
>>>
>>> Hi Andrus,
>>>
>>> Version 3.2M1. Relationship would be a normal to-one yes. I am executing
>> a
>>> query for the first 25 records of "djem_user".
>>> Configuration is the following:
>>>
>>> <db-entity name="djem_account">
>>> <db-attribute name="id" type="BIGINT" isMandatory="true"
>>> isPrimaryKey="true" />
>>> </db-entity>
>>>
>>> <db-entity name="djem_user">
>>> <db-attribute name="id" type="BIGINT" isMandatory="true"
>>> isPrimaryKey="true" />
>>> <db-attribute name="fk_acco" type="BIGINT" />
>>> </db-entity>
>>>
>>> <db-relationship name="user_acco" source="djem_user"
>> target="djem_account"
>>> toMany="false">
>>> <db-attribute-pair source="fk_acco" target="id" />
>>> </db-relationship>
>>>
>>> <db-relationship name="acco_user" source="djem_account"
>> target="djem_user"
>>> toMany="true">
>>> <db-attribute-pair source="id" target="fk_acco" />
>>> </db-relationship>
>>>
>>> Mark
>>>
>>> On Mon, Nov 14, 2016 at 6:17 PM, Andrus Adamchik <[hidden email]
>>>
>>> wrote:
>>>
>>>> Hi Mark,
>>>>
>>>> Yeah, doesn't look right. What version of Cayenne is this? And what is
>> the
>>>> relationship semantics? Is this a regular to-one?
>>>>
>>>> Andrus
>>>>
>>>>> On Nov 14, 2016, at 6:02 PM, Mark Stobbe <[hidden email]>
>> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I was wondering if this is correct:
>>>>>
>>>>> I am using an fetch limit for some queries, though I also need to make
>>>> sure
>>>>> I add the prefetch rules to refresh the data. I read in the
>> documentation
>>>>> that i should use DISJOINT_BY_ID_PREFETCH_SEMANTICS, so I did. Now
>> when
>>>> I
>>>>> look at the queries I am a little bit surprised because I see the same
>> id
>>>>> occur many times. Shouldn't this be purged before building the query?
>>>>>
>>>>> Example query would be:
>>>>>
>>>>> SELECT t0.*
>>>>> FROM   djem_account t0
>>>>> WHERE  ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 217 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 )
>>>>>      OR ( t0.id = 219 );
>>>>>
>>>>> Best regards,
>>>>> Mark Stobbe
>>>>
>>>>
>>
>>

Reply | Threaded
Open this post in threaded view
|

Re: Disjoint by id

Andrus Adamchik
In reply to this post by Andrus Adamchik
This has been fixed in 4.0 (Git master branch). I just committed corresponding pull request:

https://github.com/apache/cayenne/pull/136
https://issues.apache.org/jira/browse/CAY-2141 

Thanks,
Andrus

> On Nov 15, 2016, at 9:43 AM, Andrus Adamchik <[hidden email]> wrote:
>
> Could be a bug. I just opened a Jira [1] to investigate. From what I am seeing the current behavior is correct in respect to the returned result, but is certainly suboptimal.
>
> Andrus
>
> [1] https://issues.apache.org/jira/browse/CAY-2141
>
>> On Nov 14, 2016, at 11:25 PM, Mark Stobbe <[hidden email]> wrote:
>>
>> Hi Andrus,
>>
>> Version 3.2M1. Relationship would be a normal to-one yes. I am executing a
>> query for the first 25 records of "djem_user".
>> Configuration is the following:
>>
>> <db-entity name="djem_account">
>> <db-attribute name="id" type="BIGINT" isMandatory="true"
>> isPrimaryKey="true" />
>> </db-entity>
>>
>> <db-entity name="djem_user">
>> <db-attribute name="id" type="BIGINT" isMandatory="true"
>> isPrimaryKey="true" />
>> <db-attribute name="fk_acco" type="BIGINT" />
>> </db-entity>
>>
>> <db-relationship name="user_acco" source="djem_user" target="djem_account"
>> toMany="false">
>> <db-attribute-pair source="fk_acco" target="id" />
>> </db-relationship>
>>
>> <db-relationship name="acco_user" source="djem_account" target="djem_user"
>> toMany="true">
>> <db-attribute-pair source="id" target="fk_acco" />
>> </db-relationship>
>>
>> Mark
>>
>> On Mon, Nov 14, 2016 at 6:17 PM, Andrus Adamchik <[hidden email]>
>> wrote:
>>
>>> Hi Mark,
>>>
>>> Yeah, doesn't look right. What version of Cayenne is this? And what is the
>>> relationship semantics? Is this a regular to-one?
>>>
>>> Andrus
>>>
>>>> On Nov 14, 2016, at 6:02 PM, Mark Stobbe <[hidden email]> wrote:
>>>>
>>>> Hi,
>>>>
>>>> I was wondering if this is correct:
>>>>
>>>> I am using an fetch limit for some queries, though I also need to make
>>> sure
>>>> I add the prefetch rules to refresh the data. I read in the documentation
>>>> that i should use DISJOINT_BY_ID_PREFETCH_SEMANTICS, so I did. Now when
>>> I
>>>> look at the queries I am a little bit surprised because I see the same id
>>>> occur many times. Shouldn't this be purged before building the query?
>>>>
>>>> Example query would be:
>>>>
>>>> SELECT t0.*
>>>> FROM   djem_account t0
>>>> WHERE  ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 217 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 )
>>>>      OR ( t0.id = 219 );
>>>>
>>>> Best regards,
>>>> Mark Stobbe
>>>
>>>
>