trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dbirds...@apache.org
Subject [1/2] trafodion git commit: [TRAFODION-3071] Add missing binder checks for DATEDIFF etc. + other fixes
Date Wed, 23 May 2018 20:47:11 GMT
Repository: trafodion
Updated Branches:
  refs/heads/master 397fd2de8 -> 6b967b678


[TRAFODION-3071] Add missing binder checks for DATEDIFF etc. + other fixes


Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/9c5080c8
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/9c5080c8
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/9c5080c8

Branch: refs/heads/master
Commit: 9c5080c81b28f336e08dd685bd73607847251234
Parents: 609d7a4
Author: Dave Birdsall <dbirdsall@apache.org>
Authored: Tue May 22 16:56:18 2018 +0000
Committer: Dave Birdsall <dbirdsall@apache.org>
Committed: Tue May 22 16:56:18 2018 +0000

----------------------------------------------------------------------
 core/sql/bin/SqlciErrors.txt                    |   2 +-
 core/sql/optimizer/BindItemExpr.cpp             | 157 ++++++++++++-------
 core/sql/optimizer/ItemFunc.h                   |   3 +
 core/sql/parser/sqlparser.y                     |   5 +
 .../sql/regress/compGeneral/DIFF006.KNOWN.SB.OS |  12 --
 core/sql/regress/compGeneral/EXPECTED006.SB     |  53 +++++--
 core/sql/regress/compGeneral/TEST006            |   1 +
 core/sql/regress/core/EXPECTED037.SB            |  39 ++++-
 core/sql/regress/core/TEST037                   |  20 +++
 .../src/asciidoc/_chapters/binder_msgs.adoc     |  17 ++
 10 files changed, 230 insertions(+), 79 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/core/sql/bin/SqlciErrors.txt
----------------------------------------------------------------------
diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt
index 8146be4..23279fb 100644
--- a/core/sql/bin/SqlciErrors.txt
+++ b/core/sql/bin/SqlciErrors.txt
@@ -1242,7 +1242,7 @@ $1~String1 --------------------------------
 4179 0A000 99999 BEGINNER MAJOR DBADMIN SEQUENCE BY is not supported for stream expressions.
 4180 0A000 99999 BEGINNER MAJOR DBADMIN Stream expression is not supported for top level
DELETE statement. 
 4181 ZZZZZ 99999 BEGINNER MAJOR DBADMIN --- unused as of 03/17/04 ---
-4182 ZZZZZ 99999 BEGINNER MAJOR DBADMIN --- unused as of 03/17/04 ---
+4182 42000 99999 BEGINNER MAJOR DBADMIN Function $0~String0 operand $0~Int0 must be of type
$1~String1.
 4183 0A000 99999 BEGINNER MAJOR DBADMIN Embedded DELETE statements are not allowed on referenced
tables.
 4184 0A000 99999 BEGINNER MAJOR DBADMIN Columns that are part of a referential constraint
cannot be updated using embedded UPDATE statements. 
 4185 42000 99999 BEGINNER MAJOR DBADMIN Select list index is not allowed to be specified
in the GROUP BY clause for this query.

http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/core/sql/optimizer/BindItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindItemExpr.cpp b/core/sql/optimizer/BindItemExpr.cpp
index 6a2055f..9e84ac4 100644
--- a/core/sql/optimizer/BindItemExpr.cpp
+++ b/core/sql/optimizer/BindItemExpr.cpp
@@ -10800,6 +10800,9 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
     {
     case ITM_DATE_TRUNC_YEAR:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+
         //Cast to DATETIME YEAR first to pick up only the year.
         strcpy(buf, "CAST(CAST(@A1 AS DATETIME YEAR) AS TIMESTAMP) ;");
       }
@@ -10807,6 +10810,9 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_DATE_TRUNC_MONTH:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+
         //Get first day of year and then add in the months.
         strcpy(buf, "CAST(CAST(@A1 AS DATETIME YEAR) AS TIMESTAMP) + "
                     "CAST(MONTH(@A1)-1 AS INTERVAL MONTH);");
@@ -10815,6 +10821,9 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_DATE_TRUNC_DAY:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+
         //Note: Cast to DATE first to zero out all time fields
         strcpy(buf, "CAST(CAST(@A1 AS DATE) AS TIMESTAMP);");
       }
@@ -10822,6 +10831,9 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_DATE_TRUNC_HOUR:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+
         //Note: Cast to DATE to zero out all time fields.  Cast to TIMESTAMP in case DATE
was supplied.
         strcpy(buf,
                "CAST( CAST(@A1 AS DATE) AS TIMESTAMP) + "
@@ -10831,6 +10843,9 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_DATE_TRUNC_MINUTE:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+
         strcpy(buf, "DATE_TRUNC('HOUR',@A1) + "
                     "CAST(MINUTE(CAST(@A1 AS TIMESTAMP)) AS INTERVAL MINUTE);");
       }
@@ -10838,6 +10853,9 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_DATE_TRUNC_SECOND:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+
         strcpy(buf, "DATE_TRUNC('MINUTE',@A1) + "
                     "CAST( CAST( SECOND(CAST(@A1 AS TIMESTAMP)) AS SMALLINT) "
                     "AS INTERVAL SECOND);");
@@ -10846,6 +10864,9 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_DATE_TRUNC_CENTURY:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2)) 
+          return this;
+
         strcpy(buf, "CAST( CAST(@A1 AS DATETIME YEAR) AS TIMESTAMP ) - "
                     "CAST( MOD(YEAR(@A1),100) AS INTERVAL YEAR(4)  );");
       }
@@ -10853,6 +10874,9 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_DATE_TRUNC_DECADE:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2)) 
+          return this;
+
         strcpy(buf, "CAST( CAST(@A1 AS DATETIME YEAR) AS TIMESTAMP ) - "
                     "CAST( MOD(YEAR(@A1),10) AS INTERVAL YEAR(4)   );");
       }
@@ -10861,6 +10885,10 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
     case ITM_DATEDIFF_YEAR:
     case ITM_TSI_YEAR:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+        if (enforceDateOrTimestampDatatype(bindWA,1,3))
+          return this;
         strcpy(buf, "CAST( YEAR(@A2) - YEAR(@A1) AS INT) ;");
       }
       break;
@@ -10868,6 +10896,10 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
     case ITM_DATEDIFF_MONTH:
     case ITM_TSI_MONTH:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+        if (enforceDateOrTimestampDatatype(bindWA,1,3))
+          return this;
         strcpy(buf, "CAST( (YEAR(@A2)*12 + MONTH(@A2)) - "
                           "(YEAR(@A1)*12 + MONTH(@A1)) AS INT) ;");
       }
@@ -10875,6 +10907,10 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_MONTHS_BETWEEN:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,1))
+          return this;
+        if (enforceDateOrTimestampDatatype(bindWA,1,2))
+          return this;
 	strcpy(buf, "CASE WHEN DAY (@A1) = DAY (@A2) THEN (YEAR(@A1)*12 + MONTH(@A1) - (YEAR(@A2)*12
+ MONTH(@A2))) ELSE CAST((CAST(@A1 AS DATE) - CAST(@A2 AS DATE)) AS NUMERIC(18,6))/31 END");
       }
       break;
@@ -10882,6 +10918,10 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
     case ITM_DATEDIFF_DAY:
     case ITM_TSI_DAY:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+        if (enforceDateOrTimestampDatatype(bindWA,1,3))
+          return this;
         strcpy(buf, "CAST( CAST(@A2 AS DATE) - CAST(@A1 AS DATE) AS INT );");
       }
       break;
@@ -10889,6 +10929,10 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
     case ITM_DATEDIFF_HOUR:
     case ITM_TSI_HOUR:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+        if (enforceDateOrTimestampDatatype(bindWA,1,3))
+          return this;
         strcpy(buf,
               "CAST( (JULIANTIMESTAMP(DATE_TRUNC('HOUR',@A2)) - "
                     " JULIANTIMESTAMP(DATE_TRUNC('HOUR',@A1))) / (1000000*3600) "
@@ -10899,6 +10943,10 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
     case ITM_DATEDIFF_MINUTE:
     case ITM_TSI_MINUTE:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+        if (enforceDateOrTimestampDatatype(bindWA,1,3))
+          return this;
         strcpy(buf,
                "CAST( (JULIANTIMESTAMP(DATE_TRUNC('MINUTE',@A2)) - "
                      " JULIANTIMESTAMP(DATE_TRUNC('MINUTE',@A1))) / (1000000*60)"
@@ -10909,6 +10957,10 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
     case ITM_DATEDIFF_SECOND:
     case ITM_TSI_SECOND:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+        if (enforceDateOrTimestampDatatype(bindWA,1,3))
+          return this;
         strcpy(buf, "CAST( "
                     "(JULIANTIMESTAMP(DATE_TRUNC('SECOND',@A2)) - "
                     " JULIANTIMESTAMP(DATE_TRUNC('SECOND',@A1))) / 1000000"
@@ -10919,6 +10971,10 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
     case ITM_DATEDIFF_QUARTER:
     case ITM_TSI_QUARTER:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+        if (enforceDateOrTimestampDatatype(bindWA,1,3))
+          return this;
         strcpy(buf, "CAST( ("
                     "((YEAR(@A2)*12) + ((QUARTER(@A2)-1)*3)) - "
                     "((YEAR(@A1)*12) + ((QUARTER(@A1)-1)*3)) ) / 3 AS INT);");
@@ -10928,6 +10984,10 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
     case ITM_DATEDIFF_WEEK:
     case ITM_TSI_WEEK:
       {
+        if (enforceDateOrTimestampDatatype(bindWA,0,2))
+          return this;
+        if (enforceDateOrTimestampDatatype(bindWA,1,3))
+          return this;
         strcpy(buf, "CAST(("
                     "(CAST(@A2 AS DATE) - CAST(DAYOFWEEK(@A2)-1 AS INTERVAL DAY)) - "
                     "(CAST(@A1 AS DATE) - CAST(DAYOFWEEK(@A1)-1 AS INTERVAL DAY))"
@@ -10937,32 +10997,8 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_LAST_DAY:
       {
-	// Make sure that the child is of date datatype.
-	ItemExpr * tempBoundTree =
-	  child(0)->castToItemExpr()->bindNode(bindWA);
-	if (bindWA->errStatus()) return this;
-
-	if (tempBoundTree->getValueId().getType().getTypeQualifier() !=
-	    NA_DATETIME_TYPE)
-	  {
-	    // 4071 The operand of a LAST_DAY function must be a datetime.
-	    *CmpCommon::diags() << DgSqlCode(-4071) << DgString0(getTextUpper());
-	    bindWA->setErrStatus();
-	    return this;
-	  }
-
-	DatetimeType *dtOper = 
-	  &(DatetimeType&)tempBoundTree->getValueId().getType();
-	if ((dtOper->getPrecision() != SQLDTCODE_TIMESTAMP) &&
-	    (dtOper->getPrecision() != SQLDTCODE_DATE))
-	  {
-	    // 4071 The operand of a LAST_DAY function must be a datetime.
-	    *CmpCommon::diags() << DgSqlCode(-4071) << DgString0(getTextUpper());
-	    bindWA->setErrStatus();
-	    return this;
-	  }
-
-	setChild(0, tempBoundTree);
+        if (enforceDateOrTimestampDatatype(bindWA,0,1))
+          return this;
 
         strcpy(buf, "@A1 - CAST( DAY(@A1) -1 AS INTERVAL DAY) + INTERVAL '1' MONTH - INTERVAL
'1' DAY;");
       }
@@ -10970,36 +11006,12 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
     case ITM_NEXT_DAY:
       {
-	// Make sure that the child is of date datatype.
-	ItemExpr * tempBoundTree =
-	  child(0)->castToItemExpr()->bindNode(bindWA);
-	if (bindWA->errStatus()) 
-	  return this;
-
-	if (tempBoundTree->getValueId().getType().getTypeQualifier() !=
-	    NA_DATETIME_TYPE)
-	  {
-	    // 4071 The operand of a NEXT_DAY function must be a datetime.
-	    *CmpCommon::diags() << DgSqlCode(-4071) << DgString0(getTextUpper());
-	    bindWA->setErrStatus();
-	    return this;
-	  }
-
-	DatetimeType *dtOper = 
-	  &(DatetimeType&)tempBoundTree->getValueId().getType();
-	if ((dtOper->getPrecision() != SQLDTCODE_DATE) &&
-	    (dtOper->getPrecision() != SQLDTCODE_TIMESTAMP))
-	  {
-	    // 4071 The operand of a LAST_DAY function must be a datetime.
-	    *CmpCommon::diags() << DgSqlCode(-4071) << DgString0(getTextUpper());
-	    bindWA->setErrStatus();
-	    return this;
-	  }
-
-	setChild(0, tempBoundTree);
+	// Make sure that child(0) is of date or timestamp datatype.
+        if (enforceDateOrTimestampDatatype(bindWA,0,1))
+          return this;
 
 	// make sure child(1) is of string type
-	tempBoundTree =
+	ItemExpr * tempBoundTree =
 	  child(1)->castToItemExpr()->bindNode(bindWA);
 	if (bindWA->errStatus()) 
 	  return this;
@@ -11909,6 +11921,9 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 	//Processing of first operand.
 	ItemExpr * tempBoundTree = child(0)->castToItemExpr()->bindNode(bindWA);
 	if (bindWA->errStatus()) return this;
+
+	if (tempBoundTree->getValueId().getType().getTypeQualifier() == NA_UNKNOWN_TYPE)
+	  child(0)->getValueId().coerceType(NA_NUMERIC_TYPE);
 	
 	if (tempBoundTree->getValueId().getType().getTypeQualifier() != NA_NUMERIC_TYPE)
 	  {
@@ -12579,6 +12594,40 @@ ItemExpr *ZZZBinderFunction::tryToUndoBindTransformation(ItemExpr
*expr)
   return result;  
 }
 
+// returns true if there is an error
+bool ZZZBinderFunction::enforceDateOrTimestampDatatype(BindWA * bindWA, CollIndex childIndex,
int operand)
+{
+  // Make sure that the child is of date or timestamp datatype.
+  ItemExpr * tempBoundTree =
+    child(childIndex)->castToItemExpr()->bindNode(bindWA);
+  if (bindWA->errStatus()) 
+    return true;
+
+  bool error = (tempBoundTree->getValueId().getType().getTypeQualifier() !=
+                NA_DATETIME_TYPE);
+  if (!error)
+    {
+      DatetimeType *dtOper = 
+	  &(DatetimeType&)tempBoundTree->getValueId().getType();
+      error = ((dtOper->getPrecision() != SQLDTCODE_TIMESTAMP) &&
+	       (dtOper->getPrecision() != SQLDTCODE_DATE));
+    }
+
+  if (error)
+    {
+      // 4182 Function $0~String0 operand $0~Int0 must be of type $1~String1.
+      *CmpCommon::diags() << DgSqlCode(-4182) 
+                          << DgString0(getTextUpper())
+                          << DgInt0(operand)
+                          << DgString1("DATE or TIMESTAMP");
+      bindWA->setErrStatus();
+      return true;
+    }
+
+  setChild(childIndex, tempBoundTree);
+  return false;  // no error
+}
+
 //-------------------------------------------------------------------------
 //
 // member functions for class ItmSequenceFunction

http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/core/sql/optimizer/ItemFunc.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemFunc.h b/core/sql/optimizer/ItemFunc.h
index 7d6aa79..2062919 100644
--- a/core/sql/optimizer/ItemFunc.h
+++ b/core/sql/optimizer/ItemFunc.h
@@ -4681,6 +4681,9 @@ public:
 
   // a virtual function for performing name binding within the query tree
   virtual ItemExpr * bindNode(BindWA *bindWA);
+  
+  // helper function used by bindNode; returns true if there is an error
+  bool enforceDateOrTimestampDatatype(BindWA *bindWA, CollIndex child, int operand);
 
   // the synthesizeType method is needed only when we process an item
   // expression at DDL time, for DML the function gets transformed into

http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index 455447e..7732844 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -34246,6 +34246,7 @@ nonreserved_func_word:  TOK_ABS
                       | TOK_CONVERTTOHX_INTN
                       | TOK_COS
                       | TOK_COSH
+                      | TOK_CRC32
                       | TOK_CURDATE
                       | TOK_CURTIME
                       | TOK_D_RANK 
@@ -34295,6 +34296,7 @@ nonreserved_func_word:  TOK_ABS
                       | TOK_LTRIM
                       | TOK_MAVG
                       | TOK_MCOUNT
+                      | TOK_MD5
                       | TOK_MMAX
                       | TOK_MMIN
                       | TOK_MOD
@@ -34339,6 +34341,9 @@ nonreserved_func_word:  TOK_ABS
                       | TOK_RTRIM
                       | TOK_RVARIANCE
                       | TOK_SEQNUM
+                      | TOK_SHA
+                      | TOK_SHA1
+                      | TOK_SHA2
                       | TOK_SIGN
                       | TOK_SIN
                       | TOK_SINH

http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/core/sql/regress/compGeneral/DIFF006.KNOWN.SB.OS
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/DIFF006.KNOWN.SB.OS b/core/sql/regress/compGeneral/DIFF006.KNOWN.SB.OS
deleted file mode 100644
index 871164d..0000000
--- a/core/sql/regress/compGeneral/DIFF006.KNOWN.SB.OS
+++ /dev/null
@@ -1,12 +0,0 @@
-63c63,65
-< *** ERROR[1002] Catalog TRAFODION does not exist or has not been registered on node
@system@.
----
-> *** ERROR[4222] The DDL feature is not supported in this software version.
-> 
-> *** ERROR[8822] The statement was not prepared.
-65d66
-< --- SQL operation failed with errors.
-692c693
-< *** ERROR[4312] HP_SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS is an MXCS metadata table and
cannot be directly updated.
----
-> *** ERROR[1002] Catalog HP_SYSTEM_CATALOG does not exist or has not been registered
on node .

http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/core/sql/regress/compGeneral/EXPECTED006.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/EXPECTED006.SB b/core/sql/regress/compGeneral/EXPECTED006.SB
index 945d64d..5f80db5 100644
--- a/core/sql/regress/compGeneral/EXPECTED006.SB
+++ b/core/sql/regress/compGeneral/EXPECTED006.SB
@@ -595,7 +595,7 @@
 >>-- Error 4071
 >>prepare xx from select * from t006t1 where last_day(A) = A;
 
-*** ERROR[4071] The first operand of function LAST_DAY must be a datetime.
+*** ERROR[4182] Function LAST_DAY operand 1 must be of type DATE or TIMESTAMP.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -740,31 +740,64 @@
 >>
 >>prepare xx from select date_trunc('YEAR',a) from t006t1;
 
---- SQL command prepared.
+*** ERROR[4182] Function DATE_TRUNC operand 2 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
 >>prepare xx from select date_trunc('YEAR',a) from t006t1;
 
---- SQL command prepared.
+*** ERROR[4182] Function DATE_TRUNC operand 2 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
 >>prepare xx from select date_trunc('month',a) from t006t1;
 
---- SQL command prepared.
+*** ERROR[4182] Function DATE_TRUNC operand 2 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
 >>prepare xx from select date_trunc('century',a) from t006t1;
 
---- SQL command prepared.
+*** ERROR[4182] Function DATE_TRUNC operand 2 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
 >>prepare xx from select datediff(year,a, a) from t006t1;
 
---- SQL command prepared.
+*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
 >>prepare xx from select datediff(month,a, a) from t006t1;
 
---- SQL command prepared.
+*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare xx from select datediff(day,current_timestamp,a) from t006t1;
+
+*** ERROR[4182] Function DATEDIFF operand 3 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
 >>prepare xx from select datediff(hour,a, a) from t006t1;
 
---- SQL command prepared.
+*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
 >>prepare xx from select datediff(minute,a, a) from t006t1;
 
---- SQL command prepared.
+*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
 >>prepare xx from select datediff(second,a, a) from t006t1;
 
---- SQL command prepared.
+*** ERROR[4182] Function DATEDIFF operand 2 must be of type DATE or TIMESTAMP.
+
+*** ERROR[8822] The statement was not prepared.
+
 >>prepare xx from select datediff(quarter,c, c) from t006t5;
 
 --- SQL command prepared.

http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/core/sql/regress/compGeneral/TEST006
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/TEST006 b/core/sql/regress/compGeneral/TEST006
index 9fcfd2e..6697c3f 100644
--- a/core/sql/regress/compGeneral/TEST006
+++ b/core/sql/regress/compGeneral/TEST006
@@ -366,6 +366,7 @@ prepare xx from select date_trunc('month',a) from t006t1;
 prepare xx from select date_trunc('century',a) from t006t1;
 prepare xx from select datediff(year,a, a) from t006t1;
 prepare xx from select datediff(month,a, a) from t006t1;
+prepare xx from select datediff(day,current_timestamp,a) from t006t1;
 prepare xx from select datediff(hour,a, a) from t006t1;
 prepare xx from select datediff(minute,a, a) from t006t1;
 prepare xx from select datediff(second,a, a) from t006t1;

http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/core/sql/regress/core/EXPECTED037.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/EXPECTED037.SB b/core/sql/regress/core/EXPECTED037.SB
index c5c1e28..a414e94 100755
--- a/core/sql/regress/core/EXPECTED037.SB
+++ b/core/sql/regress/core/EXPECTED037.SB
@@ -38,6 +38,7 @@
 +>, CONSTRAINT_SCHEMA int not null 
 +>, CONTROL int not null 
 +>, CURSOR_NAME int not null 
++>, CRC32 int not null
 +>, DATA int not null 
 +>--,DATETIME int not null 
 +>, DATETIME_CODE int not null 
@@ -75,7 +76,7 @@
 >>invoke table_name;
 
 -- Definition of Trafodion table TRAFODION.SCH.TABLE_NAME
--- Definition current  Thu Dec  7 05:50:27 2017
+-- Definition current  Tue May 22 16:42:26 2018
 
   (
     SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -115,6 +116,7 @@
   , CONSTRAINT_SCHEMA                INT NO DEFAULT NOT NULL NOT DROPPABLE
   , CONTROL                          INT NO DEFAULT NOT NULL NOT DROPPABLE
   , CURSOR_NAME                      INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , CRC32                            INT NO DEFAULT NOT NULL NOT DROPPABLE
   , DATA                             INT NO DEFAULT NOT NULL NOT DROPPABLE
   , DATETIME_CODE                    INT NO DEFAULT NOT NULL NOT DROPPABLE
   , DCOMPRESS                        INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -160,6 +162,7 @@
 +>, LOW_VALUE int not null 
 +>, MAXSIZE int not null 
 +>, MBYTES int not null 
++>, MD5 int not null
 +>, MESSAGE_LEN int not null 
 +>, MESSAGE_OCTET_LEN int not null 
 +>, MESSAGE_TEXT int not null 
@@ -192,6 +195,9 @@
 +>, SERIALIZABLE int not null 
 +>, SERIALWRITES int not null 
 +>, SERVER_NAME int not null 
++>, SHA int not null
++>, SHA1 int not null
++>, SHA2 int not null
 +>, SHAPE int not null 
 +>, SHARE int not null 
 +>, SIDEINSERTS int not null 
@@ -224,7 +230,7 @@
 >>invoke system_name;
 
 -- Definition of Trafodion table TRAFODION.SCH.SYSTEM_NAME
--- Definition current  Thu Dec  7 05:50:32 2017
+-- Definition current  Tue May 22 16:42:31 2018
 
   (
     SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -240,6 +246,7 @@
   , LOW_VALUE                        INT NO DEFAULT NOT NULL NOT DROPPABLE
   , MAXSIZE                          INT NO DEFAULT NOT NULL NOT DROPPABLE
   , MBYTES                           INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , MD5                              INT NO DEFAULT NOT NULL NOT DROPPABLE
   , MESSAGE_LEN                      INT NO DEFAULT NOT NULL NOT DROPPABLE
   , MESSAGE_OCTET_LEN                INT NO DEFAULT NOT NULL NOT DROPPABLE
   , MESSAGE_TEXT                     INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -272,6 +279,9 @@
   , SERIALIZABLE                     INT NO DEFAULT NOT NULL NOT DROPPABLE
   , SERIALWRITES                     INT NO DEFAULT NOT NULL NOT DROPPABLE
   , SERVER_NAME                      INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , SHA                              INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , SHA1                             INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , SHA2                             INT NO DEFAULT NOT NULL NOT DROPPABLE
   , SHAPE                            INT NO DEFAULT NOT NULL NOT DROPPABLE
   , SHARE                            INT NO DEFAULT NOT NULL NOT DROPPABLE
   , SIDEINSERTS                      INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -3241,6 +3251,11 @@ SELECT WORK WORK from (values(0)) WORK(WORK);
 --- SQL command prepared.
 >>
 >>-- Expect success
+>>prepare s1 from SELECT CRC32 CRC32 from (values(0)) CRC32(CRC32);
+
+--- SQL command prepared.
+>>
+>>-- Expect success
 >>prepare s1 from SELECT CURDATE CURDATE from (values(0)) CURDATE(CURDATE);
 
 --- SQL command prepared.
@@ -3676,6 +3691,11 @@ SELECT DUAL DUAL from (values(0)) DUAL(DUAL);
 --- SQL command prepared.
 >>
 >>-- Expect success
+>>prepare s1 from SELECT MD5 MD5 from (values(0)) MD5(MD5);
+
+--- SQL command prepared.
+>>
+>>-- Expect success
 >>prepare s1 from SELECT MAXSIZE MAXSIZE from (values(0)) MAXSIZE(MAXSIZE);
 
 --- SQL command prepared.
@@ -4076,6 +4096,21 @@ SELECT DUAL DUAL from (values(0)) DUAL(DUAL);
 --- SQL command prepared.
 >>
 >>-- Expect success
+>>prepare s1 from SELECT SHA SHA from (values(0)) SHA(SHA);
+
+--- SQL command prepared.
+>>
+>>-- Expect success
+>>prepare s1 from SELECT SHA1 SHA1 from (values(0)) SHA1(SHA1);
+
+--- SQL command prepared.
+>>
+>>-- Expect success
+>>prepare s1 from SELECT SHA2 SHA2 from (values(0)) SHA2(SHA2);
+
+--- SQL command prepared.
+>>
+>>-- Expect success
 >>prepare s1 from SELECT SHAPE SHAPE from (values(0)) SHAPE(SHAPE);
 
 --- SQL command prepared.

http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/core/sql/regress/core/TEST037
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/TEST037 b/core/sql/regress/core/TEST037
index c7c51c5..370554c 100755
--- a/core/sql/regress/core/TEST037
+++ b/core/sql/regress/core/TEST037
@@ -80,6 +80,7 @@ create table table_name
 , CONSTRAINT_SCHEMA int not null 
 , CONTROL int not null 
 , CURSOR_NAME int not null 
+, CRC32 int not null
 , DATA int not null 
 --,DATETIME int not null 
 , DATETIME_CODE int not null 
@@ -126,6 +127,7 @@ create table system_name
 , LOW_VALUE int not null 
 , MAXSIZE int not null 
 , MBYTES int not null 
+, MD5 int not null
 , MESSAGE_LEN int not null 
 , MESSAGE_OCTET_LEN int not null 
 , MESSAGE_TEXT int not null 
@@ -158,6 +160,9 @@ create table system_name
 , SERIALIZABLE int not null 
 , SERIALWRITES int not null 
 , SERVER_NAME int not null 
+, SHA int not null
+, SHA1 int not null
+, SHA2 int not null
 , SHAPE int not null 
 , SHARE int not null 
 , SIDEINSERTS int not null 
@@ -1301,6 +1306,9 @@ prepare s1 from SELECT COSH COSH from (values(0)) COSH(COSH);
 prepare s1 from SELECT COST COST from (values(0)) COST(COST);
 
 -- Expect success
+prepare s1 from SELECT CRC32 CRC32 from (values(0)) CRC32(CRC32);
+
+-- Expect success
 prepare s1 from SELECT CURDATE CURDATE from (values(0)) CURDATE(CURDATE);
 
 -- Expect success
@@ -1559,6 +1567,9 @@ prepare s1 from SELECT LTRIM LTRIM from (values(0)) LTRIM(LTRIM);
 prepare s1 from SELECT M M from (values(0)) M(M);
 
 -- Expect success
+prepare s1 from SELECT MD5 MD5 from (values(0)) MD5(MD5);
+
+-- Expect success
 prepare s1 from SELECT MAXSIZE MAXSIZE from (values(0)) MAXSIZE(MAXSIZE);
 
 -- Expect success
@@ -1799,6 +1810,15 @@ prepare s1 from SELECT SERIALWRITES SERIALWRITES from (values(0)) SERIALWRITES(S
 prepare s1 from SELECT SERVER_NAME SERVER_NAME from (values(0)) SERVER_NAME(SERVER_NAME);
 
 -- Expect success
+prepare s1 from SELECT SHA SHA from (values(0)) SHA(SHA);
+
+-- Expect success
+prepare s1 from SELECT SHA1 SHA1 from (values(0)) SHA1(SHA1);
+
+-- Expect success
+prepare s1 from SELECT SHA2 SHA2 from (values(0)) SHA2(SHA2);
+
+-- Expect success
 prepare s1 from SELECT SHAPE SHAPE from (values(0)) SHAPE(SHAPE);
 
 -- Expect success

http://git-wip-us.apache.org/repos/asf/trafodion/blob/9c5080c8/docs/messages_guide/src/asciidoc/_chapters/binder_msgs.adoc
----------------------------------------------------------------------
diff --git a/docs/messages_guide/src/asciidoc/_chapters/binder_msgs.adoc b/docs/messages_guide/src/asciidoc/_chapters/binder_msgs.adoc
index 3d866e3..42b8509 100644
--- a/docs/messages_guide/src/asciidoc/_chapters/binder_msgs.adoc
+++ b/docs/messages_guide/src/asciidoc/_chapters/binder_msgs.adoc
@@ -2404,6 +2404,23 @@ statement.
 
 *Recovery:* Modify the statement and resubmit.
 
+[[SQL-4182]]
+== SQL 4182
+
+```
+Function <function-name> operand <operand-position> must be of type <data-type>.
+```
+
+*Cause:* The expression given for the indicated operand of the indicated function
+has the wrong data type. 
+
+*Effect:* {project-name} is unable to compile the statement.
+
+*Recovery:* Modify the statement and resubmit. If the expression in question
+is a dynamic parameter and the required
+data type is DATE, TIME, TIMESTAMP or INTERVAL, you must use a CAST expression to force
+the dynamic parameter to the required data type.
+
 [[SQL-4183]]
 == SQL 4183
 


Mime
View raw message