Field alias over function with PostgreSQL driver not working

gabrielp

Hi,

We have a field such as this:

Field::inst('coalesce(mytable.column, 0) AS mytable.column')

However, when we use the PostgreSQL driver, the value we get back for that field is always null.

When we look at the following snipped from the val method in the Editor\Field class:

// Getting data, so the db field name
$val = isset( $data[ $this->_dbField ] ) ?
    $data[ $this->_dbField ] :
    null;

The value of $this->_dbField is coalesce(mytable.column, 0) and the corresponding key in $data is named just coalesce. Because of this, the key doesn't exist and we get a null.

We observe that this may be explained due to a behavioral difference between MySQL and PostgreSQL. Take the following query as an example:

SELECT coalesce(null, 0)

In MySQL the auto-generated column name is the whole expression, coalesce(null, 0). PostgreSQL however calls the column by the function name only: coalesce.

As a workaround, we have found that the following patch resolves this bug:

--- a/include/DataTables/Editor-PHP/php/Database/Query.php
+++ b/include/DataTables/Editor-PHP/php/Database/Query.php
@@ -695,7 +695,7 @@ class Query {
                        $field = $this->_field[$i];
 
                        // Keep the name when referring to a table
-                       if ( $addAlias && $field !== '*' && strpos($field, '(') === false ) {
+                       if ( $addAlias && $field !== '*') {
                                $split = preg_split( '/ as (?![^\(]*\))/i', $field );
 
                                if ( count($split) > 1 ) {