[GRASS5] 5.7: dbf driver and 'double' initialization

[Glynn Clements]

Markus Neteler wrote:

> generally I agree, but:
> 
> echo "UPDATE dourokukan SET forward=50 WHERE forward is null" | db.execute
> DBMI-DBF driver error:
> SQL parser error in statement:
> UPDATE dourokukan SET forward=50 WHERE forward is null
> 
> Error in db_execute_immediate()
> 
> WARNING: Error while executing: "UPDATE dourokukan SET forward=50 WHERE
>          forward is null
>          "
> 
> The problem for me is "how to tell GRASS?"...

The SQL parser used by the DBF driver doesn't recognise the "IS"
keyword (or "ISNULL" or "NOTNULL"), nor does it understand the
"<expr> NOT NULL" syntax. The parser will accept:

	... WHERE forward = NULL

However, that won't work; all operators return NULL if any of their
operands are NULL, so "NULL = NULL" is NULL rather than TRUE.

[Just like r.mapcalc; except r.mapcalc has an isnull() function, which
is an exception to the "result is NULL if any arguments are NULL"
rule. The DBF driver doesn't have any exceptions.]

At a glance, it looks like it would be fairly straightforward to fix.
Try the attached (and completely untested) patch.

-- 
Glynn Clements <glynn.clements at virgin.net>

-------------- next part --------------
Index: include/sqlp.h
===================================================================
RCS file: /grassrepository/grass51/include/sqlp.h,v
retrieving revision 1.15
diff -u -r1.15 sqlp.h
--- include/sqlp.h	11 Jun 2004 16:46:16 -0000	1.15
+++ include/sqlp.h	7 Sep 2004 07:43:47 -0000
@@ -27,6 +27,9 @@
 #define SQLP_NE   16    /* <> */
 #define SQLP_MTCH 17    /* ~ */
 
+#define SQLP_ISNULL  18    /* IS NULL */
+#define SQLP_NOTNULL 19    /* IS NULL */
+
    /* Logical */
 #define SQLP_AND  21
 #define SQLP_OR   22
Index: lib/db/sqlp/lex.l
===================================================================
RCS file: /grassrepository/grass51/lib/db/sqlp/lex.l,v
retrieving revision 1.18
diff -u -r1.18 lex.l
--- lib/db/sqlp/lex.l	11 Jun 2004 16:47:23 -0000	1.18
+++ lib/db/sqlp/lex.l	7 Sep 2004 07:43:47 -0000
@@ -71,6 +71,7 @@
 [Nn][Oo][Tt]			{ return NOT; }
 [Oo][Rr][Dd][Ee][Rr]		{ return ORDER; }
 [Bb][Yy]			{ return BY; }
+[Ii][Ss]			{ return IS; }
  /* [Dd][Ii][Ss][Tt][Ii][Nn][Cc][Tt]	{ return DISTINCT; } */
  /***************************************
   * EQUAL
Index: lib/db/sqlp/yac.y
===================================================================
RCS file: /grassrepository/grass51/lib/db/sqlp/yac.y,v
retrieving revision 1.19
diff -u -r1.19 yac.y
--- lib/db/sqlp/yac.y	15 Jun 2004 07:25:49 -0000	1.19
+++ lib/db/sqlp/yac.y	7 Sep 2004 07:43:47 -0000
@@ -75,6 +75,7 @@
 %token PRECISION
 %token DATE
 %token ORDER BY
+%token IS
 
 %{
  
@@ -208,6 +209,12 @@
 		}
 	|	y_expression COMPARISON_OPERATOR y_expression {
 		    $$ = sqpNewExpressionNode ( sqpOperatorCode($2), $1, $3);
+		}
+	|	y_expression IS NULL_VALUE {
+		    $$ = sqpNewExpressionNode ( SQLP_ISNULL, NULL, $1);
+		}
+	|	y_expression NOT NULL_VALUE {
+		    $$ = sqpNewExpressionNode ( SQLP_NOTNULL, NULL, $1);
 		}
 	;	
 
Index: db/drivers/dbf/dbfexe.c
===================================================================
RCS file: /grassrepository/grass51/db/drivers/dbf/dbfexe.c,v
retrieving revision 1.27
diff -u -r1.27 dbfexe.c
--- db/drivers/dbf/dbfexe.c	12 Jun 2004 07:56:37 -0000	1.27
+++ db/drivers/dbf/dbfexe.c	7 Sep 2004 07:43:48 -0000
@@ -695,6 +695,12 @@
 			    return NODE_FALSE;
 		    }
 
+		case SQLP_ISNULL:
+		    return right == NODE_NULL ? NODE_TRUE : NODE_FALSE;
+
+		case SQLP_NOTNULL:
+		    return right != NODE_NULL ? NODE_TRUE : NODE_FALSE;
+
 		/* Logical */
 		case SQLP_AND:
 		    if ( left == NODE_NULL || right == NODE_NULL ) {