diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
index 9c784ad..bf5ef28 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
@@ -41,11 +41,14 @@
 import org.apache.asterix.metadata.entities.Index;
 import org.apache.asterix.metadata.entities.InternalDatasetDetails;
 import org.apache.asterix.metadata.utils.ArrayIndexUtil;
+import org.apache.asterix.metadata.utils.IndexUtil;
+import org.apache.asterix.metadata.utils.TypeUtil;
 import org.apache.asterix.om.base.AInt32;
 import org.apache.asterix.om.base.AOrderedList;
 import org.apache.asterix.om.base.AString;
 import org.apache.asterix.om.base.IAObject;
 import org.apache.asterix.om.constants.AsterixConstantValue;
+import org.apache.asterix.om.functions.BuiltinFunctionInfo;
 import org.apache.asterix.om.functions.BuiltinFunctions;
 import org.apache.asterix.om.typecomputer.base.TypeCastUtils;
 import org.apache.asterix.om.types.AOrderedListType;
@@ -90,7 +93,6 @@
 import org.apache.hyracks.algebricks.core.algebra.util.OperatorManipulationUtil;
 import org.apache.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule;
 import org.apache.hyracks.api.exceptions.SourceLocation;
-import org.apache.hyracks.util.OptionalBoolean;
 
 /**
  * This rule matches the pattern:
@@ -840,12 +842,7 @@
                     AbstractFunctionCallExpression fieldAccessFunc =
                             getFieldAccessFunction(new MutableObject<>(varRef), -1, indexFieldId.fieldName);
                     // create cast
-                    theFieldAccessFunc = new ScalarFunctionCallExpression(FunctionUtil.getFunctionInfo(
-                            index.isEnforced() ? BuiltinFunctions.CAST_TYPE : BuiltinFunctions.CAST_TYPE_LAX));
-                    theFieldAccessFunc.setSourceLocation(sourceLoc);
-                    // The first argument is the field
-                    theFieldAccessFunc.getArguments().add(new MutableObject<ILogicalExpression>(fieldAccessFunc));
-                    TypeCastUtils.setRequiredAndInputTypes(theFieldAccessFunc, skTypes.get(i), BuiltinType.ANY);
+                    theFieldAccessFunc = createCastExpression(index, skTypes.get(i), fieldAccessFunc, sourceLoc);
                 } else {
                     // Get the desired field position
                     int pos = indexFieldId.fieldName.size() > 1 ? -1
@@ -855,7 +852,7 @@
                             getFieldAccessFunction(new MutableObject<>(varRef), pos, indexFieldId.fieldName);
                 }
                 vars.add(fieldVar);
-                exprs.add(new MutableObject<ILogicalExpression>(theFieldAccessFunc));
+                exprs.add(new MutableObject<>(theFieldAccessFunc));
                 fieldAccessVars.put(indexFieldId, fieldVar);
             }
         }
@@ -868,6 +865,50 @@
         return currentTop;
     }
 
+    private AbstractFunctionCallExpression createCastExpression(Index index, IAType targetType,
+            AbstractFunctionCallExpression inputExpr, SourceLocation sourceLoc) throws CompilationException {
+        ScalarFunctionCallExpression castExpr;
+        if (IndexUtil.castDefaultNull(index)) {
+            castExpr = constructorFunction(targetType, inputExpr, sourceLoc);
+        } else if (index.isEnforced()) {
+            castExpr = castFunction(BuiltinFunctions.CAST_TYPE, targetType, inputExpr, sourceLoc);
+        } else {
+            castExpr = castFunction(BuiltinFunctions.CAST_TYPE_LAX, targetType, inputExpr, sourceLoc);
+        }
+        return castExpr;
+    }
+
+    private ScalarFunctionCallExpression castFunction(FunctionIdentifier castFun, IAType requiredType,
+            AbstractFunctionCallExpression inputExpr, SourceLocation sourceLoc) throws CompilationException {
+        BuiltinFunctionInfo castInfo = BuiltinFunctions.getBuiltinFunctionInfo(castFun);
+        ScalarFunctionCallExpression castExpr = new ScalarFunctionCallExpression(castInfo);
+        castExpr.setSourceLocation(sourceLoc);
+        castExpr.getArguments().add(new MutableObject<>(inputExpr));
+        TypeCastUtils.setRequiredAndInputTypes(castExpr, requiredType, BuiltinType.ANY);
+        return castExpr;
+    }
+
+    private ScalarFunctionCallExpression constructorFunction(IAType requiredType,
+            AbstractFunctionCallExpression inputExpr, SourceLocation sourceLoc) throws CompilationException {
+        FunctionIdentifier typeConstructorFun = TypeUtil.getTypeConstructor(requiredType);
+        if (typeConstructorFun == null) {
+            throw new CompilationException(ErrorCode.COMPILATION_TYPE_UNSUPPORTED, sourceLoc, "index",
+                    requiredType.getTypeName());
+        }
+        // make CONSTRUCTOR(IF_MISSING(input, NULL))
+        BuiltinFunctionInfo ifMissingInfo = BuiltinFunctions.getBuiltinFunctionInfo(BuiltinFunctions.IF_MISSING);
+        ScalarFunctionCallExpression ifMissingExpr = new ScalarFunctionCallExpression(ifMissingInfo);
+        ifMissingExpr.getArguments().add(new MutableObject<>(inputExpr));
+        ifMissingExpr.getArguments().add(new MutableObject<>(ConstantExpression.NULL));
+        ifMissingExpr.setSourceLocation(sourceLoc);
+
+        BuiltinFunctionInfo typeConstructorInfo = BuiltinFunctions.getBuiltinFunctionInfo(typeConstructorFun);
+        ScalarFunctionCallExpression constructorExpr = new ScalarFunctionCallExpression(typeConstructorInfo);
+        constructorExpr.getArguments().add(new MutableObject<>(ifMissingExpr));
+        constructorExpr.setSourceLocation(sourceLoc);
+        return constructorExpr;
+    }
+
     private ILogicalOperator introduceNewOp(ILogicalOperator currentTopOp, ILogicalOperator newOp, boolean afterOp)
             throws AlgebricksException {
         if (afterOp) {
@@ -933,9 +974,8 @@
             if (index.isPrimaryKeyIndex()) {
                 return createAnyUnknownFilterExpression(secondaryKeyVars, typeEnv, forceFilter);
             } else {
-                OptionalBoolean excludeUnknownKey =
-                        ((Index.ValueIndexDetails) index.getIndexDetails()).isExcludeUnknownKey();
-                boolean excludeUnknown = excludeUnknownKey.isPresent() && excludeUnknownKey.get();
+                Index.ValueIndexDetails indexDetails = (Index.ValueIndexDetails) index.getIndexDetails();
+                boolean excludeUnknown = indexDetails.getExcludeUnknownKey().getOrElse(false);
                 return createAllUnknownFilterExpression(secondaryKeyVars, typeEnv, forceFilter, excludeUnknown);
             }
         } else {
diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
index 70ac386..d298c7f 100644
--- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
+++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
@@ -1213,6 +1213,11 @@
                         }
                     }
 
+                    boolean isFieldFromSchema = projectTypePrime != null;
+                    if (isFieldFromSchema && stmtCreateIndex.hasCastDefaultNull()) {
+                        throw new CompilationException(ErrorCode.COMPILATION_ERROR, indexedElement.getSourceLocation(),
+                                "CAST is not allowed since field \"" + projectPath + "\" is typed");
+                    }
                     IAType fieldTypePrime;
                     boolean fieldTypeNullable, fieldTypeMissable;
                     if (projectTypeExpr == null) {
@@ -1227,7 +1232,7 @@
                             }
                             // don't allow creating an enforced index on a closed-type field, fields that
                             // are part of schema get the field type, if it's not null, then the field is closed-type
-                            if (projectTypePrime != null) {
+                            if (isFieldFromSchema) {
                                 throw new CompilationException(ErrorCode.INDEX_ILLEGAL_ENFORCED_ON_CLOSED_FIELD,
                                         indexedElement.getSourceLocation(), String.valueOf(projectPath));
                             }
@@ -1236,7 +1241,7 @@
                                 throw new CompilationException(ErrorCode.INDEX_ILLEGAL_NON_ENFORCED_TYPED,
                                         indexedElement.getSourceLocation(), indexType);
                             }
-                            if (projectTypePrime != null) {
+                            if (isFieldFromSchema) {
                                 throw new CompilationException(ErrorCode.COMPILATION_ERROR,
                                         indexedElement.getSourceLocation(), "Typed index on \"" + projectPath
                                                 + "\" field could be created only for open datatype");
@@ -1278,10 +1283,21 @@
                 throw new CompilationException(ErrorCode.COMPILATION_ERROR, sourceLoc,
                         "can only specify exclude/include unknown key for B-Tree & Array indexes");
             }
+            boolean castDefaultNullAllowed = indexType == IndexType.BTREE && !isSecondaryPrimary;
+            if (stmtCreateIndex.hasCastDefaultNull() && !castDefaultNullAllowed) {
+                throw new CompilationException(ErrorCode.COMPILATION_ERROR, sourceLoc,
+                        "Cast Default Null is only allowed for B-Tree indexes");
+            }
+            if (stmtCreateIndex.getCastDefaultNull().getOrElse(false)) {
+                if (stmtCreateIndex.isEnforced()) {
+                    throw new CompilationException(ErrorCode.COMPILATION_ERROR, sourceLoc,
+                            "Cast Default Null cannot be specified together with ENFORCED");
+                }
+            }
             Index.IIndexDetails indexDetails;
             if (Index.IndexCategory.of(indexType) == Index.IndexCategory.ARRAY) {
                 if (!stmtCreateIndex.hasExcludeUnknownKey()
-                        || !stmtCreateIndex.isExcludeUnknownKey().getOrElse(false)) {
+                        || !stmtCreateIndex.getExcludeUnknownKey().getOrElse(false)) {
                     throw new CompilationException(ErrorCode.COMPILATION_ERROR, sourceLoc,
                             "Array indexes must specify EXCLUDE UNKNOWN KEY.");
                 }
@@ -1324,7 +1340,8 @@
                 switch (Index.IndexCategory.of(indexType)) {
                     case VALUE:
                         indexDetails = new Index.ValueIndexDetails(keyFieldNames, keyFieldSourceIndicators,
-                                keyFieldTypes, overridesFieldTypes, stmtCreateIndex.isExcludeUnknownKey());
+                                keyFieldTypes, overridesFieldTypes, stmtCreateIndex.getExcludeUnknownKey(),
+                                stmtCreateIndex.getCastDefaultNull());
                         break;
                     case TEXT:
                         indexDetails = new Index.TextIndexDetails(keyFieldNames, keyFieldSourceIndicators,
@@ -1535,12 +1552,17 @@
                     Index.IndexCategory indexCategory = Index.IndexCategory.of(index.getIndexType());
                     OptionalBoolean excludeUnknownKey = OptionalBoolean.empty();
                     if (indexCategory == Index.IndexCategory.VALUE) {
-                        excludeUnknownKey = ((Index.ValueIndexDetails) index.getIndexDetails()).isExcludeUnknownKey();
+                        excludeUnknownKey = ((Index.ValueIndexDetails) index.getIndexDetails()).getExcludeUnknownKey();
+                    }
+                    OptionalBoolean castDefaultNull = OptionalBoolean.empty();
+                    if (indexCategory == Index.IndexCategory.VALUE) {
+                        castDefaultNull = ((Index.ValueIndexDetails) index.getIndexDetails()).getCastDefaultNull();
                     }
                     filesIndex = new Index(index.getDataverseName(), index.getDatasetName(),
                             IndexingConstants.getFilesIndexName(index.getDatasetName()), IndexType.BTREE,
                             new Index.ValueIndexDetails(ExternalIndexingOperations.FILE_INDEX_FIELD_NAMES, null,
-                                    ExternalIndexingOperations.FILE_INDEX_FIELD_TYPES, false, excludeUnknownKey),
+                                    ExternalIndexingOperations.FILE_INDEX_FIELD_TYPES, false, excludeUnknownKey,
+                                    castDefaultNull),
                             false, false, MetadataUtil.PENDING_ADD_OP);
                     MetadataManager.INSTANCE.addIndex(metadataProvider.getMetadataTxnContext(), filesIndex);
                     // Add files to the external files index
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.000.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.000.ddl.sqlpp
new file mode 100644
index 0000000..a26a005
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.000.ddl.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+
+USE test;
+
+CREATE DATASET ds1(id int not unknown, typed_f1 string, typed_f2 int) OPEN TYPE PRIMARY KEY id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.001.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.001.ddl.sqlpp
new file mode 100644
index 0000000..ac134c4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.001.ddl.sqlpp
@@ -0,0 +1,21 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+USE test;
+// can only use CAST (DEFAULT NULL) with BTREE
+CREATE INDEX idx ON ds1(UNNEST a : string) CAST (DEFAULT NULL);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.002.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.002.ddl.sqlpp
new file mode 100644
index 0000000..afdd07b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.002.ddl.sqlpp
@@ -0,0 +1,21 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+USE test;
+// cannot use ENFORCED and CAST (DEFAULT NULL)
+CREATE INDEX idx ON ds1(f: int?) ENFORCED CAST (DEFAULT NULL);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.003.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.003.ddl.sqlpp
new file mode 100644
index 0000000..3a4bf6a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.003.ddl.sqlpp
@@ -0,0 +1,21 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+USE test;
+// cannot use CAST with a typed field
+CREATE INDEX idx ON ds1(typed_f1) CAST (DEFAULT NULL);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.004.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.004.ddl.sqlpp
new file mode 100644
index 0000000..c1cfa20
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.004.ddl.sqlpp
@@ -0,0 +1,21 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+USE test;
+// cannot use CAST with a typed field
+CREATE INDEX idx ON ds1(typed_f2: int) CAST (DEFAULT NULL);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.000.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.000.ddl.sqlpp
new file mode 100644
index 0000000..b810727
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.000.ddl.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : test that CAST (DEFAULT NULL) casts to the target type such that NULL is produced for invalid input
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+CREATE TYPE t1 AS { id: int, s_f: string, d_f: double, i_f: int, b_f: boolean };
+CREATE DATASET ds1(t1) primary key id;
+CREATE DATASET ds2(t1) primary key id;
+
+//CREATE INDEX ds2_idx1 ON ds2(s_f: int) CAST (DEFAULT NULL);
+
+CREATE INDEX ds2_o_idx1 ON ds2(o_s_f: int, o_i_f: string) INCLUDE UNKNOWN KEY CAST (DEFAULT NULL);
+CREATE INDEX ds2_o_idx2 ON ds2(o_s_f: double, o_d_f: string) CAST (DEFAULT NULL);
+//CREATE INDEX ds2_o_idx3 ON ds2(o_s_f: boolean, o_b_f: string) CAST (DEFAULT NULL);
+
+CREATE INDEX ds2_o_idx4 ON ds2(a.s_f: int) CAST (DEFAULT NULL);
+
+CREATE INDEX ds2_o_idx5 ON ds2(a.any_f: int) CAST (DEFAULT NULL);
+CREATE INDEX ds2_o_idx6 ON ds2(a.any_f: string) CAST (DEFAULT NULL);
+CREATE INDEX ds2_o_idx7 ON ds2(a.any_f: double) CAST (DEFAULT NULL);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.001.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.001.update.sqlpp
new file mode 100644
index 0000000..eea08de
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.001.update.sqlpp
@@ -0,0 +1,43 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+INSERT INTO ds1 [
+{"id": 1, "s_f": "s",     "d_f": 1.5, "i_f": 1, "b_f": true,  "o_s_f": "s",     "o_d_f": 1.5, "o_i_f": 1, "o_b_f": true,  "a": {"s_f": "s",     "any_f": 1}},
+{"id": 2, "s_f": "2",     "d_f": 2.5, "i_f": 2, "b_f": false, "o_s_f": "2",     "o_d_f": 2.5, "o_i_f": 2, "o_b_f": false, "a": {"s_f": "2",     "any_f": 1.5}},
+{"id": 3, "s_f": "3.5",   "d_f": 3.5, "i_f": 3, "b_f": true,  "o_s_f": "3.5",   "o_d_f": 3.5, "o_i_f": 3, "o_b_f": true,  "a": {"s_f": "3.5",   "any_f": "1"}},
+{"id": 4, "s_f": "true",  "d_f": 4.5, "i_f": 4, "b_f": false, "o_s_f": "true",  "o_d_f": 4.5, "o_i_f": 4, "o_b_f": false, "a": {"s_f": "true",  "any_f": "1.5"}},
+{"id": 5, "s_f": "false", "d_f": 5.5, "i_f": 5, "b_f": false, "o_s_f": "false", "o_d_f": 5.5, "o_i_f": 5, "o_b_f": false, "a": {"s_f": "false", "any_f": "str"}},
+{"id": 6, "s_f": "6",     "d_f": 6.5, "i_f": 6, "b_f": false, "o_s_f": "6",     "o_d_f": 6.5, "o_i_f": 6, "o_b_f": false, "a": {"s_f": "6",     "any_f": true}},
+{"id": 7, "s_f": "7.5",   "d_f": 7.5, "i_f": 7, "b_f": false, "o_s_f": "7.5",   "o_d_f": 7.5, "o_i_f": 7, "o_b_f": false, "a": {"s_f": "7.5",   "any_f": false}},
+{"id": 8, "s_f": "false", "d_f": 8.5, "i_f": 8, "b_f": false, "o_s_f": "false", "o_d_f": 8.5, "o_i_f": 8, "o_b_f": false, "a": {"s_f": "false", "any_f": [1,2]}},
+{"id": 9, "s_f": "false", "d_f": 9.5, "i_f": 9, "b_f": false}
+];
+
+INSERT INTO ds2 [
+{"id": 1, "s_f": "s",     "d_f": 1.5, "i_f": 1, "b_f": true,  "o_s_f": "s",     "o_d_f": 1.5, "o_i_f": 1, "o_b_f": true,  "a": {"s_f": "s",     "any_f": 1}},
+{"id": 2, "s_f": "2",     "d_f": 2.5, "i_f": 2, "b_f": false, "o_s_f": "2",     "o_d_f": 2.5, "o_i_f": 2, "o_b_f": false, "a": {"s_f": "2",     "any_f": 1.5}},
+{"id": 3, "s_f": "3.5",   "d_f": 3.5, "i_f": 3, "b_f": true,  "o_s_f": "3.5",   "o_d_f": 3.5, "o_i_f": 3, "o_b_f": true,  "a": {"s_f": "3.5",   "any_f": "1"}},
+{"id": 4, "s_f": "true",  "d_f": 4.5, "i_f": 4, "b_f": false, "o_s_f": "true",  "o_d_f": 4.5, "o_i_f": 4, "o_b_f": false, "a": {"s_f": "true",  "any_f": "1.5"}},
+{"id": 5, "s_f": "false", "d_f": 5.5, "i_f": 5, "b_f": false, "o_s_f": "false", "o_d_f": 5.5, "o_i_f": 5, "o_b_f": false, "a": {"s_f": "false", "any_f": "str"}},
+{"id": 6, "s_f": "6",     "d_f": 6.5, "i_f": 6, "b_f": false, "o_s_f": "6",     "o_d_f": 6.5, "o_i_f": 6, "o_b_f": false, "a": {"s_f": "6",     "any_f": true}},
+{"id": 7, "s_f": "7.5",   "d_f": 7.5, "i_f": 7, "b_f": false, "o_s_f": "7.5",   "o_d_f": 7.5, "o_i_f": 7, "o_b_f": false, "a": {"s_f": "7.5",   "any_f": false}},
+{"id": 8, "s_f": "false", "d_f": 8.5, "i_f": 8, "b_f": false, "o_s_f": "false", "o_d_f": 8.5, "o_i_f": 8, "o_b_f": false, "a": {"s_f": "false", "any_f": [1,2]}},
+{"id": 9, "s_f": "false", "d_f": 9.5, "i_f": 9, "b_f": false}
+];
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.002.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.002.ddl.sqlpp
new file mode 100644
index 0000000..7255983
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.002.ddl.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : tests the bulk load path of CREATE INDEX
+ */
+
+USE test;
+
+//CREATE INDEX ds1_idx1 ON ds1(s_f: int) CAST(DEFAULT NULL);
+
+CREATE INDEX ds1_o_idx1 ON ds1(o_s_f: int, o_i_f: string) INCLUDE UNKNOWN KEY CAST(DEFAULT NULL);
+CREATE INDEX ds1_o_idx2 ON ds1(o_s_f: double, o_d_f: string) CAST(DEFAULT NULL);
+//CREATE INDEX ds1_o_idx3 ON ds1(o_s_f: boolean, o_b_f: string) CAST(DEFAULT NULL);
+
+CREATE INDEX ds1_o_idx4 ON ds1(a.s_f: int) CAST(DEFAULT NULL);
+
+CREATE INDEX ds1_o_idx5 ON ds1(a.any_f: int) CAST(DEFAULT NULL);
+CREATE INDEX ds1_o_idx6 ON ds1(a.any_f: string) CAST(DEFAULT NULL);
+CREATE INDEX ds1_o_idx7 ON ds1(a.any_f: double) CAST(DEFAULT NULL);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.003.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.003.query.sqlpp
new file mode 100644
index 0000000..3106b2f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.003.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds2", "ds2_o_idx1") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.004.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.004.query.sqlpp
new file mode 100644
index 0000000..31e5fcd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.004.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds2", "ds2_o_idx2") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.005.query.sqlpp
new file mode 100644
index 0000000..1dee7d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.005.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds2", "ds2_o_idx4") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.006.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.006.query.sqlpp
new file mode 100644
index 0000000..6401d91
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.006.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds2", "ds2_o_idx5") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.007.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.007.query.sqlpp
new file mode 100644
index 0000000..12cc91f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.007.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds2", "ds2_o_idx6") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.008.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.008.query.sqlpp
new file mode 100644
index 0000000..ee605f3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.008.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds2", "ds2_o_idx7") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.009.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.009.query.sqlpp
new file mode 100644
index 0000000..7f0ddee
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.009.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds1", "ds1_o_idx1") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.010.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.010.query.sqlpp
new file mode 100644
index 0000000..afa0afa
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.010.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds1", "ds1_o_idx2") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.011.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.011.query.sqlpp
new file mode 100644
index 0000000..db14e84
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.011.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds1", "ds1_o_idx4") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.012.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.012.query.sqlpp
new file mode 100644
index 0000000..d7d6246
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.012.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds1", "ds1_o_idx5") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.013.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.013.query.sqlpp
new file mode 100644
index 0000000..c072a76
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.013.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds1", "ds1_o_idx6") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.014.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.014.query.sqlpp
new file mode 100644
index 0000000..086720b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.014.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds1", "ds1_o_idx7") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.015.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.015.query.sqlpp
new file mode 100644
index 0000000..e3c5f25
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.015.query.sqlpp
@@ -0,0 +1,22 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+// check the index metadata
+USE test;
+
+FROM Metadata.`Index` v WHERE v.DatasetName = 'ds2' AND v.IndexName = 'ds2_o_idx2' SELECT VALUE v;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.999.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.999.ddl.sqlpp
new file mode 100644
index 0000000..86a1b59
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.999.ddl.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.003.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.003.adm
new file mode 100644
index 0000000..07490c7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.003.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, null, 9 ] }
+{ "values": [ null, "1", 1 ] }
+{ "values": [ null, "3", 3 ] }
+{ "values": [ null, "4", 4 ] }
+{ "values": [ null, "5", 5 ] }
+{ "values": [ null, "7", 7 ] }
+{ "values": [ null, "8", 8 ] }
+{ "values": [ 2, "2", 2 ] }
+{ "values": [ 6, "6", 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.004.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.004.adm
new file mode 100644
index 0000000..cc2dc3f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.004.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, null, 9 ] }
+{ "values": [ null, "1.5", 1 ] }
+{ "values": [ null, "4.5", 4 ] }
+{ "values": [ null, "5.5", 5 ] }
+{ "values": [ null, "8.5", 8 ] }
+{ "values": [ 2.0, "2.5", 2 ] }
+{ "values": [ 3.5, "3.5", 3 ] }
+{ "values": [ 6.0, "6.5", 6 ] }
+{ "values": [ 7.5, "7.5", 7 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.005.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.005.adm
new file mode 100644
index 0000000..5ae6457
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.005.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, 1 ] }
+{ "values": [ null, 3 ] }
+{ "values": [ null, 4 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 7 ] }
+{ "values": [ null, 8 ] }
+{ "values": [ null, 9 ] }
+{ "values": [ 2, 2 ] }
+{ "values": [ 6, 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.006.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.006.adm
new file mode 100644
index 0000000..1c266b2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.006.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, 4 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 8 ] }
+{ "values": [ null, 9 ] }
+{ "values": [ 0, 7 ] }
+{ "values": [ 1, 1 ] }
+{ "values": [ 1, 2 ] }
+{ "values": [ 1, 3 ] }
+{ "values": [ 1, 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.007.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.007.adm
new file mode 100644
index 0000000..b6cff8e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.007.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, 8 ] }
+{ "values": [ null, 9 ] }
+{ "values": [ "1", 1 ] }
+{ "values": [ "1", 3 ] }
+{ "values": [ "1.5", 2 ] }
+{ "values": [ "1.5", 4 ] }
+{ "values": [ "false", 7 ] }
+{ "values": [ "str", 5 ] }
+{ "values": [ "true", 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.008.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.008.adm
new file mode 100644
index 0000000..ec983bf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.008.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, 5 ] }
+{ "values": [ null, 8 ] }
+{ "values": [ null, 9 ] }
+{ "values": [ 0.0, 7 ] }
+{ "values": [ 1.0, 1 ] }
+{ "values": [ 1.0, 3 ] }
+{ "values": [ 1.0, 6 ] }
+{ "values": [ 1.5, 2 ] }
+{ "values": [ 1.5, 4 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.009.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.009.adm
new file mode 100644
index 0000000..07490c7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.009.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, null, 9 ] }
+{ "values": [ null, "1", 1 ] }
+{ "values": [ null, "3", 3 ] }
+{ "values": [ null, "4", 4 ] }
+{ "values": [ null, "5", 5 ] }
+{ "values": [ null, "7", 7 ] }
+{ "values": [ null, "8", 8 ] }
+{ "values": [ 2, "2", 2 ] }
+{ "values": [ 6, "6", 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.010.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.010.adm
new file mode 100644
index 0000000..cc2dc3f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.010.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, null, 9 ] }
+{ "values": [ null, "1.5", 1 ] }
+{ "values": [ null, "4.5", 4 ] }
+{ "values": [ null, "5.5", 5 ] }
+{ "values": [ null, "8.5", 8 ] }
+{ "values": [ 2.0, "2.5", 2 ] }
+{ "values": [ 3.5, "3.5", 3 ] }
+{ "values": [ 6.0, "6.5", 6 ] }
+{ "values": [ 7.5, "7.5", 7 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.011.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.011.adm
new file mode 100644
index 0000000..5ae6457
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.011.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, 1 ] }
+{ "values": [ null, 3 ] }
+{ "values": [ null, 4 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 7 ] }
+{ "values": [ null, 8 ] }
+{ "values": [ null, 9 ] }
+{ "values": [ 2, 2 ] }
+{ "values": [ 6, 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.012.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.012.adm
new file mode 100644
index 0000000..1c266b2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.012.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, 4 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 8 ] }
+{ "values": [ null, 9 ] }
+{ "values": [ 0, 7 ] }
+{ "values": [ 1, 1 ] }
+{ "values": [ 1, 2 ] }
+{ "values": [ 1, 3 ] }
+{ "values": [ 1, 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.013.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.013.adm
new file mode 100644
index 0000000..b6cff8e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.013.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, 8 ] }
+{ "values": [ null, 9 ] }
+{ "values": [ "1", 1 ] }
+{ "values": [ "1", 3 ] }
+{ "values": [ "1.5", 2 ] }
+{ "values": [ "1.5", 4 ] }
+{ "values": [ "false", 7 ] }
+{ "values": [ "str", 5 ] }
+{ "values": [ "true", 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.014.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.014.adm
new file mode 100644
index 0000000..ec983bf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.014.adm
@@ -0,0 +1,9 @@
+{ "values": [ null, 5 ] }
+{ "values": [ null, 8 ] }
+{ "values": [ null, 9 ] }
+{ "values": [ 0.0, 7 ] }
+{ "values": [ 1.0, 1 ] }
+{ "values": [ 1.0, 3 ] }
+{ "values": [ 1.0, 6 ] }
+{ "values": [ 1.5, 2 ] }
+{ "values": [ 1.5, 4 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.015.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.015.adm
new file mode 100644
index 0000000..6080d55
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.015.adm
@@ -0,0 +1 @@
+{ "DataverseName": "test", "DatasetName": "ds2", "IndexName": "ds2_o_idx2", "IndexStructure": "BTREE", "SearchKey": [ [ "o_s_f" ], [ "o_d_f" ] ], "IsPrimary": false, "Timestamp": "Sun Oct 31 17:55:11 PDT 2021", "PendingOp": 0, "SearchKeyType": [ "double", "string" ], "ExcludeUnknownKey": false, "Cast": { "Default": null } }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 27575fb..597034d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -4308,6 +4308,20 @@
         <output-dir compare="Text">index-bad-fields</output-dir>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="ddl">
+      <compilation-unit name="index-cast-null">
+        <output-dir compare="Text">index-cast-null</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="ddl">
+      <compilation-unit name="index-cast-null-negative">
+        <output-dir compare="Text">index-cast-null-negative</output-dir>
+        <expected-error>Cast Default Null is only allowed for B-Tree indexes</expected-error>
+        <expected-error>Cast Default Null cannot be specified together with ENFORCED</expected-error>
+        <expected-error>CAST is not allowed since field "[typed_f1]" is typed</expected-error>
+        <expected-error>CAST is not allowed since field "[typed_f2]" is typed</expected-error>
+      </compilation-unit>
+    </test-case>
   </test-group>
   <test-group name="dml">
     <test-case FilePath="dml">
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java
index a9f3a0b..566838b 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java
@@ -50,10 +50,11 @@
     // Specific to FullText indexes.
     private final String fullTextConfigName;
     private final OptionalBoolean excludeUnknownKey;
+    private final OptionalBoolean castDefaultNull;
 
     public CreateIndexStatement(DataverseName dataverseName, Identifier datasetName, Identifier indexName,
             IndexType indexType, List<IndexedElement> indexedElements, boolean enforced, int gramLength,
-            String fullTextConfigName, boolean ifNotExists, Boolean excludeUnknownKey) {
+            String fullTextConfigName, boolean ifNotExists, Boolean excludeUnknownKey, Boolean castDefaultNull) {
         this.dataverseName = dataverseName;
         this.datasetName = Objects.requireNonNull(datasetName);
         this.indexName = Objects.requireNonNull(indexName);
@@ -64,6 +65,7 @@
         this.ifNotExists = ifNotExists;
         this.fullTextConfigName = fullTextConfigName;
         this.excludeUnknownKey = OptionalBoolean.ofNullable(excludeUnknownKey);
+        this.castDefaultNull = OptionalBoolean.ofNullable(castDefaultNull);
     }
 
     public String getFullTextConfigName() {
@@ -98,10 +100,18 @@
         return excludeUnknownKey.isPresent();
     }
 
-    public OptionalBoolean isExcludeUnknownKey() {
+    public OptionalBoolean getExcludeUnknownKey() {
         return excludeUnknownKey;
     }
 
+    public boolean hasCastDefaultNull() {
+        return castDefaultNull.isPresent();
+    }
+
+    public OptionalBoolean getCastDefaultNull() {
+        return castDefaultNull;
+    }
+
     public int getGramLength() {
         return gramLength;
     }
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/ViewUtil.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/ViewUtil.java
index 7da44ee..19b8946 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/ViewUtil.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/ViewUtil.java
@@ -44,6 +44,7 @@
 import org.apache.asterix.lang.common.struct.Identifier;
 import org.apache.asterix.lang.common.struct.VarIdentifier;
 import org.apache.asterix.metadata.entities.ViewDetails;
+import org.apache.asterix.metadata.utils.TypeUtil;
 import org.apache.asterix.om.functions.BuiltinFunctions;
 import org.apache.asterix.om.types.ARecordType;
 import org.apache.asterix.om.types.ATypeTag;
@@ -143,7 +144,7 @@
             } else {
                 primeType = fieldType;
             }
-            if (getTypeConstructor(primeType) == null) {
+            if (TypeUtil.getTypeConstructor(primeType) == null) {
                 throw new CompilationException(ErrorCode.COMPILATION_TYPE_UNSUPPORTED, sourceLoc, "view",
                         primeType.getTypeName());
             }
@@ -175,8 +176,8 @@
             SourceLocation sourceLoc) throws CompilationException {
         String format = temporalDataFormat != null ? getTemporalFormat(targetType, temporalDataFormat) : null;
         boolean withFormat = format != null;
-        FunctionIdentifier constrFid =
-                withFormat ? getTypeConstructorWithFormat(targetType) : getTypeConstructor(targetType);
+        FunctionIdentifier constrFid = withFormat ? TypeUtil.getTypeConstructorWithFormat(targetType)
+                : TypeUtil.getTypeConstructor(targetType);
         if (constrFid == null) {
             throw new CompilationException(ErrorCode.COMPILATION_TYPE_UNSUPPORTED, sourceLoc, viewName.toString(),
                     targetType.getTypeName());
@@ -223,58 +224,6 @@
         return fa;
     }
 
-    public static FunctionIdentifier getTypeConstructor(IAType type) {
-        switch (type.getTypeTag()) {
-            case TINYINT:
-                return BuiltinFunctions.INT8_CONSTRUCTOR;
-            case SMALLINT:
-                return BuiltinFunctions.INT16_CONSTRUCTOR;
-            case INTEGER:
-                return BuiltinFunctions.INT32_CONSTRUCTOR;
-            case BIGINT:
-                return BuiltinFunctions.INT64_CONSTRUCTOR;
-            case FLOAT:
-                return BuiltinFunctions.FLOAT_CONSTRUCTOR;
-            case DOUBLE:
-                return BuiltinFunctions.DOUBLE_CONSTRUCTOR;
-            case BOOLEAN:
-                return BuiltinFunctions.BOOLEAN_CONSTRUCTOR;
-            case STRING:
-                return BuiltinFunctions.STRING_CONSTRUCTOR;
-            case DATE:
-                return BuiltinFunctions.DATE_CONSTRUCTOR;
-            case TIME:
-                return BuiltinFunctions.TIME_CONSTRUCTOR;
-            case DATETIME:
-                return BuiltinFunctions.DATETIME_CONSTRUCTOR;
-            case YEARMONTHDURATION:
-                return BuiltinFunctions.YEAR_MONTH_DURATION_CONSTRUCTOR;
-            case DAYTIMEDURATION:
-                return BuiltinFunctions.DAY_TIME_DURATION_CONSTRUCTOR;
-            case DURATION:
-                return BuiltinFunctions.DURATION_CONSTRUCTOR;
-            case UUID:
-                return BuiltinFunctions.UUID_CONSTRUCTOR;
-            case BINARY:
-                return BuiltinFunctions.BINARY_BASE64_CONSTRUCTOR;
-            default:
-                return null;
-        }
-    }
-
-    public static FunctionIdentifier getTypeConstructorWithFormat(IAType type) {
-        switch (type.getTypeTag()) {
-            case DATE:
-                return BuiltinFunctions.DATE_CONSTRUCTOR_WITH_FORMAT;
-            case TIME:
-                return BuiltinFunctions.TIME_CONSTRUCTOR_WITH_FORMAT;
-            case DATETIME:
-                return BuiltinFunctions.DATETIME_CONSTRUCTOR_WITH_FORMAT;
-            default:
-                return null;
-        }
-    }
-
     public static String getTemporalFormat(IAType targetType, Triple<String, String, String> temporalFormatByType) {
         switch (targetType.getTypeTag()) {
             case DATETIME:
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index facdfa7..9f9ab3d 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -284,7 +284,7 @@
         this.gramLength = gramLength;
         this.fullTextConfig = fullTextConfig;
       }
-    };
+    }
 
     private static class FunctionName {
        public DataverseName dataverse;
@@ -349,7 +349,7 @@
         super.setInput(s);
     }
 
-    public static void main(String args[]) throws ParseException, TokenMgrError, IOException, FileNotFoundException, CompilationException {
+    public static void main(String[] args) throws ParseException, TokenMgrError, IOException, FileNotFoundException, CompilationException {
         File file = new File(args[0]);
         Reader fis = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF-8"));
         SQLPPParser parser = new SQLPPParser(fis);
@@ -1192,6 +1192,7 @@
   String fullTextConfigName = null;
   Token startElementToken = null;
   Boolean excludeUnknown = null;
+  Boolean castDefaultNull = null;
 }
 {
   (
@@ -1210,17 +1211,19 @@
       )*
     <RIGHTPAREN>
     ( <TYPE> indexParams = IndexType() )? ( <ENFORCED> { enforced = true; } )?
-    ( <IDENTIFIER>
-      {
-        if (isToken(EXCLUDE)) {
-          excludeUnknown = true;
-        } else if (isToken(INCLUDE)) {
-          excludeUnknown = false;
-        } else {
-          throw createUnexpectedTokenError();
-        }
-      } <UNKNOWN> <KEY>
+    ( LOOKAHEAD({laIdentifier(EXCLUDE) || laIdentifier(INCLUDE)}) <IDENTIFIER>
+    {
+      if (isToken(EXCLUDE)) {
+        excludeUnknown = true;
+      } else if (isToken(INCLUDE)) {
+        excludeUnknown = false;
+      } else {
+        throw createUnexpectedTokenError();
+      }
+    } <UNKNOWN> <KEY>
     )?
+
+    ( <CAST><LEFTPAREN><IDENTIFIER> { expectToken(DEFAULT); } <NULL><RIGHTPAREN> { castDefaultNull = true; })?
   )
   {
     IndexType indexType;
@@ -1236,7 +1239,7 @@
     }
     CreateIndexStatement stmt = new CreateIndexStatement(nameComponents.first, nameComponents.second,
       new Identifier(indexName), indexType, indexedElementList, enforced, gramLength, fullTextConfigName, ifNotExists,
-      excludeUnknown);
+      excludeUnknown, castDefaultNull);
     return addSourceLocation(stmt, startStmtToken);
   }
 }
@@ -1365,7 +1368,7 @@
       indexName = "primary_idx_" + nameComponents.second;
     }
     CreateIndexStatement stmt = new CreateIndexStatement(nameComponents.first, nameComponents.second,
-      new Identifier(indexName), IndexType.BTREE, Collections.emptyList(), false, -1, null, ifNotExists, null);
+      new Identifier(indexName), IndexType.BTREE, Collections.emptyList(), false, -1, null, ifNotExists, null, null);
     return addSourceLocation(stmt, startStmtToken);
   }
 }
@@ -5291,6 +5294,7 @@
   | <BTREE : "btree">
   | <BY : "by">
   | <CASE : "case">
+  | <CAST : "cast">
   | <CLOSED : "closed">
   | <CREATE : "create">
   | <CROSS : "cross">
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/bootstrap/MetadataRecordTypes.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/bootstrap/MetadataRecordTypes.java
index 48d4fc5..f00090a 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/bootstrap/MetadataRecordTypes.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/bootstrap/MetadataRecordTypes.java
@@ -37,6 +37,7 @@
     public static final String FIELD_NAME_ARITY = "Arity";
     public static final String FIELD_NAME_ARGS = "Arguments";
     public static final String FIELD_NAME_AUTOGENERATED = "Autogenerated";
+    public static final String FIELD_NAME_CAST = "Cast";
     public static final String FIELD_NAME_CLASSNAME = "Classname";
     public static final String FIELD_NAME_COMPACTION_POLICY = "CompactionPolicy";
     public static final String FIELD_NAME_COMPACTION_POLICY_PROPERTIES = "CompactionPolicyProperties";
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java
index 6ca0c10..54b43db 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java
@@ -84,18 +84,18 @@
             List<List<String>> keyFieldNames, List<Integer> keyFieldSourceIndicators, List<IAType> keyFieldTypes,
             boolean overrideKeyFieldTypes, boolean isEnforced, boolean isPrimaryIndex, int pendingOp,
             OptionalBoolean excludeUnknownKey) {
-        this(dataverseName, datasetName,
-                indexName, indexType, createSimpleIndexDetails(indexType, keyFieldNames, keyFieldSourceIndicators,
-                        keyFieldTypes, overrideKeyFieldTypes, excludeUnknownKey),
+        this(dataverseName, datasetName, indexName, indexType,
+                createSimpleIndexDetails(indexType, keyFieldNames, keyFieldSourceIndicators, keyFieldTypes,
+                        overrideKeyFieldTypes, excludeUnknownKey, OptionalBoolean.empty()),
                 isEnforced, isPrimaryIndex, pendingOp);
     }
 
     public static Index createPrimaryIndex(DataverseName dataverseName, String datasetName,
             List<List<String>> keyFieldNames, List<Integer> keyFieldSourceIndicators, List<IAType> keyFieldTypes,
             int pendingOp) {
-        return new Index(dataverseName,
-                datasetName, datasetName, IndexType.BTREE, new ValueIndexDetails(keyFieldNames,
-                        keyFieldSourceIndicators, keyFieldTypes, false, OptionalBoolean.empty()),
+        return new Index(dataverseName, datasetName,
+                datasetName, IndexType.BTREE, new ValueIndexDetails(keyFieldNames, keyFieldSourceIndicators,
+                        keyFieldTypes, false, OptionalBoolean.empty(), OptionalBoolean.empty()),
                 false, true, pendingOp);
     }
 
@@ -229,7 +229,7 @@
 
     @Override
     public int compareTo(Index otherIndex) {
-        /** Gives a primary index first priority. */
+        /* Gives a primary index first priority. */
         if (isPrimaryIndex && !otherIndex.isPrimaryIndex) {
             return -1;
         }
@@ -237,7 +237,7 @@
             return 1;
         }
 
-        /** Gives a B-Tree index the second priority. */
+        /* Gives a B-Tree index the second priority. */
         if (indexType == IndexType.BTREE && otherIndex.indexType != IndexType.BTREE) {
             return -1;
         }
@@ -245,7 +245,7 @@
             return 1;
         }
 
-        /** Gives a R-Tree index the third priority */
+        /* Gives a R-Tree index the third priority */
         if (indexType == IndexType.RTREE && otherIndex.indexType != IndexType.RTREE) {
             return -1;
         }
@@ -253,7 +253,7 @@
             return 1;
         }
 
-        /** Finally, compares based on names. */
+        /* Finally, compares based on names. */
         int result = indexName.compareTo(otherIndex.getIndexName());
         if (result != 0) {
             return result;
@@ -335,13 +335,17 @@
 
         private final Boolean excludeUnknownKey;
 
+        private final Boolean castDefaultNull;
+
         public ValueIndexDetails(List<List<String>> keyFieldNames, List<Integer> keyFieldSourceIndicators,
-                List<IAType> keyFieldTypes, boolean overrideKeyFieldTypes, OptionalBoolean excludeUnknownKey) {
+                List<IAType> keyFieldTypes, boolean overrideKeyFieldTypes, OptionalBoolean excludeUnknownKey,
+                OptionalBoolean castDefaultNull) {
             this.keyFieldNames = keyFieldNames;
             this.keyFieldSourceIndicators = keyFieldSourceIndicators;
             this.keyFieldTypes = keyFieldTypes;
             this.overrideKeyFieldTypes = overrideKeyFieldTypes;
             this.excludeUnknownKey = excludeUnknownKey.isEmpty() ? null : excludeUnknownKey.get();
+            this.castDefaultNull = castDefaultNull.isEmpty() ? null : castDefaultNull.get();
         }
 
         @Override
@@ -361,10 +365,14 @@
             return keyFieldTypes;
         }
 
-        public OptionalBoolean isExcludeUnknownKey() {
+        public OptionalBoolean getExcludeUnknownKey() {
             return OptionalBoolean.ofNullable(excludeUnknownKey);
         }
 
+        public OptionalBoolean getCastDefaultNull() {
+            return OptionalBoolean.ofNullable(castDefaultNull);
+        }
+
         @Override
         public boolean isOverridingKeyFieldTypes() {
             return overrideKeyFieldTypes;
@@ -500,14 +508,14 @@
     @Deprecated
     private static Index.IIndexDetails createSimpleIndexDetails(IndexType indexType, List<List<String>> keyFieldNames,
             List<Integer> keyFieldSourceIndicators, List<IAType> keyFieldTypes, boolean overrideKeyFieldTypes,
-            OptionalBoolean excludeUnknownKey) {
+            OptionalBoolean excludeUnknownKey, OptionalBoolean castDefaultNull) {
         if (indexType == null) {
             return null;
         }
         switch (Index.IndexCategory.of(indexType)) {
             case VALUE:
                 return new ValueIndexDetails(keyFieldNames, keyFieldSourceIndicators, keyFieldTypes,
-                        overrideKeyFieldTypes, excludeUnknownKey);
+                        overrideKeyFieldTypes, excludeUnknownKey, castDefaultNull);
             case TEXT:
                 if (excludeUnknownKey.isPresent()) {
                     throw new IllegalArgumentException("excludeUnknownKey");
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
index f2a4f4f..35c34e1 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
@@ -19,6 +19,9 @@
 
 package org.apache.asterix.metadata.entitytupletranslators;
 
+import static org.apache.asterix.metadata.bootstrap.MetadataRecordTypes.FIELD_NAME_CAST;
+import static org.apache.asterix.metadata.bootstrap.MetadataRecordTypes.FIELD_NAME_DEFAULT;
+
 import java.util.ArrayList;
 import java.util.Calendar;
 import java.util.Collections;
@@ -96,6 +99,7 @@
     protected OrderedListBuilder primaryKeyListBuilder;
     protected OrderedListBuilder complexSearchKeyNameListBuilder;
     protected IARecordBuilder complexSearchKeyNameRecordBuilder;
+    protected IARecordBuilder castRecordBuilder;
     protected AOrderedListType stringList;
     protected AOrderedListType int8List;
     protected ArrayBackedValueStorage nameValue;
@@ -114,6 +118,7 @@
             innerListBuilder = new OrderedListBuilder();
             primaryKeyListBuilder = new OrderedListBuilder();
             complexSearchKeyNameRecordBuilder = new RecordBuilder();
+            castRecordBuilder = new RecordBuilder();
             complexSearchKeyNameListBuilder = new OrderedListBuilder();
             stringList = new AOrderedListType(BuiltinType.ASTRING, null);
             int8List = new AOrderedListType(BuiltinType.AINT8, null);
@@ -383,21 +388,37 @@
                         searchElements.stream().map(Pair::getSecond).map(l -> l.get(0)).collect(Collectors.toList());
                 List<IAType> keyFieldTypes = searchKeyType.stream().map(l -> l.get(0)).collect(Collectors.toList());
 
-                // Read the exclude unknown key option if applicable for an index
                 OptionalBoolean excludeUnknownKey = OptionalBoolean.empty();
-                boolean unknownKeyOptionAllowed =
-                        indexType == IndexType.BTREE && !isPrimaryIndex && !keyFieldNames.isEmpty();
-                if (unknownKeyOptionAllowed) {
-                    // default to always include unknowns for normal b-trees
+                OptionalBoolean castDefaultNull = OptionalBoolean.empty();
+                boolean isBtreeIdx = indexType == IndexType.BTREE && !isPrimaryIndex && !keyFieldNames.isEmpty();
+                if (isBtreeIdx) {
+                    // exclude unknown key value; default to always include unknowns for normal b-trees
                     excludeUnknownKey = OptionalBoolean.FALSE();
                     int excludeUnknownKeyPos = indexRecord.getType().getFieldIndex(INDEX_EXCLUDE_UNKNOWN_FIELD_NAME);
                     if (excludeUnknownKeyPos >= 0) {
                         excludeUnknownKey = OptionalBoolean
                                 .of(((ABoolean) indexRecord.getValueByPos(excludeUnknownKeyPos)).getBoolean());
                     }
+                    // cast record
+                    int castPos = indexRecord.getType().getFieldIndex(FIELD_NAME_CAST);
+                    if (castPos >= 0) {
+                        IAObject recValue = indexRecord.getValueByPos(castPos);
+                        if (recValue.getType().getTypeTag() == ATypeTag.OBJECT) {
+                            ARecord castRec = (ARecord) recValue;
+                            ARecordType castRecType = castRec.getType();
+                            // cast default value
+                            int defaultFieldPos = castRecType.getFieldIndex(FIELD_NAME_DEFAULT);
+                            if (defaultFieldPos >= 0) {
+                                IAObject defaultVal = castRec.getValueByPos(defaultFieldPos);
+                                if (defaultVal.getType().getTypeTag() == ATypeTag.NULL) {
+                                    castDefaultNull = OptionalBoolean.TRUE();
+                                }
+                            }
+                        }
+                    }
                 }
                 indexDetails = new Index.ValueIndexDetails(keyFieldNames, keyFieldSourceIndicator, keyFieldTypes,
-                        isOverridingKeyTypes, excludeUnknownKey);
+                        isOverridingKeyTypes, excludeUnknownKey, castDefaultNull);
                 break;
             case TEXT:
                 keyFieldNames =
@@ -566,6 +587,7 @@
         writeEnforced(index);
         writeSearchKeySourceIndicator(index);
         writeExcludeUnknownKey(index);
+        writeCastDefaultNull(index);
     }
 
     private void writeComplexSearchKeys(Index.ArrayIndexDetails indexDetails) throws HyracksDataException {
@@ -770,10 +792,9 @@
         switch (index.getIndexType()) {
             case BTREE:
                 if (!index.isPrimaryIndex() && !index.isPrimaryKeyIndex()) {
-                    OptionalBoolean excludeUnknownKey =
-                            ((Index.ValueIndexDetails) index.getIndexDetails()).isExcludeUnknownKey();
-                    ABoolean bVal =
-                            excludeUnknownKey.isEmpty() ? ABoolean.FALSE : ABoolean.valueOf(excludeUnknownKey.get());
+                    OptionalBoolean excludeUnknown =
+                            ((Index.ValueIndexDetails) index.getIndexDetails()).getExcludeUnknownKey();
+                    ABoolean bVal = excludeUnknown.isEmpty() ? ABoolean.FALSE : ABoolean.valueOf(excludeUnknown.get());
                     fieldValue.reset();
                     nameValue.reset();
                     aString.setValue(INDEX_EXCLUDE_UNKNOWN_FIELD_NAME);
@@ -794,4 +815,28 @@
                 break;
         }
     }
+
+    private void writeCastDefaultNull(Index index) throws HyracksDataException {
+        if (index.getIndexType() == IndexType.BTREE && !index.isPrimaryIndex() && !index.isPrimaryKeyIndex()) {
+            boolean defaultNull =
+                    ((Index.ValueIndexDetails) index.getIndexDetails()).getCastDefaultNull().getOrElse(false);
+            // "Default" field
+            if (defaultNull) {
+                castRecordBuilder.reset(RecordUtil.FULLY_OPEN_RECORD_TYPE);
+                fieldValue.reset();
+                nameValue.reset();
+                aString.setValue(FIELD_NAME_DEFAULT);
+                stringSerde.serialize(aString, nameValue.getDataOutput());
+                nullSerde.serialize(ANull.NULL, fieldValue.getDataOutput());
+                castRecordBuilder.addField(nameValue, fieldValue);
+
+                nameValue.reset();
+                fieldValue.reset();
+                aString.setValue(FIELD_NAME_CAST);
+                stringSerde.serialize(aString, nameValue.getDataOutput());
+                castRecordBuilder.write(fieldValue.getDataOutput(), true);
+                recordBuilder.addField(nameValue, fieldValue);
+            }
+        }
+    }
 }
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
index f15502f..24f2249 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
@@ -179,4 +179,8 @@
         spec.setJobletEventListenerFactory(jobEventListenerFactory);
     }
 
+    public static boolean castDefaultNull(Index index) {
+        return Index.IndexCategory.of(index.getIndexType()) == Index.IndexCategory.VALUE
+                && ((Index.ValueIndexDetails) index.getIndexDetails()).getCastDefaultNull().getOrElse(false);
+    }
 }
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
index bcd18a7..61c2df8 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
@@ -21,18 +21,28 @@
 import java.util.List;
 
 import org.apache.asterix.common.config.DatasetConfig.DatasetType;
+import org.apache.asterix.common.exceptions.CompilationException;
+import org.apache.asterix.common.exceptions.ErrorCode;
 import org.apache.asterix.common.utils.StorageConstants;
 import org.apache.asterix.external.indexing.IndexingConstants;
 import org.apache.asterix.external.operators.ExternalScanOperatorDescriptor;
+import org.apache.asterix.formats.base.IDataFormat;
 import org.apache.asterix.metadata.declared.MetadataProvider;
 import org.apache.asterix.metadata.entities.Dataset;
 import org.apache.asterix.metadata.entities.Index;
 import org.apache.asterix.metadata.entities.InternalDatasetDetails;
+import org.apache.asterix.om.functions.BuiltinFunctions;
+import org.apache.asterix.om.functions.IFunctionDescriptor;
+import org.apache.asterix.om.functions.IFunctionManager;
+import org.apache.asterix.om.typecomputer.impl.TypeComputeUtils;
 import org.apache.asterix.om.types.ARecordType;
+import org.apache.asterix.om.types.BuiltinType;
 import org.apache.asterix.om.types.IAType;
 import org.apache.asterix.runtime.utils.RuntimeUtils;
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
 import org.apache.hyracks.algebricks.common.utils.Pair;
+import org.apache.hyracks.algebricks.core.algebra.expressions.ConstantExpression;
+import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
 import org.apache.hyracks.algebricks.core.jobgen.impl.ConnectorPolicyAssignmentPolicy;
 import org.apache.hyracks.algebricks.data.IBinaryComparatorFactoryProvider;
 import org.apache.hyracks.algebricks.data.ISerializerDeserializerProvider;
@@ -48,13 +58,11 @@
 import org.apache.hyracks.api.dataflow.value.RecordDescriptor;
 import org.apache.hyracks.api.exceptions.SourceLocation;
 import org.apache.hyracks.api.job.JobSpecification;
-import org.apache.hyracks.dataflow.std.base.AbstractOperatorDescriptor;
 import org.apache.hyracks.dataflow.std.base.AbstractSingleActivityOperatorDescriptor;
 import org.apache.hyracks.dataflow.std.connectors.OneToOneConnectorDescriptor;
 import org.apache.hyracks.dataflow.std.sort.ExternalSortOperatorDescriptor;
 import org.apache.hyracks.storage.am.common.dataflow.IIndexDataflowHelperFactory;
 import org.apache.hyracks.storage.am.common.dataflow.IndexDataflowHelperFactory;
-import org.apache.hyracks.util.OptionalBoolean;
 
 public class SecondaryBTreeOperationsHelper extends SecondaryTreeIndexOperationsHelper {
 
@@ -84,11 +92,6 @@
             ExternalScanOperatorDescriptor primaryScanOp = createExternalIndexingOp(spec);
 
             // Assign op.
-            AbstractOperatorDescriptor sourceOp = primaryScanOp;
-            if (isOverridingKeyFieldTypes && !enforcedItemType.equals(itemType)) {
-                sourceOp = createCastOp(spec, dataset.getDatasetType(), index.isEnforced());
-                spec.connect(new OneToOneConnectorDescriptor(spec), primaryScanOp, 0, sourceOp, 0);
-            }
             AlgebricksMetaOperatorDescriptor asterixAssignOp =
                     createExternalAssignOp(spec, indexDetails.getKeyFieldNames().size(), secondaryRecDesc);
 
@@ -119,7 +122,7 @@
             metaOp.setSourceLocation(sourceLoc);
             spec.connect(new OneToOneConnectorDescriptor(spec), secondaryBulkLoadOp, 0, metaOp, 0);
             root = metaOp;
-            spec.connect(new OneToOneConnectorDescriptor(spec), sourceOp, 0, asterixAssignOp, 0);
+            spec.connect(new OneToOneConnectorDescriptor(spec), primaryScanOp, 0, asterixAssignOp, 0);
             if (excludeUnknown) {
                 spec.connect(new OneToOneConnectorDescriptor(spec), asterixAssignOp, 0, selectOp, 0);
                 spec.connect(new OneToOneConnectorDescriptor(spec), selectOp, 0, sortOp, 0);
@@ -141,13 +144,7 @@
             spec.connect(new OneToOneConnectorDescriptor(spec), sourceOp, 0, targetOp, 0);
 
             sourceOp = targetOp;
-            if (isOverridingKeyFieldTypes && !enforcedItemType.equals(itemType)) {
-                // primary index scan ----> cast assign
-                targetOp = createCastOp(spec, dataset.getDatasetType(), index.isEnforced());
-                spec.connect(new OneToOneConnectorDescriptor(spec), sourceOp, 0, targetOp, 0);
-                sourceOp = targetOp;
-            }
-            // primary index OR cast assign ----> assign op
+            // primary index ----> cast assign op
             targetOp = createAssignOp(spec, indexDetails.getKeyFieldNames().size(), secondaryRecDesc);
             spec.connect(new OneToOneConnectorDescriptor(spec), sourceOp, 0, targetOp, 0);
 
@@ -233,18 +230,20 @@
         boolean isOverridingKeyFieldTypes = indexDetails.isOverridingKeyFieldTypes();
         for (int i = 0; i < numSecondaryKeys; i++) {
             ARecordType sourceType;
+            ARecordType enforcedType;
             int sourceColumn;
             List<Integer> keySourceIndicators = indexDetails.getKeyFieldSourceIndicators();
             if (keySourceIndicators == null || keySourceIndicators.get(i) == 0) {
                 sourceType = itemType;
                 sourceColumn = recordColumn;
+                enforcedType = enforcedItemType;
             } else {
                 sourceType = metaType;
                 sourceColumn = recordColumn + 1;
+                enforcedType = enforcedMetaType;
             }
-            secondaryFieldAccessEvalFactories[i] = metadataProvider.getDataFormat().getFieldAccessEvaluatorFactory(
-                    metadataProvider.getFunctionManager(), isOverridingKeyFieldTypes ? enforcedItemType : sourceType,
-                    indexDetails.getKeyFieldNames().get(i), sourceColumn, sourceLoc);
+            secondaryFieldAccessEvalFactories[i] = createFieldAccessors(i, isOverridingKeyFieldTypes, enforcedType,
+                    sourceType, sourceColumn, indexDetails, indexDetails.getKeyFieldTypes().get(i));
             Pair<IAType, Boolean> keyTypePair = Index.getNonNullableOpenFieldType(
                     indexDetails.getKeyFieldTypes().get(i), indexDetails.getKeyFieldNames().get(i), sourceType);
             IAType keyType = keyTypePair.first;
@@ -302,6 +301,51 @@
 
     }
 
+    private IScalarEvaluatorFactory createFieldAccessors(int field, boolean isOverridingKeyFieldTypes,
+            IAType enforcedRecordType, ARecordType recordType, int recordColumn, Index.ValueIndexDetails indexDetails,
+            IAType fieldType) throws AlgebricksException {
+        IFunctionManager funManger = metadataProvider.getFunctionManager();
+        IDataFormat dataFormat = metadataProvider.getDataFormat();
+        IScalarEvaluatorFactory fieldEvalFactory = dataFormat.getFieldAccessEvaluatorFactory(funManger, recordType,
+                indexDetails.getKeyFieldNames().get(field), recordColumn, sourceLoc);
+        boolean castIndexedField = isOverridingKeyFieldTypes && !enforcedRecordType.equals(recordType);
+        if (!castIndexedField) {
+            return fieldEvalFactory;
+        }
+
+        IScalarEvaluatorFactory castFieldEvalFactory;
+        if (IndexUtil.castDefaultNull(index)) {
+            castFieldEvalFactory = createConstructorFunction(funManger, dataFormat, fieldEvalFactory, fieldType);
+        } else if (index.isEnforced()) {
+            IScalarEvaluatorFactory[] castArg = new IScalarEvaluatorFactory[] { fieldEvalFactory };
+            castFieldEvalFactory =
+                    createCastFunction(fieldType, BuiltinType.ANY, true, sourceLoc).createEvaluatorFactory(castArg);
+        } else {
+            IScalarEvaluatorFactory[] castArg = new IScalarEvaluatorFactory[] { fieldEvalFactory };
+            castFieldEvalFactory =
+                    createCastFunction(fieldType, BuiltinType.ANY, false, sourceLoc).createEvaluatorFactory(castArg);
+        }
+        return castFieldEvalFactory;
+    }
+
+    private IScalarEvaluatorFactory createConstructorFunction(IFunctionManager funManager, IDataFormat dataFormat,
+            IScalarEvaluatorFactory fieldEvalFactory, IAType fieldType) throws AlgebricksException {
+        // make CONSTRUCTOR(IF_MISSING(field_access, NULL))
+        IFunctionDescriptor ifMissing = funManager.lookupFunction(BuiltinFunctions.IF_MISSING, sourceLoc);
+        IScalarEvaluatorFactory nullEvalFactory = dataFormat.getConstantEvalFactory(ConstantExpression.NULL.getValue());
+        IScalarEvaluatorFactory[] ifMissingArgs = new IScalarEvaluatorFactory[] { fieldEvalFactory, nullEvalFactory };
+        ifMissing.setSourceLocation(sourceLoc);
+        IScalarEvaluatorFactory ifMissingEvalFactory = ifMissing.createEvaluatorFactory(ifMissingArgs);
+        FunctionIdentifier typeConstructorFun = TypeUtil.getTypeConstructor(TypeComputeUtils.getActualType(fieldType));
+        if (typeConstructorFun == null) {
+            throw new CompilationException(ErrorCode.COMPILATION_TYPE_UNSUPPORTED, sourceLoc, "index",
+                    fieldType.getTypeName());
+        }
+        IFunctionDescriptor typeConstructor = funManager.lookupFunction(typeConstructorFun, sourceLoc);
+        typeConstructor.setSourceLocation(sourceLoc);
+        return typeConstructor.createEvaluatorFactory(new IScalarEvaluatorFactory[] { ifMissingEvalFactory });
+    }
+
     private int[] createFieldPermutationForBulkLoadOp(int numSecondaryKeyFields) {
         int[] fieldPermutation = new int[numSecondaryKeyFields + numPrimaryKeys + numFilterFields];
         for (int i = 0; i < fieldPermutation.length; i++) {
@@ -311,11 +355,6 @@
     }
 
     private static boolean excludeUnknowns(Index index, Index.ValueIndexDetails details) {
-        if (index.isPrimaryKeyIndex()) {
-            return true;
-        } else {
-            OptionalBoolean excludeUnknownKey = details.isExcludeUnknownKey();
-            return excludeUnknownKey.isPresent() && excludeUnknownKey.get();
-        }
+        return index.isPrimaryKeyIndex() || details.getExcludeUnknownKey().getOrElse(false);
     }
 }
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
index b318fe2..035ae74 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
@@ -344,10 +344,15 @@
     }
 
     IFunctionDescriptor createCastFunction(boolean strictCast, SourceLocation sourceLoc) throws AlgebricksException {
+        return createCastFunction(enforcedItemType, itemType, strictCast, sourceLoc);
+    }
+
+    IFunctionDescriptor createCastFunction(IAType targetType, IAType inputType, boolean strictCast,
+            SourceLocation sourceLoc) throws AlgebricksException {
         IFunctionDescriptor castFuncDesc = metadataProvider.getFunctionManager()
                 .lookupFunction(strictCast ? BuiltinFunctions.CAST_TYPE : BuiltinFunctions.CAST_TYPE_LAX, sourceLoc);
         castFuncDesc.setSourceLocation(sourceLoc);
-        castFuncDesc.setImmutableStates(enforcedItemType, itemType);
+        castFuncDesc.setImmutableStates(targetType, inputType);
         return castFuncDesc;
     }
 
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/TypeUtil.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/TypeUtil.java
index b48e0a9..661ad0b 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/TypeUtil.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/TypeUtil.java
@@ -33,6 +33,7 @@
 import org.apache.asterix.metadata.entities.Dataset;
 import org.apache.asterix.metadata.entities.Function;
 import org.apache.asterix.metadata.entities.Index;
+import org.apache.asterix.om.functions.BuiltinFunctions;
 import org.apache.asterix.om.typecomputer.impl.TypeComputeUtils;
 import org.apache.asterix.om.types.AOrderedListType;
 import org.apache.asterix.om.types.ARecordType;
@@ -45,6 +46,7 @@
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
 import org.apache.hyracks.algebricks.common.utils.Pair;
 import org.apache.hyracks.algebricks.common.utils.Triple;
+import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
 
 /**
  * Provider utility methods for data types
@@ -60,6 +62,58 @@
     private TypeUtil() {
     }
 
+    public static FunctionIdentifier getTypeConstructor(IAType type) {
+        switch (type.getTypeTag()) {
+            case TINYINT:
+                return BuiltinFunctions.INT8_CONSTRUCTOR;
+            case SMALLINT:
+                return BuiltinFunctions.INT16_CONSTRUCTOR;
+            case INTEGER:
+                return BuiltinFunctions.INT32_CONSTRUCTOR;
+            case BIGINT:
+                return BuiltinFunctions.INT64_CONSTRUCTOR;
+            case FLOAT:
+                return BuiltinFunctions.FLOAT_CONSTRUCTOR;
+            case DOUBLE:
+                return BuiltinFunctions.DOUBLE_CONSTRUCTOR;
+            case BOOLEAN:
+                return BuiltinFunctions.BOOLEAN_CONSTRUCTOR;
+            case STRING:
+                return BuiltinFunctions.STRING_CONSTRUCTOR;
+            case DATE:
+                return BuiltinFunctions.DATE_CONSTRUCTOR;
+            case TIME:
+                return BuiltinFunctions.TIME_CONSTRUCTOR;
+            case DATETIME:
+                return BuiltinFunctions.DATETIME_CONSTRUCTOR;
+            case YEARMONTHDURATION:
+                return BuiltinFunctions.YEAR_MONTH_DURATION_CONSTRUCTOR;
+            case DAYTIMEDURATION:
+                return BuiltinFunctions.DAY_TIME_DURATION_CONSTRUCTOR;
+            case DURATION:
+                return BuiltinFunctions.DURATION_CONSTRUCTOR;
+            case UUID:
+                return BuiltinFunctions.UUID_CONSTRUCTOR;
+            case BINARY:
+                return BuiltinFunctions.BINARY_BASE64_CONSTRUCTOR;
+            default:
+                return null;
+        }
+    }
+
+    public static FunctionIdentifier getTypeConstructorWithFormat(IAType type) {
+        switch (type.getTypeTag()) {
+            case DATE:
+                return BuiltinFunctions.DATE_CONSTRUCTOR_WITH_FORMAT;
+            case TIME:
+                return BuiltinFunctions.TIME_CONSTRUCTOR_WITH_FORMAT;
+            case DATETIME:
+                return BuiltinFunctions.DATETIME_CONSTRUCTOR_WITH_FORMAT;
+            default:
+                return null;
+        }
+    }
+
     private static class EnforcedTypeBuilder {
         private final Deque<Triple<IAType, String, Boolean>> typeStack = new ArrayDeque<>();
         private List<Boolean> keyUnnestFlags;
diff --git a/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java b/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java
index 2009e0f..c452c9a 100644
--- a/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java
+++ b/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/functions/BuiltinFunctions.java
@@ -1815,7 +1815,7 @@
         addFunction(IS_BIT_SET_WITH_ALL_FLAG, BitValuePositionFlagTypeComputer.INSTANCE_TEST_WITH_FLAG, true);
 
         // string functions
-        addFunction(STRING_CONSTRUCTOR, AStringTypeComputer.INSTANCE, true); // TODO(ali)
+        addFunction(STRING_CONSTRUCTOR, AStringTypeComputer.INSTANCE_NULLABLE, true);
         addFunction(STRING_LIKE, BooleanFunctionTypeComputer.INSTANCE, true);
         addFunction(STRING_CONTAINS, UniformInputTypeComputer.STRING_BOOLEAN_INSTANCE, true);
         addFunction(STRING_TO_CODEPOINT, UniformInputTypeComputer.STRING_INT64_LIST_INSTANCE, true);
@@ -1876,7 +1876,7 @@
         addFunction(TO_DOUBLE, ToDoubleTypeComputer.INSTANCE, true);
         addFunction(TO_NUMBER, ToNumberTypeComputer.INSTANCE, true);
         addFunction(TO_OBJECT, ToObjectTypeComputer.INSTANCE, true);
-        addFunction(TO_STRING, AStringTypeComputer.INSTANCE, true);
+        addFunction(TO_STRING, AStringTypeComputer.INSTANCE_NULLABLE, true);
 
         addPrivateFunction(TREAT_AS_INTEGER, TreatAsTypeComputer.INSTANCE_INTEGER, true);
         addPrivateFunction(IS_NUMERIC_ADD_COMPATIBLE, BooleanOnlyTypeComputer.INSTANCE, true);