[TYPO3-core] RFC: Bugfix #7545: Explain select not working with multiple tables

[ingo schmitt]

During the discussion with michael about this bug, we came with the 
idea, that we can extract the tables from the explain results, and 
therefore we can get rid of the SQL Parser.

The attached patch solves this issue and works fine with my installations.

Ingo
> The patch has some problems which however are most likely not found unless
> you remove the condition that skips results with only one row.
> 
> It seems like $sqlParserObj->compileFromTables() is of no help because it's
> exactly the opposite of $sqlParserObj->parseFromTables(). So the result
> would be the same like _before_ the parsing operation.
> 
> Attached is a new patch which adds JOIN tables directly, not using the SQL
> parser any more. Please try.
> 
> - michael
> 
> ingo schmitt wrote:
> 
>> Hi,
>>
>> please see my attached new patch, which corrects this problem.  (also in
>> the bugtracker).
>>
>> ingo
>>
>>
>>> Hey Ingo,
>>>
>>> just tried the patch. I can reproduce an error when using tt_news in
>>> list mode. When activated "explain SQL" I get this:
>>>
>>> "SHOW INDEX FROM tt_news LEFT OUTER JOIN tt_news_cat_mm ON tt_news.uid =
>>> tt_news_cat_mm.uid_local"
>>>
>>> You have an error in your SQL syntax; check the manual that corresponds
>>> to your MySQL server version for the right syntax to use near 'LEFT
>>> OUTER JOIN tt_news_cat_mm ON tt_news.uid = tt_news_cat_mm.uid_local' at
>>> line 1
>>>
>>> Anyway, I applied your patch and still get the error.
>>>
>>>
>>> ingo schmitt wrote:
>>>> Hi Benjamin,
>>>>
>>>>> looks very good. +1 after reading. I'd really like to see this patch
>>>>> in action though.
>>>>>
>>>>> Can you give us small instructions on how to reproduce this? Thanks a
>>>>> ton!
>>>> Activate: SQL-Debug in Install-Tool
>>>> Activate: Debug and set you IP dev mask to your IP in Install-Tool
>>>> (
>>>> $TYPO3_CONF_VARS['FE']['debug'] = 1;
>>>> $TYPO3_CONF_VARS['SYS']['sqlDebug'] = '1';
>>>> )
>>>> (BTW: Where can I set the later option in installtool 2?)
>>>>
>>>> Activate: Admin-Panel in TS
>>>> Activate: "Explain SELECT queries" in Admin Panel
>>>>
>>>> Render in frontent a page with tt_news records:
>>>>
>>>> "You have an error in your SQL syntax; check the manual that
>>>> corresponds to your MySQL server version for the right syntax to use
>>>> near 'INNER JOIN tt_news_cat_mm ON (tt_news_cat.uid =
>>>> tt_news_cat_mm.uid_foreign)
>>>> ' at line 2
>>>>
>>>> SHOW INDEX FROM tt_news_cat
>>>> INNER JOIN tt_news_cat_mm ON (tt_news_cat.uid =
>>>> tt_news_cat_mm.uid_foreign)
>>>> INNER JOIN tt_news ON (tt_news.uid = tt_news_cat_mm.uid_local)"
>>>>
>>>>
>>>> ingo
>>>>
>>>>
>>>>
>>>> Mit freundlichen Gruessen
>>
>> Mit freundlichen Gruessen
> 


Mit freundlichen Gruessen
-- 
Ingo Schmitt                        mailto:is at marketing-factory.de
Marketing Factory Consulting GmbH   http://typo3.marketing-factory.de/
Content Management mit Typo3: Beratung - Schulung - Realisierung
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 7545_no_sqlparser.patch
Url: http://lists.netfielders.de/pipermail/typo3-team-core/attachments/20080226/2aa8a2a0/attachment.txt