Tiemogo, Idrissa wrote:
When I derive a table from another table containing blob type.
The describing the new table doesn’t show “blob.
sqlite create table t1 (p_id int, geometry blob);
sqlite pragma table_info(t1);
0|p_id|int|0||0
1|geometry|blob|0||0
sqlite create table t2 as select *
Janke, Julian wrote:
In my opinion, this means, we must only write a VFS implementation for
our target platform.
What file API is there?
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
James K. Lowden wrote:
1. Last I checked, SELECT in a column position in the SELECT clause as
in
select foo (select ...)
is not permitted by the SQL standard.
This example indeed is not valid SQL syntax.
However, SELECT in a column position is allowed:
select (select 42);
This
jitendar kumar wrote:
where is the temporary files location ??
1. temp_directory, if set
2. SQLITE_TMPDR, if set
3. TMPDIR, if set
4. /var/tmp
5. /usr/tmp
6. /tmp
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
Jim Wilcoxson wrote:
If you have a table where rows are inserted but never deleted, and you
have a rowid column, you can use this:
select seq from sqlite_sequence where name = 'tablename'
This works only for an AUTOINCREMENT column.
This will return instantly, without scanning any rows or
Emmanouil Karvounis wrote:
In short, we have two tables that are already sorted on a combination of
two fields (which are their primary keys) and we want to union them and
apply group by on both the aforementioned fields, like so:
select c1, c2, myagg(*) from (
select * from tableA
union
Nicolas Jäger wrote:
do
{
rc = sqlite3_step(stmt);
std::cout sqlite3_column_text (stmt, 0) , sqlite3_column_text
(stmt, 2) std::endl;
} while(rc == SQLITE_ROW);
sqlite3_step() returns SQLITE_ROW when there is a row, or SQLITE_DONE
when there are no more rows, or
Richard Hipp wrote:
So if you say group_concat(DISTINCT x,y) does that mean that the
combination of x and y must be distinct or that only x is distinct?
Are we allowed to say group_concat(x, DISTINCT y) or
group_concat(DISTINCT x, DISTINCT y). And what does the latter
mean, exactly?
Are
Dominique Devienne wrote:
On Mon, Jan 12, 2015 at 4:13 PM, Igor Tandetnik i...@tandetnik.org wrote:
On 1/12/2015 9:53 AM, Dominique Devienne wrote:
My little brain has no idea how the a;b:c/c,d came about from the input
rows, so I don't find it logical at all myself...
Simple, really. For
Staffan Tylen wrote:
Well, the SELECT is actually over 400 lines long [...] I
can't use SELECT DISTINCT X as that wouldn't give the result I want, and I
can't use SELECT DISTINCT 'ABC' either. So my Yes, it might work comment
doesn't actually hold. I see no other way than to use DISTINCT with
Max Vasilyev wrote:
http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
and want to use WHERE, but what if 'title' is not unique?
If the ORDER BY columns are not unique, you cannot know which
rows to display on which page. You must be able to uniquely
identify rows.
- This is considered
Bite Forest wrote:
Which version of sqlite can I compile through vs in c++ code?
None.
You have to compile SQLite as C, not C++.
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
J Decker wrote:
is this... 2015-01-02 20:47:18 (this is datetime( 'now', '-3600 second' )
received = 2015-01-02 13:46:23.818-0800 this is a DATETIME column recorded
in the database
SQLite has no DATETIME datatype. This is just a string.
select * from messages where received datetime(
Hick Gunter wrote:
create the primary key index ordered properly
CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...);
DESC is not necessary here; SQLite has no problem reading the index
in reverse order, if needed.
(DESC in an index is useful only when you want to optimize multiple
ORDER BYs
Roland Martin wrote:
I have a need to filter the result set before aggregate functions are
performed.
Use the WHERE clause.
Unfortunately I cannot query the security system to find out all valid
values and add these values to the where clause. I have to give a value to
the security system
Ed Willis wrote:
We were on a version which did not have the compile option
SQLITE_FTS3_MAX_EXPR_DEPTH and are moving up to one that does. As it
turns out we have run into a problem with one of our clients where
they hit this limit now where previously the query just worked.
Have a look at
Nick wrote:
On 11 Dec 2014, at 20:39, David King wrote:
Why are you trying to hard to avoid using the backup API? It sounds
like it does exactly what you want
Backup API works great if you have periods of no writing.
However, if a process writes during the backup then the API would stop
and
Simon Slavin wrote:
On 12 Dec 2014, at 10:27am, Clemens Ladisch clem...@ladisch.de wrote:
If you write your own backup tool that simply calls
sqlite3_backup_step(b, -1), the entire database is copied in
a single atomic transaction.
OP's problem is that he runs several processes which
Lukas wrote:
PRAGMA foreign_keys = ON;
create table a ( id int primary key );
create table b ( id int primary key );
create table c ( id int primary key,
aid int,
bid int,
foreign key (aid) references a (id) on delete cascade,
Shinichiro Yoshioka wrote:
I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++.
But although the compiling was successfully finished, even if I set break
point
on the source code, I can't trace the working line in sqlite3.c correctly.
How exactly are you using SQLite in your
Scott Robison wrote:
On Dec 3, 2014 12:57 AM, Clemens Ladisch clem...@ladisch.de wrote:
Do you have a standard that allows NULL? The one I quoted does not.
Note: I'll have to double check my copy of the C90 standard document, but
my re-reading of the C99 quote leads me to the conclusion
Jonathan Leslie wrote:
I'm at a cmd.exe prompt.
sqlite INSERT INTO Files (name,contents) VALUES ('tsql.lis',X$(od -A n -t
x1 tsql.lis|tr -d '\r\n\t '));
Error: near $(od -A n -t x1 tsql.lis|tr -d '\r\n\t '): syntax error
I'm trying to store the file tsql.lis as a blob, and od.exe and
Richard Hipp wrote:
But apparently there is an issue in USBAN in that it does not allow calls
to memcpy() and memset() with NULL pointers even it the count field (the
third parameter) is zero. I couldn't find anything in the memcpy() or
memset() documentation that disallowed this case.
C99,
James K. Lowden wrote:
/* Copy N bytes of SRC to DEST. */
extern void *memcpy (void *__restrict __dest,
__const void *__restrict __src, size_t __n)
__THROW __nonnull ((1, 2));
IIUC the declaration specifies the pointer cannot be NULL and the
compiler generates a
Staffan Tylen wrote:
I've just found out that the column data returned by PRAGMA table_info does
not include columns added using ALTER COLUMN ADD COLUMN.
sqlite create table t(x);
sqlite pragma table_info(t);
0|x||0||0
sqlite alter table t add y;
sqlite pragma table_info(t);
0|x||0||0
1|y||0||0
Thane Michael wrote:
I've been searching for a way to serialize an object's vector using sqlite3
but are yet find a working solution.
The question is how to model the vector in the database.
In most cases, you should normalize your tables:
class A
{
int id;
double
Yongil Jang wrote:
It is a normal work but I just want to notify that some of automatic index
log message is not easy to recognize which query made this log output.
SQLite just delivers the log message to the application; it is the
application's job to relate it to whatever it is actually
Oliver Smith wrote:
On Sun, Nov 16, 2014 at 2:18 PM, Oliver Smitholi...@kfs.org wrote:
...
CREATE TABLE t2c (id INTEGER, name text, t2_id INTEGER, UNIQUE (t2_id,
name));
EXPLAIN QUERY PLAN
SELECT t1c.t1_id, t1c.id, t2c.t2_id, t2c.id
FROM t1c,
t2 INNER JOIN
bjdodo wrote:
Sorry for resurrecting an old thread. I got the where clause working for
queries with byte array arguments based on this discussion. I cannot find
the way to use byte arrays in where clauses for update and delete
statements.
In the Android database API, execSQL() is the only
Dan Kennedy wrote:
Perhaps not as functional as a native app (so far), but looking really good!
And you can't beat the deployment.
Well, it does not replace SQL Fiddle.
I understand the desire to avoid storing data on the web server, but it would
be nice if the initial schema/query could be
Koen Van Exem wrote:
Is it a bug or feature that the autoincrement
value is being reused when a rollback is issued?
The documentation on https://www.sqlite.org/autoinc.html is a bit unclear
... it says it prevents reuse of ROWIDs from previously deleted rows.
Only a DELETE statement results
Daniel Polski wrote:
this maybe has to do with me using WAL mode, and that the update is
not yet processed enough for the other threads to fetch the new
data (so they still selects the old data), even though the trigger is
set to fire after update. Is this maybe the case?
Yes; other
RP McMurphy wrote:
Is there a way we can make the w index work with both queries and not
have to run external loops to flatten all the WHERE clauses?
http://www.sqlite.org/lang_analyze.html
sqlite .timer on
sqlite select count(*) from v wherez = 0 and
... (
dylan666 wrote:
update Table1
set Visibility=1
where ConsumerID in (select * from
Table1 join Table2
on Table1.ConsumerID = Table2.id
where Table1.visibility = 0 and Table2.visibility = 1)
Unfortunately I get this error:
only a single result allowed for a SELECT that is part of an
Hi,
the following query fails to parse, although it should be valid:
sqlite select 1 union select 1 from (select 1 as x) group by x order by 1
collate binary;
Error: no such column: x
originally reported by Michael Geier here:
RSmith wrote:
On 2014/11/08 14:21, Clemens Ladisch wrote:
the following query fails to parse, although it should be valid:
sqlite select 1 union select 1 from (select 1 as x) group by x order by
1 collate binary;
Error: no such column: x
I don't see how that can ever be valid
Yves Crespin wrote:
if we add a SQLITE_BUSY handle, can we use the sqlite3 .backup when
[some] application is running?
Is it safe or is there a risk to corrumpt the database or do we need
to change some settings ?
Using the backup API is perfectly safe for your data.
The only risk is that
Michele Pradella wrote:
I have a question about data type BIGINT
BIGINT is not a data type.
from docs (http://www.sqlite.org/datatype3.html)
This page says the data types are NULL, INTEGER, REAL, TEXT, and BLOB.
I understand that INTEGER and BIGINT results in the same affinity
Yes.
so
Baruch Burstein wrote:
SELECT col1 FROM table1 WHERE col2=:val
col2 is a textual string, and may sometimes be NULL. If I bind a string to
:val it works fine. If I bind a null-pointer the comparison fails since it
should be using ISNULL. Is there a way to do this correctly with a single
vita...@yourcmc.ru wrote:
SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE
p.login_name='vita...@yourcmc.ru'
Query plan:
SCAN TABLE bugs AS b
SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?)
Which is of course very slow.
Maybe you'll fix it in
Paul wrote:
Are additional indices, created for WITHOUT ROWID, potentially less
efficient and more cumbersome?
For tables with a rowid, the index stores the indexed columns and the
rowid. For WITHOUT ROWID tables, the index stores the indexed columns
and the primary key.
Regards,
Clemens
Rob Willett wrote:
I’d like to produce an output table based on the above data set that looks a
bit like this.
Disruption_id | Start Time | End Time
1 | 1 | 4
2 | 1 | 5
3 | 1 | 2
4 | 2 | 4
Use
Baruch Burstein wrote:
If I have an index on table1(colA, colB), will it be used for both the
where and the order by in either of these cases:
select * from table1 where colA=1 order by colB;
select * from table1 where colB=1 order by colA;
$ sqlite3
sqlite create table table1(colA, colB,
James Earl wrote:
After upgrading from SQLite 3.8.6 to 3.8.7, I am no longer able to
order group_concat values by using a subselect. For example the
following query with 3.8.6 will give me an ordered string of
items.image values based on items.position (which contains integers):
SELECT
Tristan Van Berkom wrote:
locateFKeyIndex() function issuing the not-so-informative
message foreign key mismatch [...]
o When foreign keys are enabled at CREATE TABLE time, it would
be very helpful at this point to issue an error if a foreign
key is declared which refers to a
supermariobros wrote:
Well, they all give exactly the same output.
sqlite EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE
activity_text_content MATCH 'x' ORDER BY rowid ASC LIMIT 100;
0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4:ASC (~0 rows)
sqlite
Charles Samuels wrote:
On Saturday, October 25, 2014 05:31:35 AM Richard Hipp wrote:
However, when updating a row, SQLite rewrites the entire row.
Does this still apply if the column was added due to alter table X add
column? I ask because it was my understanding that alter table added the
J Decker wrote:
On Sun, Oct 26, 2014 at 10:32 AM, Clemens Ladisch wrote:
Charles Samuels wrote:
it was my understanding that alter table added the extra column elsewhere.
It adds the extra column nowhere. When SQLite reads a table row has
fewer columns than in the CREATE TABLE statement
supermariobros wrote:
EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE
activity_text_content MATCH 'x' AND rowid1000 ORDER BY rowid ASC LIMIT 10;
0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4: (~0 rows)
If I understand it correctly it uses indexes properly on
supermariobros wrote:
If I am using FTQ that looks like this
SELECT * FROM mail WHERE body MATCH 'sqlite'
can I add to it WHERE rowid 5 AND rwoid 10
or it will significantly slow it down.
How much did it slow down when you tested it?
Anyway,
without index:
sqlite EXPLAIN QUERY PLAN
Max Vlasov wrote:
my static linking with Delphi for 3.7.8 version now complains about
_beginthreadex_/_endthreadex_.
Quick search shows than everywhere there's a recommendation to use these
functions instead of API CreateThread if one plans to use Visual c run-time
(msvcrt).
Well, by
Roy Sigurd Karlsbakk wrote:
Trying to use strftime() to extract current Year-Month seems to go nuts.
sqlite select strftime('%s', 'now');
1413536061
sqlite select strftime('%Y-%m', strftime('%s', 'now'));
3865-46
SQLite interprets a number as a Julian day number. To have it
interpreted as
Paul Sanderson wrote:
1|0|texas
2|1|new york
3|2|washington
4|0|tampa
5|0|atlanta
6|5|charleston
I'd like to add a break between groups in the results so it looks somethng
like
1|0|texas
2|1|new york
3|2|washington
4|0|tampa
5|0|atlanta
6|5|charleston
SELECT CASE WHEN previd =
Ross Altman wrote:
I need to host a fixed, unchanging database online,
An SQLite database is just a file.
and I want to make sure that anyone who downloads it cannot add to it.
It's possible to run sqlite3 thedata.db .dump thedata.sql and then
to remove any protection that is still present
RSmith wrote:
On 2014/10/14 13:09, Clemens Ladisch wrote:
SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, *
FROM (SELECT ...);
This solution from Clemens will work perfectly, and depending on the
kind of OS you use and output method it might even work to add
something
Paul Sanderson wrote:
I have a table with an integer value which is a bitmask.
0c01 readonly
0x02 hidden
0x04 system
0x10 directory
0x20 archive
I'd like to create a query which would take an attribute, say 0x07 and spit
out system, hidden, readonly
SELECT substr(CASE WHEN attr 32 THEN
Paul Sanderson wrote:
(CASE visits.transition 0xFF00 WHEN 0x0080 THEN 'Blocked'
ELSE '' END ||
CASE visits.transition 0xFF00 WHEN 0x0100 THEN 'Forward_Back'
ELSE '' END ||
...
The query seems to work OK if a single bit is set, but fails (a blank string
Jeffrey Parker wrote:
I am working with sqlite3 in python 2.7.8 and I am running into a strange
error where I get the below exception when running an insert into statement
on an empty table.
The following code executes fine in Python 2.7.5:
import sqlite3
conn=sqlite3.connect(:memory:)
john soprych wrote:
Is there a good way to create functions in sqlite where you can insure that
your function callback will be called in order?
No.
Your best bet is writing the query in such a way that the function is
applied to an already ordered sequence:
SELECT myfunc(x) FROM (SELECT x
Drago, William @ MWG - NARDAEAST wrote:
An automatic test system that I designed generates 25 data elements
for each unit tested. [...]
should I lump everything together in one table just like the .csv file
or should I create several smaller tables that group similar parameters?
I'm not sure
Peter Haworth wrote:
I'm a great believer in using CHECK constraints to do as much validation as
possible within the database rather than code it in my application.
However, I think I'm right in saying that as soon as a CHECK constraint
fails, an error is returned to my application so no
John wrote:
On 5/10/2014 19:59, Clemens Ladisch wrote:
The documentation http://www.sqlite.org/lang_datefunc.html says:
| These functions only work for dates between -01-01 00:00:00 and
| -12-31 23:59:59. For dates outside that range, the results of
| these functions are undefined
Mayank Kumar (mayankum) wrote:
will the memory used by sqlite keep on increasing, if we don't
finalize at all during the life time of the db, as we do more insert/
delete/replace operations ?
Every prepared statement needs some memory (but only a small amount).
As long as you are using these
Prakash Premkumar wrote:
On Fri, Oct 3, 2014 at 7:37 PM, Kees Nuyt k.n...@zonnet.nl wrote:
On Fri, 3 Oct 2014 18:39:29 +0530, Prakash Premkumar
prakash.p...@gmail.com wrote:
Can you please tell me which function is
sqlite actually generates the Vdbe
program for a give sql string ?
Paul Quinn wrote:
In 3.7, issuing PRAGMA index_list(tablename) to retrieve the details
of an index, sqlite3_column_count() would return 0 for automatic
integer primary key indexes, basically indicating there was no
explicit index created for that tablename. But in 3.8,
sqlite3_column_count()
Andy Bradford wrote:
sqlite SELECT strftime('%Y-%m-%d %H:%M:%S',1);
-471-11-25 12:00:00
Is this perhaps undefined behavior because it does say %Y has a range of
--?
Yes. (strftime always outputs the year with four characters, whatever
they might be.)
Regards,
Clemens
James K. Lowden wrote:
RSmith rsm...@rsweb.co.za wrote:
ID | Next | Data
1 | 4 | 'First Row'
2 | 3 | 'Eventual Fourth Row'
3 | 1 | 'Last Row'
4 | 5 | 'New Second Row'
5 | 2 | 'New Third Row'
The first question I'd have is: Where are the ordering criteria, and
Richard Hipp wrote:
Note that the use of AUTOINCREMENT has nothing to do with your
problem - I just see people using it a lot and I'm wondering why
it is so popular
MySQL needs it. Every search for autoincrement will find it. This
keyword's name appears to imply that you do _not_ get
RSmith wrote:
the time of finishing does not determine position alone, there are
bonuses and penalties which doesn't stack up to integer values, but is
measurable [...] Without going into too much detail about how bonuses
and penalties are calculated
How can anybody help you without these
Prakash Premkumar wrote:
Let's say I have tables T1,T2 and T3 with 2 columns each and I am joining
them.
The result rows will have 8 columns each.
No. The result will have between 4 and 6 columns, depending on how you
do the joins.
Example:
CREATE TABLE T1(ID1, Name);
INSERT INTO T1
RSmith wrote:
how can I accurately establish how many total-then-divide-by-2's a set
of co-values in 64-bit FP guise can withstand before the difference is
too small to make sense to the sorter in SQLite?
Internally, SQLite uses 64-bit IEEE floating-point numbers, which is the
same as double
RSmith wrote:
I have one program that inserts values to a table and determine sort
order using one standard trick that has a REAL column named
SortOrder [...]
reassign SortOrders simply in Integer steps: 1, 2, 3 etc.
ID | SortOrder | Data
1 | 1 | 'First Row'
2 | 4 |
RSmith wrote:
Clemens I'm liking the link list but did not go with it due to an expensive
insert function
Yes, you have to update two references (with prev/next), but how is that
worse than the update of all SortOrder values?
how would I get a normal SQL query ORDER BY clause to use that?
RSmith wrote:
On 2014/09/24 22:24, Clemens Ladisch wrote:
RSmith wrote:
I'm liking the link list but did not go with it due to an expensive insert
function
Yes, you have to update two references (with prev/next), but how is that
worse than the update of all SortOrder values?
Well
Paul wrote:
pragma user_version;
returns a single row with a single value which is the version, and the
command,
pragma user_version=n;
lets you change it to n. Perhaps you can use this as a flag to tell yourself
that you are working with an uninitialized database (value is 0), that
Prakash Premkumar wrote:
Can you please tell me where is the definition of the struct sqlite3_stmt ?
There is no definition of struct sqlite3_stmt.
Search for this instead:
/*
** An instance of the virtual machine. This structure contains the complete
** state of the virtual machine.
Paul wrote:
I can check whether user_version matches magic number without transaction.
No. Executing PRAGMA user_version will start an automatic transaction
if you didn't already start an explicit one.
Only when user_version does not match magic number I start transaction.
This will never be
Paul wrote:
Please note that *all* accesses to the database file are done with
transactions, including reading and writing the user_version value.
I suspect that no, not all accesses to the database file are done using
transactions.
Read-only transactions just lock the database file. (But
Yuanzhong Xu wrote:
There is a related issue:
If you use this valid efficient query as a subquery of SELECT id FROM (...),
i.e.,
SELECT id FROM (SELECT id,data FROM (SELECT * FROM t1 UNION ALL SELECT
* FROM t2) WHERE id=10 ORDER BY data);
SQLite reports error:
Error: 1st ORDER BY term
Paul wrote:
My goal is to make structure initialization of an *abstract* database atomic.
[...] if database file is missing it must be created and initialized.
http://www.sqlite.org/transactional.html
Just do the check for the database structure and the initialization inside
a transaction.
Paul wrote:
Paul wrote:
My goal is to make structure initialization of an *abstract* database
atomic.
[...] if database file is missing it must be created and initialized.
http://www.sqlite.org/transactional.html
Just do the check for the database structure and the initialization inside
Paul wrote:
Paul wrote:
How do you check if structure is initializad in an abstract databse?
struct SqliteDatabase {
...
/// Callback is called once database is created. Strictly one time.
virtual bool on_create();
...
};
struct FooDatabase : public SqliteDatabase
{
bool
Jean-Christophe Deschamps wrote:
There is another good reason why raising an exception would be
a terrible choice. When SQLite is used as a shared library by some
scripting language, there is /*no*/ possibility to trap exceptions
raised within the library.
What the SQL standard calls an
jose isaias cabrera wrote:
I know that the IN clause contains a list of something. I.e.
IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05')
So the question is, is there a shorter way for one to say something like,
IN ('2014-01-01', ..., '2014-01-05')
where the
Nao Shoji wrote:
SELECT * FROM my_table WHERE ... word MATCH ' OR OR OR ... '
FTS expression tree is too large (maximum depth 12)
This means that there are too many (more than 2^12) search words.
You could split the word list so that you have multiple queries.
(You might be
Joseph L. Casale wrote:
SELECT x.id, x.col
FROM table_a x
EXCEPT
SELECT y.id, y.col
FROM table_b y
JOIN table_a .
This query is not complete, but as far as I can tell, it is intended to
return table_a rows that do not have a matching table_b row. Is this
correct?
now I need
Joe Mucchiello wrote:
if VS13 is a supported compiler. Then whatever it reports as an error
should be fixed.
This is not an error but a warning.
You have set a compiler option to treat all warnings as errors.
Remove it.
It does not hurt to initialize the variable since I'm sure those other
sanjeev wrote:
sqlite select sdata ,timestamp from tbl_sensor where nid=4 and timestamp
=date('now','-4 days') order by timestamp ASC ;0|2014-08-28
04:00:320|2014-08-28 04:56:420|2014-08-28 04:57:410|2014-08-28
04:58:410|2014-08-28 04:59:410|2014-08-28 05:22:410|2014-08-28
[...]
This is
Simon Slavin wrote:
you're free to consider this a peculiarity of the ADO driver.
It's a peculiarity of SQLite itself (introduced in 5526e0aa3c).
It might be easily fixable. But it's not a bug.
The comment Dequote column names generated by the query flattener
shows that combined table/column
Milan Kříž wrote:
3) A query which should use a linear scan according to the SQLite
documentation (http://www.sqlite.org/fts3.html#section_1_4)
SELECT docId FROM ftsTable WHERE docId BETWEEN 20 AND 23
- gets a following query plan:
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 393216:
-
Milan Kříž wrote:
Clemens Ladisch wrote:
INDEX 1 is the full-text search.
Sorry, that's wrong.
The idxNum value is determined as follows: (see fts3Int.h)
/*
** The Fts3Cursor.eSearch member is always set to one of the following.
** Actualy, Fts3Cursor.eSearch can be greater than or equal
Milan Kříž wrote:
So does it mean that the full-text search is not performed for the following
query at all?
SELECT docId FROM ftsTable WHERE ftsTable MATCH 'a*' AND rowId IN (20,21, 22,
23)
SCAN TABLE ftsTable VIRTUAL TABLE INDEX 1:
EXECUTE LIST SUBQUERY 1
No, it means that you
Adam Devita wrote:
select id, category_id, name, min(price) as minprice
from cat_pictures
group by category_id;
I'd be reluctant to write that query because it is non standard SQL and I
can't easily (5 minutes of searching) point at a document that tells me the
expected behavior.
The
Milan Kříž wrote:
Dne 28.8.2014 14:54, Clemens Ladisch napsal(a):
Milan Kříž wrote:
So does it mean that the full-text search is not performed for the
following query at all?
No, it means that you are using a different version.
But I still cannot understand that query plan.
Then try
Joe Mistachkin wrote:
I'm unable to replicate this issue
Your query did not involve the query flattener. Try this:
select t.x from (select x from (select 1 x)) t;
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
skywind mailing lists wrote:
I hoped that somebody already tried to implement a nearest neighbor
algorithm.
Typically, objects are not axis-aligned rectangles, and the R-tree is
just an index based on the bounding boxes. Computing the (nearest)
distance would require the actual geometries.
Mario M. Westphal wrote:
The new version is 10 or more times slower than the previous build I used
(3.8.4.3).
[...]
If more information or sample data is needed, let me know.
What is _temptable?
If you have run ANALZYE, what are the contents of the sqlite_stat* tables?
What is the EXPLAIN
Mario M. Westphal wrote:
_temptable is a temporary table which contains a list of oids (integer, ~ 10
rows) to consider.
The information that oid is INTEGER PRIMARY KEY would have been helpful ...
For query 2.1
selectid order from detail
1 0 0
Martin Engelschalk wrote:
It seems the solution is to actually pass all bind variable values by
their appropriate sqlite3_bind_* - function instead of just using
sqlite3_bind_text. However, this means quite a lot of work for me.
Isn't it also work for you to converting your values to text?
Martin Engelschalk wrote:
create table TestTable (col_a numeric);
insert into TestTable (col_a) values (1);
retrieve the row, as expected:
select * from TestTable where col_a = '1';
do not retrieve the row:
select * from TestTable where coalesce(col_a, 5) = '1'
Can someone please
1 - 100 of 443 matches
Mail list logo
