Really old events shows as upcoming, bug in WordPress' generated SQL

  • Resolved hallvors

    (@hallvors)


    9 years ago

    Hi,
    I’m porting a site to WordPress and it contains some lists of events going back to the late 1990s (and a couple of misdated ones that show up as happening on January 1st 1970 but let’s ignore those..)

    My problem is that using the normal vsel shortcode, many of the really old events appear as if they were upcoming ones.

    I’ve spent a bit of time on this, gone as far as trying to debug the SQL generated by WordPress:

    cur2.execute("SELECT SQL_CALC_FOUND_ROWS wp_site_posts.ID FROM wp_site_posts INNER JOIN wp_sisu_no_postmeta ON ( wp_site_posts.ID = wp_sisu_no_postmeta.post_id ) INNER JOIN wp_sisu_no_postmeta AS mt1 ON ( wp_site_posts.ID = mt1.post_id ) WHERE 1=1 AND ( wp_sisu_no_postmeta.meta_key = 'event-date' AND ( ( mt1.meta_key = 'event-date' AND CAST(mt1.meta_value AS CHAR) >= '1456272000' ) ) ) AND wp_site_posts.post_type = 'event' AND ((wp_site_posts.post_status = 'publish')) GROUP BY wp_site_posts.ID ORDER BY wp_sisu_no_postmeta.meta_value+0 ASC LIMIT 0, 10")

    Note that CAST(mt1.meta_value AS CHAR)? Turns out that string comparisons of numerical timestamps can give you weird results. See:

    MariaDB [site_db]> select 907884000 >= 1456272000;
+-------------------------+
| 907884000 >= 1456272000 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

MariaDB [site_db]> select '907884000' >= '1456272000';
+-----------------------------+
| '907884000' >= '1456272000' |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

    This appears to be why events that are so old their time stamp string are shorter (but starts with a higher number) actually appear as future events. The >= operator doesn’t do what you expect when comparing strings instead of numbers.

    https://www.ads-software.com/plugins/very-simple-event-list/

Viewing 6 replies - 1 through 6 (of 6 total)
  • Thread Starter hallvors

    (@hallvors)

    It’s a bug in the plugin after all. You should add this:

    'type' => 'NUMERIC'

    to the inner array for the meta query:

    $vsel_meta_query = array(
		'relation' => 'AND',
		array(
			'key' => 'event-date',
			'value' => $today,
			'compare' => '>='
		)
	);

    Better do it in both vsel_shortcode.php and vsel_past_events_shortcode.php ??

    Plugin Author Guido

    (@guido07111975)

    Hi,

    I wasn’t familiar with this issue ??

    Currently I’m working on a new update and will add this argument to the meta query, no problem! Will update plugin today or tomorrow.

    Guido

    Thread Starter hallvors

    (@hallvors)

    That’s awesome Guido, thank you for this quick response! ??

    Plugin Author Guido

    (@guido07111975)

    Hi again,

    I notice this isn’t correct:

    'type' => 'NUMERIC'

    As mentioned in codex it should be:

    'meta_type' => 'NUMERIC'

    You agree?

    Guido

    Thread Starter hallvors

    (@hallvors)

    No ??

    It needs to be “type” if you add it to your $vsel_meta_query hash as suggested – see the indented bulleted list here:
    https://codex.www.ads-software.com/Class_Reference/WP_Meta_Query
    for valid attributes.

    I’ve tested – in my local install meta_type doesn’t work.

    (Although it probably would work if we moved it to somewhere else in the code – to the $vsel_query_args definition)
    -Hallvord

    Plugin Author Guido

    (@guido07111975)

    Thanks for your feedback, my mistake.

    I will NOT change it in upcoming update ??

    Guido

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Really old events shows as upcoming, bug in WordPress' generated SQL’ is closed to new replies.