[ASTERIXDB-2980][*DB][IDX] Add the option "CAST (DEFAULT NULL)" to CREATE INDEX statement
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
Add the option "CAST (DEFAULT NULL)" to CREATE INDEX statement.
- when CAST (DEFAULT NULL) is specified in CREATE INDEX, use
constructor types to cast the input type to the indexed field type as
follows: CONSTRUCTOR(IF_MISSING(indexed_field, NULL)).
- in index bulk load path, cast only the indexed fields instead of the
whole dataset record.
- allow CAST (DEFAULT NULL) only for b-trees.
- add tests.
Change-Id: I3a3ffd3735f1b311bd532dda955e08bf150ced31
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/13883
Reviewed-by: Ali Alsuliman <ali.al.solaiman@gmail.com>
Reviewed-by: Dmitry Lychagin <dmitry.lychagin@couchbase.com>
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
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">