QueryBuilder: clausola distinct in presenza di CLOB about regola-kit HOT 10 OPEN

ecco l'errore che viene su:

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB

(riproducibile anche con una query direttamente su db)

Il QueryBuilder non fa proiezioni sulle entità, tira su l'intera istanza.
Il distinct lo mette quando vi sono più entità in join e dovrebbe agire solo 
sulla
chiave primaria della root entity, non su tutte le colonne. Puoi allegare la 
porzione
di schema db inrteressata e le query HQL e SQL (dai log) che producono l'errore?
Non è che dipende da filtri sulle proprietà CLOB (indipendentemente dal 
distinct)?

In realtà le query generate da Hibernate sono così:

HQL: select
        distinct e0 
    from
        Customer e0 
    join
        e0.invoices e1 
    join
        e1.items e2  
    where
        e2.product.name = :p0 

SQL: select
            distinct customer0_.id as id0_,
            customer0_.firstName as firstName0_,
            customer0_.lastName as lastName0_,
            customer0_.street as street0_,
            customer0_.city as city0_ 
        from
            Customer customer0_ 
        inner join
            Invoice invoices1_ 
                on customer0_.id=invoices1_.CUSTOMERID 
        inner join
            ITEM items2_ 
                on invoices1_.id=items2_.INVOICEID,
            Product product3_ 
        where
            items2_.PRODUCTID=product3_.id 
            and product3_.name=?

Evidentemente quindi se, ad esempio, il campo STREET di CUSTOMER è un 
CLOB/BLOB il
distinct su Oracle fallisce miseramente :-(

Ho provato a replicare il bug nei test, impostando il campo STREET con 
type="text" in
Hibernate, ma non ci sono stati problemi. Non so se il type="text" venga 
mappato come
un CLOB in HSQL, o se semplicemente HSQL effettua il distinct anche sui CLOB...

Forse si può provare con una sottoselect come indicato qui:
http://www.dbasupport.com/forums/showthread.php?t=46785

In allegato la query SQL, quella HQL e la descrizione delle due tabelle 
coinvolte.
La colonna che dà fastidio è il CLOB (l'unico) 'progetto'.

Cmq non impazzire dietro i particolari di questo caso: il punto è proprio che 
nella
traduzione in SQL la distinct viene applicata a tutte le colonne, e non 
soltanto alla
chiave primaria (tra l'altro questo immagino che comporti anche un notevole calo
delle performance).

Ti confermo che non stiamo filtrando sulla proprietà CLOB.

Non ho trovato nulla riguardo a dei parametri configurabili per far sì che 
Hibernate
applichi la distinct solo alla chiave primaria.

A questo punto non so se convenga la tecnica della sottoselect o specificare
direttamente nella distinct (nell'hql) gli attributi ai quali applicarlo (da 
quanto
ho letto la sinassi hql lo permette).

Con Hibernate ci troviamo in questa situazione:
- non sono disponibili api per configurare eventuali politiche di distinct ed
escludere eventuali campi;
- con l'hql (che è quello che genera il nostro QueryBuilder) c'è la 
possibilità di
fare delle subselect;

Considerato questo pensavo di implementare la tecnica della subselect, in cui la
query interna applica le condizioni di filtraggio, fa le eventuali join e 
applica la
distinct ai solo campi in chiave della root entity, mentre la query esterna si 
occupa
di proiettare su tutti i campi i record con le chiavi risultanti e di applicare 
gli
ordinamenti.

Cosa ne pensate, cari? Avete proposte migliori?

Un'alternativa al "distinct" potrebbe essere usare
`query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)`

[http://www.hibernate.org/hib_docs/v3/api/org/hibernate/Query.html#setResultTran
sformer(org.hibernate.transform.ResultTransformer)
setResultTransformer]

[http://www.hibernate.org/hib_docs/v3/api/org/hibernate/criterion/CriteriaSpecif
ication.html#DISTINCT_ROOT_ENTITY
DISTINCT_ROOT_ENTITY]

[http://www.hibernate.org/117.html#A12 Hibernate does not return distinct 
results for
a query with outer join fetching enabled for a collection (even if I use the 
distinct
keyword)?]

Da verificare: problemi nella paginazione.

ci sono in effetti problemi nella paginazione: la distinct viene applicata agli
oggetti in memoria e quindi dopo aver fatto la query con i limiti di pagina.

In ogni caso anche la tecnica con subselect è impossibile da implementare nel 
nostro
caso, se si vogliono avere anche paginazione e ordinamento.

Infatti anche volendo far generare una query del tipo (ometto al query più 
esterna di
paginazione):

select re_ext
form rootEntity re_ext
where re_ext.id in 
  (
    select distinct re.id
    from rootEntity re ...joins...
    where ...filtraggi....
  )

Nota: HQL le supporta le subselect, a patto che siano nella where.

Il problema è l'ordinamento: 
- metterlo nella subselect non assicurerebbe l'ordinamento dei risultati della 
query
più esterna ed inoltre non è sintatticamente supportato in piattaforme come 
Oracle;
- si può mettere nella query esterna ma si potrebbe ordinare solo per 
propietà della
root entity (mentre in generale si può voler ordinare anche per proprietà di 
entity
in join).

Considerato quindi che non riusciamo ad avere una soluzione abbastanza 
generale, per
ora non abbiamo fatto modifiche al query builder di regola e abbiamo applicato 
una
soluzione custom al bug segnalatoci (che per fortuna rientra nel caso di 
ordinamento
per sole proprietà della root entity).
Sono state soltanto aggiunte delle api per reperire le clausole della query
normalmente generate da regola a partire da un model pattern e per eseguire una 
query
hql custom.

Altro problema del distinct quando vi è anche un order by su un campo non in 
select list (vedi anche #21):
ORA-01791: non è un'espressione ottenuta con SELECT
01791. 00000 - "not a SELECTed expression"
// addProperty("comandi[].dataUltimoTentativo", "");
// addSortedProperty("comandi[].dataUltimoTentativo", Order.desc);