pager_query, case sensitive SQL queries
| Project: | Drupal |
| Version: | 6.x-dev |
| Component: | database system |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | by design |
Jump to:
Hi,
I'm a new Drupal member, so may this bug appeared to me because I didn't followed the standards while writing the SQL query inside pager_query function.
This is my code:
$sql = 'select %s from {%s}';
$rs = pager_query(db_rewrite_sql($sql), 5, 0, NULL, 'id,year', 'my_table');According to pager_query code above, if I have 10 records in the table (my_table), then I should got 2 pages with 5 records in each one of them, but what was happened is that I got only one page without any page numbers to navigate.
OK, now if I rewrite the code above as the following:
$sql = 'SELECT %s FROM {%s}';
$rs = pager_query(db_rewrite_sql($sql), 5, 0, NULL, 'id,year', 'my_table');The code will run in perfect way, and the pages numbers will be displayed and work in normal way.
Now, if we get into the pager_query core code inside pager.inc, we find that:
if (!isset($count_query)) {
$count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'), array('SELECT COUNT(*) FROM ', ''), $query);
}The replacement options for first parameter - /SELECT.*?FROM / - are (As)
(A - PCRE_ANCHORED to match the start of string being searched)
(s - PCRE_DOTALL to include the new lines in search, if the string being searched contains multilines)
And no replacement options for second parameter - /ORDER BY .*/
According to that, the SQL query passed to pager_query (must) be capital case, otherwise you should pass the optional parameter ($count_query) of pager_query function.
The documentation of pager_query doesn't refer to the case sensitivity of the SQL query.
#1
check this out: http://drupal.org/node/2497
#2
#3
Thanks for your interest, I know about the (SQL coding conventions) of drupal, but I thought that they are just recommendations, I mean that the code should work even I didn't follow the recommendations, at the same time the documentation of (pager_query) function didn't refer at all to any problem in case of using small case SQL statements.
So, I recommend to do one the following:
1- Add a new paragraph to documentation that refer to mandatory using of SQL coding conventions.
or
2- Add the (i) option to the replacement options for first parameter /SELECT.*?FROM / - are (As) so it becomes like that /SELECT.*?FROM/(Asi), and so for second parameter /ORDER BY .*/i.
#4
+1 for this change
What harm would either of these changes cause?