List:Commits« Previous MessageNext Message »
From:Craig L Russell Date:October 18 2011 10:57pm
Subject:bzr push into mysql-5.1-telco-7.1 branch (Craig.Russell:4309 to 4310)
View as plain text  
 4310 Craig L Russell	2011-10-18
      Reimplement query optimization to improve performance:
      Analyze where clause when it is set on the query.
      Rank possible indexes by the number of terms.
      Highest rank for unique indexes (including PRIMARY)
      At query execution time, decide if a unique index is usable (no null parameters)
      If not, choose the ordered index based on whether the first comparison is usable.

    modified:
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/AndPredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/BetweenPredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/CandidateIndexImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/ComparativePredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/EqualPredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/GreaterEqualPredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/GreaterThanPredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/InPredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/LessEqualPredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/LessThanPredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/PredicateImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/PropertyImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/QueryDomainTypeImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/QueryExecutionContextImpl.java
      storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/spi/QueryExecutionContext.java
 4309 jonas oreland	2011-10-18 [merge]
      ndb - merge 70 to 71

    modified:
      mysql-test/suite/funcs_1/r/is_columns_is.result
      mysql-test/suite/funcs_1/r/is_tables_is.result
      mysql-test/suite/funcs_1/t/is_columns_is.test
      mysql-test/suite/funcs_1/t/is_tables_is.test
=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/AndPredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/AndPredicateImpl.java	2011-07-04 15:58:21 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/AndPredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -61,11 +61,13 @@ public class AndPredicateImpl extends Pr
         }
     }
 
+    @Override
     public Predicate or(Predicate predicate) {
         throw new UnsupportedOperationException(
                 local.message("ERR_NotImplemented"));
     }
 
+    @Override
     public Predicate not() {
         throw new UnsupportedOperationException(
                 local.message("ERR_NotImplemented"));
@@ -109,6 +111,7 @@ public class AndPredicateImpl extends Pr
     /** Set the keys into the operation for each predicate.
      * Each predicate must be an equal predicate for a primary or unique key.
      */
+    @Override
     public void operationEqual(QueryExecutionContext context,
             Operation op) {
         for (PredicateImpl predicate: predicates) {
@@ -120,17 +123,6 @@ public class AndPredicateImpl extends Pr
         }
     }
 
-    /** Get the best index for the operation. Delegate to the method
-     * in the superclass, passing the array of predicates.
-     *
-     * @return the best index
-     */
-    @Override
-    public CandidateIndexImpl getBestCandidateIndex(QueryExecutionContext context) {
-        return getBestCandidateIndexFor(context, predicates.toArray(
-                new PredicateImpl[predicates.size()]));
-    }
-
     /** Get the number of conditions in the top level predicate.
      * This is used to determine whether a hash index can be used. If there
      * are exactly the number of conditions as index columns, then the
@@ -144,4 +136,14 @@ public class AndPredicateImpl extends Pr
     protected int getNumberOfConditionsInPredicate() {
         return predicates.size();
     }
+
+    /** Return an array of top level predicates that might be used with indices.
+     * 
+     * @return an array of top level predicates (defaults to {this}).
+     */
+    @Override
+    protected PredicateImpl[] getTopLevelPredicates() {
+        return predicates.toArray(new PredicateImpl[predicates.size()]);
+    }
+
 }

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/BetweenPredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/BetweenPredicateImpl.java	2011-06-20 23:34:36 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/BetweenPredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -46,11 +46,13 @@ public class BetweenPredicateImpl extend
         upper.setProperty(property);
     }
 
+    @Override
     public void markParameters() {
         lower.mark();
         upper.mark();
     }
 
+    @Override
     public void unmarkParameters() {
         lower.unmark();
         upper.unmark();
@@ -66,19 +68,36 @@ public class BetweenPredicateImpl extend
         property.markUpperBound(candidateIndices, this, false);
     }
 
+    @Override
+    public void markBoundsForCandidateIndices(CandidateIndexImpl[] candidateIndices) {
+        property.markLowerBound(candidateIndices, this, false);
+        property.markUpperBound(candidateIndices, this, false);
+    }
+
     /** Set the upper and lower bounds for the operation.
      * Delegate to the property to actually call the setBounds for each
      * of upper and lower bound.
      * @param context the query context that contains the parameter values
      * @param op the index scan operation on which to set bounds
+     * @return an indicator of which bound(s) were actually set
      */
     @Override
-    public void operationSetBounds(QueryExecutionContext context,
+    public int operationSetBounds(QueryExecutionContext context,
             IndexScanOperation op, boolean lastColumn) {
-        property.operationSetBounds(lower.getParameterValue(context),
-                IndexScanOperation.BoundType.BoundLE, op);
-        property.operationSetBounds(upper.getParameterValue(context),
-                IndexScanOperation.BoundType.BoundGE, op);
+        int result = NO_BOUND_SET;
+        Object lowerValue = lower.getParameterValue(context);
+        Object upperValue = upper.getParameterValue(context);
+        if (lowerValue != null) {
+            property.operationSetBounds(lowerValue,
+                    IndexScanOperation.BoundType.BoundLE, op);
+            result |= LOWER_BOUND_SET;
+        }
+        if (upperValue != null) {
+            property.operationSetBounds(upperValue,
+                    IndexScanOperation.BoundType.BoundGE, op);
+            result |= UPPER_BOUND_SET;
+        }
+        return result;
     }
 
     /** Set the upper bound for the operation.
@@ -88,10 +107,15 @@ public class BetweenPredicateImpl extend
      * @param op the index scan operation on which to set bounds
      */
     @Override
-    public void operationSetUpperBound(QueryExecutionContext context,
+    public int operationSetUpperBound(QueryExecutionContext context,
             IndexScanOperation op, boolean lastColumn) {
-        property.operationSetBounds(upper.getParameterValue(context),
-                IndexScanOperation.BoundType.BoundGE, op);
+        Object upperValue = upper.getParameterValue(context);
+        if (upperValue != null) {
+            property.operationSetBounds(upperValue,
+                    IndexScanOperation.BoundType.BoundGE, op);
+            return UPPER_BOUND_SET;
+        }
+        return NO_BOUND_SET;
     }
 
     /** Set the lower bound for the operation.
@@ -101,10 +125,15 @@ public class BetweenPredicateImpl extend
      * @param op the index scan operation on which to set bounds
      */
     @Override
-    public void operationSetLowerBound(QueryExecutionContext context,
+    public int operationSetLowerBound(QueryExecutionContext context,
             IndexScanOperation op, boolean lastColumn) {
-        property.operationSetBounds(lower.getParameterValue(context),
-                IndexScanOperation.BoundType.BoundLE, op);
+        Object lowerValue = lower.getParameterValue(context);
+        if (lowerValue != null) {
+            property.operationSetBounds(lowerValue,
+                    IndexScanOperation.BoundType.BoundLE, op);
+            return LOWER_BOUND_SET;
+        }
+        return NO_BOUND_SET;
     }
 
     /** Create a filter for the operation. Set the condition into the
@@ -140,4 +169,9 @@ public class BetweenPredicateImpl extend
                 ScanFilter.BinaryCondition.COND_LE, filter);
     }
 
+    @Override 
+    public boolean isUsable(QueryExecutionContext context) {
+        return !(lower.getParameterValue(context) == null || upper.getParameterValue(context) == null);
+    }
+
 }

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/CandidateIndexImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/CandidateIndexImpl.java	2011-06-20 23:34:36 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/CandidateIndexImpl.java	2011-10-18 22:54:36 +0000
@@ -46,7 +46,7 @@ import java.util.List;
  * one for each index containing the column referenced by the query term.
  * 
  */
-public class CandidateIndexImpl {
+public final class CandidateIndexImpl {
 
     /** My message translator */
     static final I18NHelper local = I18NHelper.getInstance(CandidateIndexImpl.class);
@@ -63,6 +63,7 @@ public class CandidateIndexImpl {
     private CandidateColumnImpl[] candidateColumns = null;
     private ScanType scanType = PredicateImpl.ScanType.TABLE_SCAN;
     private int fieldScore = 1;
+    protected int score = 0;
 
     public CandidateIndexImpl(
             String className, Index storeIndex, boolean unique, AbstractDomainFieldHandlerImpl[] fields) {
@@ -114,7 +115,7 @@ public class CandidateIndexImpl {
 
     @Override
     public String toString() {
-        StringBuffer buffer = new StringBuffer();
+        StringBuilder buffer = new StringBuilder();
         buffer.append("CandidateIndexImpl for class: ");
         buffer.append(className);
         buffer.append(" index: ");
@@ -174,13 +175,12 @@ public class CandidateIndexImpl {
      * The last query term (candidate column) for each of the lower and upper bound is noted.
      * The method is synchronized because the method modifies the state of the instance,
      * which might be shared by multiple threads.
-     * @return the score of this index.
      */
-    synchronized int getScore() {
+    synchronized void score() {
+        score = 0;
         if (candidateColumns == null) {
-            return 0;
+            return;
         }
-        int result = 0;
         boolean lowerBoundDone = false;
         boolean upperBoundDone = false;
         if (unique) {
@@ -188,7 +188,7 @@ public class CandidateIndexImpl {
             for (CandidateColumnImpl column: candidateColumns) {
                 if (!(column.equalBound)) {
                     // not equal bound; can't use unique index
-                    return result;
+                    return;
                 }
             }
             if ("PRIMARY".equals(indexName)) {
@@ -196,7 +196,8 @@ public class CandidateIndexImpl {
             } else {
                 scanType = PredicateImpl.ScanType.UNIQUE_KEY;
             }
-            return 100;
+            score = 100;
+            return;
         } else {
             // range index
             // leading columns need any kind of bound
@@ -205,22 +206,22 @@ public class CandidateIndexImpl {
                 if ((candidateColumn.equalBound)) {
                     scanType = PredicateImpl.ScanType.INDEX_SCAN;
                     if (!lowerBoundDone) {
-                        result += fieldScore;
+                        score += fieldScore;
                         lastLowerBoundColumn = candidateColumn;
                     }
                     if (!upperBoundDone) {
-                        result += fieldScore;
+                        score += fieldScore;
                         lastUpperBoundColumn = candidateColumn;
                     }
                 } else if ((candidateColumn.inBound)) {
                     scanType = PredicateImpl.ScanType.INDEX_SCAN;
                     multiRange = true;
                     if (!lowerBoundDone) {
-                        result += fieldScore;
+                        score += fieldScore;
                         lastLowerBoundColumn = candidateColumn;
                     }
                     if (!upperBoundDone) {
-                        result += fieldScore;
+                        score += fieldScore;
                         lastUpperBoundColumn = candidateColumn;
                     }
                 } else if (!(lowerBoundDone && upperBoundDone)) {
@@ -233,7 +234,7 @@ public class CandidateIndexImpl {
                     }
                     if (!lowerBoundDone) {
                         if (hasLowerBound) {
-                            result += fieldScore;
+                            score += fieldScore;
                             lastLowerBoundColumn = candidateColumn;
                         } else {
                             lowerBoundDone = true;
@@ -241,7 +242,7 @@ public class CandidateIndexImpl {
                     }
                     if (!upperBoundDone) {
                         if (hasUpperBound) {
-                            result += fieldScore;
+                            score += fieldScore;
                             lastUpperBoundColumn = candidateColumn;
                         } else {
                             upperBoundDone = true;
@@ -259,7 +260,7 @@ public class CandidateIndexImpl {
                 lastUpperBoundColumn.markLastUpperBoundColumn();
             }
         }
-        return result;
+        return;
     }
 
     public ScanType getScanType() {
@@ -350,6 +351,7 @@ public class CandidateIndexImpl {
     class CandidateColumnImpl {
 
         protected AbstractDomainFieldHandlerImpl domainFieldHandler;
+        protected PredicateImpl predicate;
         protected PredicateImpl lowerBoundPredicate;
         protected PredicateImpl upperBoundPredicate;
         protected PredicateImpl equalPredicate;
@@ -375,7 +377,6 @@ public class CandidateIndexImpl {
         }
 
         public int getParameterSize(QueryExecutionContext context) {
-            // TODO Auto-generated method stub
             return inPredicate.getParameterSize(context);
         }
 
@@ -402,21 +403,25 @@ public class CandidateIndexImpl {
         private void markLowerBound(PredicateImpl predicate, boolean strict) {
             lowerBoundStrict = strict;
             this.lowerBoundPredicate = predicate;
+            this.predicate = predicate;
         }
 
         private void markUpperBound(PredicateImpl predicate, boolean strict) {
             upperBoundStrict = strict;
             this.upperBoundPredicate = predicate;
+            this.predicate = predicate;
         }
 
         private void markEqualBound(PredicateImpl predicate) {
             equalBound = true;
             this.equalPredicate = predicate;
+            this.predicate = predicate;
         }
 
         public void markInBound(InPredicateImpl predicate) {
             inBound = true;
             this.inPredicate = predicate;
+            this.predicate = predicate;
         }
 
         /** Set bounds into each predicate that has been defined.
@@ -428,6 +433,8 @@ public class CandidateIndexImpl {
         private int operationSetBounds(
                 QueryExecutionContext context, IndexScanOperation op, int index, int boundStatus) {
 
+            int boundSet = PredicateImpl.NO_BOUND_SET;
+
             if (logger.isDetailEnabled()) logger.detail("column: " + domainFieldHandler.getName() 
                     + " boundStatus: " + boundStatus
                     + " lastLowerBoundColumn: " + lastLowerBoundColumn
@@ -439,51 +446,53 @@ public class CandidateIndexImpl {
                 case BOUND_STATUS_NO_BOUND_DONE:
                     // can set either/both lower or upper bound
                     if (equalPredicate != null) {
-                        equalPredicate.operationSetBounds(context, op, true);
+                        boundSet |= equalPredicate.operationSetBounds(context, op, true);
                     }
                     if (inPredicate != null) {
-                        inPredicate.operationSetBound(context, op, index, true);
+                        boundSet |= inPredicate.operationSetBound(context, op, index, true);
                     }
                     if (lowerBoundPredicate != null) {
-                        lowerBoundPredicate.operationSetLowerBound(context, op, lastLowerBoundColumn);
+                        boundSet |= lowerBoundPredicate.operationSetLowerBound(context, op, lastLowerBoundColumn);
                     }
                     if (upperBoundPredicate != null) {
-                        upperBoundPredicate.operationSetUpperBound(context, op, lastUpperBoundColumn);
+                        boundSet |= upperBoundPredicate.operationSetUpperBound(context, op, lastUpperBoundColumn);
                     }
                     break;
                 case BOUND_STATUS_LOWER_BOUND_DONE:
                     // cannot set lower, only upper bound
                     if (equalPredicate != null) {
-                        equalPredicate.operationSetUpperBound(context, op, lastUpperBoundColumn);
+                        boundSet |= equalPredicate.operationSetUpperBound(context, op, lastUpperBoundColumn);
                     }
                     if (inPredicate != null) {
-                        inPredicate.operationSetUpperBound(context, op, index);
+                        boundSet |= inPredicate.operationSetUpperBound(context, op, index);
                     }
                     if (upperBoundPredicate != null) {
-                        upperBoundPredicate.operationSetUpperBound(context, op, lastUpperBoundColumn);
+                        boundSet |= upperBoundPredicate.operationSetUpperBound(context, op, lastUpperBoundColumn);
                     }
                     break;
                 case BOUND_STATUS_UPPER_BOUND_DONE:
                     // cannot set upper, only lower bound
                     if (equalPredicate != null) {
-                        equalPredicate.operationSetLowerBound(context, op, lastLowerBoundColumn);
+                        boundSet |= equalPredicate.operationSetLowerBound(context, op, lastLowerBoundColumn);
                     }
                     if (inPredicate != null) {
-                        inPredicate.operationSetLowerBound(context, op, index);
+                        boundSet |= inPredicate.operationSetLowerBound(context, op, index);
                     }
                     if (lowerBoundPredicate != null) {
-                        lowerBoundPredicate.operationSetLowerBound(context, op, lastLowerBoundColumn);
+                        boundSet |= lowerBoundPredicate.operationSetLowerBound(context, op, lastLowerBoundColumn);
                     }
                     break;
             }
-            if (!hasLowerBound()) {
-                // if this has no lower bound, set lower bound done
+            if (0 == (boundSet & PredicateImpl.LOWER_BOUND_SET)) {
+                // didn't set lower bound
                 boundStatus |= BOUND_STATUS_LOWER_BOUND_DONE;
             }
-            if (!hasUpperBound()) {
-                // if this has no upper bound, set upper bound done
+                
+            if (0 == (boundSet & PredicateImpl.UPPER_BOUND_SET)) {
+                // didn't set upper bound
                 boundStatus |= BOUND_STATUS_UPPER_BOUND_DONE;
             }
+                
             return boundStatus;
         }
 
@@ -511,8 +520,33 @@ public class CandidateIndexImpl {
         return storeIndex;
     }
 
+    public int getScore() {
+        return score;
+    }
+
     public boolean isMultiRange() {
         return multiRange;
     }
 
+    public boolean isUnique() {
+        return unique;
+    }
+
+    /** Is this index usable in the current context?
+     * If a primary or unique index, all parameters must be non-null.
+     * If a btree index, the parameter for the first comparison must be non-null
+     * @param context the query execution context
+     * @return true if all relevant parameters in the context are non-null
+     */
+    public boolean isUsable(QueryExecutionContext context) {
+        if (unique) {
+            return context.hasNoNullParameters();
+        } else {
+            // the first parameter must not be null
+            CandidateColumnImpl candidateColumn = candidateColumns[0];
+            PredicateImpl predicate = candidateColumn.predicate;
+            return predicate.isUsable(context);
+        }
+    }
+
 }

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/ComparativePredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/ComparativePredicateImpl.java	2011-06-20 23:34:36 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/ComparativePredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -46,10 +46,12 @@ public abstract class ComparativePredica
         param.setProperty(property);
     }
 
+    @Override
     public void markParameters() {
         param.mark();
     }
 
+    @Override
     public void unmarkParameters() {
         param.unmark();
     }
@@ -61,17 +63,27 @@ public abstract class ComparativePredica
     }
 
     @Override
-    public void operationSetLowerBound(QueryExecutionContext context,
+    public int operationSetLowerBound(QueryExecutionContext context,
             IndexScanOperation op, boolean lastColumn) {
         // delegate to setBounds for most operations
-        operationSetBounds(context, op, lastColumn);
+        return operationSetBounds(context, op, lastColumn);
     }
 
     @Override
-    public void operationSetUpperBound(QueryExecutionContext context,
+    public int operationSetUpperBound(QueryExecutionContext context,
             IndexScanOperation op, boolean lastColumn) {
         // delegate to setBounds for most operations
-        operationSetBounds(context, op, lastColumn);
+        return operationSetBounds(context, op, lastColumn);
+    }
+
+    @Override
+    public ParameterImpl getParameter() {
+        return param;
+    }
+
+    @Override 
+    public boolean isUsable(QueryExecutionContext context) {
+        return param.getParameterValue(context) != null;
     }
 
 }

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/EqualPredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/EqualPredicateImpl.java	2011-06-20 23:34:36 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/EqualPredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -40,21 +40,44 @@ public class EqualPredicateImpl extends 
     }
 
     @Override
-    public void operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
+    public void markBoundsForCandidateIndices(CandidateIndexImpl[] candidateIndices) {
+        property.markEqualBound(candidateIndices, this);
+    }
+
+    @Override
+    public int operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
         // can always set boundEQ
-        property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundEQ, op);
+        Object value = param.getParameterValue(context);
+        if (value != null) {
+            property.operationSetBounds(value, IndexScanOperation.BoundType.BoundEQ, op);
+            return BOTH_BOUNDS_SET;
+        } else {
+            return NO_BOUND_SET;
+        }
     }
 
     @Override
-    public void operationSetLowerBound(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
+    public int operationSetLowerBound(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
         // only set lower bound
-        property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundLE, op);
+        Object value = param.getParameterValue(context);
+        if (value != null) {
+            property.operationSetBounds(value, IndexScanOperation.BoundType.BoundLE, op);
+            return LOWER_BOUND_SET;
+        } else {
+            return NO_BOUND_SET;
+        }
     }
 
     @Override
-    public void operationSetUpperBound(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
+    public int operationSetUpperBound(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
         // only set upper bound
-        property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundGE, op);
+        Object value = param.getParameterValue(context);
+        if (value != null) {
+            property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundGE, op);
+            return UPPER_BOUND_SET;
+        } else {
+            return NO_BOUND_SET;
+        }
     }
 
     @Override

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/GreaterEqualPredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/GreaterEqualPredicateImpl.java	2011-06-20 23:34:36 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/GreaterEqualPredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -39,8 +39,19 @@ public class GreaterEqualPredicateImpl e
     }
 
     @Override
-    public void operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
-        property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundLE, op);
+    public void markBoundsForCandidateIndices(CandidateIndexImpl[] candidateIndices) {
+        property.markLowerBound(candidateIndices, this, false);
+    }
+
+    @Override
+    public int operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
+        Object lowerBound = param.getParameterValue(context);
+        if (lowerBound != null) {
+            property.operationSetBounds(lowerBound, IndexScanOperation.BoundType.BoundLE, op);
+            return LOWER_BOUND_SET;
+        } else {
+            return NO_BOUND_SET;
+        }
     }
 
     /** Set the condition into the filter.

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/GreaterThanPredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/GreaterThanPredicateImpl.java	2011-06-20 23:34:36 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/GreaterThanPredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -39,13 +39,24 @@ public class GreaterThanPredicateImpl ex
     }
 
     @Override
-    public void operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
-        if (lastColumn) {
-            // last column may be strict
-            property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundLT, op);
+    public void markBoundsForCandidateIndices(CandidateIndexImpl[] candidateIndices) {
+        property.markLowerBound(candidateIndices, this, true);
+    }
+
+    @Override
+    public int operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
+        Object lowerValue = param.getParameterValue(context);
+        if (lowerValue != null) {
+            if (lastColumn) {
+                // last column may be strict
+                property.operationSetBounds(lowerValue, IndexScanOperation.BoundType.BoundLT, op);
+            } else {
+                // not-last column must not be strict
+                property.operationSetBounds(lowerValue, IndexScanOperation.BoundType.BoundLE, op);
+            }
+            return LOWER_BOUND_SET;
         } else {
-            // not-last column must not be strict
-            property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundLE, op);
+            return NO_BOUND_SET;
         }
     }
 

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/InPredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/InPredicateImpl.java	2011-06-20 23:34:36 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/InPredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -58,7 +58,7 @@ public class InPredicateImpl extends Pre
     }
 
     @Override
-    void markBoundsForCandidateIndices(QueryExecutionContext context,
+    public void markBoundsForCandidateIndices(QueryExecutionContext context,
             CandidateIndexImpl[] candidateIndices) {
         if (parameter.getParameterValue(context) == null) {
             // null parameters cannot be used with index scans
@@ -67,6 +67,11 @@ public class InPredicateImpl extends Pre
         property.markInBound(candidateIndices, this);
     }
 
+    @Override
+    public void markBoundsForCandidateIndices(CandidateIndexImpl[] candidateIndices) {
+        property.markInBound(candidateIndices, this);
+    }
+
     /** Set bound for the multi-valued parameter identified by the index.
      * 
      * @param context the query execution context
@@ -74,27 +79,27 @@ public class InPredicateImpl extends Pre
      * @param index the index into the parameter list
      * @param lastColumn if true, can set strict bound
      */
-    public void operationSetBound(
+    public int operationSetBound(
             QueryExecutionContext context, IndexScanOperation op, int index, boolean lastColumn) {
         if (lastColumn) {
             // last column can be strict
-            operationSetBound(context, op, index, BoundType.BoundEQ);
+            return operationSetBound(context, op, index, BoundType.BoundEQ);
         } else {
             // not last column cannot be strict
-            operationSetBound(context, op, index, BoundType.BoundLE);
-            operationSetBound(context, op, index, BoundType.BoundGE);
+            return operationSetBound(context, op, index, BoundType.BoundLE) +
+                    operationSetBound(context, op, index, BoundType.BoundGE);
         }
     }
 
-    public void operationSetUpperBound(QueryExecutionContext context, IndexScanOperation op, int index) {
-        operationSetBound(context, op, index, BoundType.BoundGE);
+    public int operationSetUpperBound(QueryExecutionContext context, IndexScanOperation op, int index) {
+        return operationSetBound(context, op, index, BoundType.BoundGE);
     }
 
-    public void operationSetLowerBound(QueryExecutionContext context, IndexScanOperation op, int index) {
-        operationSetBound(context, op, index, BoundType.BoundLE);
+    public int operationSetLowerBound(QueryExecutionContext context, IndexScanOperation op, int index) {
+        return operationSetBound(context, op, index, BoundType.BoundLE);
     }
 
-    private void operationSetBound(
+    private int operationSetBound(
             QueryExecutionContext context, IndexScanOperation op, int index, BoundType boundType) {
     Object parameterValue = parameter.getParameterValue(context);
         if (parameterValue == null) {
@@ -103,8 +108,8 @@ public class InPredicateImpl extends Pre
         } else if (parameterValue instanceof List<?>) {
             List<?> parameterList = (List<?>)parameterValue;
             Object value = parameterList.get(index);
-            property.operationSetBounds(value, boundType, op);
             if (logger.isDetailEnabled()) logger.detail("InPredicateImpl.operationSetBound for " + property.fmd.getName() + " List index: " + index + " value: " + value + " boundType: " + boundType);
+            property.operationSetBounds(value, boundType, op);
         } else if (parameterValue.getClass().isArray()) {
             Object[] parameterArray = (Object[])parameterValue;
             Object value = parameterArray[index];
@@ -115,6 +120,7 @@ public class InPredicateImpl extends Pre
                     local.message("ERR_Parameter_Wrong_Type", parameter.parameterName,
                             parameterValue.getClass().getName(), "List<?> or Object[]"));
         }
+        return BOTH_BOUNDS_SET;
     }
 
     /** Set bounds for the multi-valued parameter identified by the index.
@@ -157,6 +163,7 @@ public class InPredicateImpl extends Pre
      * @param context the query execution context with the parameter values
      * @param op the operation
      */
+    @Override
     public void filterCmpValue(QueryExecutionContext context,
             ScanOperation op) {
         try {
@@ -176,6 +183,7 @@ public class InPredicateImpl extends Pre
      * @param op the operation
      * @param filter the existing filter
      */
+    @Override
     public void filterCmpValue(QueryExecutionContext context, ScanOperation op, ScanFilter filter) {
         try {
             filter.begin(Group.GROUP_OR);
@@ -190,8 +198,8 @@ public class InPredicateImpl extends Pre
                 }
             } else if (parameterValue.getClass().isArray()) {
                 Object[] parameterArray = (Object[])parameterValue;
-                for (Object parameter: parameterArray) {
-                    property.filterCmpValue(parameter, BinaryCondition.COND_EQ, filter);
+                for (Object value: parameterArray) {
+                    property.filterCmpValue(value, BinaryCondition.COND_EQ, filter);
                 }
             } else {
                 throw new ClusterJUserException(
@@ -230,4 +238,9 @@ public class InPredicateImpl extends Pre
         return result;
     }
 
+    @Override 
+    public boolean isUsable(QueryExecutionContext context) {
+        return parameter.getParameterValue(context) != null;
+    }
+
 }

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/LessEqualPredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/LessEqualPredicateImpl.java	2011-06-20 23:34:36 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/LessEqualPredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -39,8 +39,19 @@ public class LessEqualPredicateImpl exte
     }
 
     @Override
-    public void operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
-        property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundGE, op);
+    public void markBoundsForCandidateIndices(CandidateIndexImpl[] candidateIndices) {
+        property.markUpperBound(candidateIndices, this, false);
+    }
+
+    @Override
+    public int operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
+        Object upperValue = param.getParameterValue(context);
+        if (upperValue != null) {
+            property.operationSetBounds(upperValue, IndexScanOperation.BoundType.BoundGE, op);
+            return UPPER_BOUND_SET;
+        } else {
+            return NO_BOUND_SET;
+        }
     }
 
     /** Set the condition into the filter.

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/LessThanPredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/LessThanPredicateImpl.java	2011-06-20 23:34:36 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/LessThanPredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -39,13 +39,24 @@ public class LessThanPredicateImpl exten
     }
 
     @Override
-    public void operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
-        if (lastColumn) {
-            // last column may be strict
-            property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundGT, op);
+    public void markBoundsForCandidateIndices(CandidateIndexImpl[] candidateIndices) {
+        property.markUpperBound(candidateIndices, this, true);
+    }
+
+    @Override
+    public int operationSetBounds(QueryExecutionContext context, IndexScanOperation op, boolean lastColumn) {
+        Object upperValue = param.getParameterValue(context);
+        if (upperValue != null) {
+            if (lastColumn) {
+                // last column may be strict
+                property.operationSetBounds(upperValue, IndexScanOperation.BoundType.BoundGT, op);
+            } else {
+                // not-last column must not be strict
+                property.operationSetBounds(upperValue, IndexScanOperation.BoundType.BoundGE, op);
+            }
+            return UPPER_BOUND_SET;
         } else {
-            // not-last column must not be strict
-            property.operationSetBounds(param.getParameterValue(context), IndexScanOperation.BoundType.BoundGE, op);
+            return NO_BOUND_SET;
         }
     }
 

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/PredicateImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/PredicateImpl.java	2011-10-02 21:20:50 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/PredicateImpl.java	2011-10-18 22:54:36 +0000
@@ -34,6 +34,9 @@ import com.mysql.clusterj.core.util.Logg
 
 import com.mysql.clusterj.query.Predicate;
 
+import java.util.Comparator;
+import java.util.TreeSet;
+
 public abstract class PredicateImpl implements Predicate {
 
     /** My message translator */
@@ -45,6 +48,20 @@ public abstract class PredicateImpl impl
     /** My domain object. */
     protected QueryDomainTypeImpl<?> dobj;
 
+    /** The primary/unique index for this query if it exists */
+    CandidateIndexImpl uniqueIndex;
+
+    /** The comparator for candidate indices, ordered descending by score */
+    Comparator<CandidateIndexImpl> candidateIndexComparator = new Comparator<CandidateIndexImpl>() {
+        public int compare(CandidateIndexImpl o1, CandidateIndexImpl o2) {
+            return o2.score - o1.score;
+        }
+    };
+
+    /** The candidate indices ordered by score */
+    private TreeSet<CandidateIndexImpl> scoredCandidateIndices =
+        new TreeSet<CandidateIndexImpl>(candidateIndexComparator);
+
     /** Scan types. */
     protected enum ScanType {
         INDEX_SCAN,
@@ -53,6 +70,15 @@ public abstract class PredicateImpl impl
         PRIMARY_KEY
     }
 
+    /** Indicates no bound set while setting bounds on index operations */
+    public static int NO_BOUND_SET = 0;
+    /** Indicates lower bound set while setting bounds on index operations */
+    public static int LOWER_BOUND_SET = 1;
+    /** Indicates upper bound set while setting bounds on index operations */
+    public static int UPPER_BOUND_SET = 2;
+    /** Indicates both bounds set while setting bounds on index operations */
+    public static int BOTH_BOUNDS_SET = 3;
+
     public PredicateImpl(QueryDomainTypeImpl<?> dobj) {
         this.dobj = dobj;
     }
@@ -84,19 +110,19 @@ public abstract class PredicateImpl impl
         // default is nothing to do
     }
 
-    public void operationSetBounds(QueryExecutionContext context,
+    public int operationSetBounds(QueryExecutionContext context,
             IndexScanOperation op, boolean lastColumn) {
         throw new ClusterJFatalInternalException(
                 local.message("ERR_Implementation_Should_Not_Occur"));
     }
 
-    public void operationSetLowerBound(QueryExecutionContext context,
+    public int operationSetLowerBound(QueryExecutionContext context,
             IndexScanOperation op, boolean lastColumn) {
         throw new ClusterJFatalInternalException(
                 local.message("ERR_Implementation_Should_Not_Occur"));
     }
 
-    public void operationSetUpperBound(QueryExecutionContext context,
+    public int operationSetUpperBound(QueryExecutionContext context,
             IndexScanOperation op, boolean lastColumn){
         throw new ClusterJFatalInternalException(
                 local.message("ERR_Implementation_Should_Not_Occur"));
@@ -181,60 +207,106 @@ public abstract class PredicateImpl impl
     }
 
     public CandidateIndexImpl getBestCandidateIndex(QueryExecutionContext context) {
-        return getBestCandidateIndexFor(context, this);
+        return getBestCandidateIndexFor(context, getTopLevelPredicates());
     }
 
     /** Get the best candidate index for the query, considering all indices
-     * defined and all predicates in the query.
+     * defined and all predicates in the query. If a unique index is usable
+     * (no non-null parameters) then return it. Otherwise, simply choose the
+     * first index for which there is at least one leading non-null parameter.
      * @param predicates the predicates
      * @return the best index for the query
      */
     protected CandidateIndexImpl getBestCandidateIndexFor(QueryExecutionContext context,
             PredicateImpl... predicates) {
-        // Create CandidateIndexImpl to decide how to scan.
+        // if there is a primary/unique index, see if it can be used in the current context
+        if (uniqueIndex != null && uniqueIndex.isUsable(context)) {
+            if (logger.isDebugEnabled()) logger.debug("usable unique index: " + uniqueIndex.getIndexName());
+            return uniqueIndex;
+        }
+        // find the best candidate index by returning the highest scoring index that is usable
+        // in the current context; i.e. has non-null parameters
+        // TODO: it might be better to score indexes again considering the current context
+        for (CandidateIndexImpl index: scoredCandidateIndices) {
+            if (index.isUsable(context)) {
+            if (logger.isDebugEnabled()) logger.debug("usable ordered index: " + index.getIndexName());
+                return index;
+            }
+        }
+        // there is no index that is usable in the current context
+        return CandidateIndexImpl.getIndexForNullWhereClause();
+
+    }
+
+    /** Get the number of conditions in the top level predicate.
+     * This is used to determine whether a hash index can be used. If there
+     * are exactly the number of conditions as index columns, then the
+     * hash index might be used.
+     * By default (for equal, greaterThan, lessThan, greaterEqual, lessEqual)
+     * there is one condition.
+     * AndPredicateImpl overrides this method.
+     * @return the number of conditions
+     */
+    protected int getNumberOfConditionsInPredicate() {
+        return 1;
+    }
+
+    /** Analyze this predicate to determine whether a primary key, unique key, or ordered index
+     * might be used. The result will be used during query execution once the actual parameters
+     * are known.
+     */
+    public void prepare() {
+        // Create CandidateIndexImpls
         CandidateIndexImpl[] candidateIndices = dobj.createCandidateIndexes();
         // Iterate over predicates and have each one register with
         // candidate indexes.
-        for (PredicateImpl predicateImpl : predicates) {
-            predicateImpl.markBoundsForCandidateIndices(context, candidateIndices);
+        for (PredicateImpl predicateImpl : getTopLevelPredicates()) {
+            predicateImpl.markBoundsForCandidateIndices(candidateIndices);
         }
-        // Iterate over candidate indices to find one that is usable.
-        int highScore = 0;
-        // Holder for the best index; default to the index for null where clause
-        CandidateIndexImpl bestCandidateIndexImpl = 
-                CandidateIndexImpl.getIndexForNullWhereClause();
+        // Iterate over candidate indices to find those that are usable.
         // Hash index operations require the predicates to have no extra conditions
         // beyond the index columns.
+        // Btree index operations are ranked by the number of usable conditions
         int numberOfConditions = getNumberOfConditionsInPredicate();
         for (CandidateIndexImpl candidateIndex : candidateIndices) {
             if (candidateIndex.supportsConditionsOfLength(numberOfConditions)) {
-                // opportunity for a user-defined plugin to evaluate indices
+                candidateIndex.score();
                 int score = candidateIndex.getScore();
-                if (logger.isDetailEnabled()) {
-                    logger.detail("Score: " + score + " from " + candidateIndex);
+                if (score != 0) {
+                    if (candidateIndex.isUnique()) {
+                        // there can be only one unique index for a given predicate
+                        uniqueIndex = candidateIndex;
+                    } else {
+                        // add possible indices to ordered map
+                        scoredCandidateIndices.add(candidateIndex);
+                    }
                 }
-                if (score > highScore) {
-                    bestCandidateIndexImpl = candidateIndex;
-                    highScore = score;
+                if (logger.isDetailEnabled()) {
+                    logger.detail("Score: " + score + " from " + candidateIndex.getIndexName());
                 }
             }
         }
-        if (logger.isDetailEnabled()) logger.detail("High score: " + highScore
-                + " from " + bestCandidateIndexImpl.getIndexName());
-        return bestCandidateIndexImpl;
     }
 
-    /** Get the number of conditions in the top level predicate.
-     * This is used to determine whether a hash index can be used. If there
-     * are exactly the number of conditions as index columns, then the
-     * hash index might be used.
-     * By default (for equal, greaterThan, lessThan, greaterEqual, lessEqual)
-     * there is one condition.
-     * AndPredicateImpl overrides this method.
-     * @return the number of conditions
+    protected void markBoundsForCandidateIndices(CandidateIndexImpl[] candidateIndices) {
+        // default is nothing to do
+    }
+
+    /** Return an array of top level predicates that might be used with indices.
+     * 
+     * @return an array of top level predicates (defaults to {this}).
      */
-    protected int getNumberOfConditionsInPredicate() {
-        return 1;
+    protected PredicateImpl[] getTopLevelPredicates() {
+        return new PredicateImpl[] {this};
+    }
+
+    public ParameterImpl getParameter() {
+        // default is there is no parameter for this predicate
+        return null;
+    }
+
+    public boolean isUsable(QueryExecutionContext context) {
+        return false;
     }
 
 }

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/PropertyImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/PropertyImpl.java	2011-10-02 21:20:50 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/PropertyImpl.java	2011-10-18 22:54:36 +0000
@@ -54,6 +54,11 @@ public class PropertyImpl implements Pre
         this.fmd = fmd;
     }
 
+    @Override
+    public String toString() {
+        return fmd.getName();
+    }
+
     public void setComplexParameter() {
         complexParameter = true;
     }

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/QueryDomainTypeImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/QueryDomainTypeImpl.java	2011-07-04 15:58:21 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/QueryDomainTypeImpl.java	2011-10-18 22:54:36 +0000
@@ -118,6 +118,12 @@ public class QueryDomainTypeImpl<T> impl
         }
         this.where = (PredicateImpl)predicate;
         where.markParameters();
+        // statically analyze the where clause, looking for:
+        // primary keys all specified with equal
+        // unique keys all specified with equal
+        // btree index keys partly specified with ranges
+        // none of the above
+        where.prepare();
         return this;
     }
 
@@ -183,12 +189,13 @@ public class QueryDomainTypeImpl<T> impl
      * @throws ClusterJUserException if not all parameters are bound
      */
     public ResultData getResultData(QueryExecutionContext context) {
-	SessionSPI session = context.getSession();
+        SessionSPI session = context.getSession();
         // execute query based on what kind of scan is needed
         // if no where clause, scan the entire table
         CandidateIndexImpl index = where==null?
-            CandidateIndexImpl.getIndexForNullWhereClause():
-            where.getBestCandidateIndex(context);
+                CandidateIndexImpl.getIndexForNullWhereClause():
+                where.getBestCandidateIndex(context);
+
         ScanType scanType = index.getScanType();
         Map<String, Object> explain = newExplain(index, scanType);
         context.setExplain(explain);

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/QueryExecutionContextImpl.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/QueryExecutionContextImpl.java	2011-07-04 15:58:21 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/query/QueryExecutionContextImpl.java	2011-10-18 22:54:36 +0000
@@ -338,4 +338,13 @@ public class QueryExecutionContextImpl i
         return boundParameters.get(index);
     }
 
+    public boolean hasNoNullParameters() {
+        for (Object value: boundParameters.values()) {
+            if (value == null) {
+                return false;
+            }
+        }
+        return true;
+    }
+
 }

=== modified file 'storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/spi/QueryExecutionContext.java'
--- a/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/spi/QueryExecutionContext.java	2011-07-04 15:58:21 +0000
+++ b/storage/ndb/clusterj/clusterj-core/src/main/java/com/mysql/clusterj/core/spi/QueryExecutionContext.java	2011-10-18 22:54:36 +0000
@@ -69,4 +69,6 @@ public interface QueryExecutionContext {
 
     void deleteFilters();
 
+    boolean hasNoNullParameters();
+
 }

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-5.1-telco-7.1 branch (Craig.Russell:4309 to 4310) Craig L Russell19 Oct