TYPO3 Core

This is the query that fails

SELECT cache_extensions2.*, count(*) AS versions, cache_extensions2.maxintversion AS maxintversion,(
(CASE WHEN cache_extensions.extkey = "tt_news" THEN 100 ELSE 0 END) +
(CASE WHEN cache_extensions.title = "tt_news" THEN 60 ELSE 0 END) +
(CASE WHEN cache_extensions.extkey LIKE '%tt\_news%' THEN 30 ELSE 0 END) +
(CASE WHEN cache_extensions.title LIKE '%tt\_news%' THEN 10 ELSE 0 END)
) AS relevance FROM cache_extensions INNER JOIN (SELECT *, MAX(intversion) AS maxintversion FROM cache_extensions GROUP BY extkey) AS cache_extensions2 ON cache_extensions.extkey = cache_extensions2.extkey WHERE cache_extensions.repository=1 AND (cache_extensions.extkey LIKE '%tt\_news%' OR cache_extensions.title LIKE '%tt\_news%') GROUP BY cache_extensions.extkey ORDER BY relevance DESC, title DESC LIMIT 0, 50

I tried to add support for the subquery but actually there's another problem first. The complicated case is not supporter either.

Then, adding support for the subquery itself is much more complicated than it seems as the parser is quite fragile, you should first add an dumb WHERE 1=1 clause to the subquery otherwise the GROUP BY part fails, then the GROUP BY part fails because there's no space before the ending parenthesis but if you add it, parseFieldList() crashes as it seems to it that there is still something to be parsed (for sure, the rest of the "main" query). I could try to remove that test but even with all my unit tests, I fear to have side effects.

:-(

SELECT cache_extensions.*, count(*) AS versions, MAX(intversion) AS maxintversion,(
    (CASE WHEN cache_extensions.extkey = "font" THEN 100 ELSE 0 END) +
    (CASE WHEN cache_extensions.title = "font" THEN 60 ELSE 0 END) +
    (CASE WHEN cache_extensions.extkey LIKE '%font%' THEN 30 ELSE 0 END) +
    (CASE WHEN cache_extensions.title LIKE '%font%' THEN 10 ELSE 0 END)
) AS relevance 
FROM cache_extensions 
WHERE cache_extensions.repository=1 AND (cache_extensions.extkey LIKE '%font%' OR cache_extensions.title LIKE '%font%') 
GROUP BY cache_extensions.extkey 
ORDER BY title ASC LIMIT 0, 50

Gives me the same result (a few less duplicate fields) on MySQL. I don't know about other DBMS.

I'll try to find a replacement for the the CASE constructions, but at least Oracle knows some form of CASE.

CASE is officially supported by DBAL and in fact, this could work but the parser is currently still not able to parse that complicated query. Unlike the subquery this seems a bit more feasible but still, I'm not sure this will be possible without some big change in the SQL parser.

thanks to Jigal, i got it without using a subquery. This is the actual query:

SELECT cache_extensions.*, count(*) AS versions, cache_extensions.intversion AS maxintversion,(
(CASE WHEN cache_extensions.extkey = "font" THEN 100 ELSE 0 END) +
(CASE WHEN cache_extensions.title = "font" THEN 60 ELSE 0 END) +
(CASE WHEN cache_extensions.extkey LIKE '%font%' THEN 30 ELSE 0 END) +
(CASE WHEN cache_extensions.title LIKE '%font%' THEN 10 ELSE 0 END)
) AS relevance 
FROM cache_extensions 
JOIN cache_extensions AS ce ON cache_extensions.extkey = ce.extkey 
WHERE cache_extensions.lastversion=1 AND cache_extensions.repository=1 AND (cache_extensions.extkey LIKE '%font%' OR cache_extensions.title LIKE '%font%') 
GROUP BY ce.extkey 
ORDER BY relevance DESC, title DESC LIMIT 0, 50

was a bit tricky to get the latest version record and count of versions correct ;)

r3997 in em branch, you can close this issue

Actually there is still a bug but it does not show up as before, the list of remote extension is simply always empty.

Problem is to be found in em/classes/database/class.tx_em_database.php, in method getExtensionListFromRepository(). The second query is this one (Oracle) when searching for "news" in the field:

SELECT "cache_extensions".*, count(*) AS "versions", "cache_extensions"."intversion" AS "maxintversion",<br />
CASE WHEN   "cache_extensions"."extkey" = 'news' THEN 100 ELSE 10 END AS "relevance"<br />
FROM "cache_extensions" JOIN "cache_extensions" AS  "ce" ON "cache_extensions"."extkey"="ce"."extkey"<br />
WHERE   "cache_extensions"."lastversion" = 1<br />
   AND  "cache_extensions"."repository" = 1<br />
   AND ((instr(LOWER("cache_extensions"."extkey"), 'news',1,1) > 0) OR  (instr(LOWER("cache_extensions"."title"), 'news',1,1) > 0))<br />
GROUP BY "ce"."extkey"<br />
ORDER BY "cache_extensions"."title" ASC

This is an invalid query for Oracle and it returns an error which in turn is converted into an empty list.

Some hints after first analyze:

- cache_extensions cannot be linked twice without using an alias for the "main" table (before the JOIN)
- AS keyword should (or must?) be omitted for table alias (in join)
- GROUP BY clause is invalid