diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/typecast/StaticTypeCastUtil.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/typecast/StaticTypeCastUtil.java
index 0c6f2ea..3ba3e96 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/typecast/StaticTypeCastUtil.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/typecast/StaticTypeCastUtil.java
@@ -26,6 +26,7 @@
 import edu.uci.ics.asterix.om.base.AString;
 import edu.uci.ics.asterix.om.constants.AsterixConstantValue;
 import edu.uci.ics.asterix.om.functions.AsterixBuiltinFunctions;
+import edu.uci.ics.asterix.om.functions.AsterixFunctionInfo;
 import edu.uci.ics.asterix.om.pointables.base.DefaultOpenFieldType;
 import edu.uci.ics.asterix.om.typecomputer.base.TypeComputerUtilities;
 import edu.uci.ics.asterix.om.types.ARecordType;
@@ -261,6 +262,7 @@
                 String reqFieldName = reqFieldNames[j];
                 IAType reqFieldType = reqFieldTypes[j];
                 if (fieldName.equals(reqFieldName)) {
+                    //type matched
                     if (fieldType.equals(reqFieldType)) {
                         fieldPermutation[j] = i;
                         openFields[i] = false;
@@ -293,6 +295,26 @@
                         }
                     }
 
+                    // match the optional type input for a non-optional field
+                    // delay that to runtime by calling the not-null function
+                    if (fieldType.getTypeTag() == ATypeTag.UNION
+                            && NonTaggedFormatUtil.isOptionalField((AUnionType) fieldType)) {
+                        IAType itemType = ((AUnionType) fieldType).getUnionList().get(
+                                NonTaggedFormatUtil.OPTIONAL_TYPE_INDEX_IN_UNION_LIST);
+                        if (reqFieldType.equals(itemType)) {
+                            fieldPermutation[j] = i;
+                            openFields[i] = false;
+                            matched = true;
+
+                            ScalarFunctionCallExpression notNullFunc = new ScalarFunctionCallExpression(
+                                    new AsterixFunctionInfo(AsterixBuiltinFunctions.NOT_NULL));
+                            notNullFunc.getArguments().add(new MutableObject<ILogicalExpression>(arg));
+                            //wrap the not null function to the original function
+                            func.getArguments().get(2 * i + 1).setValue(notNullFunc);
+                            break;
+                        }
+                    }
+
                     // match the record field: need cast
                     if (arg.getExpressionTag() == LogicalExpressionTag.FUNCTION_CALL) {
                         ScalarFunctionCallExpression scalarFunc = (ScalarFunctionCallExpression) arg;
diff --git a/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue236.aql b/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue236.aql
new file mode 100644
index 0000000..3d8ab69
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue236.aql
@@ -0,0 +1,52 @@
+/*
+ * Description  : This test case is to verify the fix for issue236
+ 				: https://code.google.com/p/asterixdb/issues/detail?id=236
+ * Expected Res : Success
+ * Date         : 20 Dec. 2012
+ */
+
+drop dataverse SocialNetworkData if exists;
+
+create dataverse SocialNetworkData;
+use dataverse SocialNetworkData;
+
+create type TwitterUserType as open {
+screen-name: string,
+lang: string,
+friends_count: int32,
+statuses_count: int32,
+name: string,
+followers_count: int32
+}
+
+create type TweetMessageType as closed {
+tweetid: string,
+tweetid-copy: string,
+user: TwitterUserType,
+sender-location: point?,
+send-time: datetime,
+send-time-copy: datetime,
+referred-topics: {{ string }},
+message-text: string
+}
+
+create dataset TweetMessages(TweetMessageType)
+partitioned by key tweetid;
+
+
+insert into dataset TweetMessages(
+{ 
+"tweetid": "1111387810", 
+"tweetid-copy": "1111387810", 
+"user": { "screen-name": "TonyNapier#786", "lang": "en", "friends_count": 4241366, 
+"statuses_count": 97, "name": "Tony Napier", "followers_count": 5984113 }, 
+"sender-location": point("29.24,78.35"), 
+"send-time": datetime("2011-11-24T14:24:51.000Z"), 
+"send-time-copy": datetime("2011-11-24T14:24:51.000Z"), 
+"referred-topics": {{ "sprint", "wireless" }}, 
+"message-text": " love sprint its wireless is mind-blowing:)" 
+});
+
+write output to nc1:"rttest/open-closed_query-issue236.adm";
+for $r in dataset('TweetMessages')
+return $r
diff --git a/asterix-app/src/test/resources/runtimets/results/open-closed/query-issue236.adm b/asterix-app/src/test/resources/runtimets/results/open-closed/query-issue236.adm
new file mode 100644
index 0000000..65bdb8f
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/open-closed/query-issue236.adm
@@ -0,0 +1 @@
+{ "tweetid": "1111387810", "tweetid-copy": "1111387810", "user": { "screen-name": "TonyNapier#786", "lang": "en", "friends_count": 4241366, "statuses_count": 97, "name": "Tony Napier", "followers_count": 5984113 }, "sender-location": point("29.24,78.35"), "send-time": datetime("2011-11-24T14:24:51.000Z"), "send-time-copy": datetime("2011-11-24T14:24:51.000Z"), "referred-topics": {{ "sprint", "wireless" }}, "message-text": " love sprint its wireless is mind-blowing:)" }
diff --git a/asterix-app/src/test/resources/runtimets/testsuite.xml b/asterix-app/src/test/resources/runtimets/testsuite.xml
index 1e2f235..b175159 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite.xml
@@ -2399,6 +2399,11 @@
         <output-file compare="Text">query-issue208.adm</output-file>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="open-closed">
+      <compilation-unit name="query-issue236">
+       <output-file compare="Text">query-issue236.adm</output-file>
+      </compilation-unit>
+    </test-case>
     <!--
     <test-case FilePath="open-closed">
       <compilation-unit name="open-closed-15">
@@ -3817,4 +3822,4 @@
       </compilation-unit>
     </test-case>
   </test-group>
-</test-suite>
\ No newline at end of file
+</test-suite>
diff --git a/asterix-om/src/main/java/edu/uci/ics/asterix/om/functions/AsterixBuiltinFunctions.java b/asterix-om/src/main/java/edu/uci/ics/asterix/om/functions/AsterixBuiltinFunctions.java
index 42904fc..2d4419f 100644
--- a/asterix-om/src/main/java/edu/uci/ics/asterix/om/functions/AsterixBuiltinFunctions.java
+++ b/asterix-om/src/main/java/edu/uci/ics/asterix/om/functions/AsterixBuiltinFunctions.java
@@ -38,6 +38,7 @@
 import edu.uci.ics.asterix.om.typecomputer.impl.NonTaggedSumTypeComputer;
 import edu.uci.ics.asterix.om.typecomputer.impl.NonTaggedSwitchCaseComputer;
 import edu.uci.ics.asterix.om.typecomputer.impl.NonTaggedUnaryMinusTypeComputer;
+import edu.uci.ics.asterix.om.typecomputer.impl.NotNullTypeComputer;
 import edu.uci.ics.asterix.om.typecomputer.impl.OpenRecordConstructorResultType;
 import edu.uci.ics.asterix.om.typecomputer.impl.OptionalACircleTypeComputer;
 import edu.uci.ics.asterix.om.typecomputer.impl.OptionalADateTimeTypeComputer;
@@ -235,11 +236,14 @@
     public final static FunctionIdentifier AVG = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "agg-avg", 1);
     public final static FunctionIdentifier COUNT = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "agg-count", 1);
     public final static FunctionIdentifier SUM = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "agg-sum", 1);
-    public final static FunctionIdentifier LOCAL_SUM = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "agg-local-sum", 1);
+    public final static FunctionIdentifier LOCAL_SUM = new FunctionIdentifier(FunctionConstants.ASTERIX_NS,
+            "agg-local-sum", 1);
     public final static FunctionIdentifier MAX = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "agg-max", 1);
-    public final static FunctionIdentifier LOCAL_MAX = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "agg-local-max", 1);
+    public final static FunctionIdentifier LOCAL_MAX = new FunctionIdentifier(FunctionConstants.ASTERIX_NS,
+            "agg-local-max", 1);
     public final static FunctionIdentifier MIN = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "agg-min", 1);
-    public final static FunctionIdentifier LOCAL_MIN = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "agg-local-min", 1);
+    public final static FunctionIdentifier LOCAL_MIN = new FunctionIdentifier(FunctionConstants.ASTERIX_NS,
+            "agg-local-min", 1);
     public final static FunctionIdentifier GLOBAL_AVG = new FunctionIdentifier(FunctionConstants.ASTERIX_NS,
             "agg-global-avg", 1);
     public final static FunctionIdentifier LOCAL_AVG = new FunctionIdentifier(FunctionConstants.ASTERIX_NS,
@@ -394,12 +398,17 @@
     public final static FunctionIdentifier CAST_RECORD = new FunctionIdentifier(FunctionConstants.ASTERIX_NS,
             "cast-record", 1);
 
-    public final static FunctionIdentifier GET_POINT_X_COORDINATE_ACCESSOR = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "get-x", 1);
-    public final static FunctionIdentifier GET_POINT_Y_COORDINATE_ACCESSOR = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "get-y", 1);
-    public final static FunctionIdentifier GET_CIRCLE_RADIUS_ACCESSOR = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "get-radius", 1);
-    public final static FunctionIdentifier GET_CIRCLE_CENTER_ACCESSOR = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "get-center", 1);
-    public final static FunctionIdentifier GET_POINTS_LINE_RECTANGLE_POLYGON_ACCESSOR = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "get-points", 1);
-    
+    public final static FunctionIdentifier GET_POINT_X_COORDINATE_ACCESSOR = new FunctionIdentifier(
+            FunctionConstants.ASTERIX_NS, "get-x", 1);
+    public final static FunctionIdentifier GET_POINT_Y_COORDINATE_ACCESSOR = new FunctionIdentifier(
+            FunctionConstants.ASTERIX_NS, "get-y", 1);
+    public final static FunctionIdentifier GET_CIRCLE_RADIUS_ACCESSOR = new FunctionIdentifier(
+            FunctionConstants.ASTERIX_NS, "get-radius", 1);
+    public final static FunctionIdentifier GET_CIRCLE_CENTER_ACCESSOR = new FunctionIdentifier(
+            FunctionConstants.ASTERIX_NS, "get-center", 1);
+    public final static FunctionIdentifier GET_POINTS_LINE_RECTANGLE_POLYGON_ACCESSOR = new FunctionIdentifier(
+            FunctionConstants.ASTERIX_NS, "get-points", 1);
+
     public static final FunctionIdentifier EQ = AlgebricksBuiltinFunctions.EQ;
     public static final FunctionIdentifier LE = AlgebricksBuiltinFunctions.LE;
     public static final FunctionIdentifier GE = AlgebricksBuiltinFunctions.GE;
@@ -412,6 +421,9 @@
     public static final FunctionIdentifier NUMERIC_ADD = AlgebricksBuiltinFunctions.NUMERIC_ADD;
     public static final FunctionIdentifier IS_NULL = AlgebricksBuiltinFunctions.IS_NULL;
 
+    public static final FunctionIdentifier NOT_NULL = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "not-null",
+            1);
+
     public static IFunctionInfo getAsterixFunctionInfo(FunctionIdentifier fid) {
         IFunctionInfo finfo = finfoRepo.get(fid);;
         if (finfo == null) {
@@ -440,6 +452,7 @@
         add(NUMERIC_ADD, NonTaggedNumericAddSubMulDivTypeComputer.INSTANCE);
 
         // and then, Asterix builtin functions
+        add(NOT_NULL, NotNullTypeComputer.INSTANCE);
         add(ANY_COLLECTION_MEMBER, NonTaggedCollectionMemberResultType.INSTANCE);
         addPrivateFunction(AVG, OptionalADoubleTypeComputer.INSTANCE);
         add(BOOLEAN_CONSTRUCTOR, UnaryBooleanOrNullFunctionTypeComputer.INSTANCE);
diff --git a/asterix-om/src/main/java/edu/uci/ics/asterix/om/typecomputer/impl/NotNullTypeComputer.java b/asterix-om/src/main/java/edu/uci/ics/asterix/om/typecomputer/impl/NotNullTypeComputer.java
new file mode 100644
index 0000000..a9881c2
--- /dev/null
+++ b/asterix-om/src/main/java/edu/uci/ics/asterix/om/typecomputer/impl/NotNullTypeComputer.java
@@ -0,0 +1,72 @@
+/*
+ * Copyright 2009-2010 by The Regents of the University of California
+ * Licensed 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 from
+ * 
+ *     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.
+ */
+
+package edu.uci.ics.asterix.om.typecomputer.impl;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import edu.uci.ics.asterix.om.typecomputer.base.IResultTypeComputer;
+import edu.uci.ics.asterix.om.types.ATypeTag;
+import edu.uci.ics.asterix.om.types.AUnionType;
+import edu.uci.ics.asterix.om.types.IAType;
+import edu.uci.ics.hyracks.algebricks.common.exceptions.AlgebricksException;
+import edu.uci.ics.hyracks.algebricks.core.algebra.base.ILogicalExpression;
+import edu.uci.ics.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression;
+import edu.uci.ics.hyracks.algebricks.core.algebra.expressions.IVariableTypeEnvironment;
+import edu.uci.ics.hyracks.algebricks.core.algebra.metadata.IMetadataProvider;
+
+/**
+ * This class is the type computer for not-null function.
+ * If the input type is not a union, we just return it.
+ * If the input type is a union,
+ * case 1: we return a new union without null if the new union still has more than one types;
+ * case 2: we return the non-null item type in the original union if there are only null and it in the original union.
+ */
+public class NotNullTypeComputer implements IResultTypeComputer {
+
+    public static final NotNullTypeComputer INSTANCE = new NotNullTypeComputer();
+
+    @Override
+    public IAType computeType(ILogicalExpression expression, IVariableTypeEnvironment env,
+            IMetadataProvider<?, ?> metadataProvider) throws AlgebricksException {
+        AbstractFunctionCallExpression f = (AbstractFunctionCallExpression) expression;
+        IAType type = (IAType) env.getType(f.getArguments().get(0).getValue());
+        if (type.getTypeTag() != ATypeTag.UNION) {
+            // directly return the input type if it is not a union
+            return type;
+        }
+
+        AUnionType unionType = (AUnionType) type;
+        List<IAType> items = new ArrayList<IAType>();
+        // copy the item types
+        items.addAll(unionType.getUnionList());
+
+        // remove null
+        for (int i = items.size() - 1; i >= 0; i--) {
+            IAType itemType = items.get(i);
+            if (itemType.getTypeTag() == ATypeTag.NULL) {
+                items.remove(i);
+            }
+        }
+        if (items.size() == 1) {
+            //only one type is left
+            return items.get(0);
+        } else {
+            //more than two types are left
+            return new AUnionType(items, unionType.getTypeName());
+        }
+    }
+}
diff --git a/asterix-runtime/src/main/java/edu/uci/ics/asterix/runtime/evaluators/functions/NotNullDescriptor.java b/asterix-runtime/src/main/java/edu/uci/ics/asterix/runtime/evaluators/functions/NotNullDescriptor.java
new file mode 100644
index 0000000..a3f0342
--- /dev/null
+++ b/asterix-runtime/src/main/java/edu/uci/ics/asterix/runtime/evaluators/functions/NotNullDescriptor.java
@@ -0,0 +1,82 @@
+/*
+ * Copyright 2009-2010 by The Regents of the University of California
+ * Licensed 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 from
+ * 
+ *     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.
+ */
+
+package edu.uci.ics.asterix.runtime.evaluators.functions;
+
+import java.io.DataOutput;
+import java.io.IOException;
+
+import edu.uci.ics.asterix.om.functions.AsterixBuiltinFunctions;
+import edu.uci.ics.asterix.om.functions.IFunctionDescriptor;
+import edu.uci.ics.asterix.om.functions.IFunctionDescriptorFactory;
+import edu.uci.ics.asterix.om.types.ATypeTag;
+import edu.uci.ics.asterix.runtime.evaluators.base.AbstractScalarFunctionDynamicDescriptor;
+import edu.uci.ics.hyracks.algebricks.common.exceptions.AlgebricksException;
+import edu.uci.ics.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
+import edu.uci.ics.hyracks.algebricks.runtime.base.ICopyEvaluator;
+import edu.uci.ics.hyracks.algebricks.runtime.base.ICopyEvaluatorFactory;
+import edu.uci.ics.hyracks.data.std.api.IDataOutputProvider;
+import edu.uci.ics.hyracks.data.std.util.ArrayBackedValueStorage;
+import edu.uci.ics.hyracks.dataflow.common.data.accessors.IFrameTupleReference;
+
+public class NotNullDescriptor extends AbstractScalarFunctionDynamicDescriptor {
+
+    private static final long serialVersionUID = 1L;
+    public static final IFunctionDescriptorFactory FACTORY = new IFunctionDescriptorFactory() {
+        public IFunctionDescriptor createFunctionDescriptor() {
+            return new NotNullDescriptor();
+        }
+    };
+    private final static byte SER_NULL_TYPE_TAG = ATypeTag.NULL.serialize();
+
+    @Override
+    public ICopyEvaluatorFactory createEvaluatorFactory(final ICopyEvaluatorFactory[] args) {
+        return new ICopyEvaluatorFactory() {
+            private static final long serialVersionUID = 1L;
+
+            @Override
+            public ICopyEvaluator createEvaluator(final IDataOutputProvider output) throws AlgebricksException {
+                return new ICopyEvaluator() {
+                    private DataOutput out = output.getDataOutput();
+                    private ArrayBackedValueStorage outInput = new ArrayBackedValueStorage();
+                    private ICopyEvaluator eval = args[0].createEvaluator(outInput);
+                    private String errorMessage = "The input value cannot be null!";
+
+                    @Override
+                    public void evaluate(IFrameTupleReference tuple) throws AlgebricksException {
+
+                        try {
+                            outInput.reset();
+                            eval.evaluate(tuple);
+                            byte[] data = outInput.getByteArray();
+                            if (data[outInput.getStartOffset()] == SER_NULL_TYPE_TAG) {
+                                throw new AlgebricksException(errorMessage);
+                            }
+                            out.write(data, outInput.getStartOffset(), outInput.getLength());
+                        } catch (IOException e1) {
+                            throw new AlgebricksException(errorMessage);
+                        }
+                    }
+                };
+            }
+        };
+    }
+
+    @Override
+    public FunctionIdentifier getIdentifier() {
+        return AsterixBuiltinFunctions.NOT_NULL;
+    }
+
+}
\ No newline at end of file
diff --git a/asterix-runtime/src/main/java/edu/uci/ics/asterix/runtime/formats/NonTaggedDataFormat.java b/asterix-runtime/src/main/java/edu/uci/ics/asterix/runtime/formats/NonTaggedDataFormat.java
index 9e5ae39..41ca4ed 100644
--- a/asterix-runtime/src/main/java/edu/uci/ics/asterix/runtime/formats/NonTaggedDataFormat.java
+++ b/asterix-runtime/src/main/java/edu/uci/ics/asterix/runtime/formats/NonTaggedDataFormat.java
@@ -125,6 +125,7 @@
 import edu.uci.ics.asterix.runtime.evaluators.functions.LenDescriptor;
 import edu.uci.ics.asterix.runtime.evaluators.functions.LikeDescriptor;
 import edu.uci.ics.asterix.runtime.evaluators.functions.NotDescriptor;
+import edu.uci.ics.asterix.runtime.evaluators.functions.NotNullDescriptor;
 import edu.uci.ics.asterix.runtime.evaluators.functions.NumericAbsDescriptor;
 import edu.uci.ics.asterix.runtime.evaluators.functions.NumericAddDescriptor;
 import edu.uci.ics.asterix.runtime.evaluators.functions.NumericCeilingDescriptor;
@@ -414,6 +415,7 @@
 		temp.add(RegExpDescriptor.FACTORY);
 		temp.add(InjectFailureDescriptor.FACTORY);
 		temp.add(CastRecordDescriptor.FACTORY);
+		temp.add(NotNullDescriptor.FACTORY);
 
 		IFunctionManager mgr = new FunctionManagerImpl();
 		for (IFunctionDescriptorFactory fdFactory : temp) {