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.
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.
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
®
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. ;)
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 (http://www.postgresql.org/docs/8.4/static/release-7-0.html))
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
®
Right. Thank you, Norv. So it seems to be a postgres related issue. Am I supposed to file a bug then?
cheers
®
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.
Done. And it's a good moment to get a postgres installation. You will love it, I promise. :)
cheers
®
Cheers, thank you! :)
http://dev.simplemachines.org/mantis/view.php?id=3764
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).
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
®
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.
As Mark says. Specially since many of us don't use it (yet :) ), it is a little difficult to stumble upon issues.
Will do. And kuddos for all the good work of the broad SMF team! (This is not getting said often enough ;))
cheers
®
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.
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
®
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
$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
);
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 :)