db_distinct_field regex incorrect

This is a duplicate of #284392: db_rewrite_sql causing issues with DISTINCT.

I had exactly the same thing... and didn't see this post until after I'd spent a good while tracking it down... learnt lots of good stuff :)

In the function db_distinct_field a query is broken apart using sql keywords. The pattern used would match the first from in a select even if it was part of a cck fieldname - any field containing the characters from will do.

For example a I have view which creates the following SQL query

SELECT node.nid AS nid,
   node_data_field_fromtext.field_fromtext_value AS node_data_field_fromtext_field_fromtext_value
 FROM {node} node 
 INNER JOIN {users} users ON node.uid = users.uid
 LEFT JOIN {content_type_experience} node_data_field_fromtext ON node.vid = node_data_field_fromtext.vid
 WHERE (node.type in ('%s')) AND (node.status <> 0) AND (users.uid = %d)
   ORDER BY node_data_field_fromtext_field_fromtext_value ASC

this is changed to

SELECT node.nid AS nid,
   node_data_field_FROMtext.field_fromtext_value AS node_data_field_fromtext_field_fromtext_value
 FROM {node} node 
 INNER JOIN {users} users ON node.uid = users.uid
 LEFT JOIN {content_type_experience} node_data_field_fromtext ON node.vid = node_data_field_fromtext.vid
 WHERE (node.type in ('%s')) AND (node.status <> 0) AND (users.uid = %d)
   ORDER BY node_data_field_fromtext_field_fromtext_value ASC

This sql fails with the error "user warning: Unknown column 'node_data_field_FROMtext.field_fromtext_value'"

To recreate this problem (using Drupal 6)
1. Install CCK and Views and some access control module
2. Create a field called field_fromtext on a node type
3. Create a view on the nodes and sort the view by this field
4. Then look at the view will some user that's not allow to see every node...

The line of code that seems to be causing the problem is

if (preg_match('/^SELECT(.*?)FROM(.*)/is', $query, $matches)) {

... not so sure this is a edge case as plenty of words contain the letter sequence "from"
Plus I don't think this is a duplicate of #284392: db_rewrite_sql causing issues with DISTINCT as that concerns the regular expression that inserts the distinct clause.

I attach a patch which seems to solve the issue on my site

Uploading a better patch which uses regular expressions \b to find a word boundary instead of the space character.

So the only change is from

  if (preg_match('/^SELECT(.*?)FROM(.*)/is', $query, $matches)) {

to this...

  if (preg_match('/^SELECT(.*?)\bFROM\b(.*)/is', $query, $matches)) {

in both the mysql versions of db_distinct_field

Looks useful, needs more testing.

@Gabor

Here's a test that'll prove the patch works... a perhaps provide real-life scenario for french cheese lovers ;)!

Obviously this patch will be influenced by whatever is decided on this issue http://drupal.org/node/284392... but even the latest patch #365 would fail because the regex is still '/^SELECT(.*?)FROM(.*)/is' when it should be '/^SELECT(.*?)\bFROM\b(.*)/is'

db_distinct_field() looks like this now in D6:

function db_distinct_field($table, $field, $query) {
  $matches = array();
  if (!preg_match('/^SELECT\s*DISTINCT/i', $query, $matches)) {
    // Only add distinct to the outer SELECT to avoid messing up subqueries.
    $query = preg_replace('/^SELECT/i', 'SELECT DISTINCT', $query);
  }

  return $query;
}

So the offending regex in the OP doesn't even exist any more.