db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shreyas Kaushik (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-25) INSERT INTO SELECT DISTINCT ... skips some values for autoincrement column
Date Thu, 25 Nov 2004 12:31:21 GMT
     [ 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 insertion.

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.

thanks
Shreyas

--------------------------------Diff for ProjectRestrictResultSet--------------------------------
Index: java/engine/org/apache/derby/impl/sql/execute/ProjectRestrictResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/execute/ProjectRestrictResultSet.java	(revision
106542)
+++ java/engine/org/apache/derby/impl/sql/execute/ProjectRestrictResultSet.java	(working copy)
@@ -43,8 +43,10 @@
 import org.apache.derby.iapi.error.StandardException;
 
 import org.apache.derby.iapi.types.RowLocation;
+import org.apache.derby.iapi.types.Orderable;
 
 import org.apache.derby.catalog.types.ReferencedColumnsDescriptorImpl;
+import org.apache.derby.impl.jdbc.Util;
 
 
 /**
@@ -77,6 +79,9 @@
 
 	private ExecRow projRow;
 
+    private ExecRow previousRow = null;
+    private boolean firstRow = false;
+
     //
     // class interface
     //
@@ -245,6 +250,7 @@
 	    boolean restrict = false;
 	    DataValueDescriptor restrictBoolean;
 		long	beginRT = 0;
+            boolean retVal;
 
 		/* Return null if open was short circuited by false constant expression */
 		if (shortCircuitOpen)
@@ -256,8 +262,17 @@
 	    do 
 		{
 			candidateRow = source.getNextRowCore();
-			if (candidateRow != null) 
+
+            if (candidateRow != null)
 			{
+                retVal = isEquals(candidateRow);
+
+                previousRow = candidateRow.getClone();
+
+                if(!retVal && !firstRow)
+                    continue;
+
+
 				beginRT = getCurrentTimeMillis();
 				/* If restriction is null, then all rows qualify */
 				if (restriction == null)
@@ -492,7 +507,8 @@
 		if (projection != null)
 		{
 	        result = (ExecRow) projection.invoke(activation);
-		}
+        }
+
 		else
 		{
 			result = mappedResultRow;
@@ -501,10 +517,9 @@
 		// Copy any mapped columns from the source
 		for (int index = 0; index < projectMapping.length; index++)
 		{
-			if (projectMapping[index] != -1)
-			{
-				result.setColumn(index + 1, sourceRow.getColumn(projectMapping[index]));
-			}
+            if(projectMapping[index] != -1) {
+			    result.setColumn(index + 1, sourceRow.getColumn(projectMapping[index]));
+            }
 		}
 
 		/* We need to reSet the current row after doing the projection */
@@ -528,6 +543,27 @@
 		return source.isForUpdate();
 	}
 
+    private boolean isEquals(ExecRow candidateRow)  throws StandardException {
+        boolean notEqual = false;
+        DataValueDescriptor []sourceDesc, destDesc;
+
+        // Do the checking if a row is being repeated
+        if(previousRow != null) {
+            firstRow = false;
+            sourceDesc = candidateRow.getRowArray();
+            destDesc = previousRow.getRowArray();
+
+            for ( int i = 0; i < destDesc.length; i++) {
+                // Do the actual comparision here
+                if(!(sourceDesc[i].compare(Orderable.ORDER_OP_EQUALS,destDesc[i],true,true)))
{
+                   notEqual = true;
+                }
+            }
+        }
+        if(previousRow == null)
+           firstRow = true;
+        return notEqual;
+    }
 }
-------------------------------------Diff for ProjectRestrictResultSet ends--------------------------


> INSERT INTO SELECT DISTINCT ... skips some values for autoincrement column
> --------------------------------------------------------------------------
>
>          Key: DERBY-25
>          URL: http://nagoya.apache.org/jira/browse/DERBY-25
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.0.2.0
>     Reporter: Tulika Agrawal
>     Priority: Minor

>
> Reporting for Mamta Satoor.
> If we use insert into desttable, select 
> distinct from source, into a desttable which has autoincrement 
> column in it, we might see gaps in the autoincrement column if 
> there are duplicated rows in the source table. The reason for 
> this is Derby projects values into destination table columns before 
> building a distinct resultset from the source table. The piece 
> of code doing this is in org.apache.derby.impl.sql.execute.ProjectRestrictResultSet class's
getNextRowCore() method where it calls doProjection. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://nagoya.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


Mime
View raw message