Bug #25885: DBAL fails with LIMIT clause - TYPO3 Core - TYPO3 Forge

Actions

Copy link

Bug #25885

closed

DBAL fails with LIMIT clause

Added by Sigfried Arnold over 13 years ago. Updated almost 11 years ago.

Status:

Rejected

Priority:

Should have

Assignee:

Category:

Database API (Doctrine DBAL)

Target version:

Start date:

2011-04-11

Due date:

% Done:

Estimated time:

TYPO3 Version:

PHP Version:

Tags:

Complexity:

Is Regression:

Sprint Focus:

Description

This piece of code ignores the LIMIT optimization:

            $query = $GLOBALS['TYPO3_DB']->SELECTquery(
                "uid",
                "pages",
                "uid IN(" . implode(',', $arr) . ") AND doktype = 1",
                "", 
                "",
                "5" 
            );

            unset($arr);

            $res = $GLOBALS['TYPO3_DB']->sql(TYPO3_db, $query);

            while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
                $arr[] = $row['uid'];
            }

Resulting SQL-String ($query) is:

SELECT uid FROM pages WHERE uid IN(8,9,10,16,29,30,31,32,33) AND doktype = 1 LIMIT 5

It should return ($arr should contain them) 5 datasets with no specific order - but it returns all.

If the statement is changed (added a catalog-prefix to the table) it works:

SELECT uid FROM t3_sandbox.pages WHERE uid IN(8,9,10,16,29,30,31,32,33) AND doktype = 1 LIMIT 5

I have not figured out why its happening but it can be reproduced on multiple servers and in TYPO3 4.4 and 4.5 enviroments - i have not tested any other DB engine besides MySQL though.

Currently i use a very dirty workarond with array_splice() to LIMIT the entries in the Array, but this won't be a solution for bigger amouts of data.

Actions

Copy link

Updated by Xavier Perseguers over 13 years ago

Project changed from TYPO3 Core to 329

Actions

Copy link

Updated by Xavier Perseguers over 13 years ago

Category set to MySQL

Actions

Copy link

Updated by Sigfried Arnold over 13 years ago

Of course the workaround is array_slice() not array_splice()

Actions

Copy link

Updated by Xavier Perseguers over 13 years ago

Status changed from New to Needs Feedback

Hello,

Please check whether bug #25833 is related to your problem (fix was committed this morning).

Furthermore, your code uses method ->sql() which is bad (unless the query is actually created by Extbase itself). You never should use this method if you write your own queries. Instead, you should use methods ->exec_SELECTquery() and alike.

Actions

Copy link

Updated by Sigfried Arnold over 13 years ago

Thanks for the hint with exec_SELECTquery() - but #25833 does not solve the problem.

I figured out that not only LIMIT is not working but ORDER BY does not work either.

Problem is "uid" - i played around a bit and it works fine if you use backticks around the term uid.

"`uid` IN(" . implode(',', $arr) . ")",

But since when is uid a reserved keyword - documentation does not mention anything accordingly and i cant find anything i t3lib_db.php which could be responsible for that behaviour.

Actions

Copy link

Updated by Xavier Perseguers over 13 years ago

Just tried locally with a Sequel Pro (Desktop App equivalent of PhpMyAdmin for Mac OS X) and both queries run properly, even without the catalog prefix.

Actions

Copy link

Updated by Xavier Perseguers over 13 years ago

Status changed from Needs Feedback to Rejected

I cannot reproduce. Just tried with following code on a MySQL database and it works as expected:

$query = $GLOBALS['TYPO3_DB']->SELECTquery(
    'uid',
    'pages',
    'uid IN(1,102,103,18,54,16,25,24,27,46) AND doktype = 1',
    '',
    '',
    '4'
);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
$arr = array();
while (($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) !== FALSE) {
    $arr[] = $row['uid'];
}
t3lib_utility_Debug::debug($arr, 'data');

Using: