TYPO3 Core

// See if condition is EXISTS with a subquery

if (preg_match('/^EXISTS[[:space:]]*[(]/', $parseString)) {

$stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(EXISTS)');

$this->nextPart($parseString, '^([(])');

$stack[$level][$pnt[$level]]['func']['subquery'] = $this->parseSELECT($parseString);

// Seek to new position in parseString after parsing of the subquery

$parseString = $stack[$level][$pnt[$level]]['func']['subquery']['parseString'];

unset($stack[$level][$pnt[$level]]['func']['subquery']['parseString']);

if (!$this->nextPart($parseString, '^([)])')) {

return 'No ) parenthesis at end of subquery';

}

} else {

// Support calculated value only for:

// - "&" (boolean AND)

// - "+" (addition)

// - "-" (substraction)

// - "*" (multiplication)

// - "/" (division)

// - "%" (modulo)

$calcOperators = '&|\+|-|\*|\/|%';

// Fieldname:

if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)([[:space:]]+|' . $calcOperators . '|<=|>=|<|>|=|!=|IS)')) {

// Parse field name into field and table:

$tableField = explode('.', $fieldName, 2);

$stack[$level][$pnt[$level]]['table'] = $tableField[0];

$stack[$level][$pnt[$level]]['field'] = $tableField[1];

$stack[$level][$pnt[$level]]['table'] = '';

$stack[$level][$pnt[$level]]['field'] = $tableField[0];

} else {

return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);

// See if the value is calculated:

$stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString, '^(' . $calcOperators . ')');

if (strlen($stack[$level][$pnt[$level]]['calc'])) {

// Finding value for calculation:

$calc_value = $this->getValue($parseString);

$stack[$level][$pnt[$level]]['calc_value'] = $calc_value;

if (count($calc_value) == 1 && is_string($calc_value[0])) {

// Value is a field, store it to allow DBAL to post-process it (quoting, remapping)

$tableField = explode('.', $calc_value[0], 2);

$stack[$level][$pnt[$level]]['calc_table'] = $tableField[0];

$stack[$level][$pnt[$level]]['calc_field'] = $tableField[1];

$stack[$level][$pnt[$level]]['calc_table'] = '';

$stack[$level][$pnt[$level]]['calc_field'] = $tableField[0];

}

// Find "comparator":

$stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString, '^(<=|>=|<|>|=|!=|NOT[[:space:]]+IN|IN|NOT[[:space:]]+LIKE|LIKE|IS[[:space:]]+NOT|IS)');

if (strlen($stack[$level][$pnt[$level]]['comparator'])) {

if (preg_match('/^CONCAT[[:space:]]*\(/', $parseString)) {

$this->nextPart($parseString, '^(CONCAT[[:space:]]?[(])');

$values = array(

'operator' => 'CONCAT',

'args' => array(),

);

$cnt = 0;

while ($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) {

// Parse field name into field and table:

$tableField = explode('.', $fieldName, 2);

if (count($tableField) == 2) {

$values['args'][$cnt]['table'] = $tableField[0];

$values['args'][$cnt]['field'] = $tableField[1];

} else {

$values['args'][$cnt]['table'] = '';

$values['args'][$cnt]['field'] = $tableField[0];

}

// Looking for comma:

$this->nextPart($parseString, '^(,)');

$cnt++;

}

// Look for ending parenthesis:

$this->nextPart($parseString, '([)])');

$stack[$level][$pnt[$level]]['value'] = $values;

} else if (t3lib_div::inList('IN,NOT IN', $stack[$level][$pnt[$level]]['comparator']) && preg_match('/^[(][[:space:]]*SELECT[[:space:]]+/', $parseString)) {

$this->nextPart($parseString, '^([(])');

$stack[$level][$pnt[$level]]['subquery'] = $this->parseSELECT($parseString);

// Seek to new position in parseString after parsing of the subquery

$parseString = $stack[$level][$pnt[$level]]['subquery']['parseString'];

unset($stack[$level][$pnt[$level]]['subquery']['parseString']);

if (!$this->nextPart($parseString, '^([)])')) {

return 'No ) parenthesis at end of subquery';

}

} else {

// Finding value for comparator:

$stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString, $stack[$level][$pnt[$level]]['comparator']);

if ($this->parse_error) {

return $this->parse_error;

}

}

// Checking if we are back to level 0 and we should still decrease level,

// meaning we were probably parsing a subquery and should return here:

if ($level === 0 && preg_match('/^[)]/', $parseString)) {

// Return the stacks lowest level:

return $stack[0];

}

} elseif (isset($v['func'])) {

$output .= ' ' . trim($v['modifier']) . ' ' . $v['func']['type'] . ' (' . $this->compileSELECT($v['func']['subquery']) . ')';

} elseif (isset($v['func'])) {

$output .= ' ' . trim($v['modifier']) . ' ' . $v['func']['type'] . ' (' . $this->compileSELECT($v['func']['subquery']) . ')';

} elseif (isset($v['func'])) {

$where_clause[$k]['func']['subquery'] = $this->quoteSELECTsubquery($v['func']['subquery']);

} elseif (isset($sqlPartArray[$k]['func'])) {

$subqueryDefaultTable = $sqlPartArray[$k]['func']['subquery']['FROM'][0]['table'];

$this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['SELECT'], $subqueryDefaultTable);

$this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['FROM'], $subqueryDefaultTable);

$this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['WHERE'], $subqueryDefaultTable);

/**

* @test

* @see http://bugs.typo3.org/view.php?id=12758

*/

public function whereClauseSupportsExistsKeyword() {

$parseString = 'EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';

$whereParts = $this->fixture->parseWhereClause($parseString);

$this->assertTrue(is_array($whereParts), $whereParts);

$this->assertTrue(empty($parseString), 'parseString is not empty');

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=12758

*/

public function existsClauseIsProperlyCompiled() {

$sql = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';

$expected = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';

$actual = $this->cleanSql($this->fixture->debug_testSQL($sql));

$this->assertEquals($expected, $actual);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=12758

*/

public function existsWhereClauseIsProperlyQuoted() {

$query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(

'*',

'tx_crawler_process',

'active = 0 AND NOT EXISTS (' .

$GLOBALS['TYPO3_DB']->SELECTsubquery(

'*',

'tx_crawler_queue',

'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)'

) .

')'

));

$expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS (';

$expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0';

$expected .= ')';

$this->assertEquals($expected, $query);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=12758

*/

public function subqueryIsRemappedForExistsWhereClause() {

$selectFields = '*';

$fromTables = 'tx_crawler_process';

$whereClause = 'active = 0 AND NOT EXISTS (' .

$GLOBALS['TYPO3_DB']->SELECTsubquery(

'*',

'tx_crawler_queue',

'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0'

) .

')';

$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 * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS (';

$expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0';

$expected .= ')';

$this->assertEquals($expected, $query);

}