search

hankinsoft / SQLPro

SQLPro bug & features tracking.
104 stars 27 forks source link

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

Closed chipbeaulieu closed 3 months ago

chipbeaulieu commented 3 months ago

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

SQLite3 SQLPro
chipbeaulieu commented 3 months ago

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.

SQLPro2
hankinsoft commented 3 months ago

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

chipbeaulieu commented 3 months ago

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 https://github.com/hankinsoft/SQLPro/issues/990#issuecomment-2156443730, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABCXQG35URUM6L6JUZ4OFI3ZGQY25AVCNFSM6AAAAABI66R6KKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNJWGQ2DGNZTGA. You are receiving this because you authored the thread.

chipbeaulieu commented 3 months ago

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 https://github.com/hankinsoft/SQLPro/issues/990#issuecomment-2156443730, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABCXQG35URUM6L6JUZ4OFI3ZGQY25AVCNFSM6AAAAABI66R6KKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNJWGQ2DGNZTGA. You are receiving this because you authored the thread.

hankinsoft commented 3 months ago

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

hankinsoft commented 3 months ago

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 commented 3 months ago

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 https://github.com/hankinsoft/SQLPro/issues/990#issuecomment-2158671250, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABCXQG7GFEAH5PMDWR5PQCLZGXBK7AVCNFSM6AAAAABI66R6KKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNJYGY3TCMRVGA. You are receiving this because you authored the thread.

hankinsoft commented 3 months ago

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 commented 3 months ago

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 https://github.com/hankinsoft/SQLPro/issues/990#issuecomment-2162802735, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABCXQGZG676P6PXAVFADCHDZHAYGLAVCNFSM6AAAAABI66R6KKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNRSHAYDENZTGU. You are receiving this because you authored the thread.