GNU bug report logs - #79011
sqlite-mode-delete does not work correctly when field value is NULL

Previous Next

Full log

View this message in rfc822 format

From: help-debbugs <at> gnu.org (GNU bug Tracking System)
To: Eli Zaretskii <eliz <at> gnu.org>
Cc: tracker <at> debbugs.gnu.org
Subject: bug#79011: closed (sqlite-mode-delete does not work correctly
 when field value is NULL)
Date: Sat, 26 Jul 2025 08:52:02 +0000

[Message part 1 (text/plain, inline)]

Your message dated Sat, 26 Jul 2025 11:51:00 +0300
with message-id <86tt2z1gjv.fsf <at> gnu.org>
and subject line Re: bug#79011: sqlite-mode-delete does not work correctly when field value is NULL
has caused the debbugs.gnu.org bug report #79011,
regarding sqlite-mode-delete does not work correctly when field value is NULL
to be marked as done.

(If you believe you have received this mail in error, please contact
help-debbugs <at> gnu.org.)


-- 
79011: https://debbugs.gnu.org/cgi/bugreport.cgi?bug=79011
GNU Bug Tracking System
Contact help-debbugs <at> gnu.org with problems

[Message part 2 (message/rfc822, inline)]

From: "Yue Yi" <include_yy <at> qq.com>
To: "bug-gnu-emacs" <bug-gnu-emacs <at> gnu.org>
Subject: sqlite-mode-delete does not work correctly when field value is NULL
Date: Mon, 14 Jul 2025 13:57:19 +0800

[Message part 3 (text/plain, inline)]

Hello Emacs maintainers, Please consider the database table created by the following code: --------------------------------&gt;8<-------------------------------- (let ((db (sqlite-open "test.sqlite3")))   (with-sqlite-transaction db     (sqlite-execute db "CREATE TABLE tmp (id, txt)")     (sqlite-execute db "INSERT INTO tmp VALUES (1, null)")     (sqlite-execute db "INSERT INTO tmp values (2, '')"))) --------------------------------&gt;8<-------------------------------- Then, open test.sqlite3 with `sqlite-mode-open-file' and expand the database contents. Use DEL (sqlite-mode-delete) to try deleting the first and second rows. After pressing g (sqlite-mode-list-tables), you'll find that the first row still exists and hasn't been deleted as expected. The reason for this issue is that when `sqlite-mode-delete' constructs the SQL statement, it concatenates conditions like (FIELD = ?) even when a field's value is NULL, instead of using (FIELD IS ?). To check whether a field is NULL, the correct syntax is IS NULL or IS NOT NULL. Here is one possible fix: diff --git a/lisp/sqlite-mode.el b/lisp/sqlite-mode.el index a4b96b02b48..82d5cc80a2c 100644 --- a/lisp/sqlite-mode.el +++ b/lisp/sqlite-mode.el @@ -204,9 +204,12 @@ sqlite-mode-delete       (format "delete from \"%s\" where %s"               (cdr table)               (string-join -              (mapcar (lambda (column) -                        (format "\"%s\" = ?" (car (split-string column " ")))) -                      (cons "rowid" (sqlite-mode--column-names (cdr table)))) +              (cl-mapcar (lambda (column value) +                           (format "\"%s\" %s ?" +                                   (car (split-string column " ")) +                                   (if value "=" "is"))) +                         (cons "rowid" (sqlite-mode--column-names (cdr table))) +                         row)                " and "))       row)      (delete-region (line-beginning-position) (progn (forward-line 1) (point))))) Regards.

[Message part 4 (text/html, inline)]

[Message part 5 (message/rfc822, inline)]

From: Eli Zaretskii <eliz <at> gnu.org>
To: "Yue Yi" <include_yy <at> qq.com>
Cc: 79011-done <at> debbugs.gnu.org
Subject: Re: bug#79011: sqlite-mode-delete does not work correctly when field
 value is NULL
Date: Sat, 26 Jul 2025 11:51:00 +0300

> Date: Mon, 14 Jul 2025 13:57:19 +0800
> From:  "Yue Yi" via "Bug reports for GNU Emacs,
>  the Swiss army knife of text editors" <bug-gnu-emacs <at> gnu.org>
> 
> Please consider the database table created by the following code:
> 
> -------------------------------->8<--------------------------------
> (let ((db (sqlite-open "test.sqlite3")))
>   (with-sqlite-transaction db
>     (sqlite-execute db "CREATE TABLE tmp (id, txt)")
>     (sqlite-execute db "INSERT INTO tmp VALUES (1, null)")
>     (sqlite-execute db "INSERT INTO tmp values (2, '')")))
> -------------------------------->8<--------------------------------
> 
> Then, open test.sqlite3 with `sqlite-mode-open-file' and expand the
> database contents. Use DEL (sqlite-mode-delete) to try deleting the
> first and second rows. After pressing g (sqlite-mode-list-tables),
> you'll find that the first row still exists and hasn't been deleted as
> expected.
> 
> The reason for this issue is that when `sqlite-mode-delete' constructs
> the SQL statement, it concatenates conditions like (FIELD = ?) even when
> a field's value is NULL, instead of using (FIELD IS ?). To check whether
> a field is NULL, the correct syntax is IS NULL or IS NOT NULL.
> 
> Here is one possible fix:

Thanks, installed on the master branch, and closing the bug.

Previous Next

GNU bug tracking system
Copyright (C) 1999 Darren O. Benham, 1997,2003 nCipher Corporation Ltd, 1994-97 Ian Jackson.