SQL errors since c2acab2

[tokudan]

Since the commit c2acab2, postgresql complains about SQL errors: Mar 24 15:01:02 ashe postgres[19657]: [3-1] 2015-03-24 15:01:02 CET [19657-1] redmatrix@redmatrix ERROR: column "item.created" must appear in the GROUP BY clause or be used in an aggregate function at character 244 Mar 24 15:01:02 ashe postgres[19657]: [3-2] 2015-03-24 15:01:02 CET [19657-2] redmatrix@redmatrix STATEMENT: SELECT parent from item Mar 24 15:01:02 ashe postgres[19657]: [3-3] #011#011#011#011WHERE uid != 1 Mar 24 15:01:02 ashe postgres[19657]: [3-4] #011#011#011#011AND item_private = 0 AND item_restrict = 0 AND uid in (8,2,6) Mar 24 15:01:02 ashe postgres[19657]: [3-5] #011#011#011#011AND (item_flags & 32) > 0 Mar 24 15:01:02 ashe postgres[19657]: [3-6] #011#011#011#011 and ( created > '2015-03-22 14:01:02' or edited > '2015-03-22 14:01:02' ) GROUP BY parent ORDER BY created ASC LIMIT 100 ;

The errors do not seem to apear when brosing the hub, only when remote systems try to contact it. At least that's what the times I'm seeing the errors in my log suggest - they're not close to my own poller.php cronjobs and appear while I'm sleeping.

I have reverted the commit locally and am testing if that solves the issue.

[habeascodice]

SQL standard states that rows are to be sorted after the result set has been determined. An alternative to distinct on (distinct actually doesn't work) would be to turn it into a join like:

SELECT p.id as parent from item p inner join item i on (p.id=i.parent) WHERE i.uid != %d AND i.item_private = 0 AND i.item_restrict = 0 AND i.uid in (" . stream_perms_api_uids(PERMS_PUBLIC,10,1) . ") AND (i.item_flags & %d) > 0 $sql_extra ORDER BY i.created ASC $limit",

we can't do that because of $sql_extra, but we can do a little less pretty:

SELECT p.id AS parent FROM item p INNER JOIN (SELECT parent FROM item
WHERE uid != %d AND item_private = 0 AND item_restrict = 0 AND uid in (" . stream_perms_api_uids(PERMS_PUBLIC,10,1) . ") AND (item_flags & %d) > 0 $sql_extra ORDER BY created ASC) i ON (p.id=i.parent) GROUP BY p.id $limit",

This has the added benefit of being able to move the limit to the number of items rather than the number of parents if that's what you're after.

[friendica]

The issue is that I removed created from the rows returned after I looked and didn't see it used anywhere - it was used in the ordering and that's what choked.. This probably isn't needed anyway since we often order it after searching for the kids.

[tokudan]

00b3c4c doesn't seem to have solved the issue. The error is pretty much the same: Mar 25 19:50:38 ashe postgres[23030]: [3-1] 2015-03-25 19:50:38 CET [23030-1] redmatrix@redmatrix ERROR: column "item.created" must appear in the GROUP BY clause or be used in an aggregate function at character 16 Mar 25 19:50:38 ashe postgres[23030]: [3-2] 2015-03-25 19:50:38 CET [23030-2] redmatrix@redmatrix STATEMENT: SELECT parent, created from item Mar 25 19:50:38 ashe postgres[23030]: [3-3] #011#011#011#011WHERE uid != 1 Mar 25 19:50:38 ashe postgres[23030]: [3-4] #011#011#011#011AND item_private = 0 AND item_restrict = 0 AND uid in (8,6,2) Mar 25 19:50:38 ashe postgres[23030]: [3-5] #011#011#011#011AND (item_flags & 32) > 0 Mar 25 19:50:38 ashe postgres[23030]: [3-6] #011#011#011#011 and ( created > '2015-03-23 18:50:38' or edited > '2015-03-23 18:50:38' ) GROUP BY parent ORDER BY created ASC LIMIT 100 ;

Removing created from the result and from the sorting would solve the issue. This query works fine when I execute it manually in the database: SELECT parent from item WHERE uid != 1 AND item_private = 0 AND item_restrict = 0 AND uid in (8,6,2) AND (item_flags & 32) > 0 and ( created > '2015-03-23 18:50:38' or edited > '2015-03-23 18:50:38' ) GROUP BY parent LIMIT 100 ;

[habeascodice]

just pulled and this is still broken

LINE 1: SELECT parent, created from item ^, backtrace: include/dba/dba_driver.php:q():331, include/items.php:q():4395, mod/zotfeed.php:zot_feed():42, index.php:zotfeed_init():290 2015-03-28 17:36:04 SELECT parent, created from item WHERE uid != 1 AND item_private = 0 AND item_restrict = 0 AND uid in (3,22,24,11,16,23,7,9,4,28) AND (item_flags & 32) > 0 and ( created > '2015-03-26 17:36:04' or edited > '2015-03-26 17:36:04' ) GROUP BY parent ORDER BY created ASC LIMIT 100 ; returned false ERROR: column "item.created" must appear in the GROUP BY clause or be used in an aggregate function

[habeascodice]

this is pretty major. I can't even get git to revert to the previous commit for some reason, and so I'm getting a ton of failures on my.federated.social. Messing with this is taking major time away from ecommerce :( anything more brute-force I might try to stop the errors will corrupt my git tree and make things even more of a pain in the butt time-killer.

[habeascodice]

thanks!

[habeascodice]

Looks good here. Submitter reopen if necessary.

[ghost]

This issue was moved to redmatrix/redmatrix#5

[ghost]

This issue was moved to redmatrix/redmatrix#186