This is a known bug specific to SQL Server -
http://objectstyle.org/jira/secure/ViewIssue.jspa?key=CAY-115
To fix it, internally we will have to change generated SQL semantics to
avoid using DISTINCT and still get correct results when joining on
to-many. Hopefully there will be a clean solution for 1.1 that doesn't
require the whole SQL translation layer rewrite ;-)
Andrus
On Jul 23, 2004, at 9:36 PM, Gary Jarrel wrote:
> Hey All!
>
> Maybe it's my lack of thinking again, but I'm trying to execute the
> following code:
>
> Map params = new HashMap();
> params.put("categoryName", solutionName);
> params.put("active", Constants.DEFAULT_DB_TRUE); //Constants
> -> Boolean(true)
>
> Expression exp = Expression.fromString(
> "categoryName = $categoryName and solutionArray.active =
> $active")
> .expWithParameters(params);
>
> SelectQuery q = new SelectQuery(SolutionCategory.class, exp);
> q.addPrefetch("solutionArray");
> q.setDistinct(false);
>
> List parentSolutions = ctx.performQuery(q);
>
> The generated SQL Code however as per the log is:
>
> SELECT DISTINCT t0.active, t0.category_name, t0.description, t0.image,
> t0.summary FROM solution_category t0, solution t1 WHERE
> t0.category_name =
> t1.category_name AND ((t0.category_name = ?) AND (t1.active = ?))
> [bind:
> 'USB Printers', 1]
>
> Notice that although I've set the q.setDistinct(false) Cayenne put the
> distinct keyword into the SQL. This in turn throws an SQL Server
> Exception
> saying that DISTINCT can not be used with text, ntext or image data.
> The
> DISINCT key word only started appearing when I added "and
> solutionArray.active = $active" to the expression, hence causing it to
> do a
> join, because I'm only interested in active solutions. If I was to
> remove
> the and clause, then the query executes fine, but retrieves both
> active and
> inactive solutions.
>
> Is this behavior expected? I'd assume it's got something to do with
> the fact
> that I'm doing the join.
>
> Thanks for the help
>
> - Gary
>
>
>
This archive was generated by hypermail 2.0.0 : Fri Jul 23 2004 - 21:51:29 EDT