RFC 28: OGR SELECT Functions

Author: Frank Warmerdam
Contact: warmerdam@…
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.

How will DISTINCT be handled? It seems like this is a form of summarization mode and is likely to be only supported in some very limited forms.

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