Android Question select limit 1 in error

Lucas Siqueira

Active Member
Licensed User
Longtime User
B4X:
#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



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





1697824077834.png
 

Attachments

  • erro.zip
    13.8 KB · Views: 42

Lucas Siqueira

Active Member
Licensed User
Longtime User
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.

1697824900011.png
 
Upvote 0

Lucas Siqueira

Active Member
Licensed User
Longtime User
for anyone who wants to simulate the error in sqlite

SQL:
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;
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Nice study Lucas. I think the problem is with the SELECT statement here:
B4X:
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:
B4X:
Log("select before creating columns")
Dim rs As ResultSet = SQL1.ExecQuery("SELECT id FROM client LIMIT 1")   'this line works

This will work also:
B4X:
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:
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.
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
Longtime User
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
 
Upvote 0

Lucas Siqueira

Active Member
Licensed User
Longtime User
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.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
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.
SQL:
PRAGMA table_info(client)
 

Attachments

  • test.zip
    13.4 KB · Views: 39
Last edited:
Upvote 0

Lucas Siqueira

Active Member
Licensed User
Longtime User
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.
SQL:
PRAGMA table_info(client)
must be the page cache
 
Upvote 0
Top