Problem using to-many relationship on where clause

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

Problem using to-many relationship on where clause

Juan Manuel Diaz Lara
I am using 4.0.M5, previously this query worked as I expected:

                 ObjectSelect<Product> q = ObjectSelect.query(Product.class)                .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))                .and(Product.CODE.like("USBVGA");
                List<Product> results = q.select(context);

The objective is to get products for which we have stock in any location, StockCurrentCorporativo has the stock by location.
I restricted the example query to just one specific product and expect to get only one product in results, but got results.size() > 1, with all items being the same product (the same object on my tests).

The generated SQL from the log is:
    SELECT <<all columns from t0 table>>
    FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (t0.id = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)     ORDER BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']


EXISTENCIAS_CORPORATIVAS is a toMany relationship from Product and have type StockCurrentCorporativo, defined in _Product as :

     public static final Property<List<StockCurrentCorporativo>> EXISTENCIAS_CORPORATIVAS = Property.create("existenciasCorporativas", List.class);


 To be fair, I do not know when this started, the production app is working well, this is on a my development environment when I started to work for a new requirement.
I reviewed the mapping and it looks ok, any help will be appreciated.

Atte. Juan Manuel Díaz Lara
Reply | Threaded
Open this post in threaded view
|

Re: Problem using to-many relationship on where clause

Juan Manuel Diaz Lara
More details, this code should return only one product entity, but produces a result list with 4 items, all items being the same product entity (the purpose is get products which have stock in at leas one location):


ObjectSelect<Product> q = ObjectSelect.query(Product.class)  .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))  .and(Product.CODE.like("USBVGA");List<Product> results = q.select(context);

(this is could be expressed in plain SQL as a correlated query inside an EXISTS operation over table StockCurrentCorporativo)
Generates :

SELECT <<all columns from t0 table>>
FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (t0.id = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)     ORDER BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']

This produces 4 identical rows form database that end up on only one product entity but repeated 4 times on results list (results.size() = 4).
Changig the query to

ObjectSelect<Product> q = ObjectSelect.query(Product.class)  .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))  .and(Product.CODE.like("USBVGA");q.prefetch(Product.EXISTENCIAS_CORPORATIVAS.joint()); // <-- change
List<Product> results = q.select(context);


produces this SQL:

SELECT <<all columns from t0 table>> FROM public.products t0 LEFT JOIN public.stockcurrent_corporativo t1 ON (t0.id = t1.product) JOIN public.stockcurrent_corporativo t2 ON (t0.id = t2.product) WHERE (t2.units > ?) AND (t0.code LIKE ?) ORDER BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']


note that the two references to the relationship traslate to two joins. This SQL query produces 16 identical rows!!, that end up in only one product entity, but surprisingly, results list have only one item (product), and this is what is expected !!.
All this is annoying, or maybe I am not understanding well the query rules of cayenne.
Yes, I can make the query with SQLTemplate, but would like to use only object oriented queries. Could it be done ? In general. how to include conditions over the many size of relationships ?.
Please help.

Atte. Juan Manuel Díaz Lara

    On Sunday, November 26, 2017, 8:57:50 PM CST, Juan Manuel Diaz Lara <[hidden email]> wrote:  
 
 I am using 4.0.M5, previously this query worked as I expected:

                 ObjectSelect<Product> q = ObjectSelect.query(Product.class)                .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))                .and(Product.CODE.like("USBVGA");
                List<Product> results = q.select(context);

The objective is to get products for which we have stock in any location, StockCurrentCorporativo has the stock by location.
I restricted the example query to just one specific product and expect to get only one product in results, but got results.size() > 1, with all items being the same product (the same object on my tests).

The generated SQL from the log is:
    SELECT <<all columns from t0 table>>
    FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (t0.id = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)     ORDER BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']


EXISTENCIAS_CORPORATIVAS is a toMany relationship from Product and have type StockCurrentCorporativo, defined in _Product as :

     public static final Property<List<StockCurrentCorporativo>> EXISTENCIAS_CORPORATIVAS = Property.create("existenciasCorporativas", List.class);


 To be fair, I do not know when this started, the production app is working well, this is on a my development environment when I started to work for a new requirement.
I reviewed the mapping and it looks ok, any help will be appreciated.

Atte. Juan Manuel Díaz Lara  
Reply | Threaded
Open this post in threaded view
|

Re: Problem using to-many relationship on where clause

Lon Varscsak
I think the part that's missing is a "distinct".  I'm assuming there are 4
stockcurrent_corporativo rows for that one product, but if it had a
distinct it would be showing one.  I don't think that
ObjectSelect/FluentSelect have the ability to turn on/off distinct, but for
me, this always generates a "distinct" in the query.  I'm not sure why it
would be different for you.

-Lon

On Mon, Nov 27, 2017 at 8:45 PM, Juan Manuel Diaz Lara <
[hidden email]> wrote:

> More details, this code should return only one product entity, but
> produces a result list with 4 items, all items being the same product
> entity (the purpose is get products which have stock in at leas one
> location):
>
>
> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");List<Product> results =
> q.select(context);
>
> (this is could be expressed in plain SQL as a correlated query inside an
> EXISTS operation over table StockCurrentCorporativo)
> Generates :
>
> SELECT <<all columns from t0 table>>
> FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (t0.id
> = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)     ORDER BY
> t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>
> This produces 4 identical rows form database that end up on only one
> product entity but repeated 4 times on results list (results.size() = 4).
> Changig the query to
>
> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");q.prefetch(Product.
> EXISTENCIAS_CORPORATIVAS.joint()); // <-- change
> List<Product> results = q.select(context);
>
>
> produces this SQL:
>
> SELECT <<all columns from t0 table>> FROM public.products t0 LEFT JOIN
> public.stockcurrent_corporativo t1 ON (t0.id = t1.product) JOIN
> public.stockcurrent_corporativo t2 ON (t0.id = t2.product) WHERE
> (t2.units > ?) AND (t0.code LIKE ?) ORDER BY t0.name [bind: 1->units:0.0,
> 2->code:'USBVGA']
>
>
> note that the two references to the relationship traslate to two joins.
> This SQL query produces 16 identical rows!!, that end up in only one
> product entity, but surprisingly, results list have only one item
> (product), and this is what is expected !!.
> All this is annoying, or maybe I am not understanding well the query rules
> of cayenne.
> Yes, I can make the query with SQLTemplate, but would like to use only
> object oriented queries. Could it be done ? In general. how to include
> conditions over the many size of relationships ?.
> Please help.
>
> Atte. Juan Manuel Díaz Lara
>
>     On Sunday, November 26, 2017, 8:57:50 PM CST, Juan Manuel Diaz Lara
> <[hidden email]> wrote:
>
>  I am using 4.0.M5, previously this query worked as I expected:
>
>                  ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>              .where(Product.EXISTENCIAS_CORPORATIVAS.dot(
> StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");
>                 List<Product> results = q.select(context);
>
> The objective is to get products for which we have stock in any location,
> StockCurrentCorporativo has the stock by location.
> I restricted the example query to just one specific product and expect to
> get only one product in results, but got results.size() > 1, with all items
> being the same product (the same object on my tests).
>
> The generated SQL from the log is:
>     SELECT <<all columns from t0 table>>
>     FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (
> t0.id = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)     ORDER
> BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>
>
> EXISTENCIAS_CORPORATIVAS is a toMany relationship from Product and have
> type StockCurrentCorporativo, defined in _Product as :
>
>      public static final Property<List<StockCurrentCorporativo>>
> EXISTENCIAS_CORPORATIVAS = Property.create("existenciasCorporativas",
> List.class);
>
>
>  To be fair, I do not know when this started, the production app is
> working well, this is on a my development environment when I started to
> work for a new requirement.
> I reviewed the mapping and it looks ok, any help will be appreciated.
>
> Atte. Juan Manuel Díaz Lara
>
Reply | Threaded
Open this post in threaded view
|

Re: Problem using to-many relationship on where clause

Juan Manuel Diaz Lara
I am agree that this kind of query should generate a SQL DISTINCT, but the fact is that adding a joint()brings only one product on result even the sql generates 16 rows, so Cayenne is doing some kind of IN-MEMORY DISTINCT that is not done without a joint(), maybe a bug because there are two different executions paths. 
I will create a new mapping with only this two tables to recheck is not a problem of model or any other mistake. I'll report results.
thanks.

Atte. Juan Manuel Díaz Lara

    On Tuesday, November 28, 2017, 11:42:14 AM CST, Lon Varscsak <[hidden email]> wrote:  
 
 I think the part that's missing is a "distinct".  I'm assuming there are 4
stockcurrent_corporativo rows for that one product, but if it had a
distinct it would be showing one.  I don't think that
ObjectSelect/FluentSelect have the ability to turn on/off distinct, but for
me, this always generates a "distinct" in the query.  I'm not sure why it
would be different for you.

-Lon

On Mon, Nov 27, 2017 at 8:45 PM, Juan Manuel Diaz Lara <
[hidden email]> wrote:

> More details, this code should return only one product entity, but
> produces a result list with 4 items, all items being the same product
> entity (the purpose is get products which have stock in at leas one
> location):
>
>
> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");List<Product> results =
> q.select(context);
>
> (this is could be expressed in plain SQL as a correlated query inside an
> EXISTS operation over table StockCurrentCorporativo)
> Generates :
>
> SELECT <<all columns from t0 table>>
> FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (t0.id
> = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER BY
> t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>
> This produces 4 identical rows form database that end up on only one
> product entity but repeated 4 times on results list (results.size() = 4).
> Changig the query to
>
> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");q.prefetch(Product.
> EXISTENCIAS_CORPORATIVAS.joint()); // <-- change
> List<Product> results = q.select(context);
>
>
> produces this SQL:
>
> SELECT <<all columns from t0 table>> FROM public.products t0 LEFT JOIN
> public.stockcurrent_corporativo t1 ON (t0.id = t1.product) JOIN
> public.stockcurrent_corporativo t2 ON (t0.id = t2.product) WHERE
> (t2.units > ?) AND (t0.code LIKE ?) ORDER BY t0.name [bind: 1->units:0.0,
> 2->code:'USBVGA']
>
>
> note that the two references to the relationship traslate to two joins.
> This SQL query produces 16 identical rows!!, that end up in only one
> product entity, but surprisingly, results list have only one item
> (product), and this is what is expected !!.
> All this is annoying, or maybe I am not understanding well the query rules
> of cayenne.
> Yes, I can make the query with SQLTemplate, but would like to use only
> object oriented queries. Could it be done ? In general. how to include
> conditions over the many size of relationships ?.
> Please help.
>
> Atte. Juan Manuel Díaz Lara
>
>    On Sunday, November 26, 2017, 8:57:50 PM CST, Juan Manuel Diaz Lara
> <[hidden email]> wrote:
>
>  I am using 4.0.M5, previously this query worked as I expected:
>
>                  ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>              .where(Product.EXISTENCIAS_CORPORATIVAS.dot(
> StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");
>                List<Product> results = q.select(context);
>
> The objective is to get products for which we have stock in any location,
> StockCurrentCorporativo has the stock by location.
> I restricted the example query to just one specific product and expect to
> get only one product in results, but got results.size() > 1, with all items
> being the same product (the same object on my tests).
>
> The generated SQL from the log is:
>    SELECT <<all columns from t0 table>>
>    FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (
> t0.id = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER
> BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>
>
> EXISTENCIAS_CORPORATIVAS is a toMany relationship from Product and have
> type StockCurrentCorporativo, defined in _Product as :
>
>      public static final Property<List<StockCurrentCorporativo>>
> EXISTENCIAS_CORPORATIVAS = Property.create("existenciasCorporativas",
> List.class);
>
>
>  To be fair, I do not know when this started, the production app is
> working well, this is on a my development environment when I started to
> work for a new requirement.
> I reviewed the mapping and it looks ok, any help will be appreciated.
>
> Atte. Juan Manuel Díaz Lara
>  
Reply | Threaded
Open this post in threaded view
|

Re: Problem using to-many relationship on where clause

Juan Manuel Diaz Lara
I created a new project, with entities Products -toMany-> StockcurrentCorporativo, with same results.
Tracing code, I arrived to DefaultSelectTranslator#isUnsupportedForDistinct, this is the cause for cayenne not including a DISTINCT in generated SQL, because I have columns mapping to types "not supported for distinct clause". But I found a note on method DefaultSelectTranslator#isSuppressingDistinct that tell that cayenne should should do an in-memory  distinct operation. so this is not working well (a bug ?).
At least I conclude that:
1. Cayenne is deciding with types of columns can be used for DISTINCT, but this is not generally true, some db's ignore not sortable columns when used in DISTINCT queries. Maybe this decision should be delegated to the specific db translator.

2. There is a bug because cayenne must perform the in-memory distinct when suppressing DISTINCT in generated SQL.
I removed my longvarbinary columns and the problem gone, a DISTINCT SQL was generated, confirming the previous conclusions.
Well, this was a try, in reality, I can not simply remove my longvarbinary columns.
Does some body can report this as a bug ?.
Thanks.


Atte. Juan Manuel Díaz Lara

    On Tuesday, November 28, 2017, 2:04:43 PM CST, Juan Manuel Diaz Lara <[hidden email]> wrote:  
 
 I am agree that this kind of query should generate a SQL DISTINCT, but the fact is that adding a joint()brings only one product on result even the sql generates 16 rows, so Cayenne is doing some kind of IN-MEMORY DISTINCT that is not done without a joint(), maybe a bug because there are two different executions paths. 
I will create a new mapping with only this two tables to recheck is not a problem of model or any other mistake. I'll report results.
thanks.

Atte. Juan Manuel Díaz Lara

    On Tuesday, November 28, 2017, 11:42:14 AM CST, Lon Varscsak <[hidden email]> wrote: 
 
 I think the part that's missing is a "distinct".  I'm assuming there are 4
stockcurrent_corporativo rows for that one product, but if it had a
distinct it would be showing one.  I don't think that
ObjectSelect/FluentSelect have the ability to turn on/off distinct, but for
me, this always generates a "distinct" in the query.  I'm not sure why it
would be different for you.

-Lon

On Mon, Nov 27, 2017 at 8:45 PM, Juan Manuel Diaz Lara <
[hidden email]> wrote:

> More details, this code should return only one product entity, but
> produces a result list with 4 items, all items being the same product
> entity (the purpose is get products which have stock in at leas one
> location):
>
>
> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");List<Product> results =
> q.select(context);
>
> (this is could be expressed in plain SQL as a correlated query inside an
> EXISTS operation over table StockCurrentCorporativo)
> Generates :
>
> SELECT <<all columns from t0 table>>
> FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (t0.id
> = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER BY
> t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>
> This produces 4 identical rows form database that end up on only one
> product entity but repeated 4 times on results list (results.size() = 4).
> Changig the query to
>
> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");q.prefetch(Product.
> EXISTENCIAS_CORPORATIVAS.joint()); // <-- change
> List<Product> results = q.select(context);
>
>
> produces this SQL:
>
> SELECT <<all columns from t0 table>> FROM public.products t0 LEFT JOIN
> public.stockcurrent_corporativo t1 ON (t0.id = t1.product) JOIN
> public.stockcurrent_corporativo t2 ON (t0.id = t2.product) WHERE
> (t2.units > ?) AND (t0.code LIKE ?) ORDER BY t0.name [bind: 1->units:0.0,
> 2->code:'USBVGA']
>
>
> note that the two references to the relationship traslate to two joins.
> This SQL query produces 16 identical rows!!, that end up in only one
> product entity, but surprisingly, results list have only one item
> (product), and this is what is expected !!.
> All this is annoying, or maybe I am not understanding well the query rules
> of cayenne.
> Yes, I can make the query with SQLTemplate, but would like to use only
> object oriented queries. Could it be done ? In general. how to include
> conditions over the many size of relationships ?.
> Please help.
>
> Atte. Juan Manuel Díaz Lara
>
>    On Sunday, November 26, 2017, 8:57:50 PM CST, Juan Manuel Diaz Lara
> <[hidden email]> wrote:
>
>  I am using 4.0.M5, previously this query worked as I expected:
>
>                  ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>              .where(Product.EXISTENCIAS_CORPORATIVAS.dot(
> StockCurrentCorporativo.UNITS).gt(0.0))
> .and(Product.CODE.like("USBVGA");
>                List<Product> results = q.select(context);
>
> The objective is to get products for which we have stock in any location,
> StockCurrentCorporativo has the stock by location.
> I restricted the example query to just one specific product and expect to
> get only one product in results, but got results.size() > 1, with all items
> being the same product (the same object on my tests).
>
> The generated SQL from the log is:
>    SELECT <<all columns from t0 table>>
>    FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (
> t0.id = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER
> BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>
>
> EXISTENCIAS_CORPORATIVAS is a toMany relationship from Product and have
> type StockCurrentCorporativo, defined in _Product as :
>
>      public static final Property<List<StockCurrentCorporativo>>
> EXISTENCIAS_CORPORATIVAS = Property.create("existenciasCorporativas",
> List.class);
>
>
>  To be fair, I do not know when this started, the production app is
> working well, this is on a my development environment when I started to
> work for a new requirement.
> I reviewed the mapping and it looks ok, any help will be appreciated.
>
> Atte. Juan Manuel Díaz Lara
>   
Reply | Threaded
Open this post in threaded view
|

Re: Problem using to-many relationship on where clause

Nikita Timofeev
Answered at dev@ list, but leave a note here too.
Looks like a bug to me too. Will investigate and file a bug (and
ideally make a fix) if required.

On Wed, Nov 29, 2017 at 9:17 AM, Juan Manuel Diaz Lara
<[hidden email]> wrote:

> I created a new project, with entities Products -toMany-> StockcurrentCorporativo, with same results.
> Tracing code, I arrived to DefaultSelectTranslator#isUnsupportedForDistinct, this is the cause for cayenne not including a DISTINCT in generated SQL, because I have columns mapping to types "not supported for distinct clause". But I found a note on method DefaultSelectTranslator#isSuppressingDistinct that tell that cayenne should should do an in-memory  distinct operation. so this is not working well (a bug ?).
> At least I conclude that:
> 1. Cayenne is deciding with types of columns can be used for DISTINCT, but this is not generally true, some db's ignore not sortable columns when used in DISTINCT queries. Maybe this decision should be delegated to the specific db translator.
>
> 2. There is a bug because cayenne must perform the in-memory distinct when suppressing DISTINCT in generated SQL.
> I removed my longvarbinary columns and the problem gone, a DISTINCT SQL was generated, confirming the previous conclusions.
> Well, this was a try, in reality, I can not simply remove my longvarbinary columns.
> Does some body can report this as a bug ?.
> Thanks.
>
>
> Atte. Juan Manuel Díaz Lara
>
>     On Tuesday, November 28, 2017, 2:04:43 PM CST, Juan Manuel Diaz Lara <[hidden email]> wrote:
>
>  I am agree that this kind of query should generate a SQL DISTINCT, but the fact is that adding a joint()brings only one product on result even the sql generates 16 rows, so Cayenne is doing some kind of IN-MEMORY DISTINCT that is not done without a joint(), maybe a bug because there are two different executions paths.
> I will create a new mapping with only this two tables to recheck is not a problem of model or any other mistake. I'll report results.
> thanks.
>
> Atte. Juan Manuel Díaz Lara
>
>     On Tuesday, November 28, 2017, 11:42:14 AM CST, Lon Varscsak <[hidden email]> wrote:
>
>  I think the part that's missing is a "distinct".  I'm assuming there are 4
> stockcurrent_corporativo rows for that one product, but if it had a
> distinct it would be showing one.  I don't think that
> ObjectSelect/FluentSelect have the ability to turn on/off distinct, but for
> me, this always generates a "distinct" in the query.  I'm not sure why it
> would be different for you.
>
> -Lon
>
> On Mon, Nov 27, 2017 at 8:45 PM, Juan Manuel Diaz Lara <
> [hidden email]> wrote:
>
>> More details, this code should return only one product entity, but
>> produces a result list with 4 items, all items being the same product
>> entity (the purpose is get products which have stock in at leas one
>> location):
>>
>>
>> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
>> .and(Product.CODE.like("USBVGA");List<Product> results =
>> q.select(context);
>>
>> (this is could be expressed in plain SQL as a correlated query inside an
>> EXISTS operation over table StockCurrentCorporativo)
>> Generates :
>>
>> SELECT <<all columns from t0 table>>
>> FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (t0.id
>> = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER BY
>> t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>>
>> This produces 4 identical rows form database that end up on only one
>> product entity but repeated 4 times on results list (results.size() = 4).
>> Changig the query to
>>
>> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
>> .and(Product.CODE.like("USBVGA");q.prefetch(Product.
>> EXISTENCIAS_CORPORATIVAS.joint()); // <-- change
>> List<Product> results = q.select(context);
>>
>>
>> produces this SQL:
>>
>> SELECT <<all columns from t0 table>> FROM public.products t0 LEFT JOIN
>> public.stockcurrent_corporativo t1 ON (t0.id = t1.product) JOIN
>> public.stockcurrent_corporativo t2 ON (t0.id = t2.product) WHERE
>> (t2.units > ?) AND (t0.code LIKE ?) ORDER BY t0.name [bind: 1->units:0.0,
>> 2->code:'USBVGA']
>>
>>
>> note that the two references to the relationship traslate to two joins.
>> This SQL query produces 16 identical rows!!, that end up in only one
>> product entity, but surprisingly, results list have only one item
>> (product), and this is what is expected !!.
>> All this is annoying, or maybe I am not understanding well the query rules
>> of cayenne.
>> Yes, I can make the query with SQLTemplate, but would like to use only
>> object oriented queries. Could it be done ? In general. how to include
>> conditions over the many size of relationships ?.
>> Please help.
>>
>> Atte. Juan Manuel Díaz Lara
>>
>>    On Sunday, November 26, 2017, 8:57:50 PM CST, Juan Manuel Diaz Lara
>> <[hidden email]> wrote:
>>
>>  I am using 4.0.M5, previously this query worked as I expected:
>>
>>                  ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>>              .where(Product.EXISTENCIAS_CORPORATIVAS.dot(
>> StockCurrentCorporativo.UNITS).gt(0.0))
>> .and(Product.CODE.like("USBVGA");
>>                List<Product> results = q.select(context);
>>
>> The objective is to get products for which we have stock in any location,
>> StockCurrentCorporativo has the stock by location.
>> I restricted the example query to just one specific product and expect to
>> get only one product in results, but got results.size() > 1, with all items
>> being the same product (the same object on my tests).
>>
>> The generated SQL from the log is:
>>    SELECT <<all columns from t0 table>>
>>    FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (
>> t0.id = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER
>> BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>>
>>
>> EXISTENCIAS_CORPORATIVAS is a toMany relationship from Product and have
>> type StockCurrentCorporativo, defined in _Product as :
>>
>>      public static final Property<List<StockCurrentCorporativo>>
>> EXISTENCIAS_CORPORATIVAS = Property.create("existenciasCorporativas",
>> List.class);
>>
>>
>>  To be fair, I do not know when this started, the production app is
>> working well, this is on a my development environment when I started to
>> work for a new requirement.
>> I reviewed the mapping and it looks ok, any help will be appreciated.
>>
>> Atte. Juan Manuel Díaz Lara
>>



--
Best regards,
Nikita Timofeev
Reply | Threaded
Open this post in threaded view
|

Re: Problem using to-many relationship on where clause

Juan Manuel Diaz Lara
Workaround:
Changed mapping from LONGVARBINARY to  BINARY (DefaultSelectTranslator#isUnsupportedForDistinct does not catch this type as unsupported).

Atte. Juan Manuel Díaz Lara

    On Wednesday, November 29, 2017, 9:15:27 AM CST, Nikita Timofeev <[hidden email]> wrote:  
 
 Answered at dev@ list, but leave a note here too.
Looks like a bug to me too. Will investigate and file a bug (and
ideally make a fix) if required.

On Wed, Nov 29, 2017 at 9:17 AM, Juan Manuel Diaz Lara
<[hidden email]> wrote:

> I created a new project, with entities Products -toMany-> StockcurrentCorporativo, with same results.
> Tracing code, I arrived to DefaultSelectTranslator#isUnsupportedForDistinct, this is the cause for cayenne not including a DISTINCT in generated SQL, because I have columns mapping to types "not supported for distinct clause". But I found a note on method DefaultSelectTranslator#isSuppressingDistinct that tell that cayenne should should do an in-memory  distinct operation. so this is not working well (a bug ?).
> At least I conclude that:
> 1. Cayenne is deciding with types of columns can be used for DISTINCT, but this is not generally true, some db's ignore not sortable columns when used in DISTINCT queries. Maybe this decision should be delegated to the specific db translator.
>
> 2. There is a bug because cayenne must perform the in-memory distinct when suppressing DISTINCT in generated SQL.
> I removed my longvarbinary columns and the problem gone, a DISTINCT SQL was generated, confirming the previous conclusions.
> Well, this was a try, in reality, I can not simply remove my longvarbinary columns.
> Does some body can report this as a bug ?.
> Thanks.
>
>
> Atte. Juan Manuel Díaz Lara
>
>    On Tuesday, November 28, 2017, 2:04:43 PM CST, Juan Manuel Diaz Lara <[hidden email]> wrote:
>
>  I am agree that this kind of query should generate a SQL DISTINCT, but the fact is that adding a joint()brings only one product on result even the sql generates 16 rows, so Cayenne is doing some kind of IN-MEMORY DISTINCT that is not done without a joint(), maybe a bug because there are two different executions paths.
> I will create a new mapping with only this two tables to recheck is not a problem of model or any other mistake. I'll report results.
> thanks.
>
> Atte. Juan Manuel Díaz Lara
>
>    On Tuesday, November 28, 2017, 11:42:14 AM CST, Lon Varscsak <[hidden email]> wrote:
>
>  I think the part that's missing is a "distinct".  I'm assuming there are 4
> stockcurrent_corporativo rows for that one product, but if it had a
> distinct it would be showing one.  I don't think that
> ObjectSelect/FluentSelect have the ability to turn on/off distinct, but for
> me, this always generates a "distinct" in the query.  I'm not sure why it
> would be different for you.
>
> -Lon
>
> On Mon, Nov 27, 2017 at 8:45 PM, Juan Manuel Diaz Lara <
> [hidden email]> wrote:
>
>> More details, this code should return only one product entity, but
>> produces a result list with 4 items, all items being the same product
>> entity (the purpose is get products which have stock in at leas one
>> location):
>>
>>
>> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
>> .and(Product.CODE.like("USBVGA");List<Product> results =
>> q.select(context);
>>
>> (this is could be expressed in plain SQL as a correlated query inside an
>> EXISTS operation over table StockCurrentCorporativo)
>> Generates :
>>
>> SELECT <<all columns from t0 table>>
>> FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (t0.id
>> = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER BY
>> t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>>
>> This produces 4 identical rows form database that end up on only one
>> product entity but repeated 4 times on results list (results.size() = 4).
>> Changig the query to
>>
>> ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>> .where(Product.EXISTENCIAS_CORPORATIVAS.dot(StockCurrentCorporativo.UNITS).gt(0.0))
>> .and(Product.CODE.like("USBVGA");q.prefetch(Product.
>> EXISTENCIAS_CORPORATIVAS.joint()); // <-- change
>> List<Product> results = q.select(context);
>>
>>
>> produces this SQL:
>>
>> SELECT <<all columns from t0 table>> FROM public.products t0 LEFT JOIN
>> public.stockcurrent_corporativo t1 ON (t0.id = t1.product) JOIN
>> public.stockcurrent_corporativo t2 ON (t0.id = t2.product) WHERE
>> (t2.units > ?) AND (t0.code LIKE ?) ORDER BY t0.name [bind: 1->units:0.0,
>> 2->code:'USBVGA']
>>
>>
>> note that the two references to the relationship traslate to two joins.
>> This SQL query produces 16 identical rows!!, that end up in only one
>> product entity, but surprisingly, results list have only one item
>> (product), and this is what is expected !!.
>> All this is annoying, or maybe I am not understanding well the query rules
>> of cayenne.
>> Yes, I can make the query with SQLTemplate, but would like to use only
>> object oriented queries. Could it be done ? In general. how to include
>> conditions over the many size of relationships ?.
>> Please help.
>>
>> Atte. Juan Manuel Díaz Lara
>>
>>    On Sunday, November 26, 2017, 8:57:50 PM CST, Juan Manuel Diaz Lara
>> <[hidden email]> wrote:
>>
>>  I am using 4.0.M5, previously this query worked as I expected:
>>
>>                  ObjectSelect<Product> q = ObjectSelect.query(Product.class)
>>              .where(Product.EXISTENCIAS_CORPORATIVAS.dot(
>> StockCurrentCorporativo.UNITS).gt(0.0))
>> .and(Product.CODE.like("USBVGA");
>>                List<Product> results = q.select(context);
>>
>> The objective is to get products for which we have stock in any location,
>> StockCurrentCorporativo has the stock by location.
>> I restricted the example query to just one specific product and expect to
>> get only one product in results, but got results.size() > 1, with all items
>> being the same product (the same object on my tests).
>>
>> The generated SQL from the log is:
>>    SELECT <<all columns from t0 table>>
>>    FROM public.products t0 JOIN public.stockcurrent_corporativo t1 ON (
>> t0.id = t1.product) WHERE (t1.units > ?) AND (t0.code LIKE ?)    ORDER
>> BY t0.name [bind: 1->units:0.0, 2->code:'USBVGA']
>>
>>
>> EXISTENCIAS_CORPORATIVAS is a toMany relationship from Product and have
>> type StockCurrentCorporativo, defined in _Product as :
>>
>>      public static final Property<List<StockCurrentCorporativo>>
>> EXISTENCIAS_CORPORATIVAS = Property.create("existenciasCorporativas",
>> List.class);
>>
>>
>>  To be fair, I do not know when this started, the production app is
>> working well, this is on a my development environment when I started to
>> work for a new requirement.
>> I reviewed the mapping and it looks ok, any help will be appreciated.
>>
>> Atte. Juan Manuel Díaz Lara
>>



--
Best regards,
Nikita Timofeev