Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

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

Abierto
  • ¿Algo no está bien?
#531 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
40/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
php, sql, sqlite
Área
database

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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.

Lenguaje dominante
PHP
Estrellas
362
Forks
69
Merge medio
3 d 11 h
PR fusionados (30 d)
12

Preparar el entorno

Abrir en Codespaces

Inicia el contenedor de desarrollo del proyecto en tu navegador, con tu propia cuenta de GitHub.

  • Sin Dockerfile ni archivo de Docker Compose
  • Sin plantilla de pull request
  • Sin guía de contribución

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de WordPress/sqlite-database-integration

Todos los issues de WordPress/sqlite-database-integration

Issues similares

Más issues de PHP

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.