SQLPro for SQLite display a column value as NULL when a value is not null.

[chipbeaulieu]

Describe the bug
SQLPro is displaying values in a column as NULL when the value is not null.

To Reproduce
This is certainly a strange bug.

1. using Terminal, open an existing sqlite db.
2. execute .read File to run a set up of update commands on a table (7 records, 2 columns)
3. Exit sqlite cl, and open the same db in SQLPro
4. Query on those same records
5. A random, but repeatable list of the records will show NULL in the first of the updated columns
6. Opening the same db in command line with the same query shows the values as expected.

Other notes:

1. Reloading the db in SQLPro has no effect
2. Running the same update commands in SQLPro wrapped in a TRANSACTION has no affect - the value still displays as NULL
3. If I run the same update commands in SQLPro w/o a transaction, the values now appear in the query as expected.
4. Also, I am updating two columns, but only the first one is ever displayed as null
5. Restarting SQLPro does not fix the display issue.
6. If I query for NULL in SQLPro, it does not return the records.

Expected behavior
When records are updated outside SQLPro, the value should be displayed in SQLPro.

Screenshots
The first screen shot shows the command I use in SQLite to update the database with a query showing a value.
The second screen shot shows the query from the same db returning the 8 updated records. All 8 records were updated and should return results, but records 1,2,3,5,6 show NULL.

Environment details (please complete the following information):

• MacBookPro M2 Max
• OS: macOS 14.5
• SQLPro app Version: SQLPro for SQLite, Version 2024.30 (Build 4374.5)
• Installation source: Mac App Store
• Target database server: sqlite

[chipbeaulieu]

I am uploading another screen shot of SQLPro running the identical query as sqlite3. The above query would return the same record, but I want to make sure the steps are the same.

[hankinsoft]

Any chance you could provide the database, or even a create a database which this can be reproduced with?

[chipbeaulieu]

Thought you might need that. Here are two files in a zip archive: BiblesCopyV.sqlite - the database QueryInput.txt - the file to use in CL sqlite3 with the .read command If you run the following query immediately in SQLPro, it shows several nulls in the Scripture column. Running the same query in sqlite3 shows the correct values. select verseNumber, scripture from verses where bibleid=206 and book=66 and chapter=19 and versenumber between 1 and 20 If you copy the update commands from the QueryInput file and run them in SQLPro, and then check the above query, the nulls are gone. Run the .read command in sqlite3, reload db in SQLPro and run the above query, and the nulls are back.  Note: I truncated most of the db file and then VACUUMed it. The issue remains. Regards, Chip.

…

On Jun 9, 2024, at 7:02 AM, Kyle Hankinson ***@***.***> wrote: Any chance you could provide the database, or even a create a database which this can be reproduced with? — Reply to this email directly, view it on GitHub <#990 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABCXQG35URUM6L6JUZ4OFI3ZGQY25AVCNFSM6AAAAABI66R6KKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNJWGQ2DGNZTGA>. You are receiving this because you authored the thread.

[chipbeaulieu]

Kyle, Can you confirm you received my files? The first time, it bounced back saying the files were too large. I zipped them and resent. Thanks, Chip.

…

On Jun 9, 2024, at 7:02 AM, Kyle Hankinson ***@***.***> wrote: Any chance you could provide the database, or even a create a database which this can be reproduced with? — Reply to this email directly, view it on GitHub <#990 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABCXQG35URUM6L6JUZ4OFI3ZGQY25AVCNFSM6AAAAABI66R6KKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNJWGQ2DGNZTGA>. You are receiving this because you authored the thread.

[hankinsoft]

Hi, not received - could you email them to kyle @ hankinsoft.com? Thanks!

[hankinsoft]

Hi - could you try:
https://sqlprostudio.s3.us-east-1.amazonaws.com/sqlite/SQLProSQLite.2024.31.app.zip

And let me know if that sorts it out? If so, I will get the Mac app store version updated as well.

[chipbeaulieu]

Kyle, Yes, that fixed it. I suspect it has something to do with the quotation marks used on those records? I noticed they didn’t seem to be the normal unicode versions of the curly quotes. Thanks for the quick response. It took a while to drill down to find this issue. And thanks for a great product. Regards, Chip.

…

On Jun 10, 2024, at 11:33 AM, Kyle Hankinson ***@***.***> wrote: Hi - could you try: https://sqlprostudio.s3.us-east-1.amazonaws.com/sqlite/SQLProSQLite.2024.31.app.zip And let me know if that sorts it out? If so, I will get the Mac app store version updated as well. — Reply to this email directly, view it on GitHub <#990 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABCXQG7GFEAH5PMDWR5PQCLZGXBK7AVCNFSM6AAAAABI66R6KKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNJYGY3TCMRVGA>. You are receiving this because you authored the thread.

[hankinsoft]

Awesome, glad to hear it. Yep, it was to do with the quotes. The App Store build is available now as well. If you happen to get the chance, would you consider leaving a rating or review on the App Store? As an independent developer, they help me out a ton. Please let me know if you have any other issues.

[chipbeaulieu]

Thanks again. Got the update and left a review!

…

On Jun 12, 2024, at 7:45 AM, Kyle Hankinson ***@***.***> wrote: Awesome, glad to hear it. Yep, it was to do with the quotes. The App Store build is available now as well. If you happen to get the chance, would you consider leaving a rating or review on the App Store? As an independent developer, they help me out a ton. Please let me know if you have any other issues. — Reply to this email directly, view it on GitHub <#990 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABCXQGZG676P6PXAVFADCHDZHAYGLAVCNFSM6AAAAABI66R6KKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNRSHAYDENZTGU>. You are receiving this because you authored the thread.