The current blog module is not displaying the blogs on the site saying that there have been no blogs created.
if I enable the errors to be displayed and go to http://my.website/blog I get ..

    * No blog entries have been created.

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /var/www/testdrupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT DISTINCT n.nid, n.created FROM node n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC LIMIT 10 OFFSET 0 in /var/www/testdrupal/modules/blog/blog.pages.inc on line 67.

If at line 67 of blog.pages.inc I replace the line

$result = pager_query(db_rewrite_sql("SELECT n.nid, n.created FROM {node} n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC"), variable_get('default_nodes_main', 10));

with

$result = pager_query(db_rewrite_sql("SELECT n.nid, n.created, n.sticky FROM {node} n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC"), variable_get('default_nodes_main', 10));

It works fine.

This issue was in 6.17 and is still in 6.19. I'm using a postgresql database.

CommentFileSizeAuthor
#2 blog.patch714 bytesibeardslee

Comments

ibeardslee’s picture

ibeardslee commented

$result = pager_query(db_rewrite_sql("SELECT n.nid, n.created FROM {node} n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.created DESC"), variable_get('default_nodes_main', 10));
.. also works. Not really sure if the list of blogs should be affected by stickiness.

ibeardslee’s picture

ibeardslee commented
StatusFileSize
new blog.patch714 bytes

All going well I've attached the appropriate patch for this.

ibeardslee’s picture

ibeardslee commented
Status: Active » Needs review

Status: Needs review » Needs work

The last submitted patch, blog.patch, failed testing.

ibeardslee’s picture

ibeardslee commented
Version: 6.19 » 6.x-dev

A diff of the blog.pages.inc in the 6.19 version and the 6.x.dev version shows there are no changes.

// $Id: blog.pages.inc,v 1.6.2.3 2009/09/14 15:08:00 goba Exp $

ibeardslee’s picture

ibeardslee commented
Status: Needs work » Needs review

#2: blog.patch queued for re-testing.

joachim’s picture

joachim commented
Status: Needs review » Postponed (maintainer needs more info)

> ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Which version of PostGres are you using? AFAIK that is no longer a requirement.

ibeardslee’s picture

ibeardslee commented

8.3.11

Hmm looking through the documentation that does seems to be correct ..

http://www.postgresql.org/docs/8.3/static/sql-select.html
"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;"

BUT, that doesn't explain ..

Why that error message is appearing,
Why the change stops the error message appearing, or
Why further up in that same file is a query structured to include all the ORDER BY fields in the SELECT fields ..

Line 28: $result = pager_query(db_rewrite_sql("SELECT n.nid, n.sticky, n.created FROM {node} n WHERE n.type = 'blog' AND n.uid = %d AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC"), variable_get('default_nodes_main', 10), 0, NULL, $account->uid);

For consistency it'd be nice to get it working :)

ibeardslee’s picture

ibeardslee commented

Hmm ok that just got stranger ..

It works if I run this directly from psql ...

SELECT n.nid, n.created FROM node n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC;

ibeardslee’s picture

ibeardslee commented
Drupal 6.19
Access to update.php Protected
CAPTCHA Already 355 blocked form submissions
Configuration file Protected
Cron maintenance tasks Last run 3 sec ago
You can run cron manually.
Database updates Up to date
Drupal core update status Up to date
File system Writable (public download method)
GD library 2.0 or higher
Module and theme update status Up to date
PHP 5.2.4-2ubuntu5.10
PHP memory limit 32M
PHP register globals Disabled
PostgreSQL database 8.3.11
Unicode library PHP Mbstring Extension
Update notifications Enabled
Web server Apache/2.2.8 (Ubuntu) PHP/5.2.4-2ubuntu5.10 with Suhosin-Patch mod_ssl/2.2.8 OpenSSL/0.9.8g
ibeardslee’s picture

ibeardslee commented

ok another let's just go further down the rabbit hole ...

I also get that same error message ..

warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /var/www/testdrupal/includes/database.pgsql.inc on line 139.

for the taxonomy module.

Maybe the real error is in includes/database.pgsql.inc not handling that properly?

ibeardslee’s picture

ibeardslee commented

SELECT DISTINCT is the problem.

Even though the code in the blog.page.inc is SELECT n.nid .. I now recognise that in the error message it is saying that SELECT DISTINCT n.nid .. is being run.

Adding to my comment #9, if I run the following directly from psql ..

SELECT DISTINCT n.nid, n.created FROM node n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC;

I get an error ..

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

josh waihi’s picture

josh waihi commented
Status: Postponed (maintainer needs more info) » Reviewed & tested by the community

DISTINCT is definitely the problem. Not much we can do about that. So the only option is to add the field in the SELECT list as the patch suggests. I don't see how its harmful.

joachim’s picture

joachim commented

Yeah, it looks like n.created was added to the SELECT at some point for that reason too, as it's not used later.

So +1 from me.

Postgres is a real pain :(

josh waihi’s picture

josh waihi commented

@joachim if people wrote ANSI SQL compliant code, it wouldn't be an issue ;)

joachim’s picture

joachim commented

Seeing as how you have to pay just to read the standard, that's not going to happen very often.
And anyway, if the ANSI standard requires adding irrelevant fields to the SELECT just to use them in the ORDER, then it's crack!

ibeardslee’s picture

ibeardslee commented

Meh .. whether it is sloppy code on the behalf of PostgreSQL itself or people writing sloppy modules.

Something is broken in one of the core Drupal modules, not working with one of the different database engines that Drupal says it supports .. but there is a fix/patch/workaround.

And I've noticed at least one other place that seems to have the same error message, so this probably won't be the last I try and get sorted.

What needs to be done to get whoever has postponed patch to move it to the next step?

joachim’s picture

joachim commented

Nothing. The patch is now marked as RTBC. Just needs one of the D6 branch maintainers to look at it and commit it :)

josh waihi’s picture

josh waihi commented

Pay? what? http://www.contrib.andrew.cmu.edu/~shadow/sql/ - its free, plus PostgreSQL mention in there doc where they don't follow the standard.

Saying you should follow the standard is like saying you should write compliant HTML - it makes no sense.

Kafu’s picture

Kafu commented

Patch #2 worked for me.

Status: Reviewed & tested by the community » Needs work

The last submitted patch, blog.patch, failed testing.

ibeardslee’s picture

ibeardslee commented 
Detect a non-applicable patch
Ensure the patch applies to the lastest checkotu of the code-base.

Meh, it was against the latest when it was submitted. If it takes 2 months run the test there are bigger issues than bad SQL in the code.

joachim’s picture

joachim commented
Status: Needs work » Reviewed & tested by the community

The bot had an off day -- loads of my patches got marked as failing, clearly because the bot couldn't even find the patch.

Back to RTBC.

joachim’s picture

joachim commented

#2: blog.patch queued for re-testing.

Status: Reviewed & tested by the community » Needs work

The last submitted patch, blog.patch, failed testing.

ibeardslee’s picture

ibeardslee commented

Is this another issue with the testing bot? If not, can someone point out where the patch fails, or what I did wrong to in creating the patch for it to fail. There are a bunch of similar issues that'll need to be patched and I want to make sure I have the process right.

ibeardslee’s picture

ibeardslee commented

Problem still exists in 6.20. And in the 6.x-dev as of 2010-Dec-14

Status: Needs work » Closed (outdated)

Automatically closed because Drupal 6 is no longer supported. If the issue verifiably applies to later versions, please reopen with details and update the version.