News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Show new replies to your posts (bug?)

Started by habakuk, September 18, 2009, 05:52:10 AM

Previous topic - Next topic

habakuk

Using 2.0rc1 on postgresql, I get a SQL error when trying to use "Show new replies to your posts":

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
File: /Library/Apache2/htdocs/goodeye/forum/Sources/Recent.php
Line: 1078


> Select Distinct wants to have the distinct field in the order by clause. So, I added "ORDER BY t.id_topic" instead of
"ORDER BY {raw:sort}" and the error goes away.

However, the query produces not the expected results anyway. I see it returns all new posts form topics where I've posted to, but not necessarily started by me.

Could someone confirm the first part to be sort of a bug, maybe only related to postgres?

And maybe someone could tell me if I misunderstood the  intent of the query.

Thank you!

cheers
®

BTW: yeah, I checked the sources/recent.php in the very latest release and it is the same code base.



Aleksi "Lex" Kilpinen

Quote from: habakuk on September 18, 2009, 05:52:10 AM
However, the query produces not the expected results anyway. I see it returns all new posts form topics where I've posted to, but not necessarily started by me.
That is the way it is supposed to work, to help you follow topics you have posted in.
Slava
Ukraini!
"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum. Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

How you can help SMF

habakuk

Thank you, LexArma.

Well, I find it a bit ambiguous. 

"Show replies to subscribed threads" would better describe the actual functionality, no?

For, the replies often aren't to MY posts, but to the post of the thread opener. I think it would be quite handy to have the functionality to list new posts to threads I opened myself.

cheers
®

Aleksi "Lex" Kilpinen

Quote from: habakuk on September 18, 2009, 06:43:42 AM
"Show replies to subscribed threads" would better describe the actual functionality, no?

For, the replies often aren't to MY posts, but to the post of the thread opener. I think it would be quite handy to have the functionality to list new posts to threads I opened myself.
Well, SMF holds no real subscribing to topics (and does not do threads) so not really.
You can "subscribe" to a topic though, clicking the "Notify" button in a topic, so that SMF sends you an e-mail when new posts are made.

The Show unread posts since last visit button works like it says, and Show new replies to your posts works by showing all topics that you have posted in, and someone has posted after you, that you haven't read yet. That is a bit hard word in a small sentence, and "Show new replies to your posts" is a bit of a compromise I give you that much. ;)

Slava
Ukraini!
"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum. Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

How you can help SMF

Norv

Quote from: habakuk on September 18, 2009, 05:52:10 AM
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
File: /Library/Apache2/htdocs/goodeye/forum/Sources/Recent.php
Line: 1078


> Select Distinct wants to have the distinct field in the order by clause. So, I added "ORDER BY t.id_topic" instead of
"ORDER BY {raw:sort}" and the error goes away.
It looks like Postgres-specific restriction. Please check out:
http://archives.postgresql.org/pgsql-sql/2007-02/msg00161.php
or perhaps this little note:
Quote
Require SELECT DISTINCT target list to have all ORDER BY columns
(from Postgresql 8.4 release 7.0 changelog)
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

habakuk

Ok, I see we have to be careful with the wording. :)

With "Thread" I actually meant "Topics". I can get a list of all topics I started. It would, imho, be very handy to get a list of new (unread) posts that have been posted in one of my Topics.

So, I actually long for a "Show new replies to your topics" functionality.

cheers
®

habakuk

Right. Thank you, Norv. So it seems to be  a postgres related issue. Am I supposed to file a bug then?

cheers
®



Norv

As you wish, really.
I will keep a note for it myself, in case I make sure or can reproduce it at least once, but for now I don't have a working Postgres installation.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

habakuk

Done. And it's a good moment to get a postgres installation. You will love it, I promise. :)

cheers
®

Norv

To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github


karlbenson

The bug is now assigned to me.

Unfortunately there are a few postgresql bugs in RC1. Alot have already been fixed already for RC2.  Hopefully this one has already (or will be once I get to work).

If you can, I'd recommend using mysql.  SMF is most stable with it (since was only db type supported prior to 2.0).
In truth I think most people will prefer mysql and be aware of the difficulties in switching later).

habakuk

Thanks for your message. I have to confess I will try to stay away from mysql as far as I can go, even if I have to maintain certain files myself. Mysql's license model is such a bogus thing I have decided to steer clear and use postgres or SQLite for everything I can.

I understand the effect of mass... and you focussing on mysql. And so I truly appreciate any fix you can bring into the code base.

cheers
®

Something like that

It's SMF's goal to fully support PostgreSQL, obviously. If you can find any other bugs in PostgreSQL, please report them! Even if it's a performance tweak or slow query, let us know.

Norv

As Mark says. Specially since many of us don't use it (yet :) ), it is a little difficult to stumble upon issues.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

habakuk

Will do. And kuddos for all the good work of the broad SMF team! (This is not getting said often enough ;))

cheers
®

karlbenson

Using postgresql is one thing, but I definately would not recommend Sqlite
We've supported it for people who want intranet type forums without having to keep mysql running on a server etc.

But as someone who has bug reported, bug tested and bug fixed for Sqlite, I can tell you its a bloody nightmare.
Especially since smf only supports sqlite 2, not pdo 3.  Poor functionality, poor performance, poor support.

habakuk

No worries... SQLite is on my target list for some smaller projects where I develop the frontend - single user systems that need a fast database. I stick with postgres for everything else. :)

cheers
®

Oldiesmann

This has been fixed for RC2. Thanks for reporting :)

If you'd like to fix it on your forum, here's what you do.

Sources/Recent.php

Find
Code (Lines 1054-155) Select
$request = $smcFunc['db_query']('', '
SELECT DISTINCT t.id_topic


Replace
$request = $smcFunc['db_query']('unread_replies', '
SELECT DISTINCT t.id_topic


Sources/Subs-Db-postgresql.php

Find
Code (Lines 305-307) Select
);

if (isset($replacements[$identifier]))


Add before that
'unread_replies' => array(
'~t.id_topic~' => 't.id_topic, {raw:sort}',
),


Thanks for bringing this to our attention :)
Michael Eshom
Christian Metal Fans

Advertisement: