db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: [jira] Commented: (DERBY-25) INSERT INTO SELECT DISTINCT ... skips some values for autoincrement column
Date Thu, 25 Nov 2004 16:13:21 GMT
Hash: SHA1

Shreyas Kaushik (JIRA) wrote:

>      [
http://nagoya.apache.org/jira/browse/DERBY-25?page=comments#action_55864 ]
> Shreyas Kaushik commented on DERBY-25:
> --------------------------------------
> I was looking at this issue, trying to make a patch for this. I use
the following logic to do this:
> 1) Initially previous row is null
> 2) SInce this is the first row add it to the temp ResultSet for later
> 3) After this store the current Row as previous Row.
> 4) For every pass compare previous row and current row, if same don't
insert otherwise
>    insert and do a projection.
>   I will put the code that I wrote for this inline here. At this point
of time I still have some failures while running the tests which I hope
to resolve soon.

My guess is that you are now eliminating rows from a result set that
should not be eliminated. ProjectRestrictResultSet is a general purpose
result set to filter rows both horizontally (predicates) and vertically
(columns). It seems from your changes (the call to isEquals) the code
will always eliminate duplicate consecutive rows. Thus in a simple query

select a, b from t where c = ?

the code now will incorrectly remove adjacent rows that happen to have
the same column values. Note that in many cases the input to
ProjectRestrictResultSet will not be ordered, which is what you change
would require.

The result set classes (which are not java.sql.ResultSet) are arranged
together in a tree by the generate class to fulfill the functionality of
the SQL statement. For a query rows flow from the bottom of the tree
upwards, towards the top result set, which is the result set that plugs
into the JDBC result set that forms the result of the query. Thus my
simple query above may be formed by a ProjectRestrictResultSet above a
TableScanResultSet. In general any result set does not know or care what
type of result set(s) is feeding it or what kind of result set it is
feeding into.

I don't how how this 'insert ... select distinct from ...' query is put
together in terms of result sets, and thinking about it briefly I not
sure I understand how the bug is happening. It would be useful if you
could get the query plan (the layout of the tree of result sets) for the
complete statement, and then see where the identity column is being
defined. Maybe it is happening too low in the tree.

This is a great way to learn Derby, get in the code, modify it and if it
doesn't seem to work the way you thought it would, ask the list!

> thanks
> Shreyas
> --------------------------------Diff for
> Index:
> ===================================================================

> +            if (candidateRow != null)
>  			{
> +                retVal = isEquals(candidateRow);
> +
> +                previousRow = candidateRow.getClone();
> +
> +                if(!retVal && !firstRow)
> +                    continue;

Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org


View raw message