Postgresql requires DISTINCT field to be as leftmost column in ORDER BY [#859190]

Hi to all !
I'm fairly new to drupal and only know postgresql databases, so I created a site with postgresql support.
There were errors with image module as for postgresql integration, and so I had to modify the sources to have the module work as expected.
I'm no developper, and surely enough not a drupal one, but in case what I found can help, here are the modifications I had to do :
in image_gallery_handler_field_gallery_cover.inc line 213, I had to add n.sticky, n.created to the list of selected fields so as to have the query created to be executed with postgresql.
In image_gallery.views.inc I had to add sticky to the list of selected fields also, it refers to lines 60, 73 and 86.
In a general way, in postgres you are not allowed to do something like what follows :
select field1 from table order by field2;
If you want to do so, then you must do instead select field1, field2 from table order by field2;

I really that what I wrote here will help.

Comment	File	Size	Author
#7	image_gallery_handler_field_gallery_cover.inc_.patch	670 bytes	geo27
#7
#7	image_gallery.views_.inc_.patch	1.42 KB	geo27
#7

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

joachim CreditAttribution: joachim commented 21 July 2010 at 07:02

Status:

Active

» Closed (works as designed)

You in fact are:

It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT result list. Thus the following statement is valid:

SELECT name FROM distributors ORDER BY code;

(see http://www.postgresql.org/docs/8.2/static/sql-select.html)

Comment #2

geo27 CreditAttribution: geo27 commented 21 July 2010 at 10:43

Well, as a matter of fact, I hadn't seen it in the official documentation, so I'd guess that I would have to apologize. The fact is that without the modifications I proposed in my original post, the module doesn't work. I use postgresql version 8.4 and I've checked the docs of this version, there is the same paragraph you point to.
That's annoying.
I'll investigate further and try to give you a better explanation.
Thanks for the reply anyway.

Comment #3

geo27 CreditAttribution: geo27 commented 21 July 2010 at 10:57

Well, I've just asked on #postgresql on irc, showing an example (I deleted all the modifs I made to the files, and reverted my drupal site to english). I'll leave it as is so that you can check it by yourself : http://mamaison.homelinux.org/image/tid/2 for example.
The answer I got is that one can't mix distinct and order by this way. So my forst comment should have read :
you can't do select distinct field1 from table order by field2;
I really hope that this will help.

Comment #4

joachim CreditAttribution: joachim commented 21 July 2010 at 11:39

Status:

Closed (works as designed)

» Active

Sigh. Postgresql just seems really quite rubbish to me :/

Can you post your changes as a patch please?

Comment #5

geo27 CreditAttribution: geo27 commented 21 July 2010 at 12:08

I would do that with real pleasure if I knew how to give you a clean patch file. Could you give me the commands to launch ? I'll post the patches right away !

Comment #6

joachim CreditAttribution: joachim commented 21 July 2010 at 12:12

http://drupal.org/patch

Comment #7

geo27 CreditAttribution: geo27 commented 21 July 2010 at 12:20

File	Size
image_gallery.views_.inc_.patch	1.42 KB

image_gallery_handler_field_gallery_cover.inc_.patch	670 bytes

Here are the two patch files. I really hope they will help.
Both have *very* minor changes, but they seems to work properly on my server now.

Comment #8

joachim CreditAttribution: joachim commented 21 July 2010 at 12:39

+++ image_gallery.views.inc	2010-07-21 14:12:26.000000000 +0200
@@ -70,7 +70,7 @@ function image_gallery_views_data_alter(
+      'field'  => 'nid, sticky',

That won't work at all -- that breaks the Views data structure. See the API docs for hook_views_data().

Also, for future reference, changes to a package should be rolled as a single patch, from the base folder. So for this, you'd go to /sites/all/modules/image and issue the diff command from there.

Powered by Dreditor.

Comment #9

geo27 CreditAttribution: geo27 commented 21 July 2010 at 12:59

Ok. Do you want me to redo the patch (with your correction) ?

I'm very sorry. I've never done all this before. I've been using drupal for nearly one year or so, and I'm not fully aware of what is underground. I try to help, this time, and this is not that easy for me. Furthermore, english is not my mother tongue, and I always fear to be misunderstood as for my intentions.

Comment #10

joachim CreditAttribution: joachim commented 21 July 2010 at 13:20

That's okay -- hook_views_data() is a very complex beast. At any rate, I don't think you need to make any changes at all to image_gallery_views_data_alter().

For the rest, I will have to look at this in more depth, as the '***ORDER_CLAUSE***' can be a variety of things depending on user-chosen options IIRC, so I think it's actually more complex than just adding sticky and created to the SELECT.

Won't happen for a while, as I am extremely busy this week and next. Feel free to continue working on this in the meantime if you like (or indeed anyone else who would like to jump in!)