Subject: Re: bug#79011: sqlite-mode-delete does not work correctly when field value is NULL
From: Eli Zaretskii <[email-masked]>
To: "Yue Yi" <[email-masked]>
Cc: [email-masked]
Date: Sat, 26 Jul 2025 11:51:00 +0300
List-Id: <debbugs-submit.debbugs.gnu.org>

> 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" <[email-masked]>
> 
> 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.