TYPO3 Core

Using patch from #21688 which seems to add missing support for subqueries in an IN where clause, I found a problem when trying to use either v1 or v2 of the patch with new caching framework... New caching framework basically does this:

$GLOBALS['TYPO3_DB']->execDELETEquery(
'cachingframework_cache_hash_tags',
'identifier IN (' .
$GLOBALS['TYPO3_DB']->SELECTquery(
'identifier',
'cachingframework_cache_pages',
'crdate + lifetime < ' . time() . ' AND lifetime > 0'
) .
')'
);

Problem is that this creates a SELECT statement that is already quoted (when using PostgreSQL or Oracle) and puts this into a DELETE statement which is not yet quoted. This seems to be a good way to generate a SUBQUERY but actually leads to something that is not consistent for parsing.

Moreover, this quotes the fields but does not do any remapping of field or table names. When using DBAL, the remapping is performed BEFORE quoting, that is before calling method SELECTquery. As such, the subquery should be hard-coded, without using ->SELECTquery and patch from #21688 would then be able to properly parse and execute the query when using DBAL.

I thought again and we have to prevent quoting to let it work with DBAL. This means 3 possibilities:

1) Add a parameter to method SELECTquery that is used only by DBAL to prevent quoting (something like "$noQuote = FALSE")
2) Rewrite the subquery without using SELECTquery, just as it's done in EXT:crawler
3) Add a new method to t3lib_db that aims at creating subqueries, typically something like SELECTsubquery() which would not be overriden by DBAL (no need) and that would only allow list of fields, from tables and where clause as parameters.

My preferred solution is 3). This method should copy relevant code from SELECTquery, not calling it...

Patch 12800-core.diff adds new method SELECTsubquery to t3lib_db, just as discussed above. And adds the additional field to the sql parser to allow just any subquery to be parsed from within DBAL. As this just adds a fields, this has no side-effect

Patch 12800-dbal.diff adds support for subqueries in an IN where clause in DBAL (taken and adapted from #21688 with a few unit tests to ensure all is fine.

Added additional unit-test in patch 12800-dbal_v2.diff to make sure subquery is properly remapped (which was the case).

Committed to SVN Trunk (rev. 6589)

Committed to DBAL Trunk (rev. 27125)
Committed to DBAL branch 1-0 (rev. 27126)