Bug #16708
closed
WHERE func(expr) IN (n,...m) is broken
Added by Martin Kutschker over 17 years ago.
Updated about 14 years ago.
Category:
Database API (Doctrine DBAL)
Description
IMHO this is valid SQL, but any SQL function breaks the parsing and the executed query runs without any WHERE clause.
(issue imported from #M4493)
Files
Can be reproduced with this unit test:
/**
* @test
* @see http://bugs.typo3.org/view.php?id=4493
/
public function sqlFunctionAndInOperatorCanBeParsed() {
$query = $this->cleanSql($this->fixture->SELECTquery(
'',
'pages',
'MAX IN (1,2,3,4)'
));
$expected = 'SELECT * FROM pages WHERE MAX IN (1,2,3,4)';
$this->assertEquals($expected, $query);
}
Question: do you have a use case when this is needed? I mean this is valid SQL but a usecase would be great.
Actually I have no idea. Could have been either in an extension of mine or - more likely - an extension from TER that I've tried to make it run on PostgreSQL.
But in theory this should be no problem for the parser. There's nothing special with IN. :)
Xavier,
this could be useful to compare dates for example (find all rows where the months was 3):
SELECT * FROM tx_table WHERE EXTRACT=3;
The above could be a typical PostgreSQL, but I am sure that in MySQL similar functions exists,
But it shows the usefulness of using functions.
Ries
Ries,
I see, but this is something to allow what this bug is about (supporting function before IN) and this is something else to support "extended" SQL as in your example.
I agree SQL parser may be better but I work on real problems that actually block TYPO3 from running with typical extensions and when it makes sense to other "typical" queries.
Xavier,
Agreed... I haven't seen any extension using this.. however I did see more 'advanced' usage of SQL in the last 2 years from users...
We need to see where it leads to I think.
Ries
Could not reproduce this bug anymore with latest trunk.
I committed attached patch 4493.diff as rev. 29978. It contains unit tests for MIN and MAX functions.
Problem with "real" queries should be created as new bug entries.
Also available in: Atom
PDF