Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

`DATE_FORMAT()` translation is incorrect for most MySQL format specifiers, silently breaking `WP_Date_Query` time queries

Aperta
  • Qualcosa non va?
#491 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
48/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Tranquilla
Stack tecnologico
php, sqlite, wordpress
Ambito
backend, database

Direzione di ricerca

Inizia da wp-includes/database/sqlite/class-wp-mysql-on-sqlite.php, esaminando la mappa di traduzione di DATE_FORMAT() e il workaround per il cast a float, quindi confrontala con WP_Date_Query::build_time_query() in wp-includes/class-wp-date-query.php. Controlla il meccanismo degli UDF registrati in WP_SQLite_PDO_User_Defined_Functions e verifica la versione minima di SQLite del progetto. Il lavoro è completato quando gli specificatori MySQL supportati e i quattro formati temporali di WP_Date_Query producono risultati corretti senza discrepanze silenziose.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

bug

This is something I encountered when working on https://github.com/wp-cli/entity-command/pull/639 (using WP_Site_Query with WP_Date_Query). The report below is AI-generated, so please take it with a grain of salt.

WP_Date_Query filtering by an exact time silently returns zero rows under the SQLite driver, with no SQL error and nothing in $wpdb->last_error. The same query returns the correct rows on MySQL/MariaDB.

Digging into it, the immediate cause is narrow (a missing float cast), but it sits on top of a broader problem: 21 of the 30 entries in MYSQL_DATE_FORMAT_TO_SQLITE_STRFTIME_MAP produce wrong output, and 9 of those produce plausible-looking wrong values rather than NULL. Several map MySQL specifiers onto valid-but-unrelated SQLite specifiers — e.g. MySQL %b (abbreviated month) is mapped to SQLite %M, which is minutes.

Tested against sqlite-database-integration 3.0.0, SQLite 3.45.1, WordPress trunk.

Reproduction

// A site registered at 2014-10-21 07:30:15.
$sites = get_sites( [
    'date_query' => [ [
        'column' => 'registered',
        'year'   => 2014, 'month'  => 10, 'day'    => 21,
        'hour'   => 7,    'minute' => 30,  'second' => 15,
    ] ],
] );
MySQL SQLite
year + month + day matches matches
… + hour + minute + second matches 0 rows

$wpdb->last_error is empty in the failing case — the query executes successfully and just silently matches nothing.

The SQL WP_Site_Query generates is:

SELECT wp_blogs.blog_id FROM wp_blogs
WHERE (
  ( YEAR( wp_blogs.registered ) = 2014
    AND MONTH( wp_blogs.registered ) = 10
    AND DAYOFMONTH( wp_blogs.registered ) = 21
    AND DATE_FORMAT( wp_blogs.registered, '%H.%i%s' ) = 7.301500 )
)

The YEAR()/MONTH()/DAYOFMONTH() parts translate fine. The DATE_FORMAT() comparison is what fails.

Defect 1 — the float cast covers only one of the four formats WP_Date_Query emits

class-wp-mysql-on-sqlite.php has an explicit workaround for MySQL's string-to-float comparison semantics:

$cast_to_float = "'%H.%i'" === $mysql_format;
if ( true === $cast_to_float ) {
    return sprintf( 'CAST(STRFTIME(%s, %s) AS FLOAT)', $format, $date );
}

But WP_Date_Query::build_time_query() builds its format string incrementally and can emit four different values, all compared against %f (a float):

// wp-includes/class-wp-date-query.php
if ( null !== $hour ) { $format .= '%H.'; } else { $format .= '0.'; }
$format .= '%i';
if ( null !== $second ) { $format .= '%s'; }
return $wpdb->prepare( "DATE_FORMAT( $column, %s ) $compare %f", $format, $time );
Format Emitted when Cast applied?
%H.%i hour + minute ✅
%H.%i%s hour + minute + second ❌
0.%i minute only ❌
0.%i%s minute + second ❌

So three of the four are compared as string-vs-float and never match. Notably 0.%i produces the textually-correct '0.30' and still fails, purely because SQLite won't compare '0.30' to 0.30.

Checking in_array( $mysql_format, [ "'%H.%i'", "'%H.%i%s'", "'0.%i'", "'0.%i%s'" ], true ) would cover all four.

Defect 2 — MySQL %S / %s (seconds) are mapped to SQLite %s (Unix timestamp)

'%S' => '%s',
'%s' => '%s',

MySQL %S and %s both mean seconds, 00–59. SQLite %s is seconds since 1970-01-01; SQLite's seconds-of-minute is uppercase %S.

DATE_FORMAT('2014-10-21 07:30:15', '%S')
  MySQL  => '15'
  SQLite => '1413876615'

This is what turns %H.%i%s into '07.301413876615' above. Both entries should map to '%S'.

Defect 3 — multi-specifier expansions are never re-translated

'%r' => '%h:%i:%s %A',
'%T' => '%H:%i:%s',

These expand to MySQL specifiers, but the translation is a single strtr() call, which by design never revisits text it has already substituted. So %i and %s survive into the SQLite format string, %i is not a valid SQLite specifier, and strftime() returns NULL for the whole expression.

DATE_FORMAT('2014-10-21 07:30:15', '%T')
  MySQL  => '07:30:15'
  SQLite => NULL

SQLite 3.44+ supports %T natively, so '%T' => '%T' works. %r needs '%I:%M:%S %p' (SQLite specifiers).

Full comparison

Every MySQL specifier, DATE_FORMAT('2014-10-21 07:30:15', <code>), MariaDB 10.11 vs sqlite-database-integration 3.0.0 on SQLite 3.45.1:

Silently wrong — returns a plausible value, so callers cannot detect the failure:

Code Meaning Mapped to MySQL SQLite
%b Abbreviated month %M (minute) Oct 30
%M Full month name %F (ISO date) October 2014-10-21
%W Full weekday name %l (12-hour) Tuesday 7
%S Seconds %s (Unix time) 15 1413876615
%s Seconds %s (Unix time) 15 1413876615
%e Day of month %j (day of year) 21 294
%D Day + suffix %jS 21st 294S
%u Week (Mon-first) %W (Sun-first) 43 42
%v Week (Mon-first) %W (Sun-first) 43 42

Returns NULL — mapped to a specifier this SQLite build does not have:

Code Meaning Mapped to MySQL SQLite
%a Abbreviated weekday %D Tue NULL
%c Month, no padding %n 10 NULL
%h Hour (12) %h 07 NULL
%I Hour (12) %h 07 NULL
%j Day of year %z 294 NULL
%k Hour (24), no padding %G 7 NULL
%l Hour (12), no padding %g 7 NULL
%p AM/PM %A AM NULL
%r 12-hour time %h:%i:%s %A 07:30:15 AM NULL
%T 24-hour time %H:%i:%s 07:30:15 NULL
%x ISO year %o 2014 NULL
%y 2-digit year %y 14 NULL

Correct: %d, %H, %i, %m, %U, %V, %w, %X, %Y.

Suggested direction

SQLite 3.44 (Nov 2023) added a batch of specifiers that resolve most of these directly:

MySQL Currently Native SQLite equivalent
%e %j %e
%h, %I %h %I
%j %z %j
%p %A %p
%S, %s %s %S
%T %H:%i:%s %T
%r %h:%i:%s %A %I:%M:%S %p

Two caveats on that list: SQLite's %k and %l are space-padded (' 7') where MySQL's are unpadded ('7'), so those need trimming rather than a straight mapping. And %e/%I/%p/%T require SQLite ≥ 3.44 — worth confirming against the project's minimum supported version.

The remainder have no native equivalent and would need a UDF: %a, %b, %M, %W (locale-independent name lookups), %D (ordinal suffix), %c and %y (trimming), and %x/%u/%v (ISO week/year — SQLite's %G/%V cover these but returned NULL on 3.45.1 here, so they look like 3.46+). The driver already registers UDFs via WP_SQLite_PDO_User_Defined_Functions, so the mechanism is in place.

Separately, it may be worth having an unmappable specifier throw rather than silently emit a wrong value — the existing Could not translate a DATE_FORMAT() format exception never fires for these cases because strtr() always returns a non-empty string, so the if ( ! $format ) guard cannot catch them.

Lingua principale
PHP
Stelle
362
Fork
67
Merge medio
5g 17h
PR unite (30g)
5

Preparare l'ambiente

Apri in Codespaces

Avvia il container di sviluppo del progetto nel browser, con il tuo account GitHub.

  • Nessun Dockerfile né file Docker Compose
  • Nessun modello di pull request
  • Nessuna guida per i contributori

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di WordPress/sqlite-database-integration

Tutte le issue di WordPress/sqlite-database-integration

Issue simili

Altre issue su PHP

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.