Android Question select limit 1 in error

[Lucas Siqueira]

#Region Shared Files
#CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files"
'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
#End Region

'Ctrl + click to export as zip: ide://run?File=%B4X%\Zipper.jar&Args=Project.zip

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private SQL1 As SQL
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
        
    SQL1.Initialize(File.DirInternal, "MyDb.db", True)
    
    Button1_Click
End Sub

Private Sub Button1_Click   
    
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS client")
    
    Log("create table")
    SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS client (id INTEGER PRIMARY KEY AUTOINCREMENT)")
    
    Log("select before creating columns")
    Dim rs As ResultSet = SQL1.ExecQuery("SELECT * FROM client LIMIT 1")
    Do While rs.NextRow
        LogColor(rs.ColumnCount, xui.Color_Green)
    Loop
    
    Log("create column in table")
    SQL1.ExecNonQuery("ALTER TABLE client ADD COLUMN name TEXT DEFAULT ''")
    SQL1.ExecNonQuery("ALTER TABLE client ADD COLUMN age TEXT DEFAULT ''")
    SQL1.ExecNonQuery("ALTER TABLE client ADD COLUMN country TEXT DEFAULT ''")
    
    Log("insert")
    SQL1.ExecNonQuery2("INSERT INTO client VALUES (null, ?, ?, ?)", Array As Object("joe", 22, "brazil"))
    
    Log("1 - select after creating the columns")
    Dim rs As ResultSet = SQL1.ExecQuery("SELECT * FROM client LIMIT 1")
    Do While rs.NextRow
        LogColor("rs.ColumnCount: " & rs.ColumnCount, xui.Color_Green)
    Loop
    
    Log("2 - select after creating the columns")
    Dim rs As ResultSet = SQL1.ExecQuery("SELECT * FROM client LIMIT 1")
    Do While rs.NextRow
        LogColor("rs.ColumnCount: " & rs.ColumnCount, xui.Color_Green)
    Loop
    
End Sub

Logger connected to:  motorola moto e(7) power
--------- beginning of crash
--------- beginning of main
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create (first time) **
create table
select before creating columns
create column in table
insert
1 - select after creating the columns
rs.ColumnCount: 1
2 - select after creating the columns
rs.ColumnCount: 4
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
** Activity (main) Resume **

I'm creating a table with just one field.
After that I do a select to check how many records are in the table and display the number of columns, (as I didn't insert any records in the table, nothing comes up).

then I include 3 new fields in the table.

After the table has 4 fields, I do an insert, everything goes well, without any errors so far.

After the insert, I do a select to bring me all the records in the table, but due to some error it only brings me the id field (which was the field created along with the table).

The strange thing is that I execute the same select twice, the first time only brings me the id field. the second time brings me the 4 fields (id, name, age, parents).

 

[Lucas Siqueira]

if I remove the part that makes the select before adding the other 3 columns.

when the select happens it is correct, it does not generate any error.

It's as if SQLite stayed in memory that only has one column, because it tried to execute the select when it didn't have any records.

 

[Lucas Siqueira]

I understand what caused the error, and I know how to avoid it.

but I want to understand why sqlite was left in memory that only has one field in the table. Does it save the last select to save memory?

 

[Lucas Siqueira]

Simulation in SQLite itself in the same application sequence presents the same error.

 

[Lucas Siqueira]

for anyone who wants to simulate the error in sqlite

DROP TABLE IF EXISTS client;

CREATE TABLE IF NOT EXISTS client (id INTEGER PRIMARY KEY AUTOINCREMENT);
SELECT * FROM client LIMIT 1;

ALTER TABLE client ADD COLUMN name TEXT DEFAULT '';
ALTER TABLE client ADD COLUMN age TEXT DEFAULT '';
ALTER TABLE client ADD COLUMN country TEXT DEFAULT '';
INSERT INTO client VALUES (null, 'joe', 22, 'brazil');
SELECT * FROM client LIMIT 1;

SELECT * FROM client LIMIT 1;

 

[Mahares]

Nice study Lucas. I think the problem is with the SELECT statement here:

Log("select before creating columns")
    Dim rs As ResultSet = SQL1.ExecQuery("SELECT * FROM client LIMIT 1")   'problem

If you use the id in the SELECT statement it will work for you like below and the rest of your code will work fine:

Log("select before creating columns")
Dim rs As ResultSet = SQL1.ExecQuery("SELECT id FROM client LIMIT 1")   'this line works

This will work also:

Log("select before creating columns")
    Dim rs As ResultSet = SQL1.ExecQuery("SELECT rowid, * FROM client  LIMIT 1") 'this line works

Other ways will work: If you remove the LIMIT 1 completely or replace with LIMIT -1, or replace with LIMIT 5, the rest of the code and result will look good.
The reason for this may lie in this link:

https://sqlite.org/lang_select.html#limitoffset

More specifically this statement: Or, if the SELECT statement would return less than N rows without a LIMIT clause, then the entire result set is returned. If this explanation is not adequate, come back.

 

[aeric]

I didn't run the test but I just share my thought.

1. Close a recordset after each query
2. Specify the column names when inserting new record where null value is not required for auto increment id column

 

[Mahares]

1. Close a recordset after each query

No. It does not make a difference Please test before posting.

 

[Lucas Siqueira]

I did several tests, if I specify the column names instead of using *, it works normally.

I think it's a bug in SQLite, I did the simulation in SQLite itself without using b4x. If you look at the .gif of the publication you will see the error.

I think there is something connected with sqlite storing select results to return data faster and have better performance.

 

[aeric]

It is weird, it does not happen in B4J.

if SQLite stayed in memory that only has one column

The issue also happen when the initial table is created with more than one column.

If you remove the LIMIT 1 completely

The issue is still persist if LIMIT 1 is removed or even if LIMIT 10 is used.

Does it save the last select to save memory?

Not the last select but if the query has executed before.
e.g.
Query 1: SELECT * FROM client LIMIT 10
Query 2: SELECT * FROM client LIMIT 20 ' rs.ColumnCount: 4
Query 3: SELECT * FROM client LIMIT 10 ' rs.ColumnCount: 1 <-- it seems results of query 1 is cached

I think it is caused by a feature call page cache

All SQLite database connections running within a single process share a single page cache. The page cache caches data read from database files in main-memory on a per-page basis. When SQLite requires data from a database file to satisfy a database query, it checks the page cache for usable cached versions of the required database pages before loading it from the database file.

Source: https://www.sqlite.org/draft/fileio.html

My suggestion is to use the following query if you want to check for table columns.

PRAGMA table_info(client)

 

[Lucas Siqueira]

It is weird, it does not happen in B4J.


The issue also happen when the initial table is created with more than one column.


The issue is still persist if LIMIT 1 is removed or even if LIMIT 10 is used.


Not the last select but if the query has executed before.
e.g.
Query 1: SELECT * FROM client LIMIT 10
Query 2: SELECT * FROM client LIMIT 20 ' rs.ColumnCount: 4
Query 3: SELECT * FROM client LIMIT 10 ' rs.ColumnCount: 1 <-- it seems results of query 1 is cached

I think it is caused by a feature call page cache

Source: https://www.sqlite.org/draft/fileio.html

My suggestion is to use the following query if you want to check for table columns.

PRAGMA table_info(client)

must be the page cache

 

[Mahares]

must be the page cache

I think it is caused by a feature call page cache

If it is a cache problem, why is it that when I execute this to avoid assigning any cache:

SQL1.ExecNonQuery("PRAGMA cache_size = 0")

It still shows the behavior you had.

 

[Lucas Siqueira]

Indeed, even setting cach_size=0, the error continues, perhaps it is a sqlite bug.

 

[Erel]

Moved to the questions forum. The behavior is 100% on the SQLite engine side.