Re: [sqlite] Seg fault using json_each() subquery
Yes, that old version (from about 2 years ago) crashes (on Windows).
No, the current one does not crash and produces two output lines.
This is using your test SQL with the spelling errors fixed.
CREATE TABLE mimmo (key TEXT PRIMARY KEY, value TEXT, valueJson TEXT, blockNo
INTEGER, txnNo INTEGER, metadata TEXT);
INSERT INTO mimmo (key, valueJson) VALUES ('key2',
'{"peppo":[["a","b"],["c","d"]]}');
SELECT s.key FROM mimmo AS s, json_each(json_extract(s.valueJson, '$.peppo'))
AS a WHERE (SELECT COUNT(*) FROM json_each(a.value)) > 0;
SQLite version 3.30.0 2019-07-23 21:48:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE mimmo (key TEXT PRIMARY KEY, value TEXT, valueJson TEXT,
blockNo INTEGER, txnNo INTEGER, metadata TEXT);
sqlite> INSERT INTO mimmo (key, valueJson) VALUES ('key2',
'{"peppo":[["a","b"],["c","d"]]}');
sqlite> SELECT s.key FROM mimmo AS s, json_each(json_extract(s.valueJson,
'$.peppo')) AS a WHERE (SELECT COUNT(*) FROM json_each(a.value)) > 0;
key2
key2
bisect complete
1 GOOD2018-01-27 18:55:18 6ea8ba312c38365d
4 GOOD2018-01-26 22:41:59 7daa687340e47597
7 BAD 2018-01-26 18:59:25 029ebcd30cb261d9 CURRENT
6 BAD 2018-01-26 18:37:34 ace0644a1a2a42a3
5 BAD 2018-01-25 20:50:46 30b9258294e3028e
3 BAD 2018-01-24 18:28:39 090a64faaac579c6
2 BAD 2018-01-23 20:22:15 b58b60b2c0729b73
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Charles Leifer
>Sent: Tuesday, 23 July, 2019 20:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Seg fault using json_each() subquery
>
>Partial traceback (bdb 18.1.32):
>
>Program received signal SIGSEGV, Segmentation fault.
>0x7798e560 in jsonEachColumn () from ./lib/libdb_sql-18.1.so
>(gdb) bt full
>#0 0x7798e560 in jsonEachColumn () from ./lib/libdb_sql-
>18.1.so
>No symbol table info available.
>#1 0x779b29b6 in sqlite3VdbeExec () from ./lib/libdb_sql-
>18.1.so
>No symbol table info available.
>
>Unfortunately this is next-to-useless, but may provide a clue?
>
>On Tue, Jul 23, 2019 at 11:04 AM Carlo Innocenti
>
>wrote:
>
>> I'm using sqlite with BDB as the persistence store; if you can't
>> reproduce it (same sqlite version), I suppose it means it's a
>BerkeleyDB
>> issue, and I'll follow up with them.
>> Thanks!
>>
>> On 7/23/2019 12:01, Richard Hipp wrote:
>> > Unable to reproduce. Are you sure you are sending the correct
>script?
>> > What operating system are you running on? How did you compile
>> > SQLite?
>> >
>> > On 7/23/19, Carlo Innocenti wrote:
>> >> I have a segmentation fault which happens when trying to run a
>subquery
>> >> based on json_each() if the argument is the value of an outer
>> >> json_each() row:
>> >>
>> >> CREATE TABLE mimmo (key TEXT PRIMARY KEY, value TEXT,
>valueJson
>> >> TEXT, blockNo INTEGER, txnNo INTEGER, metadata TEXT);
>> >> INSER INTO mimmo (key, valueJson) VALUES ('key2',
>> > Did you mean to say "INSERT" here, instead of "INSER"?
>> >
>> >> '{"peppo":[["a","b"],["c","d"]]}');
>> >> SELECT s.key FROM mimmo AS s,
>json_each(json_extract(s.valueJson,
>> >> '$.peppo')) AS a WHERE (SELECT COUNT(*) FROM
>json_each(a.value)) >
>> 0;
>> >>
>> >> sqlite> select sqlite_version();
>> >> 3.18.2
>> >>
>> >> Am I doing anything obviously wrong? Or is this a known
>> problem/limitation?
>> >> Thanks!
>> >> Minollo
>> >>
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>> >>
>> >
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seg fault using json_each() subquery
Partial traceback (bdb 18.1.32):
Program received signal SIGSEGV, Segmentation fault.
0x7798e560 in jsonEachColumn () from ./lib/libdb_sql-18.1.so
(gdb) bt full
#0 0x7798e560 in jsonEachColumn () from ./lib/libdb_sql-18.1.so
No symbol table info available.
#1 0x779b29b6 in sqlite3VdbeExec () from ./lib/libdb_sql-18.1.so
No symbol table info available.
Unfortunately this is next-to-useless, but may provide a clue?
On Tue, Jul 23, 2019 at 11:04 AM Carlo Innocenti
wrote:
> I'm using sqlite with BDB as the persistence store; if you can't
> reproduce it (same sqlite version), I suppose it means it's a BerkeleyDB
> issue, and I'll follow up with them.
> Thanks!
>
> On 7/23/2019 12:01, Richard Hipp wrote:
> > Unable to reproduce. Are you sure you are sending the correct script?
> > What operating system are you running on? How did you compile
> > SQLite?
> >
> > On 7/23/19, Carlo Innocenti wrote:
> >> I have a segmentation fault which happens when trying to run a subquery
> >> based on json_each() if the argument is the value of an outer
> >> json_each() row:
> >>
> >> CREATE TABLE mimmo (key TEXT PRIMARY KEY, value TEXT, valueJson
> >> TEXT, blockNo INTEGER, txnNo INTEGER, metadata TEXT);
> >> INSER INTO mimmo (key, valueJson) VALUES ('key2',
> > Did you mean to say "INSERT" here, instead of "INSER"?
> >
> >> '{"peppo":[["a","b"],["c","d"]]}');
> >> SELECT s.key FROM mimmo AS s, json_each(json_extract(s.valueJson,
> >> '$.peppo')) AS a WHERE (SELECT COUNT(*) FROM json_each(a.value)) >
> 0;
> >>
> >> sqlite> select sqlite_version();
> >> 3.18.2
> >>
> >> Am I doing anything obviously wrong? Or is this a known
> problem/limitation?
> >> Thanks!
> >> Minollo
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Estimated Costs and Memory DBs
While looking through the SQLite3 source trying to find answers to some questions I had about virtual tables, I noticed that the memory DB is implemented as a VFS rather than a database. Is my understanding correct that this means that the estimated cost the query planner uses for memory tables will be equal to that of the same database on disk? Shouldn't memory DBs always have a much lower cost to cause the query planner to prefer intensive operations on memory DBs rather than disk DBs? I know there's a field in the table definition for cost multiplier that could perhaps be used for this purpose, but as far as I can tell this is only ever used by ANALYZE and it's theoretically impossible that memory DBs could even use it. As for the question I was originally looking for an answer to, I am writing a virtual table for a different file format, and it is expected that my virtual table will be held completely in memory. What should I do with the estimatedCost value from xBestIndex? According to the documentation this should be an approximation of the number of disk accesses for the query, which would be 0 in this case. But it's clearly vastly faster to do a query on an indexed column, meaning the cost for an indexed column should be much lower than the cost for an unindexed column. How should I be doing this? -- Justin Olbrantz (Quantam) "Ardente veritate Urite mala mundi Ardente veritate Incendite tenebras mundi" ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unable to use date fields in sqlite
Note https://www.sqlite.org/datatype3.html says: "Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions." This is partially true. The date and time functions will optionally parse 'a timezone indicator of the form "[+-]HH:MM" or just "Z"'. The same functions will not create this optional form. i.e. strftime is missing '%z' as a conversion specifier. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seg fault using json_each() subquery
I'm using sqlite with BDB as the persistence store; if you can't
reproduce it (same sqlite version), I suppose it means it's a BerkeleyDB
issue, and I'll follow up with them.
Thanks!
On 7/23/2019 12:01, Richard Hipp wrote:
Unable to reproduce. Are you sure you are sending the correct script?
What operating system are you running on? How did you compile
SQLite?
On 7/23/19, Carlo Innocenti wrote:
I have a segmentation fault which happens when trying to run a subquery
based on json_each() if the argument is the value of an outer
json_each() row:
CREATE TABLE mimmo (key TEXT PRIMARY KEY, value TEXT, valueJson
TEXT, blockNo INTEGER, txnNo INTEGER, metadata TEXT);
INSER INTO mimmo (key, valueJson) VALUES ('key2',
Did you mean to say "INSERT" here, instead of "INSER"?
'{"peppo":[["a","b"],["c","d"]]}');
SELECT s.key FROM mimmo AS s, json_each(json_extract(s.valueJson,
'$.peppo')) AS a WHERE (SELECT COUNT(*) FROM json_each(a.value)) > 0;
sqlite> select sqlite_version();
3.18.2
Am I doing anything obviously wrong? Or is this a known problem/limitation?
Thanks!
Minollo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seg fault using json_each() subquery
Unable to reproduce. Are you sure you are sending the correct script?
What operating system are you running on? How did you compile
SQLite?
On 7/23/19, Carlo Innocenti wrote:
> I have a segmentation fault which happens when trying to run a subquery
> based on json_each() if the argument is the value of an outer
> json_each() row:
>
> CREATE TABLE mimmo (key TEXT PRIMARY KEY, value TEXT, valueJson
> TEXT, blockNo INTEGER, txnNo INTEGER, metadata TEXT);
> INSER INTO mimmo (key, valueJson) VALUES ('key2',
Did you mean to say "INSERT" here, instead of "INSER"?
> '{"peppo":[["a","b"],["c","d"]]}');
> SELECT s.key FROM mimmo AS s, json_each(json_extract(s.valueJson,
> '$.peppo')) AS a WHERE (SELECT COUNT(*) FROM json_each(a.value)) > 0;
>
> sqlite> select sqlite_version();
> 3.18.2
>
> Am I doing anything obviously wrong? Or is this a known problem/limitation?
> Thanks!
> Minollo
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Seg fault using json_each() subquery
I have a segmentation fault which happens when trying to run a subquery
based on json_each() if the argument is the value of an outer
json_each() row:
CREATE TABLE mimmo (key TEXT PRIMARY KEY, value TEXT, valueJson
TEXT, blockNo INTEGER, txnNo INTEGER, metadata TEXT);
INSER INTO mimmo (key, valueJson) VALUES ('key2',
'{"peppo":[["a","b"],["c","d"]]}');
SELECT s.key FROM mimmo AS s, json_each(json_extract(s.valueJson,
'$.peppo')) AS a WHERE (SELECT COUNT(*) FROM json_each(a.value)) > 0;
sqlite> select sqlite_version();
3.18.2
Am I doing anything obviously wrong? Or is this a known problem/limitation?
Thanks!
Minollo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unable to use date fields in sqlite
On Tue Jul 23, 2019 at 04:41:59PM +0200, Andreas Kretzer wrote: > Am 23.07.2019 um 16:22 schrieb Steve Leonard: > > > > 4)I have tried several combinations of creating a new field > > of type numeric, blob, real, and integer and then tried to update If you want to work with date *strings* of the form '-mm-dd' you probably want to use TEXT. > > 3) I want to select all records where myfdt is between 11/05/2011 and > > 12/30/2011 have had no success > > Could someone please post the correct sql statement to do this? > > > > queries like this give no results: > > select * from mytable > > where myfdt > '11/06/2011' > > and myfdt < '04/13/2018' If you have modified your data to be -mm-dd then your queries should also be in that format: SELECT * FROM mytable WHERE myfdt > '2011-11-06' AND myfdt < '2018-04-13' -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unable to use date fields in sqlite
Hi Steve, beside what I said in my last statement, you should also have a look at https://www.sqlite.org/datatype3.html https://www.sqlite.org/lang_datefunc.html This may help you to understand, how SQLite handles specific data types, that might be understood (or at least be handled) different than in other DB engines. Andreas Am 23.07.2019 um 16:22 schrieb Steve Leonard: > > 1) I imported a new table using dbbrowser for sqlite from a csv file > one of the fields is named myfdat and the first record contains > '12/01/2011' > > 2) the import created a table with the field myfdt as type text and > the value matches the csv file > > 3) I want to select all records where myfdt is between 11/05/2011 and > 12/30/2011 have had no success > Could someone please post the correct sql statement to do this? > > queries like this give no results: > select * from mytable > where myfdt > '11/06/2011' > and myfdt < '04/13/2018' > > 4)I have tried several combinations of creating a new field > of type numeric, blob, real, and integer and then tried to update > this new field using several date functions, among them: > update mytable > set myfdt = > substr(myfdt, 7) || "-" || substr(myfdt,4,2) || "-" || substr(myfdt, > 1,2) > the sql ran ok, but the data in the new field is in the -dd-mm format > > depending on which combination I used, I get either no results > or I get wrong answers because a date like 12/04/2011 is > treat like it is April 12 instead of December 4th. > > Thank you. > Steve > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unable to use date fields in sqlite
Am 23.07.2019 um 16:22 schrieb Steve Leonard: > > 1) I imported a new table using dbbrowser for sqlite from a csv file > one of the fields is named myfdat and the first record contains > '12/01/2011' > > 2) the import created a table with the field myfdt as type text and > the value matches the csv file > > 3) I want to select all records where myfdt is between 11/05/2011 and > 12/30/2011 have had no success > Could someone please post the correct sql statement to do this? > > queries like this give no results: > select * from mytable > where myfdt > '11/06/2011' > and myfdt < '04/13/2018' > > 4)I have tried several combinations of creating a new field > of type numeric, blob, real, and integer and then tried to update > this new field using several date functions, among them: > update mytable > set myfdt = > substr(myfdt, 7) || "-" || substr(myfdt,4,2) || "-" || substr(myfdt, > 1,2) > the sql ran ok, but the data in the new field is in the -dd-mm format You are basically on a "good" way - just swap day and month field (-mm-dd) and you're done. This is still a string though ... update mytable set myfdt = substr(myfdt, 7) || "-" || substr(myfdt,1,2) || "-" || substr(myfdt, 4,2) > > depending on which combination I used, I get either no results > or I get wrong answers because a date like 12/04/2011 is > treat like it is April 12 instead of December 4th. If you want to deal with dates like this, you must "order" the parts of that string in descending order! Andreas > > Thank you. > Steve > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] unable to use date fields in sqlite
1) I imported a new table using dbbrowser for sqlite from a csv file one of the fields is named myfdat and the first record contains '12/01/2011' 2) the import created a table with the field myfdt as type text and the value matches the csv file 3) I want to select all records where myfdt is between 11/05/2011 and 12/30/2011 have had no success Could someone please post the correct sql statement to do this? queries like this give no results: select * from mytable where myfdt > '11/06/2011' and myfdt < '04/13/2018' 4)I have tried several combinations of creating a new field of type numeric, blob, real, and integer and then tried to update this new field using several date functions, among them: update mytable set myfdt = substr(myfdt, 7) || "-" || substr(myfdt,4,2) || "-" || substr(myfdt, 1,2) the sql ran ok, but the data in the new field is in the -dd-mm format depending on which combination I used, I get either no results or I get wrong answers because a date like 12/04/2011 is treat like it is April 12 instead of December 4th. Thank you. Steve ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE with Kotlin
Hi Guys I want to write an application in kotlin which uses SQLITE. Is there already a good Kotlin package which provides SQLITE services (I know that there is one for Android development, but I’m not developing on top of Android) Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
