= RFC 28: OGR SELECT Functions = 

Author: Frank Warmerdam[[BR]]
Contact: warmerdam@pobox.com[[BR]]
Status: Development

== Summary ==

The OGR SQL evaluation engine currently does not allow general purpose functions to be applied to columns in SELECT statements.  Some special purpose functions are supported (ie. CAST, COUNT, AVG, MAX, MIN, and SUM), but not as part of more general expressions and generally in very constrained arrangements.  It is the intent of this work item to extend the OGR SQL engine to support fairly general purpose expression evaluation in the output field list of OGR SQL SELECT statements and to implement a few preliminary processing functions in a fashion compatible with standard SQL.  For example, after implementation it is intended the following could be evaluated.

{{{
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers
SELECT id, "Regional Road" AS roadtypename FROM roads where roadtype=3
SELECT (subtotal+salestax) as totalcost from invoice_info
}}}

== Technical Approach ==

... to be filled in, but the intention is that the existing expression evaluation engine for the WHERE clause would be extended and used also on the SELECT field generation side.

It should be noted that as a side effect WHERE clauses will also support more general expressions - not just logical comparisons.  For instance:

 SELECT * WHERE (subtotal+salestax) > 100.0


== New Functions ==

 * Math: +, -, *, /, **
 * String: CONCAT, SUBSTR

== SELECT Rules ==

{{{
SELECT <field-list> FROM <table_def>
     [LEFT JOIN <table_def> 
      ON [<table_ref>.]<key_field> = [<table_ref>.].<key_field>]*
     [WHERE <where-expr>] 
     [ORDER BY <sort specification list>]

<field-list> ::= <column-spec> [ { , <column-spec> }... ]

<column-spec> ::= <field-spec> [ <as clause> ]
                 | CAST ( <field-spec> AS <data type> ) [ <as clause> ]

<field-spec> ::= [DISTINCT] <field_ref>
                 | <cumm-field-func> ( [DISTINCT] <field-ref> )
                 | <field-expr>
                 | Count(*)

<field-expr> ::= <field_ref>
                 | <constant-value>
                 | <field-expr> <field-operator> <field-expr>
                 | <field-func> ( <field-expr-list> )
                 | ( <field-expr> )

<field-expr-list> ::= field-expr
                 |  field-expr , field-expr-list
                 |  <empty>

<as clause> ::= [ AS ] <column_name>

<data type> ::= character [ ( field_length ) ]
                | float [ ( field_length ) ]
                | numeric [ ( field_length [, field_precision ] ) ]
                | integer [ ( field_length ) ]
                | date [ ( field_length ) ]
                | time [ ( field_length ) ]
                | timestamp [ ( field_length ) ]

<cumm-field-func> ::= AVG | MAX | MIN | SUM | COUNT

<field-operator> ::= '+' | '-' | '/' | '*' | '||'

<field-func> ::= CONCAT | SUBSTR

<field_ref>  ::= [<table_ref>.]field_name
}}}

== Special Notes ==

The existing CAST operator will likely be reworked as a regular function.  This might also imply it can work on the WHERE clause side.

The MAX, MIN, AVG, SUM and COUNT operators will not be handled by the expression evaluator.  Their implementation will remain a 
special case for summarized results. 

== Compatability Implications ==

There are no anticipated backward compatability problems.  For the most part the change results in some OGR SQL statements to work that would previously have generated an error. 

== Performance Implications ==

I am hopeful that the evaluation speed will not be significantly different for simple selections, but each output field will need to be evaluated as an expression (with presumably one value-from-field node).


== Implementation Plan ==

Frank Warmerdam will implement, test and document for the GDAL/OGR 1.8 release. 

== Testing == 

== Documentation ==