[GRASS5] sql expression weirdnesses

[Glynn Clements]

Daniel Calvelo Aros wrote:

> I'm baffled by the following:
> 
> <session>
> GRASS 6.0.0 (pe):~/t/ > db.select dist2002 sql="select cat from dist2002 where
> (ALT_DIST < 500)" |wc #Ok.
>      421     421    1809
> GRASS 6.0.0 (pe):~/t/ > db.select dist2002 sql="select cat from dist2002 where
> (ALT_DIST/2+ALT_DIST/2 < 500)" |wc #Ooops.
>     1829    1829    8033
> GRASS 6.0.0 (pe):~/t/ > db.select dist2002 sql="select cat from dist2002 where
> (ALT_DIST*2 < 1000)" |wc #Ok
>      421     421    1809
> GRASS 6.0.0 (pe):~/t/ > db.select dist2002 sql="select cat from dist2002 where
> (ALT_DIST+ALT_DIST < 1000)" |wc #Still ok.
>      421     421    1809
> GRASS 6.0.0 (pe):~/t/ > db.select dist2002 sql="select cat from dist2002 where
> (ALT_DIST+ALT_DIST/2 < 750)" |wc #Still ok.
>      421     421    1809
> GRASS 6.0.0 (pe):~/t/ > db.select dist2002 sql="select cat from dist2002 where
> (ALT_DIST/2+ALT_DIST < 750)" |wc #Ooops. Non-commuting sum
>     1829    1829    8033
> GRASS 6.0.0 (pe):~/t/ > db.select dist2002 sql="select cat from dist2002 where
> ((ALT_DIST/2)+ALT_DIST < 750)" |wc #Puzzled...
> DBMI-DBF driver error:
> SQL parser error in statement:
> select cat from dist2002 where ((ALT_DIST/2)+ALT_DIST < 750)
> Error in db_open_select_cursor()
> 
>        0       0       0
> </session>
> 
> Is there a (hopefully documented) limitation in the sql parser that provokes
> these things? Is it some NULL-related weirdness?

The SQL parser doesn't implement the normal precedence rules for
arithmetic operators, so e.g.:

	(ALT_DIST/2+ALT_DIST/2 < 500)

parses as:

	((((ALT_DIST/2)+ALT_DIST)/2) < 500)

Also, it doesn't allow parentheses within arithmetic expressions, only
logical (boolean) expressions.

I don't have a copy of the SQL standard, but I'm pretty sure that both
of those are bugs.

At a glance, I would guess that the y_expression rule at the bottom of
lib/db/sqlp/yac.y should be split into something like:

y_expression:
		y_product			{ $$ = $1; }
	|	y_expression '+' y_product {
		    $$ = sqpNewExpressionNode ( sqpOperatorCode("+"), $1, $3 );
		}
	|	y_expression '-' y_product {
		    $$ = sqpNewExpressionNode ( sqpOperatorCode("-"), $1, $3 );
		}
	;

y_product:
		y_atom				{ $$ = $1; }
	|	y_expression '*' y_atom {
		    $$ = sqpNewExpressionNode ( sqpOperatorCode("*"), $1, $3 );
		}
	|	y_expression '/' y_atom {
		    $$ = sqpNewExpressionNode ( sqpOperatorCode("/"), $1, $3 );
		}
	;

y_atom:
		y_value				{ $$ = $1; }
	|	y_column			{ $$ = $1; }
	|	'(' y_expression ')'		{ $$ = $2; }
	;

Also the ARITHMETICAL_OPERATOR token would need to be removed from
lex.l and yac.y, as its current definition makes it impossible to
treat / differently to + and -.

This gives * and / precedence of over + and -, forces
left-associativity, and allows parenthesised expressions.

-- 
Glynn Clements <glynn at gclements.plus.com>