Sorting by publish_on gives wrong result if unpublish_on is present

Hi Exlin,

If you have publish_on as the first field in the sort sequence, then all nodes with no publish_on value will be placed together in the sequence, then these would be ordered by the second field (post date). I do not think that views can interleave two fields and sort on the combined value, which is what I think you are implying by your comment "publish on value or post date both being equally valid".

Having said that, bugs by their nature are always surprising, so it could very well be doing something odd. Could you give us a screen shot, or example, so we can see the resulting order and why it is wrong. Then we can help to solve it.

Jonathan

Thank you for the screen shots. I have added them into this issue directly, for safety and future proofing. I think there is something odd going on, but I cannot see exactly what is happening yet.

In the views settings here, you have the filter of 'Content: published = Yes', but this means that only published content will be shown. So sorting by 'Scheduler: publish on date' will have no effect because these nodes will not have a publish_on date.

So in the results of the view here, those top two nodes must be published. Is that right? Also can you modify the view to add 'publish on date' and 'unpublished on date' into the display, and add labels, so that we can see exactly the data that views is using.

I will also try to replicate this too.

Jonathan

OK, I have discovered what the problem is, but I'm not sure how to solve it. It is caused by design of the scheduler db table, where if only one of the scheduler dates is entered then the other one is zero. When the node has neither scheduled date entered there is no row in the db table. That's ok so far, as we cater for zero in the scheduler module processing.

Views builds the sql, and it has a left join from {node} to {scheduler} which is fine, but if you order by publish_on date, then zeros in the scheduler table force those rows to be placed higher in the sequence than nodes which have neither value, because the join produces null. Zero is great than null, so even though the nodes effectively have the same publish_on date (i.e. no date) the ones with an unpublished_on date get placed higher in the result than nodes with neither date, even though unpublish_on is not in the sort definition.

Here is some SQL which you can execute in phpMyAdmin (or whatever db admin you use), to show this in detail:

SELECT node.title, node.nid, node.created, from_unixtime(node.created) as created_fmt, scheduler.publish_on, from_unixtime(scheduler.publish_on) as pub_on, 
scheduler.unpublish_on, from_unixtime(scheduler.unpublish_on) as unpub_on
FROM node
LEFT JOIN scheduler ON node.nid = scheduler.nid
WHERE (( (node.created >= unix_timestamp()-86400) AND (node.status = '1') ))
ORDER BY scheduler.publish_on DESC, node.created DESC

This replicates the same filtering and sorting as per your view, and hopefully it will show the same incorrect results as you have posted.

OK, now we know the cause, how do we solve it? One obvious question to ask is why the database table columns are specified as 'not null' forcing us to insert zero to represent 'no date'. If the value could be left as null then this problem would be immediately solved, but there would probably be quite a bit of re-work in the module coding. I have often wondered why we had to convert missing dates to zero, and this continuously throws up the 1st Jan 1970 format strings during debug which is bad. I would very much like to understand why we cannot change the table to allow nulls, providing the rest of the module code was checked and fixed as necessary.

Jonathan

Just found #1040168: Store empty scheduling dates as null not zero which was reported in January 2011, but postponed until after the 1.0 release for D7. The cause of the problem is exactly the same - storing empty dates as zero instead of null.

Closing this issue as a duplicate.
Follow up work will be done on #1040168: Store empty scheduling dates as null not zero