The "union all" and "limit ? offset ?" cause SQL_FULL exception。

[Dustone-JavaWeb]

Version: 3.34.0 Runtime env: jdk jdk-8u251-linux-arm32-vfp-hflt Cortex-A8 x1 800Mhz 1GB RAM Linux core version 4.14.79 Yocto.

I have some child DB Files , Each of then about 256MB , 50000 lines. In order to get some data from these dbs, I attach rhem with main.db then excute some paging query sql。

-- get the total count
select count(1) from ( select * from data_4.local_data union all  select * from data_5.local_data union all  select * from data_6.local_data union all  select * from data_7.local_data union all  select * from data_8.local_data union all  select * from data_9.local_data union all  select * from data_10.local_data union all  select * from data_11.local_data  )where 1=1  and ts>= ? and ts<?  and key_id= ? 
-- get the page data
select * from ( select * from data_4.local_data union all  select * from data_5.local_data union all  select * from data_6.local_data union all  select * from data_7.local_data union all  select * from data_8.local_data union all  select * from data_9.local_data union all  select * from data_10.local_data union all  select * from data_11.local_data  )where 1=1  and ts>= ? and ts<?  and key_id= ?  limit ? offset ?

I create an http api to excute it. . At first 1000 lines it working well, When I put some query param, It broken with disturb exception stacktrace

org.sqlite.SQLiteException: [SQLITE_FULL]  Insertion failed because database is full (database or disk is full)
        at org.sqlite.core.DB.newSQLException(DB.java:1012)
        at org.sqlite.core.DB.newSQLException(DB.java:1024)
        at org.sqlite.core.DB.execute(DB.java:866)
        at org.sqlite.jdbc3.JDBC3PreparedStatement.execute(JDBC3PreparedStatement.java:54)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:67)
        at io.vertx.ext.jdbc.impl.actions.JDBCQuery.execute(JDBCQuery.java:48)
        at io.vertx.ext.jdbc.impl.actions.JDBCQuery.execute(JDBCQuery.java:30)
        at io.vertx.ext.jdbc.impl.actions.AbstractJDBCAction.handle(AbstractJDBCAction.java:59)
        at io.vertx.ext.jdbc.impl.actions.AbstractJDBCAction.lambda$execute$0(AbstractJDBCAction.java:67)
        at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$2(ContextImpl.java:313)
        at io.vertx.core.impl.TaskQueue.run(TaskQueue.java:76)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
        at java.lang.Thread.run(Thread.java:748)

table construct sql

create table local_data (id integer not null,ts tdatetime not null,key_id text,data_type text,json_data json);
create index data_select_index_1 on local_data (ts);
create index data_select_index_2 on local_data (key_id);
create index data_select_index_3 on local_data (id);

[michael-o]

Does this work from the command line?

[Dustone-JavaWeb]

@michael-o the problen in the command line is bigger

[Dustone-JavaWeb]

@michael-o I tried on my win10 laptop with same data, select 10000 rows, It works well.

[michael-o]

I don't understand why you are constantly showing some REST requests, but a proper issue isolation is to write a standalone, CLI application which depicts the problem.

[gotson]

@Dustone-JavaWeb please provide a minimum reproducible example if you want us to have a look at this, else i will close this.