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

DATE_ADD() and DATE_SUB() differ from MySQL for DATE values, month ends, and QUARTER and composite units

Aperta
  • Qualcosa non va?
#531 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
40/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
php, sql, sqlite
Ambito
database

Direzione di ricerca

The fix belongs in the shared translation that translate_interval_arithmetic() performs, which the issue says #510 introduces, so check whether #510 has landed before starting. Run the SQL cases from the report against the plugin and compare each result with the MySQL values listed, covering return type, month-end clamping, QUARTER and composite units, and ADDDATE/SUBDATE. Done when every case in the report matches MySQL and the existing tests still pass.

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

Descrizione

Reproduction

CREATE TABLE s (id INT PRIMARY KEY, renews_on DATE);
INSERT INTO s VALUES (1, '2026-01-31');

-- 1. A DATE with a date-only unit returns a DATETIME.
SELECT DATE_ADD('2026-09-01', INTERVAL 7 DAY);           -- '2026-09-08 00:00:00'  (MySQL: '2026-09-08')
SELECT COUNT(*) FROM s WHERE DATE_ADD(renews_on, INTERVAL 7 DAY) = '2026-02-07';
                                                         -- 0                      (MySQL: 1)

-- 2. Month and year arithmetic overflows instead of clamping to the last day of the month.
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);         -- '2024-03-02 00:00:00'  (MySQL: '2024-02-29')
SELECT DATE_ADD('2024-02-29', INTERVAL 1 YEAR);          -- '2025-03-01 00:00:00'  (MySQL: '2025-02-28')
UPDATE s SET renews_on = DATE_ADD(renews_on, INTERVAL 1 MONTH);
                                                         -- stores '2026-03-03'    (MySQL: '2026-02-28')

-- 3. QUARTER and composite units return NULL.
SELECT DATE_ADD('2026-01-15', INTERVAL 1 QUARTER);                      -- NULL  (MySQL: '2026-04-15')
SELECT DATE_ADD('2026-01-15 10:00:00', INTERVAL '1:30' HOUR_MINUTE);    -- NULL  (MySQL: '2026-01-15 11:30:00')
UPDATE s SET renews_on = DATE_ADD(renews_on, INTERVAL 1 QUARTER);
                                                         -- stores NULL, even in strict mode

-- 4. ADDDATE() and SUBDATE() are not supported.
SELECT ADDDATE('2026-07-13', INTERVAL 7 DAY);            -- syntax error           (MySQL: '2026-07-20')
SELECT SUBDATE('2026-07-13', 7);                         -- no such function       (MySQL: '2026-07-06')

Reproduced on trunk (5deb4d6) with PHP 8.3 and SQLite 3.45.1. The first case was found by a real query comparing a DATE column with DATE_ADD().

Impact

  • Case 1 affects reads only. On write, the value is cast to the column type, so a DATE column still stores '2026-09-08'. But comparisons against DATE_ADD() on a DATE value never match.
  • Cases 2 and 3 silently store wrong data. A monthly renewal from January 31 lands on March 3, and a quarterly one erases the date.

Notes

#510 mentions cases 1 to 3 as "worth their own change" and moves the shared translation into translate_interval_arithmetic(), so a fix would best build on it. MySQL's rules, from the date and time functions docs:

  • The result is a DATE when the input is a DATE and the unit is YEAR, QUARTER, MONTH, WEEK, or DAY (or a composite of them, like YEAR_MONTH). Otherwise, it is a DATETIME.
  • When MONTH, QUARTER, or YEAR arithmetic produces a day that doesn't exist in the resulting month, it is adjusted to the last day of that month.
  • ADDDATE(date, INTERVAL ...) is a synonym for DATE_ADD(), and ADDDATE(date, days) adds days. The same applies to SUBDATE().

I'm happy to contribute a PR on top of #510, or separately if you prefer.

Use of AI

This report was prepared with Claude Code.

Lingua principale
PHP
Stelle
362
Fork
69
Merge medio
3g 11h
PR unite (30g)
12

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.