Since you did not specify an ORDER BY clause, SQLite is free to return rows in
*any* order. The order may even change if the underlying schema changes and
SQLite finds a better way to compute the results.
A "covering index" is one that contains all the fields required from a certain
table to fu
I am fully aware of this and I have no problem at all with this behaviour.
I just wondered why it choose the particular plan in this situation.
There is no criticism, just curiosity.
RBS
On Thu, Jan 12, 2017 at 12:45 AM, Richard Hipp wrote:
> On 1/11/17, Bart Smissaert wrote:
> >
> > The resul
Dear list members:
Im trying to read a .csv file and introduce in an SQLite table, using the
insert into command.
In the .csv file I have not used this Id field (it was defined as integer
and autoincrease).
I will appreciate any help about how to copy from a .csv file to an SQLite
tabl
Following the code in mem1.c, Why SQLite Use Different Malloc Zone While CPU
Count 1?
static int sqlite3MemInit(void *NotUsed){
#if defined(__APPLE__) !defined(SQLITE_WITHOUT_ZONEMALLOC)
int cpuCount;
size_t len;
if( _sqliteZone_ ){
return SQLITE_OK;
}
len = sizeof(cpuCount);
/* One us
On Jan 11, 2017, at 4:01 PM, Jim Callahan
wrote:
>
>> How much doing all that is worth is a different question, since the calls
> made through this
>> proposed system() SQLite function would also likely be non-portable. In
> this very example,
>> there is no wc on Windows.
>
> I would suggest
I am not even sure myself this is the right path.
I have table with file names and need operations to be performed on columns
(i.e. on files). Results, numeric or new file names, are to be recorded in a
column. I see two ways:
From bash script, make list of rows, run commands, load results to
It can. You declare the column to have a collation of NOCASE. In the case of
the select you gave which does not d=contain any wildcards (and therefore is
not needful of LIKE) you can pronounce:
SELECT trim(name) FROM names WHERE name = 'Ben' and name = 'benjamin' collate
nocase
However sin
On 1/11/17, Simon Slavin wrote:
>
> On 12 Jan 2017, at 12:49am, Richard Hipp wrote:
>
>> For years I have threatened to make it a feature of SQLite that it
>> really does output the rows in some random order if you omit the ORDER
>> BY clause
>
> SQLITE_CONFIG_RANDOMIZEWHATYOUCAN_LOL
>
The "PRAG
On 12 Jan 2017, at 12:45am, Richard Hipp wrote:
> On 1/11/17, Bart Smissaert wrote:
>>
>> The result is that the output is descending on DOB.
>> I expected and preferred if the output was ascending on rowid.
>
> If you omit the ORDER BY clause, then the SQL database engine (*any*
> engine, no
On 1/11/17, Richard Hipp wrote:
>
> If you omit the ORDER BY clause, then the SQL database engine (*any*
> engine, not just SQLite) is free to return the rows in whatever random
> order it chooses. And it does not need to explain itself when it
> does. :-)
>
For years I have threatened to make
On 1/11/17, Bart Smissaert wrote:
>
> The result is that the output is descending on DOB.
> I expected and preferred if the output was ascending on rowid.
If you omit the ORDER BY clause, then the SQL database engine (*any*
engine, not just SQLite) is free to return the rows in whatever random
or
Say I have a table created like this:
create table table1(
[id] integer primary key,
[dob] integer)
with an index (not unique) on dob
and I run this SQL:
select id, dob from table1
then the query plan I get is:
SCAN TABLE TABLE1 USING COVERING INDEX IDX_TABLE1_DOB
The result is that the outp
On 1/11/17, Bob Friesenhahn wrote:
>
> For anyone thinking that it is a good idea to embed shell
> functionality in the SQL interpreter, it makes the SQL interpreter
> much less secure for untrusted inputs.
Nobody is suggesting that this become a standard feature of the
language. Roman wants a "
> On Jan 11, 2017, at 1:34 PM, R Smith wrote:
>
> SELECT a FROM t WHERE a = b COLLATE NOCASE;
D’ohh! I overlooked the COLLATE operator. Perfect.
(In my situation, these strings are not coming directly from columns, so
setting collation on columns doesn’t make sense.)
—Jens
__
On Wed, 11 Jan 2017, Jim Callahan wrote:
How much doing all that is worth is a different question, since the calls
made through this
proposed system() SQLite function would also likely be non-portable. In
this very example,
there is no wc on Windows.
I would suggest renaming the proposed
Hello again !
I did some changes and got a better plan but it takes for ever, it seems
that sqlite3 can not merge the where clause before the "group by" scan
the whole tables:
===
CREATE VIEW despesas_municipio_orgao_list_view AS
SELECT
a."municipio_id" rowid,
a."ano_exercicio",
> How much doing all that is worth is a different question, since the calls
made through this
> proposed system() SQLite function would also likely be non-portable. In
this very example,
> there is no wc on Windows.
I would suggest renaming the proposed system() function bash() since now
and in t
On Jan 11, 2017, at 3:11 PM, Richard Hipp wrote:
>
> On 1/11/17, Scott Hess wrote:
>> UPDATE result SET nRows = system('wc', '-l', fileNames);
>>
>> ...
>> [Though, yes, this means you'll have to use fork() and execlp() and
>> waitpid() to implement, rather than popen().
>
> Which further mea
Hello Richard !
I just tested the latest sqlite3 with this "Changes to allow some
multi-row UPDATE statements to avoid the two-pass approach." and before
it was blowing up my machine (swapping) and now the memory usage remains
basically the same as the startup without doing nothing.
Thank yo
On 1/11/17, Scott Hess wrote:
> Though it may be cleaner long-term to implement system() to pass
> individual arguments, rather than passing a single string which will
> have to be re-processed by the shell. So the API would end up like:
> UPDATE result SET nRows = system('wc', '-l', fileNames)
Though it may be cleaner long-term to implement system() to pass
individual arguments, rather than passing a single string which will
have to be re-processed by the shell. So the API would end up like:
UPDATE result SET nRows = system('wc', '-l', fileNames);
The reason I suggest this is because
I know it is not magic. It is SQLite developers' brain and effort. But it looks
like magic to me.
Yes, this is example I see how I could use.
I am still thinking if this is what I really need. I am working with images and
need, among other things, some summary measures to be placed into tables
Hello Richard !
It seems that sqlite query plan is having trouble with this query too:
===
create table if not exists municipios(
id integer primary key,
name varchar not null unique collate nocase_slna
);
create table if not exists municipios_orgaos(
id integer primary key,
na
On 11 Jan 2017, at 9:34pm, R Smith wrote:
> Doesn't this already do the trick?
>
> SELECT a FROM t WHERE a = b COLLATE NOCASE;
Right. Better still, if you declare the columns as COLLATE NOCASE in the first
place, the comparison is done ignoring case without you having to state it in
the SEL
On 1/11/17, Roman Fleysher wrote:
> Yes, Richard, this is exactly what I mean.
>
Then maybe use the https://www.sqlite.org/src/file/ext/misc/rot13.c
extension as a prototype from which to develop yours.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-us
Yes, Richard, this is exactly what I mean.
Roman
From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of
Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 11, 2017 4:34 PM
To: SQLite mailing list
Subject: Re: [sqlite] extension
> may need ability to run bash commands and assign result to a column. For
example:
>
UPDATE result SET nRows =` wc -l fileNames` ;
Might be easier to run Bash commands in Bash; write the results to a file
and then redirect the file into SQLite.
See for example, this Nabble thread.
http://
On 1/11/17, Roman Fleysher wrote:
> Dear SQLites,
>
> I am using exclusively sqlite3 shell for all the processing and may need
> ability to run bash commands and assign result to a column. For example:
>
> UPDATE result SET nRows =` wc -l fileNames` ;
>
> Here I used `` as would be in bash for co
On 2017/01/11 11:19 PM, Darren Duncan wrote:
On 2017-01-11 10:11 AM, Jens Alfke wrote:
And while we’re at it, I’d like to see a case-insensitive string
equality operator.
Yes, that shorthand can be useful. But don't make it a pragma that
overrides the meaning of "=", which would be a world
Dear SQLites,
I am using exclusively sqlite3 shell for all the processing and may need
ability to run bash commands and assign result to a column. For example:
UPDATE result SET nRows =` wc -l fileNames` ;
Here I used `` as would be in bash for command substitution. This would run wc
command
On 2017-01-11 10:11 AM, Jens Alfke wrote:
And while we’re at it, I’d like to see a case-insensitive string equality
operator.
Yes, that shorthand can be useful. But don't make it a pragma that overrides
the meaning of "=", which would be a world of hurt, it needs a different name.
-- Darren
On 2017/01/11 7:55 PM, Anony Mous wrote:
Here's the problem as I see it (demo SQL is lame, but makes the point):
SELECT trim(name) FROM names WHERE name LIKE('Ben') and name
ILIKE('benjamin')
...you can't do that in SqLite using a pragma, can you? If you can, I'd
sure like to learn how.
On Wed, Jan 11, 2017 at 10:28 AM, Rael Bauer wrote:
> 1) Should the howtocompile.html webpage not include this in the "Building A
> Windows DLL" instructions?
Tough question. Normally, I'd say this is toolset knowledge, and
really doesn't belong in those sort of directions. But since you're
not
@ Random Coder.: Thanks, that was the exact issue. Using a def file
solved the problem.
1) Should the howtocompile.html webpage not include this in the
"Building A Windows DLL" instructions?
2) What is the -Ox flag? I did not find what that was for.
-Rael
> On Jan 11, 2017, at 9:55 AM, Anony Mous wrote:
>
> Textual data has case. Sometimes that matters. Sometimes it doesn't. A
> database engine should be able to cleanly deal with that without forcing
> the programmer to write custom code.
+1. And while we’re at it, I’d like to see a case-insensi
Here's the problem as I see it (demo SQL is lame, but makes the point):
SELECT trim(name) FROM names WHERE name LIKE('Ben') and name
ILIKE('benjamin')
...you can't do that in SqLite using a pragma, can you? If you can, I'd
sure like to learn how.
If you can't, not to belabor the point, but y
Ken,
That went to the mailing list ... to which you are also a recipient of
;)
Thanks,
Chris
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
I try both "SELECT 1,0;" and "SELECT 1, cast(0 as real)"?
The same error:
Exception calling "WriteToServer" with "1" argument(s): "The given value of
type SqlDecimal from the data source cannot be converted to type decimal of the
specified target column."
-Original Message-
From: sqlite-
What is the result if you exchange your currently executed statement to "SELECT
1,0;" ? Or to "SELECT 1, cast(0 as real)"?
The effect of declaring a NUMERIC(5,5) field in SQLite is that it's affinity
becomes NUMERIC. The value .0 thus becomes the integer 0. The other legal
values (.1 to
On Wed, Jan 11, 2017 at 2:11 AM, Rael Bauer wrote:
> I am trying to compile the latest amalgamation (3160200) as a dll on Windows
> 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll compiles fine,
> however trying to use this dll in various tools (Delphi) results in the
> error "sqlite
On Wed, 11 Jan 2017 12:11:36 +0200
Rael Bauer wrote:
> I am trying to compile the latest amalgamation (3160200) as a dll on
> Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll
> compiles fine, however trying to use this dll in various tools
> (Delphi) results in the error "s
I didn't assign any value directly.
This is what I did:
1. On SQLite create source table.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);
2. On MS SQL Server 2014 (SP2) create empty destination table.
CREATE TABLE dbo.ToNum (
id IN
Keith,
Good point. Did not know this exists.
Ken
On 01/10/2017 09:48 PM, Simon Slavin wrote:
On 11 Jan 2017, at 1:02am, Keith Medcalf wrote:
You are correct, however, if there were a unique constraint placed on
tracks.name, then a given track could only appear once (in the first case), or
Domingo,
Thanks for the email, but I don't think I am your inteded recipient.
Ken
On 01/10/2017 12:11 PM, Domingo Alvarez Duarte wrote:
Hello Richard !
Now that you are dealing with this ticket
http://www.sqlite.org/src/info/c92ecff2ec5f1784 could be a good moment
to acknowledge the proble
On Wed, Jan 11, 2017 at 11:11 AM, Rael Bauer wrote:
> I am trying to compile the latest amalgamation (3160200) as a dll on
> Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll
> compiles fine, however trying to use this dll in various tools (Delphi)
> results in the error "sqli
Hi,
I am trying to compile the latest amalgamation (3160200) as a dll on
Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll
compiles fine, however trying to use this dll in various tools (Delphi)
results in the error "sqlite3_open not found".
I tried compiling with:
cl sql
46 matches
Mail list logo
