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
>
