[TYPO3-core] FYI #6196: IFNULL operator cannot be parsed

[Xavier Perseguers]

Hi,

FYI following patch have been committed to Core trunk (rev. 6899) and DBAL trunk (rev. 29977)

Type: Bugfix / Feature

Bugtracker reference:
http://bugs.typo3.org/view.php?id=6196

Branches: trunk

Problem:
When using tt_news and choosing to restrict the list of news with a set of news categories, FE rendering crashes with:

Could not parse where clause in /path/to/dbal/class.ux_t3lib_db.php : 1072

This is caused by tt_news which issues this kind of query:

1=1 AND (IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)) AND tt_news.sys_language_uid IN (0,-1) AND tt_news.pid > 0 AND tt_news.pid IN (61) AND tt_news.deleted=0 AND tt_news.t3ver_state<=0 AND 
tt_news.hidden=0 AND tt_news.starttime<=1266057900 AND (tt_news.endtime=0 OR tt_news.endtime>1266057900) AND (tt_news.fe_group='' OR tt_news.fe_group IS NULL OR tt_news.fe_group='0' OR 
(tt_news.fe_group LIKE '%,0,%' OR tt_news.fe_group LIKE '0,%' OR tt_news.fe_group LIKE '%,0' OR tt_news.fe_group='0') OR (tt_news.fe_group LIKE '%,-1,%' OR tt_news.fe_group LIKE '-1,%' OR 
tt_news.fe_group LIKE '%,-1' OR tt_news.fe_group='-1'))

Problem is the IFNULL(tt_news_cat_mm.uid_foreign, 0) which cannot be parsed ATM.


Original problem description from Marcel Alburg:
i wanna use IrFAQ with DBAL and PostgreSQL. PostGresql dont use IFNULL(..) and because of that, i changed that to COALESCE.

But now, i've a error (the same error like with IFNULL).

SQL engine parse ERROR: No field name found as expected in parseWhereClause(): near "COALESCE(tx_irfaq_q_cat_mm.uid_foreign,0) = 1) AND"

Additional information:

- Oracle's equivalent is NVL(field, default_value)
- MSSQL's equivalent is ISNULL(field, default_value)
- PostgreSQL's equivalent is COALESCE but as usual with PostgreSQL, support for IFNULL as been added as a MySQL-compatibility function

There might still be a few incompatibility in tt_news but they are not related to IFNULL operator anymore

-- 
Xavier Perseguers
http://xavier.perseguers.ch/en
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 6196_core.diff
URL: <http://lists.typo3.org/pipermail/typo3-team-core/attachments/20100213/4f08b2e9/attachment.txt>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 6196_dbal_v2.diff
URL: <http://lists.typo3.org/pipermail/typo3-team-core/attachments/20100213/4f08b2e9/attachment.asc>