[TYPO3-core] RFC: Bug #6953: Mapping of fieldnames in SQL-functions like MIN(), MAX() or SUM() does not work

[Martin Kutschker]

Christian Trabold schrieb:
> 
> Problem:
> If you use SQL-Functions like MIN(), MAX() or SUM() in SQL-Queries the 
> fieldmapping does not work. Example:
> 
> SELECT tstamp, script, SUM(exec_time) as calc_sum, count(*) AS qrycount, 
> MAX(errorFlag) as error FROM tx_dbal_debuglog
> 
> will produce
> 
> SELECT tstamp, script, SUM(exec_time) as calc_sum, count(*) AS qrycount, 
> MAX(errorFlag) as error FROM tx_dbal_debuglog
> 
> 
> even if the mapping for the field "errorFlag" would be "errorflag" (with 
> a small f).
> 
> 
> This gets problematic if your database supports only lowercase 
> fieldnames and the fieldname has upper-case letters (eg. 
> tx_dbal_debuglog:errorFlag), because the field can not be found, which 
> results in an error message.
> 
> 
> 
> Solution:
> With the attached patch the DBAL knows how to handle fieldnames inside 
> SQL-Functions and maps it correctly to the desired value.
> 
> The patch adds a new condition in class.ux_t3lib_db.php->map_sqlParts() 
> which checks for content in "func_content". If a mapping instruction for 
> "func_content" can be found, the mapping will be processed.

Unfortunately the SQL parser is quite clever, but not clever enough
  to further parse the function. Function arguments could be 
column,"quoted_column" or 'string literal', but they all will end up 
as-is in func_content. But this is probably ok, because the parser 
doesn't seem to like quoting of fields

What's more you may have something like that "max(1+floor(column))" in 
this case the first element of the function content array will be "1" 
and not a fieled name. So at least you must loop through the complete 
function content array to make this really work.

Masi

PS: I suggest to add ceil,ceiling,power,abs,sign and round to the list 
of supported SQL functions. They are all supported by PostgreSQL.