Bug: "select distinct" fails when array columns present for some DBS

[jburel]

Try to save ROI

Caused by: omero.ApiUsageException
    serverStackTrace = "ome.conditions.ApiUsageException: could not execute query; SQL [select distinct roi0_.id as id53_0_, shapes2_.id as id1_1_, to_char(roi0_.description) as descript2_53_0_, roi0_.creation_id as creation7_53_0_, roi0_.external_id as external8_53_0_, roi0_.group_id as group9_53_0_, roi0_.owner_id as owner10_53_0_, roi0_.permissions as permissi3_53_0_, roi0_.update_id as update11_53_0_, roi0_.image as image53_0_, roi0_.keywords as keywords53_0_, roi0_.namespaces as namespaces53_0_, roi0_.source as source53_0_, roi0_.version as version53_0_, shapes2_.creation_id as creation50_1_1_, shapes2_.external_id as external51_1_1_, shapes2_.group_id as group52_1_1_, shapes2_.owner_id as owner53_1_1_, shapes2_.permissions as permissi3_1_1_, shapes2_.update_id as update54_1_1_, shapes2_.fillColor as fillColor1_1_, shapes2_.fillRule as fillRule1_1_, shapes2_.fontFamily as fontFamily1_1_, shapes2_.fontSize as fontSize1_1_, shapes2_.fontStretch as fontStre8_1_1_, shapes2_.fontStyle as fontStyle1_1_, shapes2_.fontVariant as fontVar10_1_1_, shapes2_.fontWeight as fontWeight1_1_, shapes2_.g as g1_1_, shapes2_.locked as locked1_1_, shapes2_.roi as roi1_1_, shapes2_.strokeColor as strokeC14_1_1_, shapes2_.strokeDashArray as strokeD15_1_1_, shapes2_.strokeDashOffset as strokeD16_1_1_, shapes2_.strokeLineCap as strokeL17_1_1_, shapes2_.strokeLineJoin as strokeL18_1_1_, shapes2_.strokeMiterLimit as strokeM19_1_1_, shapes2_.strokeWidth as strokeW20_1_1_, shapes2_.theC as theC1_1_, shapes2_.theT as theT1_1_, shapes2_.theZ as theZ1_1_, shapes2_.transform as transform1_1_, shapes2_.vectorEffect as vectorE25_1_1_, shapes2_.version as version1_1_, shapes2_.visibility as visibility1_1_, to_char(shapes2_.points) as points1_1_, to_char(shapes2_.textValue) as textValue1_1_, shapes2_.cx as cx1_1_, shapes2_.cy as cy1_1_, shapes2_.anchor as anchor1_1_, shapes2_.baselineShift as baselin33_1_1_, shapes2_.decoration as decoration1_1_, shapes2_.direction as direction1_1_, shapes2_.glyphOrientationVertical as glyphOr36_1_1_, shapes2_.writingMode as writing37_1_1_, shapes2_.x as x1_1_, shapes2_.y as y1_1_, to_char(shapes2_.d) as d1_1_, shapes2_.height as height1_1_, shapes2_.rx as rx1_1_, shapes2_.width as width1_1_, shapes2_.bytes as bytes1_1_, shapes2_.pixels as pixels1_1_, shapes2_.ry as ry1_1_, shapes2_.x1 as x46_1_1_, shapes2_.x2 as x47_1_1_, shapes2_.y1 as y48_1_1_, shapes2_.y2 as y49_1_1_, shapes2_.discriminator as discrimi1_1_1_, shapes2_.roi as roi53_0__, shapes2_.id as id0__, shapes2_.roi_index as roi57_0__ from roi roi0_ inner join image image1_ on roi0_.image=image1_.id inner join shape shapes2_ on roi0_.id=shapes2_.roi and (
                        
                          ( shapes2_.group_id = ? AND 
                             ( 1 = ? OR 
                               1 = ? OR 
                               shapes2_.owner_id = ?
                             )
                          ) OR
                          shapes2_.group_id = 1 OR 
                         1 = ?
                        ) where (
                        
                          ( roi0_.group_id = ? AND 
                             ( 1 = ? OR 
                               1 = ? OR 
                               roi0_.owner_id = ?
                             )
                          ) OR
                          roi0_.group_id = 1 OR 
                         1 = ?
                        ) and image1_.id=? order by roi0_.id]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
                        	at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:629)
                        	at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
                        	at org.springframework.orm.hibernate3.HibernateInterceptor.invoke(HibernateInterceptor.java:117)
                        	at

Similar issue seen with clobs. The problem stems from the array (and clob) columns not being comparable.

[jmoore]

Removed "select distinct roi" in favor of the use of a HashSet to reduce duplicates. Will commit with the rest of the story branch.