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
<ntimof...@objectstyle.com> 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
<jmdia...@yahoo.com.invalid> 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
><jmdia...@yahoo.com.INVALID> 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
><lon.varsc...@gmail.com> 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 <
> jmdia...@yahoo.com.invalid> 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
>> <jmdia...@yahoo.com.INVALID> 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
