Let me ask this by mere curiosity.
SQLite will accept and process the following:
CREATE TABLE x (a CHAR PRIMARY KEY);
CREATE TABLE y (a CHAR PRIMARY KEY);
CREATE TABLE z (a CHAR REFERENCES x(a) REFERENCES y(a));
I didn't check if the last FK is even valid normative SQL and that
isn't the heart
> > Since we use recursive triggers, set recursive_triggers pragma
> > beforehand if not yet done.
>
>Cunning. A bit of a Rube Goldberg apparatus though, no?
Huh? Still way more flexible than having to modify C source of a vtable
module, should you have to adapt anything.
Yeah, it's kind of con
>On Wed, May 18, 2011 at 4:10 PM, Petite Abeille
> wrote:
> > On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote:
> >> How can I simulate a
> >> calendar table(maybe using the strftime funtion)?
> >
> > Well, you have two broad options:
> >
> > (1) materialize the calendar as a table
> > (2) virt
Dear list,
Is there a way to make SQLite accept this kind of constraint:
CREATE TABLE tab (
id INTEGER NOT NULL,
data INTEGER,
CHECK(data = 0 or not exists (select 1 from tab where id = data)));
This toy exemple doesn't mean much as it is and the actual situation is
a bit more involved
>I agree with what you stated but it would have been more clearer if
>the result of the update statement was a "RECORD NOT FOUND" return
>value since it did not find any that met the query's criteria. How can
>you say that the UPDATE was successful when the record you were
>looking for does no
Without a view (but with a trigger) and certainly open to improvement
(9 is the
MAX_ENTRIES parameter):
CREATE TABLE "log" (
"id" INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE AUTOINCREMENT,
"seq" INTEGER CONSTRAINT "ix1Seq" UNIQUE ON CONFLICT REPLACE,
"data" CHAR);
CREATE TRIGGER "t
>I happen to have a code path such that the select statement can return
>1, 3
>or 5 columns. I know I could go based on count, but if I could do it by
>name that would be safer. I had not considered the point that multiple
>columns could have the same name, though, so I fully understand why suc
>How about:
>
>SELECT count() FROM ();
You can do that (and variations) but this is a completely distinct
statement.
I meant that there is no possibility to recover the row count of a
result set before it goes to completion (by iterating step), just
because the SQLite engine has no idea itsel
>How does one go about finding out how many rows a query returns?
This is the number of time sqlite3_step can be called successfully
until it returns SQLITE_DONE.
>Is there a way to find out the id of a particular column?
AFAICT column don't have ids. You can read column names or alias using
Change that into:
select date('2011-04-29', quote(-3) || ' day');
(note the space before day).
Looks like a parsing change.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
I apologize if double-post, the first one didn't make it to the list.
-
Hi,
>Hi all, I am interested in seeing "fuzzy searching" in SQLite, for
>lack of a better term. This type of search would return more results
>than LIKE curr
>True. I will get rid of the habit of using double quotes for string
>literals.
>Thanks for information. But most of the databases support this non
>standard
>behavior.
Yeah ... until things break under your feet due to a new version not
sticking to the "non-standard" behavior anymore or pars
> The apostrophes are escaped by apostrophes.
Correct. http://www.sqlite.org/faq.html#q14
> One more way you can do.
>
>insert into () values ("*Goin' Down
> >> the Road Feelin' Bad*");
>
>It is double quotes before and after *. Similarly double quotes will
>be escaped by one more double qu
>My date column is set when the program starts and i do not want it to
>change.
How is this date column set in the database without inserting anything?
> So I have my with and two columns and
>. I have say 5 values (1 2 3 4 5) that I wanted inserted
>into mytable where the date is equal t
>Newbie here. i'm trying to insert multiple values into a table by a
>certain
>date and when I use where clause it fails. This is my code "insert
>into db
>(table) values ('value') where date = 'date range'". Thanks for any help.
There is no where clause in insert statements, it wouldn't mak
>It's impossible when you use standard sqlite3 command line utility. In
>your particular case you have 2 options: either write your own
>application that will recognize some kind of value as null and insert
>it instead of plain text, or you can use temporary table like this:
Another way is to use
>3. edit the database file with a hex editor, replacing "~~" with "0D 0A".
That seems pretty dangerous! Rather update the table using the
standard replace() function.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-
>It's a pain in the arse that one has to pay for copies of the
>ISOs. What do they think we're paying them for ?
I also find this a perverse effect totally contrary to their mission or
at least its spirit: produce good standards for public use. Without
free access to reference up-to-date doc
>This page has a lot of info about
>Decimal Number support, including
>a set of libraries:
>
>http://speleotrove.com/decimal/
Yes! IBM and Intel are two of the big names having done significant
work in this direction and made research and/or results publicly available.
>There are many cases where people are doing calculations or using
>numbers expecting them to retain all digits. This would allow the BCD
>type to be used for that if they really need it.
Currency conversions (rarely exact!) or tax (or margin) calculations
come to mind as very common uses req
At 18:46 23/03/2011, you wrote:
>Current US national debt is 16 digits.
Nothing less? That's where the bug lies.
OK, OK, I'm out ;-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
At 09:43 23/03/2011, you wrote:
>I am working on one application which require sorting for Korean Language.
>
>The Korean Characters sort by Jamo(Hangul Jamo) ie based on KSX1001
>character code.
>
>Does sqlite3 or any other package support this type of sorting ?
>If not , then any clue to carry o
Hi Dear list,
I'm looking into system.data.sqlite and I've been asking myself
questions about the most portable encoding of a text passphrase to
sqlite_[re]key.
It seems to me that the only portable way is to pass an UTF-8 string
since it's the only encoding invariant wrt byte order and chara
>But what I postulate is that you can't physically write *the same* record
>over and over more than 90 times per second on a 5400 rpm drive,
>unless the
>drive, OS, or filesystem implements something like wear-leveling,
>where the
>physical location of sectors is constantly changing.
It's still
>So if the drive is 5400 rpm, 227 is much more than 5400/60=90 and even if
>it's 7200 (manufacturers sometimes upgrade drives inside portable hd
>without
>prior notice), it's still twice as much as 7200/60=120.
5400/60, 7200/60 ... those values rely on the assumption that
successive LBAs are ma
>Your goals make a lot of sense. However I think you can do my second
>suggestion. Compile with STAT2 code included by default, but make the
>analyze command only build stat1 by default.
>
>This will result in no change in default behaviour, but means that anyone
>wanting to use stat2 can easily
>Could you please show me how to write the condition (to filter out
>characters)?
Use the strfilter function found in extension-functions.c downloadable
from http://www.sqlite.org/contrib/
Once built and loaded, you can use a trigger similar to
create trigger if not exists trFilterAB after ins
>It seemed strange that a simple "select * from table" that I was doing
>was so slow. The table contained about 20 columns (fields) and 300
>rows. The select took about 1.5 seconds. (using SQLite Expert).
Does the run time settle at 1.5 s after a few runs or is that a
first-run time ?
As an a
Hi Igor,
>I'm not quite sure what you are talking about, but see the
>documentation of like() and glob() functions here:
You're right about LIKE and GLOB.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/l
> > Sorry for elementary questions but when implementing a REGEXP function
> > (I'm using PCRE from pcre.org) I need to know in which order the two
> > arguments (target, pattern) will be passed to the function.
> >
> > I also would like to retain the last pattern used in compiled form
> > (connec
Hi all,
Sorry for elementary questions but when implementing a REGEXP function
(I'm using PCRE from pcre.org) I need to know in which order the two
arguments (target, pattern) will be passed to the function.
I also would like to retain the last pattern used in compiled form
(connection-wise).
>when i just launch th application, at the beginning the query can take
>around fews seconds... but after some time (10 - 20 minutes), it's take
>only few ms !
>
>so i guess it's because the windows cache in memory the database file ?
>
>so how to speed up this time to make windows cache more fast
>An end user (think: your mom) wants to upgrade her smartphone to the
>latest
>OS release. That new OS release includes the latest shared library for
>SQLite. But in so doing, some percentage of the apps she has downloaded
>cease to work. Sure, the problem really is that the apps were incorrec
>This is, technically, a compatibility break. On the other hand, there
>appear to be vast numbers of smartphone applications that currently depend
>on undefined behavior and will suddenly stop working if we don't make this
>change.
I understand the proposed change will have no incidence for corr
>thanks Simon and Igor for your help on this a few weeks ago, but I
>need an
>addition to
>
>UPDATE Aircraft SET CN = '*' where CN = '' or CN is null;
>
>what I need to do is replace blank fields in a specific row, sort of
>a double where where statement as in:
>
>UPDATE Aircraft SET CN = '*' whe
Richard,
>Use floating point for the purpose for which it was created: scientific
>calculations. If you need to know your bank balance to 17 significant
>figures, use integers. Store the values as cents instead of dollar and do
>the conversion in your application.
I do exactly that in my own ap
>I can't help but wonder how decimal math is supposed to make 1/3 + 1/3
>+ 1/3 better.
Sorry it was almost a private joke here. It's a very common maths
question / challenge about what infinite decimal expansion means.
If you add 0.... (where ellipsis means infinite number of digit
3
Scott,
>SQLite handles rounding by running the value through the internal
>printf with the appropriate precision. As best I can tell the
>internal printf adds half a unit at the appropriate position, then
>truncates.
Yep, truncation is a way to do it but doesn't meet layman expectations
in many
> > select round(3.05, 1)
>3.0
>
>Is this expected behavior for SQLite?
>The documentation isn't specific on the rounding strategy that is used.
>
>My personal expectation was that this would round to 3.1.
You _expect_ that 3.05 will represent exactly as 3.05 in IEEE, but it
that the case? Shou
Stephan,
>Thanks a lot for your help. It seems we have a problem with our qdbc
>command. Its terminateing a sql command when a ; is detected. With sqlite
>console your syntax is working:
Sorry for misleading you with a phantom missing closing parenthesis, I
was doing (or rather trying to do) too
>hu-intel:/dev/shmem> /fs/sda0/opt/mm/bin/qdbc -dmme "CREATE TRIGGER
>genre_custom
>_insert AFTER INSERT ON library_genres BEGIN INSERT INTO
>genre_custom(genre_id,
>genre, type) VALUES(NEW.genre_id, NEW.genre, (select type from
>podcasts_custom w
>here NEW.genre in (SELECT podcast FROM podcasts_c
>/fs/sda0/opt/mm/bin/qdbc -dmme "CREATE TRIGGER genre_custom_insert AFTER
>INSERT ON library_genres BEGIN INSERT INTO genre_custom(genre_id, genre,
>type) VALUES(NEW.genre_id, NEW.genre, (select type from podcasts_custom
>where NEW.genre in (SELECT podcast FROM podcasts_custom))); END"
>Actually i want to support Multilingual Linguistic Sorts.
>The sorting order shall be approached by separating the character set into
>three groups:
> a. special characters (i.e. +, -, &, Space, etc.)
> b. digits (0-9)
> c. Latin/Greek(language) letters.
>
>The digits shall be sorted below th
>we develop a bookmark database for urls, http://arado.sf.net and used
>SQLite with Qt.
>With 7000 database entries of urls the search for a keyword takes up
>to 15 seconds to respond.
>That is quite a long time, why is SQL so slow? is there a way to
>improve the speed besides to switch to another
Hi,
SQLite sounds pretty reasonnable to me:
>select "select distinct favicon_id from moz_places";
>select distinct favicon_id from moz_places;
Returns NULL
>select "deleting: standard method...";
>delete from moz_favicons where id not in (select distinct favicon_id
>from moz_places); -- here i
Harish,
>We have a problem with a sql query.
>In a table, a column called "name" contains character data that may
>include
>alpha, numeric and special characters. It is required to sort in such
>a way
>that names starting with alpha characters are listed first, then numerals
>and finally special
>It's not contradictory. I say that "real cost" of sqlite3_open is
>parsing the schema. Igor says that this cost is actually deferred from
>inside sqlite3_open call to the first "substantive" sqlite3_step call.
>So you will have to pay this price anyway, just profiler output would
>be somewhat con
Pavel,
> > 1) How "expensive" is a call to sqlite3_open. Does a call to
> sqlite3_enable_shared_cache make it "cheaper"?
>
>Its cost depends on the size of your schema as it gets parsed during
>open.
Isn't this contradictory with an answer by Igor made in a recent thread?
>Subject: Re: [sqlite
At 14:26 26/11/2010, you wrote:
>N.b., there is a severe bug (pointers calculated based on truncated
>16-bit
>values above plane-0) in a popular Unicode-properties SQLite extension.
>The extension only attempts covering a few high-plane charactersif
>memory
>serves, three of thhem in array 198;
>Simon, I'll try that and see what difference itmakes
That or build a :memory: DB, populate it, build indices and then only
back it up to disk using the backup API. That requires you have enough
memory available, but should be really fast if you have.
_
>I import a CSV flat file into a table (using sqliteman as GUI). The
>flat file has NULL values as simply no data between two delimiters (I
>use 'tab' as Delimiter).
>
>The resulting table does not treat 'no data' between 2 delimiters as
>NULL. When I run CHECK constraints etc. on it it behav
Look in your mailbox.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>As far as I can tell, turning on foreign_keys for a database is not
>persistent. It only seems to be valid for that connection/session. So
>this means I have to prefix every command that I send to the database
>with a command to turn on foreign_keys and I therefore can't issue
>one-off comman
>But Roger, the "layer sitting in front of SQLite" is a programming
>environment which provides its own (black-box) connectivity to SQLite, and
>it isn't going to be calling any DLL into which one will have injected
>a UDF
>library in the manner you have laid out, and it's not going to let the
> > What is the rationale about placing complex conditions in the ON part
> > of an inner join rather than in an WHERE clause?
>
>Except for outer joins, the difference is purely stylistic. They are
>functionally equivalent. In fact, SQLite internally rewrites the
>former to the latter, before g
What is the rationale about placing complex conditions in the ON part
of an inner join rather than in an WHERE clause?
I understand that the ON clause will limit the temporary table
processed by a subsequent WHERE clause, while a larger table will be
filtered by the WHERE part if no selective O
r beyond what SQL would allow.
--
Jean-Christophe Deschamps
eMail: <mailto:j...@q-e-d.org>j...@q-e-d.org
SnailsTo: 1308, route du Vicot
40230 Saint Jean de Marsacq
France
GSM: +33 (0)6 15 10 19 29
Home:
>I'm aware that SQLite offers the functionality of in-memory databases,
>but I
>don't know how to read the data and send it over the wire to the server or
>how to push the data into the in-memory database of the server.
The backup API included in SQLite offers the facility to backup/restore
(in
>I am parsing fields on the fly and then creating tables, unfortunately
>one of the fields is "Order" and is a "special word" in SQL as is not
>allowed. Is there a way around this instead of intercepting with perl
>s'/Order/Orders/g'
Can you wrap every column name inside double quotes or squar
>What's the best way to copy data from one db to another?
>
>Given 2 databases with identical schemas, one full of data and the
>other empty, the brute force way would be to perform selects on the
>source db, then for each row, perform an insert into the destination
>db. Is there a more efficient
>I use vb or in SQLlite Expert the * does not seem to work
> >From my testing * doesn't seem to work in the likeif I use '%33' it
> returns
>everything with test33if I use '33%' it returns everything with
>33testif I
>use '%33%' it returns everything with 33test, test33 which is the same as
>*
>How do I search for the asterix character * ??
>
>This doesn't work:
>select field1 from table1 where field1 like '%FH%*%'
>as the * character here seems to be ignored.
>
>Using the latest version of SQLite.
You're doing it right. Either you use a wrapper that messes with * in
litterals or th
>Running in sqlite application in Virtual Box, attempt to open a
>database with sqlite from a shared network folder
>\\Vboxsvr\testdata however the open16 and openv2 (with read
>only) both fail --- rc = 14. File opens file if moved locally to
>hard drive.
>
>using latest version of sqlite3
>I use sqlite from within Autoit V3 (Autoit is a windows-oriented
>basic-like language)
AutoIt, while a scripting language can be seen and used as a RAD
platform. It enjoys good support, up to date SQLite embedding and
executables produced can include any file your application needs, like
a
>Is there any way to load a sqlite3 db from a location in memory?
>Ideally, I'd like to have a memory pointer (eg, something provided via
>malloc), which I could then use as the handle sqlite3 uses to load the
>database.
>
>The reason I'm trying to do this: I have an encrypted database file
>t
>z> i wonder is there RECURSIVE select function in sqlite? the background
>z> for the question are: create table objects (id INTEGER PRIMARY KEY
>z> AUTOINCREMENT, name text unique) create table tree(id int, child_id
>z> int, PRIMARY KEY(id, child_id)) i want to draw the whole tree, is
>there
>z>
>The Minimal-Perfect-Hash-INTERSECTION-OF-VECTORS approach might benefit
>queries against tables having several million rows. What I'm wondering
>(and
>lack the C skills to find out for myself) is whether SQLite's underlying
>algorithms for INTERSECT could be optimized with a minimal perfect hash
> > In my low-concurrency, familly-business context, I have no problem at
> > all setting 3 hours timeout using the built-in function, when the
> > slowest transaction may only take less than 5 minutes.
>
>With this condition as a 4th one in your list and with 5th one stating
>that you have less t
Let me take a reality check for the case of my own usage. If I
guarantee that the following conditions are all true:
All R^n (Read-Read-...-Read) atomic operations are enclosed in BEGIN
transactions.
All W^n (Write-Write-...-Write) and RMW (Read-Modify-Write) atomic
operations are enclosed i
>
>I would first create an INTEGER primary key and then place an index on
>name,
>another on i_from, and another on i_to, and then see if the approach below
>has any benefit.
>
>When I tried this with a geo-queryit was actually slower than the standard
>select, and I'm curious if that's always go
>I need to find out how many specific weekdays (e.g., how many Sundays)
>I have in any given range of dates.
>My problem: How to use the COUNT function in combination with the
>strftime() function.
>
>$ sqlite3 test.db3
>SQLite version 3.6.20
>sqlite> create table test (date VARCHAR(20), money I
Tim,
>But did I say that GLOB uses an index if it has been overloaded? No. I
>wrote that if LIKE has been overloaded, queries that contain LIKE
>won't use
>the index. Typically, GLOB won't have been overridden too just
>because LIKE
>has been overridden: the rationale for overriding the LIKE
Hi gurus,
I'm aware of the limitations that generally preclude using SQLite over
a network.
Anyway do you think that doing so with every read or write operation
wrapped inside an explicit exclusive transaction can be a safe way to
run a DB for a group of 10 people under low load (typically 2Kb
Tim,
>Queries using GLOB do use the index on the column in question (i.e.
>optimization is attempted)
>Queries using LIKE do not use that index if the LIKE operator has been
>overridden.
Sorry but GLOB doesn't use an index either if LIKE/GLOB has been
overloaded. This is consistent with the doc
At 14:31 26/04/2010, you wrote:
>If the implementation of SQLite you are using overrides the LIKE operator
>(as more than a few do), then SQLite will not make use of an index on the
>column in question. Use the GLOB operator instead.
I doubt it. GLOB is absolutely nothing more or less than an in
> > Contrary to what occurs in other engines, SQLite seems to
> > assert constraints at insert/delete time, ignoring the fact that
> > (insert or delete) trigger will increment or decrement the upper part
> > of the tree interval on HI and LO keys (in the case of a nested tree).
>
>This /should/ b
>I found the the restriction of updating unique column in ver3.6.21.
>and same problem is reported in follwoing mail
>
>Marc-Andre Gosselin wrote:
>date: Thu, 16 Jun 2005
>title: "[sqlite] Update unique column"
> >
> > I discovered a behavior in SQLite 2.8.16 that doesn't conform to
> the SQL
> >
>We just experienced the hard way that overloading certain built-in SQL
>function can interfere with core SQL commands if the overloaded function
>behaves differently from the built-in function.
>
>Not surprising, after looking at the sources:
>
>* ALTER TABLE - alter.c uses SUBSTR and LIKE.
>* VA
>Both of these tools show a version of the database that is different
>from what i see in the command line and they are equivalent in their
>discrepancies (they are different from the command line but the same
>as each other).
>
>So heres the basica scenario:
>
>1. i update my database with the co
Hi Tom,
>BTW- if you haven't done so already; it may be of use to the user to add
>extensions: VirtualText and Jean-Christophe Deschamps has an extension
>for fuzzy search for example.
There is no problem. Alexey put it on his website under the extension
for Unicode folder. Pl
>I have a (small) directed graph which I would be able to fins all
>ancestors or descendents of a certain vertex (transitive closure?).
>So, using this graph:
>
>CREATE TABLE levels_levels (parent_id INTEGER ,child_id INTEGER,
>UNIQUE(parent_id, child_id));
>INSERT INTO "levels_levels" VALUES(6,7)
Is it allowable/safe to invoke more than once any sqlite3_result_*()
function? In other terms, subsequent invokation of any result function
will it harmlessly override a previous one?
As in:
init...
// once for all, post null return in anticipation for the various cases
where
// parameters or
>We currently use sqlite 3.6.23. We have a big problem with characters with
>accents or other special characters in path to database file, for
>example in
>Czech Windows XP the "Application Data" folder is translated to "Data
>aplikací" so if the accented 'í' is in path the sqlite3.exe writes tha
>is anybody aware of a possibility to do s.th. like
>select * from table where field like '[A|a]%'
Unless non-standard compile option and provided you don't issue
PRAGMA case_sensitive_like = 1;
LIKE is case-insensitive, so LIKE 'A%' is the same as LIKE 'a%'
SQLite offers another filtering
>Yours returns 1 or 0. Mine returns length if found, otherwise 0.
That's true but the subject title led me to believe that the OP
intended to have a 0 vs. nonzero return for not-exists vs exists condition.
___
sqlite-users mailing list
sqlite-users@
>Andrea Galeazzi wrote:
> > I've got a table T made up of only two fields: INT id (PRIMARY KEY) and
> > INT length.
> > I need a statement in order to yield 0 when the key doesn't exist.
>
>Well, "select 0;" fits your spec (you never said what should be
>returned when the key does exist). I woul
>Why not just
>
> update tbl set col1 = col1;
>
>or perhaps
>
> update tbl set col1 = cast(col1 as text);
>
>I'm not sure the former will actually change anything, but the latter
>should.
Yes my untold question was merely if simpler col = col way could be
simply ignored. You're right a
>And, of course, you'll have a table where some rows (old ones) contain
>integers and others (new ones) contain text. SQLite is fine with this,
>but your client software might not be prepared to deal with it. Some
>comparisons might behave in surprising ways.
I imagine that in such case, the b
Hi Alexey,
>1. See internal sqlite instarray interface:
>http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.c
>http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.h
>http://sqlite.mobigroup.ru/src/finfo?name=test/intarray.test
>
>Note: http://sqlite.mobigroup.ru include official
>Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i
><= 5"?
>
>Then it would also work for ordered types that aren't ordinal, such as
>rationals
>and strings and blobs and dates etc, and it would work for very large
>ranges,
>since there's no conceptual need to generate all
>ATTACH DATABASE ?1 as sysDB
AFAIK you can't use parameter binding for anything else than litteral
values.
It makes sense since it would be impossible for the parser and
optimizer to evaluate and produce run-time code for a statement without
knowing beforehand which database or column the sta
>Ah. You want table-valued functions, like this:
>
>http://msdn.microsoft.com/en-us/library/ms191165.aspx
Thanks Igor, that's what I had in mind.
>In any case, SQLite doesn't support table-valued functions. The
>closest thing to it is a virtual table:
OK, got it, but this is a bit of heavy en
>Why not just
>
>select some_scalar_function(i) where i between 1 and 5;
That's because we then get
No such column: i.
That was not very important. I would have the use for such possibility
but I can live without. My question was just curiosity about whether
something along the line could wo
I'm trying to determine if a Range(from, to) function can be made as an
extension function.
Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3,
4, 5) for use in constructs similar to
select some_scalar_function(i) where i in range(1, 5);
without having to build a ta
>sqlite> select * from test where text like '_';
Underscore '_' is LIKE wildcard for any single character, percent '%'
matches any substring.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlit
>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 stat
>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
> 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 under
>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
>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
201 - 300 of 424 matches
Mail list logo
