User list query fails on SQL Server [#1013396]

I use drupal 7 -rc4 , IIS and microsoft SQL server.
I have also installed the latest (dev version) of MSSQL drivers.
Once I enable the devel module I get the following error

PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified.: SELECT DISTINCT TOP(10) u.uid AS uid FROM {users} u LEFT OUTER JOIN {users_roles} r ON u.uid = r.uid WHERE ( (u.uid > :db_condition_placeholder_0) AND (u.status > :db_condition_placeholder_1) AND( (u.uid = :db_condition_placeholder_2) OR (r.rid IN (:db_condition_placeholder_3)) ) ) ORDER BY u.access DESC; Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => 3 ) in devel_switch_user_list() (line 767 of C:\inetpub\wwwroot\dr7\sites\all\modules\devel\devel.module)

I comment line 767 of devel.module ($uids = $query->execute()->fetchCol(); )
and then I get the following error:

PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'.: SELECT TOP(10) uid FROM {users} WHERE uid > 0 AND uid NOT IN () AND status > 0 ORDER BY access DESC; Array ( ) in devel_switch_user_list() (line 783 of C:\inetpub\wwwroot\dr7\sites\all\modules\devel\devel.module).

I comment the line 783 ($uids = db_query_range('SELECT uid FROM {users} WHERE uid > 0 AND uid NOT IN (:uids) AND status > 0 ORDER BY access DESC', 0, $list_size - $num_links, array(':uids' => array_keys($links)))->fetchCol();)
and everything works Ok.

Is this an issue of devel module or do I have to report this to MSSQL driver team?

Regards

Comment	File	Size	Author
#12	1013396_devel_userlist_query.patch	427 bytes	ben.bunk
#12
#5	1013396_devel_userlist_query.patch	435 bytes	ben.bunk
#5

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

salvis

he/his

CreditAttribution: salvis commented 3 January 2011 at 16:25

Yes, please move this issue to the MSSQL driver queue.

This query works just fine with MySQL. We could probably change it so that its translation would be acceptable to MSSQL, but this should not be necessary.

Comment #2

ben.bunk CreditAttribution: ben.bunk commented 4 January 2011 at 00:55

The MSSQL Driver issue #1013400: Devel Module bug with IIS / SQL Server

Comment #3

moshe weitzman CreditAttribution: moshe weitzman commented 4 January 2011 at 15:34

Title:

IIS7 - Miscrosoft SQL Server

» User list query fails on SQL Server

I'm interested in changing this query so it gets more compatible. Patch welcome.

Comment #4

salvis

he/his

CreditAttribution: salvis commented 5 January 2011 at 01:59

Sorry, I'm unable to create a patch right now, but if I understand the error message correctly, then MSSQL would be happy if we added u.access (and access in the second query) to the field list.

fetchCol() has a default column index of 0, so this should work.

But it should be fixed in the driver, as suggested in the linked issue.

Comment #5

ben.bunk CreditAttribution: ben.bunk commented 5 January 2011 at 23:42

File	Size
1013396_devel_userlist_query.patch	435 bytes

Heres a patch for this.

Comment #6

salvis

he/his

CreditAttribution: salvis commented 6 January 2011 at 11:10

Status:

Active

» Needs work

Did you test this? From the OP I gather that we need this twice...

Comment #7

ben.bunk CreditAttribution: ben.bunk commented 6 January 2011 at 20:27

Yes I tested this and it works correctly. The user posted that he commented the line to fetch the query that is fixed in the above patch (basically not executing the query).

The second query he lists failed because it uses a result set from the first query. If you look the error shows an empty IN() clause which was fixed with the patch above.

Comment #8

salvis

he/his

CreditAttribution: salvis commented 6 January 2011 at 20:59

That's right, but the second query also has an ORDER BY access clause without including access in the field list.

I suspect that your first query delivered enough records so that the second one was not executed.

Comment #9

ben.bunk CreditAttribution: ben.bunk commented 7 January 2011 at 17:41

The DISTINCT clause is what causes the problems. If DISTINCT is present in a SELECT statement all ORDER BY elements must appear in the SELECT list. Some examples I put together from SQL 2008 are below:

Query 1 - ORDER BY item not in the SELECT list:
SELECT DISTINCT u.uid FROM test_users u ORDER BY u.access DESC

Query 1 Result:
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Query 2 - All ORDER BY items now in the SELECT list:
SELECT u.uid FROM test_users u ORDER BY u.access DESC

Query 2 Result:
uid
1
0

If a ORDER BY element exists all SELECT items must appear in the GROUP BY section but the reverse is not true.

Query 3 - SELECT item not in the GROUP BY list:
SELECT u.uid FROM test_users u GROUP BY u.access
Query 3 Result:
Msg 8120, Level 16, State 1, Line 1
Column 'test_users.uid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Query 4 - All SELECT items now in the GROUP BY list + additional items:
SELECT u.uid FROM test_users u GROUP BY u.uid, u.access
Query 4 Result:
uid
0
1