openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel Lee" <tsunfang...@gmail.com>
Subject Re: [jira] Commented: (OPENJPA-312) derby fails with duplicate primary key(s) in group by list
Date Fri, 10 Aug 2007 23:55:58 GMT
Per Kevin's question/suggestions and problem found by David, a new patch
is attached here for review.

Thanks to Kavin for the comments.  The code using sql or sqlGetSQL() is
based on the type of the arguments for the column(s).  sqlGetSQL() call is
needed to get the String of the column if the column is passed in as a
SQLBuffer.

For making the suggested code working, I have to add the checking of the
existence of the column into the logic as the following:

        getJoins(joins, true);
        if (_grouping == null) {
            _grouping = new SQLBuffer(_dict);
            if (_grouping.getSQL().indexOf(sql) < 0)
                _grouping.append(sql);
       }
       else if (_grouping.getSQL().indexOf(sql) < 0)
            _grouping.append(", " + sql);
This may not be cheaper than testing if (_grouping == null)...  I have
modified the code to avoid overlooking any substring of a column as an
existing column and attach the patch here.  Please let me know if you have
further concerns.  Thanks.

Here is part of the changes...

    public void groupBy(SQLBuffer sql, Joins joins) {
        getJoins(joins, true);
        if (!columnInGrouping(sql.getSQL())) {
            if (_grouping == null)
                _grouping = new SQLBuffer(_dict);
            else
                _grouping.append(", ");
            _grouping.append(sql.getSQL());
        }
    }
    private boolean columnInGrouping(String col) {
        if (_grouping == null)
            return false;

        StringTokenizer st = new StringTokenizer(_grouping.getSQL(),
                " ,\t\n\r\f");
        while (st.hasMoreTokens()) {
            if (StringUtils.equals(st.nextToken(), col))
                return true;
        }
        return false;
    }

Thanks,
Daniel

On 8/10/07, Kevin Sutter (JIRA) <jira@apache.org> wrote:
>
>
>    [
> https://issues.apache.org/jira/browse/OPENJPA-312?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12519064]
>
> Kevin Sutter commented on OPENJPA-312:
> --------------------------------------
>
> Daniel,
> The basic idea of the patch looks okay, but the code changes do not look
> consistent.  In some cases, you use "sql" and in others, you are using "
> sql.getSQL()".  I would prefer just using "sql".
>
> Also, you check for an empty _grouping after you check if the desired
> group sub-string is already present in the _grouping.  This seems
> backwards.  I know it's only being used to determine whether to use a comma
> or not.  So, instead of doing these extra empty checks, could we do
> something along the following.  I think this accomplishes the same goal.
>
>         getJoins(joins, true);
>         if (_grouping == null) {
>             _grouping = new SQLBuffer(_dict);
>             _grouping.append(sql);
>        }
>        else if (_grouping.getSQL().indexOf(sql) < 0)
>             _grouping.append(", " + sql);
>
> Thanks,
> Kevin
>
>
>
> > derby fails with duplicate primary key(s) in group by list
> > ----------------------------------------------------------
> >
> >                 Key: OPENJPA-312
> >                 URL: https://issues.apache.org/jira/browse/OPENJPA-312
> >             Project: OpenJPA
> >          Issue Type: Bug
> >          Components: sql
> >    Affects Versions: 1.0.0
> >            Reporter: Daniel Lee
> >            Assignee: Daniel Lee
> >            Priority: Minor
> >             Fix For: 1.0.0
> >
> >         Attachments: OPENJPA-312.patch
> >
> >
> > derby fails with duplicate primary key(s) in group by list
> > With query "select o.customer, avg(o.amount) from Order o group by
> o.customer" the push-down query contains duplicate columns in the group by
> clause.  This is okay when DB2 and other DB that tolerate the duplicates but
> Derby returns error.
> > Of course, we can ask fix on Derby but we can also easy fix in OpenJPA
> to avoid duplicates in the group by list.  Please refer to the following for
> the error result and the attach patch for the fix.
> > Output from running the query that generate duplicate in the group by
> list:
> > 6429  demo  TRACE  [main] openjpa.Query - Executing query: select
> o.customer, avg(o.amount) from Order o group by o.customer
> > 6639  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094861122, conn
> 1639735740> executing prepstmnt 1405375428 SELECT t1.countryCode, t1.id,
> t1.version, t1.city, t1.state, t1.street, t1.zip, t1.creditRating, t1.name,
> AVG(t0.amount) FROM Order t0 INNER JOIN Customer t1 ON
> t0.customer_countryCode = t1.countryCode AND t0.customer_id = t1.id GROUP
> BY t1.countryCode, t1.id, t1.version, t1.countryCode, t1.id, t1.city,
> t1.state, t1.street, t1.zip, t1.countryCode, t1.id, t1.creditRating,
> t1.name
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message