TYPO3 Core

I can confirm the bug with following new Oracle unit test:

/** * @test * @see http://bugs.typo3.org/view.php?id=14372
*/
public function fieldFromAliasIsRemapped() {
$selectFields = 'news.uid';
$fromTables = 'tt_news AS news';
$whereClause = 'news.uid = 1';
$groupBy = '';
$orderBy = '';

$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
    $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));

$expected = 'SELECT "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1';
    $this->assertEquals($expected, $query);
}

Result in phpUnit is:

Failed asserting that <string:SELECT "news"."uid" FROM "ext_tt_news" AS "news" WHERE "news"."uid" = 1> is equal to <string:SELECT "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1>.

I see a few problems with the attached patch (only tested first chunk yet as it is already tricky without joins :D):

- Mapping is always copied to the alias even if there not alias (have a mapping for "tt_news", make a query on this table but without aliasing "tt_news", first chunk of the patch applies although this table is not aliased)

=> I updated first chunk to additionally test if there's an alias. After that unit test is OK.

- Remapping is tricked if the alias name corresponds to a table name having an alias. Shown with following unit test:

/** * Trick here is that we already have a mapping for both table tt_news and table tt_news_cat * (see tests/fixtures/oci8.config.php) which is used as alias name. * * @test * @see http://bugs.typo3.org/view.php?id=14372
*/
public function fieldFromAliasIsRemappedWithoutBeingTricked() {
$selectFields = 'tt_news_cat.uid';
$fromTables = 'tt_news AS tt_news_cat';
$whereClause = 'tt_news_cat.uid = 1';
$groupBy = '';
$orderBy = '';

$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
    $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));

$expected = 'SELECT "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1';
    $this->assertEquals($expected, $query);
}

Result in phpUnit is:

Failed asserting that <string:SELECT "ext_tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "ext_tt_news_cat"."news_uid" = 1> is equal to <string:SELECT "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1>.

- Your patch alters the mapping instructions by adding mapping for the alias. This has the side effect of altering further queries too as shown with following unit test:

/** * @test * @see http://bugs.typo3.org/view.php?id=14372
*/
public function aliasRemappingDoesNotAlterFurtherQueries() {
$selectFields = 'foo.uid';
$fromTables = 'tt_news AS foo';
$whereClause = 'foo.uid = 1';
$groupBy = '';
$orderBy = '';

// First call to possibly alter (in memory) the mapping from localconf.php 
    $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);

$selectFields = 'uid';
    $fromTables   = 'foo';
    $whereClause  = 'uid = 1';
    $groupBy      = '';
    $orderBy      = '';

$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
    $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));

$expected = 'SELECT "uid" FROM "foo" WHERE "uid" = 1';
    $this->assertEquals($expected, $query);
}

Result in phpUnit is:

Failed asserting that <string:SELECT "news_uid" FROM "foo" WHERE "news_uid" = 1> is equal to <string:SELECT "uid" FROM "foo" WHERE "uid" = 1>.

As you see, this is a bit more tricky as it seems to be ;-)

I currently have a working patch but I'm still going to test some really nasty query to check if it works too...

I just uploaded _v2 of the diff file which is OK for most queries. However there's still a problem with this nasty unit test (not yet part of the patch):

/** * @test * @see http://bugs.typo3.org/view.php?id=14372
*/
public function aliasRemappingInSubqueryDoesNotAffectMainQuery() {
$selectFields = 'foo.uid';
$fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
$whereClause = 'tt_news_cat_mm.uid_foreign IN (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
$groupBy = '';
$orderBy = 'foo.uid';

$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
    $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));

$expected = 'SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
    $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
    $expected .= ' WHERE "ext_tt_news_cat_mm"."uid_foreign" IN (';
    $expected .=     'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
    $expected .= ')';
    $expected .= ' ORDER BY "foo"."news_uid"';
    $this->assertEquals($expected, $query);
}

Current code generates this (prettyfied with breaks):

SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"
INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"
WHERE "ext_tt_news_cat_mm"."uid_foreign" IN (
SELECT "foo"."news_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0
) ORDER BY "foo"."news_uid"

instead of

SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo"
INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"
WHERE "ext_tt_news_cat_mm"."uid_foreign" IN (
SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0
) ORDER BY "foo"."news_uid"

(difference is in subquery where external alias name is taken into account).

I said it! This is really a nasty query ;-)

OK, I got it ;-) I now have to do the same for EXISTS and test with sub-subqueries... It's funny to work with visibility scopes...

This time, I think I have a patch working really in depth...

Committed to:

- DBAL_trunk (rev. 33376)
- DBAL_1-1 (rev. 33377)
- DBAL_1-0 (rev. 33378)

For DBAL_1-0, I removed unit test aliasRemappingWithExistsSubqueryDoesNotAffectMainQuery as EXISTS is not supported in this branch