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

Previous Next

To reply to this bug, email your comments to 79011 AT debbugs.gnu.org.

Toggle the display of automated, internal messages from the tracker.

Message #5 received at submit <at> debbugs.gnu.org (full text, mbox):

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 1 (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 2 (text/html, inline)]

Previous Next

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