Alexey,
I can certainly tell you what is causing this behaviour:
When sqlite does a comparison, it checks the *column* affinity from both
sides. In your example, neither sides come from a column, but are
constants. Therefore, sqlite uses the NONE affinity on comparison (I
think, but I
Now, it is probable that the core can be patched as you request by
assigning affinity to constants during parsing, but I haven't had an
opportunity to look into it and I would have to check very
thoroughly that it will not brake something else if I do patch it
for you. At this precise
Hi,
I spotted this while looking into something else for Alexey:
The following sql is supposedly valid:
select 1 in (1)'hello';
where as
select * from t where 1 in (1)'hello';
returns Error: near 'hello': syntax error (which I would expect!)
This occurs on v3.6.22, but I haven't checked it
Hello!
On Monday 01 March 2010 08:05:06 Darren Duncan wrote:
Alexey Pechnikov wrote:
Hello!
On Thursday 25 February 2010 02:53:32 Igor Tandetnik wrote:
http://www.sqlite.org/datatype3.html
For conversions between TEXT and REAL storage classes, SQLite considers
the conversion to be
On Mar 1, 2010, at 6:08 PM, Alexey Pechnikov wrote:
It would seem to me that asking [1='1'] *should* return false
because the
numeric and character string value domains are logically disjoint,
so no value
from one could ever equal a value from another, and so SQLite's
answer is
wcl...@gfs-hofheim.de wrote:
The following sql is supposedly valid:
select 1 in (1)'hello';
Apparently this gets interpreted as
select (1 in (1)) as hello;
(as is optional), under SQLite's long-standing policy of accepting single and
double quotes more or less interchangeably.
--
Igor
Regarding:
BTW, can you recommend me suitable UI to work with SQLite?
Maybe you've already tried out some of the GUI tools listed at:
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
I'm not sure what your requirements and preferences are, or what operating
system you're using, or
HI,
How could i get the rows affected by current sql statement ?
sqlite3_changes can only get the rows affected by the most recently sql
statement which really affect rows.
But i don't know whether current sql statement can affect rows.
For example:
1) First ,i open the database connection.
sqlite3_changes() is exactly what you should use in this case. And I
didn't understand why did you find it unsuitable for you?
Pavel
On Mon, Mar 1, 2010 at 8:22 AM, yangchen yangc...@cn.fujitsu.com wrote:
HI,
How could i get the rows affected by current sql statement ?
sqlite3_changes can
SELECT count(*) WHERE NOT text IS NULL
requires that the complete text column is loaded. With a stored LOB
this results in crazy performance.
How did you find that? What do you mean by requires loading of the
whole text column? It pretty much can require even loading of text
columns that
SELECT count(*) WHERE NOT text IS NULL
requires that the complete text column is loaded. With a stored LOB
this results in crazy performance.
How did you find that? What do you mean by requires loading of the
whole text column? It pretty much can require even loading of text
columns that
Anyway, it seems the OP has a point in saying that it would be nice
--and I would say 'natural'-- to have the optimizer enhanced to handle
NOT condition as efficiently as it handles condition, provided
such enhancement can be done with only little changes.
I totally disagree with you. Let's
I totally disagree with you. Let's say you have 1,000,000 rows and 100
of them contain NULL. In this situation selecting NOT NULL will select
almost all rows which means that using index in this case doesn't give
any performance boost. So here using full scan for NOT NULL condition
is better and
The following sql is supposedly valid:
select 1 in (1)'hello';
Apparently this gets interpreted as
select (1 in (1)) as hello;
(as is optional), under SQLite's long-standing policy of
accepting single and double quotes more or less
interchangeably.
Hmm, I'd not have guessed. Thanks -
The point was that NOT condition is
simply reversing the issue of a binary test, in fine, and that seems
essentially independant of the use of an index for determining condition.
I agree with the point that NOT prevents using an index for some
reason. I'm not sure but probably that's because
maybe NOT is implemented the same way as any other
function and so it cannot be optimized using index.
That's possible, but other logical operators don't exhibit the same
bahavior and will not prevent the use of indexes. That NOT is not
being handled at the same _logical_ level than AND and
On Mon, Mar 01, 2010 at 06:03:49PM +0100, Jean-Christophe Deschamps scratched
on the wall:
I totally disagree with you. Let's say you have 1,000,000 rows and 100
of them contain NULL. In this situation selecting NOT NULL will select
almost all rows which means that using index in this case
Hello!
I need to check if parent of group is exist or not.
Parent stored in parent column and may be NULL or will be existing
idgroup column value.
It does not works because subqueries prohibited in CHECK constraints.
My table showed below (incorrect constraint commented).
I did not get an e-mail response and the status check URL has expired. Thank
you
I have found the following:
BEGIN; UPDATE entry SET Spinnaker=No ,Singlehand=Doublehand
,Doublehand=Third ,Multi=Multihull ,Furler= WHERE ROWID=45; COMMIT
Results in the value of the column Singlehand
I have been experiencing a strange issue with savepoint error for the
library version 3.6.22.
I have the following code:
char* sql = SAVEPOINT sp;;
char* err;
slim_int ret = sqlite3_exec(sqlitehandle, sql, NULL, NULL, err);
The ret is 1 which is SQLITE_ERROR
and err = near \sp\: syntax error
I
Did you consider using foreign keys (assuming you have the latest
version of SQLite)?
Pavel
On Mon, Mar 1, 2010 at 10:47 AM, Артемий Васюков a...@cranix.org wrote:
Hello!
I need to check if parent of group is exist or not.
Parent stored in parent column and may be NULL or will be existing
On Mon, Mar 01, 2010 at 06:47:41PM +0300, ?? ??
scratched on the wall:
Hello!
I need to check if parent of group is exist or not.
Parent stored in parent column and may be NULL or will be existing
idgroup column value.
It does not works because subqueries
On Mon, Mar 01, 2010 at 02:06:05PM -0600, Jay A. Kreibich scratched on the wall:
Also, it is highly unlikely the expression parent == NULL does what
you want, given that it will *always* return NULL. Try parent IS
NOT NULL.
Er... I mean parent IS NULL.
-j
--
Jay A. Kreibich J
What happens if you change double quote marks to single
quote marks?
On Mon, 2010-03-01 at 08:52 -0800, Ray Irvine wrote:
I did not get an e-mail response and the status check URL has expired.
Thank you
I have found the following:
BEGIN; UPDATE entry SET Spinnaker=No
Hello!
On Monday 01 March 2010 14:48:46 Dan Kennedy wrote:
On Mar 1, 2010, at 6:08 PM, Alexey Pechnikov wrote:
It would seem to me that asking [1='1'] *should* return false
because the
numeric and character string value domains are logically disjoint,
so no value
from one could
So indexes are not used for NOT conditions, as NOT conditions
generally require a full scan, regardless. Yes, it is a simple
reverse of a binary test, but the reverse of a specific indexed
lookup of a known value is a table scan to gather all the unknown
values.
Jay,
I
On Mon, Mar 1, 2010 at 2:44 PM, Jean-Christophe Deschamps j...@q-e-d.org
wrote:
The actual reason for the way NOT works as for now may be due to the
fact that negating a condition may cause the resulting set to be in
fact itself the union of two subsets.
Say the where condition K is col =
NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT
(col = 12345).
You're right of course! (and I was even saying about nulls treated apart)
But, in your view, that the set can be non-contiguous for
negative/negated conditions would it explain that current code can't
make
On Mon, Mar 1, 2010 at 3:12 PM, Jean-Christophe Deschamps j...@q-e-d.org
wrote:
NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT
(col = 12345).
You're right of course! (and I was even saying about nulls treated apart)
But, in your view, that the set can be
Hi all,
I've been playing around with the FTS3 (via the amalgamation src) on a
mobile device and it's working well. But my db file size is getting
pretty big and I was looking for a way to compress it. I've seen some
earlier posts from Alexey for his compression modifications to the
FTS3
I haven't been able to think of how it would preclude using the index,
but I suspect it's more a matter of needing a similar-but-different
codepath to optimize for the NOT case, rather than a simple invert
this codepath relying on the existing case. Which is really just
another way of stating
On Mon, Mar 01, 2010 at 11:44:51PM +0100, Jean-Christophe Deschamps scratched
on the wall:
So indexes are not used for NOT conditions, as NOT conditions
generally require a full scan, regardless. Yes, it is a simple
reverse of a binary test, but the reverse of a specific indexed
Hi there,
I'm wondering how larger BLOBs in a database affect performance of accessing
the non-blob data. We've got a database with potentially a few million
records in the main tables (of just strings and ints), but joined to that data
set we want to store up to thousands (maybe 75000 max)
33 matches
Mail list logo