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

Previous Next

To add a comment to this bug, you must first unarchive it, by sending
a message to control AT debbugs.gnu.org, with unarchive 79011 in the body.
You can then email your comments to 79011 AT debbugs.gnu.org in the normal way.

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)]

Message #10 received at 79011-done <at> debbugs.gnu.org (full text, mbox):

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.