[sqlite] Documentation inconsistency

[Tony Papadimitriou]

From here: https://www.sqlite.org/faq.html#q5

> (16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round 
> up?

This example seems to be no longer valid.  Although the explanation is still 
valid in general, the particular example “SELECT ROUND(9.95,1)” actually 
returns 10.0

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Issue (not bug) with generated columns

[Tony Papadimitriou]

Consider this:

.print Issue with generated columns

create table a(n,s as (n+1));
insert into a values(1),(2),(3);
select * from a;

create table b as select * from a;
.print table b converted the generated column into a regular column
select * from b;

delete from a;
insert into a select * from b;
select * from a;

.print Fails as there are two real columns in b but only one in a
-- Error: near line 12: table a has 1 columns but 2 values were supplied


It’s common practice (for some of us, at least) to copy a table to another for 
manipulation,
and then copy the finished work back to the original table.

With generated columns the new table gets the generated columns as regular 
columns (no complains).
Now, copying back to the original table produces an error as there is a 
mismatch in the number of real columns.

Of course one may specify each and every column manually (and there could many 
of them).

Suggestions:

1. It’d be nice to have some way to specify `select *` that would ignore 
generated columns.
Maybe, `select real *` or something of that sort to indicate we want `*` to 
select only actual columns, not virtual.

2. Another possibility would be, when copying over virtual columns, the column 
to be counted (for position) but data ignored as it’s read-only.

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Issue (not bug) with generated columns

[Tony Papadimitriou]

Consider this:

.print Issue with generated columns

create table a(n,s as (n+1));
insert into a values(1),(2),(3);
select * from a;

create table b as select * from a;
.print table b converted the generated column into a regular column
select * from b;

delete from a;
insert into a select * from b;
select * from a;

.print Fails as there are two real columns in b but only one in a
-- Error: near line 12: table a has 1 columns but 2 values were supplied


It’s common practice (for some of us, at least) to copy a table to another for 
manipulation,
and then copy the finished work back to the original table.

With generated columns the new table gets the generated columns as regular 
columns (no complains).
Now, copying back to the original table produces an error as there is a 
mismatch in the number of real columns.

Of course one may specify each and every column manually (and there could many 
of them).

Suggestions:

1. It’d be nice to have some way to specify `select *` that would ignore 
generated columns.
Maybe, `select real *` or something of that sort to indicate we want `*` to 
select only actual columns, not virtual.

2. Another possibility would be, when copying over virtual columns, the column 
to be counted (for position) but data ignored as it’s read-only.

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quick way to determine optimal page size?

[Tony Papadimitriou]

Tens of databases (from a few MBs to almost GB), so it's good to keep them 
at their minimum size (for disk and backup savings).


I often save several megabytes by going to the 'right' size, eg., just today 
I went from ~110MB down to ~80MB in one of them ('vacuum'ed before and after 
so it's just the page size making this difference).  Sometimes, very small 
page sizes give best results, sometimes the other way around.


Some databases do well in the same page size as new data is added, but for 
some others you need to recalculate as their content changes.
Still, you can't know in advance which ones can do better unless you 
actually try it.  And, that's the main problem.
I have to try with ~100 DBs to get a significant benefit in just a few of 
them (about 5-10), until next time.


Anyway, I thought I'd ask.

-Original Message- 
From: David Raymond

Sent: Wednesday, July 31, 2019 10:48 PM
To: SQLite mailing list
Subject: Re: [sqlite] Quick way to determine optimal page size?

Not that I'm aware of no. How much of a difference are you seeing for your 
database size depending on the page size you try?


-Original Message-
From: sqlite-users  On Behalf 
Of Tony Papadimitriou

Sent: Wednesday, July 31, 2019 3:29 PM
To: General Discussion of SQLite Database 


Subject: [sqlite] Quick way to determine optimal page size?

Instead of brute force “pragma page_size=xxx; vacuum;” for each page size 
and each database to determine which one produces the smallest file, is 
there some quicker way?


Thanks.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Quick way to determine optimal page size?

[Tony Papadimitriou]

Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and 
each database to determine which one produces the smallest file, is there some 
quicker way?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Double CTRL-C in shell get you completely out

[Tony Papadimitriou]

Recently CTRL-C was improved to abort the query and stay in the CLI.  This is 
very good.

However, if (accidentally, e.g., key bounce) a second CTRL-C is entered, it 
will escape back to the console.

Could it be changed so that either:

1. Only CTRL-D (Linux) or CTRL-Z (Windows) is used to exit the CLI

-- or --

2. If doing CTRL-C at the CLI prompt when nothing’s running to first confirm 
(like ‘Are you sure?’ with a No default, so that an explicit ‘Y’ has to be 
entered)?

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unexpected/undocumented REPLACE() behavior

[Tony Papadimitriou]

Example:

--- CUT ---
create table t(s text);
insert into t values ('1'),('null'),('3');

.print 'BEFORE'
select rowid,* from t;
update t set s = replace(s,'null',null)
--where s = 'null'  --adding this works of course but that’s not my point
;

.print 'AFTER'
select rowid,* from t;
--- CUT ---

The documentation says: “The replace(X,Y,Z) function returns a string formed by 
substituting string Z for every occurrence of string Y in string X. The BINARY 
collating sequence is used for comparisons. If Y is an empty string then return 
X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior 
to processing.”

“substituting string Z for every occurrence of string Y” implies that if there 
is no occurrence of string Y nothing should happen to the original string, 
right?

Accordingly, my expectation is that either:
1. null will remain null as there is not really a string (even empty) that can 
truly represent it, or
2. we allow null to be converted to empty string so that the “returns a string” 
requirement can be satisfied.

or, maybe
3. using null for the Z part gives an error.

In either [1] or [2] above, however, if the target string (Y part) is not 
found, the result should be unaltered.
The replacement seems to occur regardless of the target being found or not.

... and everything becomes null.

Note: I know SQLite3 tries to mimic in some ways Postgres, so I tried with it 
also, and got the same unexpected behavior.
From a quick look, I haven’t seen anything in their documentation on REPLACE to 
justify it either.

I consider this behavior wrong, or (easier way out) the documentation should 
make a special note about null behaving the way it does.

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Change delimiter with CSV extension?

[Tony Papadimitriou]

Is it possible to change the delimiter with the CSV virtual table extension?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unclear about some shell command differences

[Tony Papadimitriou]

Can someone explain the behavioral differences of the following shell commands?

.backup ?DB? FILEBackup DB (default "main") to FILE
.clone NEWDB Clone data into NEWDB from the existing database
.save FILE   Write in-memory database into FILE

They all seem to create a new db from the db currently active.
(.backup can optionally copy an attached db but in the default case, is it 
different?)

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] readfile() enhancement request

[Tony Papadimitriou]

Me neither. Thanks for that tip.  It works great for my use case!

Thanks to all for their suggestions.  I also liked the getclip.bat script.

-Original Message- 
From: Donald Griggs

Sent: Friday, May 17, 2019 11:40 PM
To: SQLite mailing list
Subject: Re: [sqlite] readfile() enhancement request

Somehow I'd never noticed that.   Great feature!

On Fri, May 17, 2019 at 2:46 PM David Raymond 
wrote:


Are you on an OS with a working edit() function?
https://www.sqlite.org/cli.html#the_edit_sql_function

In Windows using notepad I can do this for example:

insert into t values ('simple field', edit('', 'notepad'));

You can even use it for multiple fields and it'll open one at a time

insert into t (field1, field2) values (edit('This is for field1',
'notepad'), edit('This is for field2', 'notepad'));


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Tony Papadimitriou
Sent: Friday, May 17, 2019 12:34 PM
To: SQLite mailing list
Subject: [sqlite] readfile() enhancement request

It’s quite often (for me, at least) the case I need to do something like
this from the command line:

>sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text
copied from some other app’)

The problem is the multi-line text cannot be copy-pasted directly into the
command line as the first newline will terminate the command.  So, I’ve
been using readline() like so:

First, save the copied text into some arbitrary file (e.g., xxx), and then
do

>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘xxx’))

What would be much easier is for readfile to accept standard input when
provided with no empty name,
i.e., readfile(‘’).

Then, it would read standard input (i.e., wait for me to type/paste my
text) until CTRL-Z/D.

---
>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘’))
My text typed/pasted here
...
...
CTRL-Z/D
---

This could also be used to capture directly into the db the output of some
other program without first having to save it to a file.

I’m not sure how multiple readfile(‘’) on the same command should be
handled, either not allowed, or they all get a copy of the same input.

Thanks.
___
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

[sqlite] readfile() enhancement request

[Tony Papadimitriou]

It’s quite often (for me, at least) the case I need to do something like this 
from the command line:

>sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text copied 
>from some other app’)

The problem is the multi-line text cannot be copy-pasted directly into the 
command line as the first newline will terminate the command.  So, I’ve been 
using readline() like so:

First, save the copied text into some arbitrary file (e.g., xxx), and then do

>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘xxx’))

What would be much easier is for readfile to accept standard input when 
provided with no empty name,
i.e., readfile(‘’).

Then, it would read standard input (i.e., wait for me to type/paste my text) 
until CTRL-Z/D.

---
>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘’))
My text typed/pasted here
...
...
CTRL-Z/D
---

This could also be used to capture directly into the db the output of some 
other program without first having to save it to a file.

I’m not sure how multiple readfile(‘’) on the same command should be handled, 
either not allowed, or they all get a copy of the same input.

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can't update repo

[Tony Papadimitriou]

Oops!  autonsync somehow was off.  Thanks and apologies for noise.

-Original Message- 
From: Richard Hipp 
Sent: Saturday, April 20, 2019 3:46 PM 
To: SQLite mailing list 
Subject: Re: [sqlite] Can't update repo 


On 4/20/19, Tony Papadimitriou  wrote:

My SQLite3 repo does not update to the latest.  It stopped at 2019-04-17
[a3ab588329]

c:\sqlite3>fossil up


What happens if you try these commands:

   fossil pull
   fossil up trunk

--
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can't update repo

[Tony Papadimitriou]

My SQLite3 repo does not update to the latest.  It stopped at 2019-04-17 
[a3ab588329]

c:\sqlite3>fossil up
---
checkout: 03f2e78899fad99b0a0951b3a408268276954d4c 2019-04-17 21:12:05 UTC
tags: trunk
comment:  Small performance and size optimization in sqlite3WalFindFrame(). 
(user: drh)
changes:  None. Already up-to-date

I’m using REMOTE https://www.sqlite.org/src as per 
https://www.sqlite.org/src/doc/trunk/README.md#vauth

Is this correct?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

[Tony Papadimitriou]

True, but SQLite3 is known to provide several conveniences that are not 
necessarily standard SQL.


-Original Message- 
From: Simon Slavin

Sent: Friday, April 12, 2019 7:11 PM

On 12 Apr 2019, at 5:00pm, Tony Papadimitriou  wrote:


update t set s = replace(s, 'USA', '___'),
s = replace(s,'US','USA'),
s = replace(s,'___','USA');


To add to the answers other people gave, there's no set order for SQL to 
process these changes.  The SQL definition doesn't specify that they'll be 
done in any particular order.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected or wrong result and no warning/error, bug?

[Tony Papadimitriou]

I know this, thanks.  I simply made a test case that can be run in MySQL, 
Postgreq and SQLite3.


-Original Message- 
From: Chris Locke



create table t(s varchar(5));


Also note that SQLite doesn't 'understand' varchar (it uses text) and it
doesn't limit the entry to 5 characters.
This doesn't help your issue directly, but does highlight that you've not
read the SQLite documentation, and aren't creating tables properly.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unexpected or wrong result and no warning/error, bug?

[Tony Papadimitriou]

create table t(s varchar(5));

insert into t values('US'),('USA');

update t set s = replace(s, 'USA', '___'),
 s = replace(s,'US','USA'),
 s = replace(s,'___','USA');

select * from t;

-- Expected answer:
-- USA
-- USA
--
-- MySQL gets it right
-- Postgres prints error about setting the same column multiple times
-- SQLite3 (latest and older) no changes or wrong result but no error/warning
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestions: '.mode insert table' output to be within transaction, and REPLACE equivalent

[Tony Papadimitriou]

A couple of suggestions (I don’t know if they have come up before):

1. I often update the same table (e.g., a phone directory table) in various 
independent databases and it’s simpler to cascade the changes by doing this:

sql a.db “.mode insert table” “select * from table” | sql b.db

than with this:

sql a.db “.mode insert table_name” “select * from table_name” > xxx.sql
sql b.db
>begin;
>.read xxx.sql
>end;

The problem is the shell command method lacks the transaction and the process 
takes a lot longer for large tables.

So, my suggestion is for the ‘.mode insert table’ output to automatically 
include the beginning BEGIN; and the corresponding ending END; (or COMMIT;).

If the current behavior is also required as is then perhaps some option to do 
it this or that way so that this procedure can be accomplished with a single 
line shell command.

2. For the same case as above, but instead of INSERT to use REPLACE (or INSERT 
OR REPLACE) with a new mode, e.g.: .mode replace table_name

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

[Tony Papadimitriou]

If it helps, I can reproduce with the mentioned binary on Win7 but I cannot 
with my own compiled version (using MSVC).


-Original Message- 
From: Ralf Junker


On 23.01.2018 15:31, Richard Hipp wrote:


I'm still unable to reproduce this problem.


sqlite3.exe from this ZIP:

  https://www.sqlite.org/2018/sqlite-tools-win32-x86-322.zip

Running on Windows 7:

SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
9.22337203685478e+18
sqlite> SELECT CAST ('9223372036854775807' AS NUMERIC);
9223372036854775807

Notice the trailing white space which makes the difference.

Ralf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Tony Papadimitriou]

I reported this same issue in May 2017 
(http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
I too consider this a problem (diffing dumps is one good reason why) but, 
unfortunately, this was an intentional change by this check-in:


[7359fcac] Increase the number of significant digits in floating point 
literals on ".dump" output from the shell.


Tony
-Original Message- 
From: Iulian Onofrei


This is clearly a bug, as it outputs incorrect and different output from the
previous versions.

I have a "REAL" column with float values having up to 2 decimals, and using
".dump" with the latest version incorrectly converts them like this:

"0.05" -> "0.050002775"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[Tony Papadimitriou]

You can use .output file and .print for most cases (except those that need a 
calculated result) to simplify your script a bit.


-Original Message- 
From: Shane Dev


I have found a way achieve this purely in the SQLite shell. The trick is to
make all rows in tcout1 SQL statements and then execute them.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compilation failure for sqlite3_analyzer

[Tony Papadimitriou]

sqlite3_analyzer.c(207289): fatal error C1083: Cannot open include file: 
'tcl.h': No such file or directory
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[Tony Papadimitriou]

MySQL does not seem to have a problem with it.

-Original Message- 
From: Scott Robison


On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou <to...@acm.org> wrote:

create table t(s);
insert into t values ('A'),('A'),('B');

select group_concat(s,', ') from t group by null;   -- OK
select group_concat(distinct s) from t group by null;   -- OK
select group_concat(distinct s,', ') from t group by null;  -- ERROR

-- The moment the optional delimiter is given along with DISTINCT you get 
this error:

-- Error: near line 6: DISTINCT aggregates must have exactly one argument


A limitation of the SQL syntax. I suspect this might work for your
example use case: select group_concat(s, ',') from (select distinct s
as s from t) group by null;

Tested and confirmed here.
___
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] GROUP_CONCAT with DISTINCT bug

[Tony Papadimitriou]

Even simpler, then...
select group_concat(distinct 1,',');

-Original Message- 
From: petern 


Simpler one line test case also parses incorrectly:

WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c) FROM t;
"group_concat(DISTINCT c)"
1

WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c,',') FROM t;
Error: DISTINCT aggregates must have exactly one argument


On Tue, Jan 2, 2018 at 12:36 PM, Tony Papadimitriou <to...@acm.org> wrote:


create table t(s);
insert into t values ('A'),('A'),('B');

select group_concat(s,', ') from t group by null;   -- OK
select group_concat(distinct s) from t group by null;   -- OK
select group_concat(distinct s,', ') from t group by null;  -- ERROR

-- The moment the optional delimiter is given along with DISTINCT you get
this error:
-- Error: near line 6: DISTINCT aggregates must have exactly one argument

-- Thank you.
___
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] GROUP_CONCAT with DISTINCT bug

[Tony Papadimitriou]

create table t(s);
insert into t values ('A'),('A'),('B');

select group_concat(s,', ') from t group by null;   -- OK
select group_concat(distinct s) from t group by null;   -- OK
select group_concat(distinct s,', ') from t group by null;  -- ERROR

-- The moment the optional delimiter is given along with DISTINCT you get this 
error:
-- Error: near line 6: DISTINCT aggregates must have exactly one argument

-- Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA help

[Tony Papadimitriou]

Is there some way to get a list of all available pragma options from the 
SQLite3 shell?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 crash with NULL in snippet() function's 5th parameter

[Tony Papadimitriou]

(SQLite v3.21.0 with FTS5 enabled)

If instead of ‘’ (empty string) one uses NULL for the 5th parameter in the 
snippet() function it consistently crashes.

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] printf() with UTF-8 and \n \t format

[Tony Papadimitriou]

Great! Didn't think of the char() function at all.  (Although I would prefer 
a platform independent \n)


Thanks.

-Original Message- 
From: Keith Medcalf



So, is there any way to advance to next line from a command line
printf()?


sqlite> select printf('%s%s%s', 'line 1', char(10), 'line 2');
line 1
line 2

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] printf() with UTF-8 and \n \t format

[Tony Papadimitriou]

-Original Message- 
From: R Smith


On 2017/12/19 8:37 PM, Tony Papadimitriou wrote:

A couple of questions about printf

1. Does it work with UTF-8? If so, how?


- Yes.
- Very nicely.


I'm using SQL v3.21 and UTF-8 does not work correctly.  (Not from the 
command line.)


I tried with latest trunk and it works fine.  Hmmm.
Let's try with the precompiled Windows binary for v3.21.0
It works.  Hmmm!
(...many, many trials later...)

Let's try with different default options.

Ta da!!!

When using the -column option (my own binary has this as default) the 
problem shows up.  With the official default of -list option the problem is 
not there.

And, it happens with the latest trunk, also.

So, that looks like a bug.

2. Does it understand \n and \t?  I put actual line breaks inside the 
string >which is OK if run from script file but it won’t work with 
one-liners on the >command-line.>


The \n, \t, \r etc. are really dependent on some factors (OS etc.).


Yes, I know all that.  The question was if it understands them, not how they 
might behave depending on OS.


When I use \t or \n I get actual \t and \n strings displayed instead of 
tabbing and advancing line, respectively.


So, is there any way to advance to next line from a command line printf()?


Ryan


Thanks 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] printf() with UTF-8 and \n \t format

[Tony Papadimitriou]

A couple of questions about printf

1. Does it work with UTF-8? If so, how?

2. Does it understand \n and \t?  I put actual line breaks inside the string 
which is OK if run from script file but it won’t work with one-liners on the 
command-line.

Thank you
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug with expression evaluation?

[Tony Papadimitriou]

-Original Message- 
From: J. King


Someone please correct me if I'm wrong, but I believe it's mandated by the 
SQL standard that integer division is used when both operands are integers.


I really don't know what the standard says, but here are two different 
opinions in implementation.


MySQL example:
mysql> select 1/2;
++
| 1/2|
++
| 0.5000 |
++
1 row in set (0.13 sec)

PostgreSQL example:
psql=# select 1/2;
?column?
--
   0
(1 row)


Your synthetic example doesn't use a fixed table, but if it did the easiest 
solution for you would probably be to define any columns where you need 
arbitrary precision as REAL rather than INTEGER, and SQLite column 
>affinity would do the rest.


SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has 
no affinity. "

It seems that 'no affinity' gets translated to integer affinity, then.

Is there a way to default to float?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a bug with expression evaluation?

[Tony Papadimitriou]

I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to 
zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100));
select column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from 
(values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  
(Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to 
default to float math rather than integer?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using computed column once in SELECT

[Tony Papadimitriou]

I don’t know if this has come up before.

Example:

select cast((julianday('now')-julianday(dob))/365.25 as int) age,
   87-cast((julianday('now')-julianday(dob))/365.25 as int) life_expectancy

(... assuming 87 year average life span)

This works, but as you see the age calculation has to be repeated in every 
column that needs it (and there could many more).

Why not be able to this instead?

select cast((julianday('now')-julianday(dob))/365.25 as int) age,
   87-age life_expectancy

Note that age is defined before it is referenced.

Apparently, ‘age’ can be used in a subsequent join but not a subsequent column 
definition.
Is there a technical limitation for this or simply an unimplemented feature?

BTW, is ‘now’ value locked during the query execution to avoid the possibility 
(however small) of two columns ending up with different age calculations (e.g., 
running during date crossover on someone’s birthday)?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integerprimary key

[Tony Papadimitriou]

Possible something like this may work:

create table fruit(id integer primary key, name text);

insert into fruit values
(1,'apple'),
(2,'pear'),
(3,'kiwi');

select * from fruit order by id;

begin;
update fruit set id = -id where id > 1;
update fruit set id = 1-id where id < 0;
end;

insert into fruit values(2,'banana');

select * from fruit order by id;

-Original Message- 
From: Shane Dev

Sent: Sunday, November 19, 2017 10:37 PM
To: SQLite mailing list
Subject: [sqlite] how into insert row into middle of table with 
integerprimary key


Let's say I have a table of fruit -

sqlite> .sch fruit
CREATE TABLE fruit(id integer primary key, name text);

with some entries -

sqlite> select * from fruit;
id|name
1|apple
2|pear
3|kiwi

Is there an easy way to insert 'banana' between apple and pear while still
maintaining a consistent order of the ID field?

desired result -

sqlite> select * from fruit;
1|apple
2|banana
3|pear
4|kiwi
___
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] Is this error expected?

[Tony Papadimitriou]

Strike the last, my bad.  It works correctly.  I forgot to define the id 
part.  So, just inconsistent.


select * from (values(1,12345)) join (values(1,54321)) using(column1);

-Original Message- 
From: Tony Papadimitriou

Sent: Sunday, November 12, 2017 10:37 PM
To: SQLite mailing list
Subject: Re: [sqlite] Is this error expected?

Now the problem seems to affect implicit column definitions, I tried the
following:

select * from (values(12345)) join (values(54321)) using(column1);

Does not produce any output (incorrectly IMO), but it also does not give any
error like the rowid case (inconsistent -- if we accept the error is the
correct approach).

-Original Message- 
From: Tony Papadimitriou


It's actually even simpler to show this (without CTE defining a rowid):

create table t1(v); insert into t1 values(12345);
create table t2(v); insert into t2 values(54321);

select * from t1 join t2 using(rowid);  -- THIS ONE FAILS
select * from t1 join t2 on t1.rowid = t2.rowid;  -- THIS ONE WORKS

I understand what you say and it does seem to work as you described but I'm
not sure I can agree this is how it *should* work.
The fact that rowid is an implicit column should not matter, IMO.

If both t1.rowid and t2.rowid (being implicitly defined columns) can be
found, they should (IMO) also be found by USING as these are logically
equivalent.

The only difference of the two forms is that the result of a SELECT * will
include this column once with USING, and twice with ON ... = ...

Thank you for your response.

-Original Message- 
From: Keith Medcalf


Not really.  Table TAB does not contain a column named rowid.  tab.rowid
refers to the non-column representing the row number of a row in the table.

If you declared table TAB to actually have a column called rowid then it
would work just fine, even if that column rowid still contained the row
number of the row in the table.

sqlite> create table tab(rowid integer primary key, val);
sqlite> insert into tab(val) values(1);
sqlite> with t(rowid,val) as (
  ...>   select rowid,val from tab
  ...>   union
  ...>   select rowid,tab.val
  ...> from tab join t using (rowid)
  ...> )
  ...> select * from t;
1|1

However, in this case your (below) query will fail since you now have a
column called "rowid" in each table, and you did not specify which one you
wanted to select ...

---
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 Tony Papadimitriou
Sent: Sunday, 12 November, 2017 11:21
To: General Discussion of SQLite Database
Subject: [sqlite] Is this error expected?

An example to reproduce a problem I noticed:


create table tab(val);
insert into tab values(1);

with t(rowid,val) as (
 select rowid,val from tab
 union
 select rowid,tab.val
   from tab join t on t.rowid = tab.rowid
   --from tab join t using(rowid)
 )
select * from t;


If the 1st FROM is replaced by the 2nd commented out FROM ... USING
SQLite3 throws this error:

Error: near line 4: cannot join using column rowid - column not
present in both tables

Aren’t these two FROM clauses practically equivalent?

(SQLite version 3.21.0 2017-10-24 18:55:49)
___
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 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this error expected?

[Tony Papadimitriou]

Now the problem seems to affect implicit column definitions, I tried the 
following:


select * from (values(12345)) join (values(54321)) using(column1);

Does not produce any output (incorrectly IMO), but it also does not give any 
error like the rowid case (inconsistent -- if we accept the error is the 
correct approach).


-Original Message- 
From: Tony Papadimitriou


It's actually even simpler to show this (without CTE defining a rowid):

create table t1(v); insert into t1 values(12345);
create table t2(v); insert into t2 values(54321);

select * from t1 join t2 using(rowid);  -- THIS ONE FAILS
select * from t1 join t2 on t1.rowid = t2.rowid;  -- THIS ONE WORKS

I understand what you say and it does seem to work as you described but I'm
not sure I can agree this is how it *should* work.
The fact that rowid is an implicit column should not matter, IMO.

If both t1.rowid and t2.rowid (being implicitly defined columns) can be
found, they should (IMO) also be found by USING as these are logically
equivalent.

The only difference of the two forms is that the result of a SELECT * will
include this column once with USING, and twice with ON ... = ...

Thank you for your response.

-Original Message- 
From: Keith Medcalf


Not really.  Table TAB does not contain a column named rowid.  tab.rowid
refers to the non-column representing the row number of a row in the table.

If you declared table TAB to actually have a column called rowid then it
would work just fine, even if that column rowid still contained the row
number of the row in the table.

sqlite> create table tab(rowid integer primary key, val);
sqlite> insert into tab(val) values(1);
sqlite> with t(rowid,val) as (
  ...>   select rowid,val from tab
  ...>   union
  ...>   select rowid,tab.val
  ...> from tab join t using (rowid)
  ...> )
  ...> select * from t;
1|1

However, in this case your (below) query will fail since you now have a
column called "rowid" in each table, and you did not specify which one you
wanted to select ...

---
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 Tony Papadimitriou
Sent: Sunday, 12 November, 2017 11:21
To: General Discussion of SQLite Database
Subject: [sqlite] Is this error expected?

An example to reproduce a problem I noticed:


create table tab(val);
insert into tab values(1);

with t(rowid,val) as (
 select rowid,val from tab
 union
 select rowid,tab.val
   from tab join t on t.rowid = tab.rowid
   --from tab join t using(rowid)
 )
select * from t;


If the 1st FROM is replaced by the 2nd commented out FROM ... USING
SQLite3 throws this error:

Error: near line 4: cannot join using column rowid - column not
present in both tables

Aren’t these two FROM clauses practically equivalent?

(SQLite version 3.21.0 2017-10-24 18:55:49)
___
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] Is this error expected?

[Tony Papadimitriou]

It's actually even simpler to show this (without CTE defining a rowid):

create table t1(v); insert into t1 values(12345);
create table t2(v); insert into t2 values(54321);

select * from t1 join t2 using(rowid);  -- THIS ONE FAILS
select * from t1 join t2 on t1.rowid = t2.rowid;  -- THIS ONE WORKS

I understand what you say and it does seem to work as you described but I'm 
not sure I can agree this is how it *should* work.

The fact that rowid is an implicit column should not matter, IMO.

If both t1.rowid and t2.rowid (being implicitly defined columns) can be 
found, they should (IMO) also be found by USING as these are logically 
equivalent.


The only difference of the two forms is that the result of a SELECT * will 
include this column once with USING, and twice with ON ... = ...


Thank you for your response.

-Original Message- 
From: Keith Medcalf


Not really.  Table TAB does not contain a column named rowid.  tab.rowid 
refers to the non-column representing the row number of a row in the table.


If you declared table TAB to actually have a column called rowid then it 
would work just fine, even if that column rowid still contained the row 
number of the row in the table.


sqlite> create table tab(rowid integer primary key, val);
sqlite> insert into tab(val) values(1);
sqlite> with t(rowid,val) as (
  ...>   select rowid,val from tab
  ...>   union
  ...>   select rowid,tab.val
  ...> from tab join t using (rowid)
  ...> )
  ...> select * from t;
1|1

However, in this case your (below) query will fail since you now have a 
column called "rowid" in each table, and you did not specify which one you 
wanted to select ...


---
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 Tony Papadimitriou
Sent: Sunday, 12 November, 2017 11:21
To: General Discussion of SQLite Database
Subject: [sqlite] Is this error expected?

An example to reproduce a problem I noticed:


create table tab(val);
insert into tab values(1);

with t(rowid,val) as (
 select rowid,val from tab
 union
 select rowid,tab.val
   from tab join t on t.rowid = tab.rowid
   --from tab join t using(rowid)
 )
select * from t;


If the 1st FROM is replaced by the 2nd commented out FROM ... USING
SQLite3 throws this error:

Error: near line 4: cannot join using column rowid - column not
present in both tables

Aren’t these two FROM clauses practically equivalent?

(SQLite version 3.21.0 2017-10-24 18:55:49)
___
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] Is this error expected?

[Tony Papadimitriou]

An example to reproduce a problem I noticed:


create table tab(val);
insert into tab values(1);

with t(rowid,val) as (
  select rowid,val from tab
  union
  select rowid,tab.val
from tab join t on t.rowid = tab.rowid
--from tab join t using(rowid)
  )
select * from t;


If the 1st FROM is replaced by the 2nd commented out FROM ... USING
SQLite3 throws this error:

Error: near line 4: cannot join using column rowid - column not present in both 
tables

Aren’t these two FROM clauses practically equivalent?

(SQLite version 3.21.0 2017-10-24 18:55:49)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.19.3 containing an important bug fix

[Tony Papadimitriou]

Does this also affect FOSSIL, or is auto vacuum never used there?

-Original Message- 
From: D. Richard Hipp

Sent: Thursday, June 08, 2017 6:57 PM
To: sqlite-annou...@mailinglists.sqlite.org ; 
sqlite-users@mailinglists.sqlite.org

Subject: [sqlite] Version 3.19.3 containing an important bug fix

A bug in the auto_vacuum logic for SQLite versions 3.16.0 through 3.19.2 can 
(rarely) lead to database corruption.  SQLite version 3.19.3 has just been 
released to fix this bug.


   https://sqlite.org/
   https://sqlite.org/download.html

See https://www.sqlite.org/src/info/fda22108 for more information about the 
bug.  The problem can only arise on databases that have auto_vacuum enabled. 
Nevertheless, all users are encouraged to update.  If you have database 
files that are in auto_vacuum mode, you should consider running VACUUM on 
those databases after updating, in order to clear any database 
inconsistencies that might have been introduced by this bug.


If you encounter any problems with this release, please send email to 
sqlite-users@mailinglists.sqlite.org or directly to me.  Thanks.


--
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Tony Papadimitriou]

Apparently, this was an intentional change by this check-in :

[7359fcac] Increase the number of significant digits in floating point literals 
on ".dump" output from the shell.

I don't know what problem this change actually solved (as in my case it 
introduced one) but for my own copy I changed %!.20g to %!.16g and everything 
seems to be OK now without compromising accuracy (I hope).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .DUMP displays floats differently from SELECT

[Tony Papadimitriou]

While trying to search/replace some text from an SQLite3 dump I noticed that, 
unfortunately, .DUMP does not produce the exact same numbers as a plain SELECT 
on the same values.

I know all about expected floating point inaccuracies, but I don’t see why it 
should matter in this case as we have two different places in the same app 
(SQLite3) where the same number is ‘displayed’ using whatever default format.  
IMO, in both places the same number should display exactly the same (i.e., 
using the same format), both for consistency and easy verification between dump 
and actual database.

Below is a sample script and its output that demonstrate the issue (using 
SQLite 3.18.0 2017-03-28 18:48:43):

SCRIPT:

create table xxx(x);
insert into xxx values(1.23);
select * from xxx;
.dump

OUTPUT:

1.23  
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO xxx VALUES(1.2299822);
COMMIT;

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can not get data

[Tony Papadimitriou]

LIKE is case insensitive, while = is not.

-Original Message- 
From: lizhu...@whaty.com


when I query :
select * from downloadVideo_table where sectionId = 
'402814a34b823b23014bfc228fe9588c'


then I query the length of 'sectionId', it is 32.that is right.
select length(sectionId) from downloadVideo_table where site_code = 'jyzdy'

at last,
I query this:
select * from downloadVideo_table where sectionId like 
'402814a34b823b23014bfc228fe9588c%'

or this:
select * from downloadVideo_table where sectionId like 
'%402814a34b823b23014bfc228fe9588c'

I saw the data right.
it is so weird.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which pragmas are persistent?

[Tony Papadimitriou]

-Original Message- 
From: no...@null.net 


What would be useful (at least via the shell CLI) is a "list_pragmas"
pragma that shows for example something like this:

   sqlite> PRAGMA list_pragmas;


PRAGMA list;

would be less redundant.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another .DUMP issue with v 3.18.0

[Tony Papadimitriou]

-Original Message- 
From: Simon Slavin


I notice that the command on those lines is "sql" rather than the "sqlite3" 
I would expect.  Tony, do you get the same error if you type those things 
into the SQLite3 shell, rather than feeding them in using >your command 
shell ?  It works fine for me.  If not, what shell are you using ?


I'm also using SQLite version 3.18.0 2017-03-28 18:48:43

SQL is the latest release version of SQLITE3 renamed to SQL so that
1. it does not collide with the latest trunk which I keep as SQLITE3 for 
testing, and

2. because it's faster/easier to type all the time.

I noticed you have the same version but there is no column in the INSERT 
statement.  This got me thinking what is different.  And, I remembered that 
I have a headers on default on mine.


So, please try this instead:

sql xxx.db ".headers on" ".dump" 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another .DUMP issue with v 3.18.0

[Tony Papadimitriou]

Here's the dump I get:

c:\temp>sql xxx.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(`a b c`);
INSERT INTO xxx(a b c) VALUES(1);
COMMIT;

Do you see the problem?
INSERT specifies column name without quotes.

However, I also tried with latest trunk, and I get a different dump:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(`a b c`);
INSERT INTO xxx VALUES(1);
COMMIT;

The column name is missing.  Hmm!
This is on Win7 machine if it matters.

-Original Message- 
From: Keith Medcalf

Sent: Saturday, April 08, 2017 2:08 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Another .DUMP issue with v 3.18.0


Try using different quotes, not ones that have meaning to the shell.


--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Tony Papadimitriou
Sent: Friday, 7 April, 2017 17:04
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Another .DUMP issue with v 3.18.0

-Original Message-
From: Josh Hunsaker

>On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote:
>> On 4/7/17, Tony Papadimitriou wrote:
>>>
>>> sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx
values('1')"
>>> sql .dump xxx.db | sql
>>>
>>
>> I'm unable to repro.
>>

>Is this possibly because the shell that Tony is using is evaluating `time
>zone`?

>I can repro, but in my case, the obvious cause is that the `time zone`
>portion gets evaluated to an empty string.

Well, try this instead:
sql xxx.db "CREATE TABLE xxx(`a b c`)" "insert into xxx values(1)"

The problem is the column has spaces and it's not quoted.

___
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] Another .DUMP issue with v 3.18.0

[Tony Papadimitriou]

-Original Message- 
From: Josh Hunsaker



On Fri, Apr 7, 2017 at 3:02 PM, Richard Hipp wrote:

On 4/7/17, Tony Papadimitriou wrote:


sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')"
sql .dump xxx.db | sql



I'm unable to repro.



Is this possibly because the shell that Tony is using is evaluating `time 
zone`?



I can repro, but in my case, the obvious cause is that the `time zone`
portion gets evaluated to an empty string.


Well, try this instead:
sql xxx.db "CREATE TABLE xxx(`a b c`)" "insert into xxx values(1)"

The problem is the column has spaces and it's not quoted. 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Another .DUMP issue with v 3.18.0

[Tony Papadimitriou]

The following dump cannot be used to rebuild the database because the column 
name is not properly quoted giving an error.

To reproduce:

sql xxx.db "CREATE TABLE xxx(`time zone`)" "insert into xxx values('1')"
sql .dump xxx.db | sql

Error: near line 4: near "zone": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[Tony Papadimitriou]

Here's one possibility (simplified table for example):

create table desktops(
 indexno integer not null unique,
 nametextnot null primary key
);

insert into desktops values
(1,'CompA'),
(2,'CompB'),
-- we want to insert new record here bumping all above by one
(3,'CompD'),
(4,'CompE');

select * from desktops order by indexno;  --BEFORE

-- Assuming largest indexno is initially N (in this example 4)
-- Add N+1 (or N+x where x > 0) to all records over and including
  the one spot you want freed (in this example 3)
-- Subtract N from all over N+1 you added previously
-- Insert the new record into the now empty slot

begin;
update desktops set indexno = indexno + 5 where indexno >=3;
update desktops set indexno = indexno - 4 where indexno > 5;
insert into desktops values(3,'CompC');
end;

select * from desktops order by indexno;  --AFTER

-Original Message- 
From: Cecil Westerhof

Sent: Saturday, January 21, 2017 12:54 PM
To: SQLite mailing list
Subject: [sqlite] How to circumvent UNIQUE constraint

I have the following (work in progress) table:
CREATE  TABLE desktops(
   nameTEXTNOT NULL PRIMARY KEY,
   indexNo INTEGER NOT NULL UNIQUE,
   value   TEXTNOT NULL UNIQUE,
   waitSeconds INTEGER NOT NULL
);

​I want to insert a record in front of​ the others, so indexNo has to be
increased with one for all records. I would think that this would work:
UPDATE desktops
SET indexNo = indexNo  + 1

But it does not, it gives:
Error: UNIQUE constraint failed: desktops.indexNo

​How can I make this work?

--
Cecil Westerhof
___
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] Autoincrement sequence not updated by UPDATE

[Tony Papadimitriou]

Well, I got different results (so maybe it's version related).  I tried it 
on MySQL v5.7.16-log:

++--+
| id | v|
++--+
|  2 | two  |
| 10 | one  |
++--+
++--+
| id | v|
++--+
| 10 | one  |
| 40 | two  |
++--+
++---+
| id | v |
++---+
|  3 | three |
| 10 | one   |
| 30 | two   |
++---+

-Original Message- 
From: Simon Slavin

Sent: Tuesday, November 01, 2016 1:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE


On 1 Nov 2016, at 11:44am, Andy Ling  wrote:


It remembers..


Ah, neat.  Thanks for the testing.  And the "show create table" command you 
used makes it clear that the engine keeps a record for the table. 
Apparently a single value for the table's primary key rather than a value 
for each "INTEGER PRIMARY KEY AUTOINCREMENT" column.


I'd be interested in the equivalents for progresql and Oracle, if anyone is 
set up to find out.  Maybe we could work up a table like the one in




Simon.
___
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] Podcast with Dr Hipp: SQLite history, success and funding

[Tony Papadimitriou]

>> > 1. Why SQLite is popular.
>>
>> The answers to those question mentioned in the podcast may be good ones 
>> but I
>> think the main reason is that it's free.  Completely, unmistakably, free.
>
>Necessary but not sufficient.
>It's free, and the license is as non-restrictive as it is possible to be.

Certainly true!  However, there are tons of free & liberally licensed 
software out there, many (most?) of which are failures in terms of public 
acceptance.

So, even these two alone do not seem to be entirely sufficient.

But SQLite has one greater attribute.  It comes with a proven commitment of 
EXCELLENT support & maintenance.  A true quality product.  Bugs are killed 
practically instantly after being discovered, and new features added on a 
regular basis.  You rarely get this kind of support even from paid software.

(Many open source projects have bugs waiting for months or years for someone 
to be bothered to fix, often driving people away!)

To sum it up, a big thanks to Richard and his team!

[sqlite] .DUMP output compatibility

[Tony Papadimitriou]

Windows!

-Original Message- 
From: Richard Hipp
Sent: Friday, May 06, 2016 1:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] .DUMP output compatibility

On 5/5/16, Tony Papadimitriou  wrote:
> Is it possible for .DUMP to produce table/field names quoted with `
> (backquote) instead of ? (double quote) for compatibility with MySQL?
> Or is this already adjustable by some setting I missed?

Pipe the output through sed (https://en.wikipedia.org/wiki/Sed)
-- 
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

[sqlite] .DUMP output compatibility

[Tony Papadimitriou]

Is it possible for .DUMP to produce table/field names quoted with ` (backquote) 
instead of ? (double quote) for compatibility with MySQL?
Or is this already adjustable by some setting I missed?

Thank you.

[sqlite] CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP

[Tony Papadimitriou]

BTW, the following

with xxx(current_date,date) as (
  select '2000-01-01',current_date
  )
  select *,current_date,date from xxx;

outputs:

current_date  datecurrent_date  date  
  --    --
2000-01-012016-02-10  2016-02-102016-02-10

As you can see the user defined current_date is masked by SQLite3's built-in 
name.  (If this expected, at least a warning about using a reserved name would 
be nice.)
To me, however, the user-defined name should mask the built-in one.

Regards.

[sqlite] SQLDIFF problem

[Tony Papadimitriou]

You're probably right.

You can try this shorter schema and get the same problem.

create table xxx("" text);

However, my point was that if SQLite3 allows it, SQLDIFF should be able to 
handle it also.

Thanks.

-Original Message- 
From: Tim Streater
Sent: Monday, November 30, 2015 12:41 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLDIFF problem

On 29 Nov 2015 at 18:55, tonyp at acm.org wrote:

> Here?s a report about an SQLDIFF utility problem.
> sqldiff: SQL statement error: near "IS": syntax error
>
> To reproduce, put the schema below (produced automatically by .IMPORT from 
> a
> CSV file so don?t bother with its appearance ? but it works in sqlite3)
> inside some file like xxx.sql, and then do the following (I tried on a 
> Win7
> machine):
>
> sqlite3 xxx.db < xxx.sql
> sqldiff xxx.db xxx.db
>
> SCHEMA (xxx.sql)
> 
> CREATE TABLE raw(
>  "Date" TEXT,

...

>  "Contact Phone Number" TEXT,
>  "" TEXT
> );

Well I don't imagine it's going to like:

  "" TEXT


--
Cheers  --  Tim






___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

[sqlite] Small bug in ".dump", ".schema" and ".fullschema"

[Tony Papadimitriou]

I tried your example.

'.schema' and '.fullschema' do show the problem you describe.

However, for me, '.dump' worked correctly placing the semicolon on a line by 
itself right after the comment.

- Original Message - 
From: "sqlite-mail" 
To: 
Sent: Wednesday, August 12, 2015 1:14 AM
Subject: [sqlite] Small bug in ".dump", ".schema" and ".fullschema"


> Hello !
>
> Working with sqlite3 I noticed that sqlite3 ".dump", ".schema" and
> ".fullschema" outputs the contents of the field "sql" stored in
> "sqlite_master" and if the sql statement ends with a comment the resulted
> dump will be invalid see example:
>
> CREATE VIEW "event_event_ticket_list_view" AS
> SELECT a."id", a."name", a."price", a."deadline", a."seats_max",
> a."product_id", a."event_id"
> FROM "event_event_ticket" AS a
> --LEFT JOIN "product_product" AS b ON a."product_id" = b."id"; <
> here is the problem

Re: [sqlite] Whish List for 2015

[Tony Papadimitriou]

Can you explain a bit more?

* Who is 'we'?  Is this an open source project somewhere?
* How is the combined Lua & SQLite3 executable created?  Do you have some 
makefile (for Windows and Linux) or some instructions for manual 
compilation?


Thanks.

-Original Message- 
From: Nelson, Erik - 2

Sent: Tuesday, December 23, 2014 5:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Whish List for 2015

Tony Papadimitriou Sent on Tuesday, December 23, 2014 10:26 AM


Problem 1: Currently, any scripts have to be stored outside the database
in separate files meaning there one-file-holds-everything deal is lost
(and organizing these according to the database they refer also becomes
a bit of an issue), and



Problem 2: Dynamically passing parameters to those scripts is not very
easy without using some kind of external scripting (like Lua, Python,
etc.) which is not likely to exist on someone else's computer, so
transferring just the database file is not enough, as the recipient
also need to install other components besides SQLite3.


FWIW, we just compile the lua interpreter together with sqlite3.

Installing the resulting binary should take care of both of your 
requirements.


Erik

--
This message, and any attachments, is for the intended recipient(s) only, 
may contain information that is privileged, confidential and/or proprietary 
and subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Whish List for 2015

[Tony Papadimitriou]

OK, if everyone if putting down their wish list for 2015, here's mine:

Using the SQLite shell is very convenient for quick retrieval or 
modification of data any SQLite database but there are two significant 
problems (in my view) that make this of very limited utility, and  this 
proposed feature would solve (to a 'lite' extent):


Problem 1: Currently, any scripts have to be stored outside the database in 
separate files meaning there one-file-holds-everything deal is lost (and 
organizing these according to the database they refer also becomes a bit of 
an issue), and


Problem 2: Dynamically passing parameters to those scripts is not very easy 
without using some kind of external scripting (like Lua, Python, etc.) which 
is not likely to exist on someone else's computer, so transferring just the 
database file is not enough, as the recipient also need to install other 
components besides SQLite3.


Proposal:

Minimal stored procedure functionality -- or, maybe call it stored query or 
stored script since I would not expect any new programming constructs like 
IF ... THEN ... ELSE
All that is needed is a way to put one or more SQL sentences in a single 
file (stored inside the database file in some system table) and call them as 
a single statement with optional parameters (e.g., call from shell with 
something like @procedure_name parm1, parm2, parm3) where parameters are 
simple positional text replacement -- like a simple text replacement macro 
processor would do.


Example:

CREATE PROC sample AS (
SELECT table1.* FROM table1,...,tableN
 WHERE ... possibly complicated join ...
 AND last_name like :1
 ORDER BY :2;
);

@sample 'Smith%',tax_id

would become:

SELECT table1.* FROM table1,...,tableN
 WHERE ... possibly complicated join ...
 AND last_name like 'Smith%'
 ORDER BY last_name,tax_id;

(If you don't like CREATE PROC, make it CREATE SCRIPT or something else, 
although I think PROC is good enough as it allows for possible future 
expansion with more capabilities -- wish lists for 2016 and beyond.)


-Original Message- 
From: Philip Warner

Sent: Tuesday, December 23, 2014 2:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Whish List for 2015


Native UNICODE?

Dates? UUID (stored as bytes, displayed as string)? I know...new data type
representations are unlikely.

Triggers with declared variables that are preserved across invocations?
(...using temp tables is a pita for storing, say, 5 numbers)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted bylength

[Tony Papadimitriou]

I forgot to also factor out the ORDER BY.  So, the updated query is:

-
with t as (
 select GroupName, JobName, Start, End, Status,
(strftime('%s', End) - strftime('%s', Start)) as Length
   from ReportJobs
   where PlanDate = '2014-02-13'
   order by Length desc
 )
select * from (select * from t where GroupName like 'GRP01%' LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP04%' LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP12%' LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP15%' LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP20%' LIMIT 10);
-

The double select [select * from  (select * from t ...] is required because 
LIMIT is only allowed at the end of a UNION, so you need a sub-query to 
overcome this.  (BTW, this seems like an artificial restriction that could 
be removed, but I could be wrong.) 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

[Tony Papadimitriou]

One obvious shortcut would be to factor out the common part of the select 
using WITH


(Hopefully accurate) example:

with t as (
 select GroupName, JobName, Start, End, Status, (strftime('%s', End) - 
strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13'

 )
select * from (select * from t where GroupName like 'GRP01%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP04%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP12%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP15%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP20%' ORDER BY Length 
DESC LIMIT 10);


-Original Message- 
From: pihu...@free.fr

Sent: Tuesday, October 14, 2014 12:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

Hello!

I'm trying to find a way to reduce the length of the following query using 
SQLite:


select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP01%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP04%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP12%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP15%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP20%' ORDER BY Length 
DESC LIMIT 10);


I want to select the ten longest jobs for five different groups (GRP01%, 
GRP04%, GRP12%, GRP15% and GRP20%). I can't find a solution without using 
this "UNION ALL" trick.


Contents in 'Start' and 'End' columns are ISO 8601 formatted.
I use "(strftime('%s', End) - strftime('%s', Start)) as Length" to calculate 
the length of each job, then "ORDER BY Length DESC LIMIT 10" for each group.


Do you know a simplest/better way to perform this query?
Thanks


Pierre
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there equivalent to MySQL IF() function?

[Tony Papadimitriou]

Totally agree.  And this is exactly why my natural first reaction was to 
expect a syntax error, because in my mind you can't compare a non-boolean 
expr to a boolean expr.


I need to start C-ing everything differently.  Not easy... :)

Thank you (and I didn't imply there was malice, ... just a pointless check)

-Original Message- 
From: RSmith

Sent: Tuesday, October 07, 2014 2:33 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there equivalent to MySQL IF() function?


a Boolean value is dichotomous and has no other possible state than the 
base 2 states - so evaluating it and comparing it to an integer supporting 
2^64 states or

indeed any other type of multi-value field is not useful


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there equivalent to MySQL IF() function?

[Tony Papadimitriou]

Well, it is exactly because I understand the difference between a boolean 
expression and a non-boolean expression, along with a bit misleading 
documentation, that I got confused.


It is usually those who are used to only the C-like treatment of a boolean 
result as being equivalent to an integer result that have no problem with 
this interpretation. :)


Thank you for clarifying.

-Original Message- 
From: RSmith

Sent: Tuesday, October 07, 2014 2:09 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there equivalent to MySQL IF() function?


Do you understand the difference between a boolean result and a value 
result from an expression?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there equivalent to MySQL IF() function?

[Tony Papadimitriou]

OK, I think I managed to figure out what it means by reading a little more 
about the two CASE cases.


'CASE expr WHEN' compares the base expr with the WHEN expr, whereas
'CASE WHEN' compares the WHEN expr to true

So, the difference is more than just how many times 'x' is evaluated. 
(Maybe the documentation needs some improvement.)


Thanks to all.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there equivalent to MySQL IF() function?

[Tony Papadimitriou]

You're right, ... but in that page it says:

The only difference between the following two CASE expressions is that the x 
expression is evaluated exactly once in the first example but might be 
evaluated multiple times in the second:

CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END 

So, my understanding is that the second CASE (for my example) should give the 
same result as the first CASE.

-Original Message- 
From: Constantine Yannakopoulos 
Sent: Tuesday, October 07, 2014 1:36 PM 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Is there equivalent to MySQL IF() function? 

On Tue, Oct 7, 2014 at 1:13 PM, Tony Papadimitriou <to...@acm.org> wrote:

> As you can see, the second select gives unexpected output, and according
> to the syntax diagram it's not supposed to be a valid variation of the CASE
> statement.  Is that normal?


http://www.sqlite.org/sessions/lang_expr.html

Looks perfectly valid​. "expr" can be any expression, including logical
ones such as "a < 10". Logical expressions in SQLite are just like any
other expressions and evaluate to 0 or 1. ​For example, it is perfectly
​legal to

SELECT a.a < 10 FROM a
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there equivalent to MySQL IF() function?

[Tony Papadimitriou]

Thanks.

It seems quite a bit more verbose than the IF() function, but it works, so I 
can't complain.


I played with it a bit, and I did notice there are two forms: CASE expr WHEN 
and CASE WHEN


When I accidentally put an expr (CASE expr WHEN) *AND* explicit WHEN 
conditions rather than values, there was no syntax error and the result was 
unexpected:


Example:

drop table if exists a;
create table a(a);
insert into a values(2),(10),(128);
select * from a;
select a,case when a<10 then 1 when a < 20 then 2 else 3 end cased from 
;  -- gives expected result 1,2,3
select a,case a when a<10 then 1 when a < 20 then 2 else 3 end cased from 
a; -- gives unexpected result 3,3,3 and no syntax error


As you can see, the second select gives unexpected output, and according to 
the syntax diagram it's not supposed to be a valid variation of the CASE 
statement.  Is that normal?


-Original Message- 
From: RSmith

Sent: Tuesday, October 07, 2014 12:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there equivalent to MySQL IF() function?

SELECT CASE WHEN (AGE<3) THEN 'Baby' WHEN (AGE BETWEEN 4 AND 18) THEN 
'Child' ELSE 'Adult' END



On 2014/10/07 11:15, Tony Papadimitriou wrote:

Hi all,

Is there any an equivalent function to the MySQL 
IF(condition,true_expr,false_expr) function?


For example, SELECT AGE,IF(AGE < 3,"BABY",IF(AGE < 18,"CHILD","ADULT"));

If not, please add to wish list :)

TIA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is there equivalent to MySQL IF() function?

[Tony Papadimitriou]

Hi all,

Is there any an equivalent function to the MySQL 
IF(condition,true_expr,false_expr) function?


For example, SELECT AGE,IF(AGE < 3,"BABY",IF(AGE < 18,"CHILD","ADULT"));

If not, please add to wish list :)

TIA 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] archive of database

[Tony Papadimitriou]

My recommendation is to dump the SQLite database into a corresponding .sql 
file, and then compress that ASCII file using your favorite compressor (ZIP, 
RAR, etc.)


In my view, this method has two advantages over copying the actual binary 
file for backup purposes, but no obvious disadvantages.


1. Compression ratio is always (at least with my own tests) significantly 
better on the ASCII .sql file than the SQLite binary equivalent.  So, you 
need less storage for your backups, or less bandwidth for transmitting the 
database.
2. In case future versions of SQLite use a different incompatible format, 
your backups from long-long ago will still be able to rebuild the database 
using the current version of SQLite.  Even in the unlikely event, some 
source level incompatibilities appear, you can easily edit those in the 
ASCII text file using a plain text editor, whereas the trying to convert the 
binary to a compatible format is not necessarily a trivial job, assuming you 
no longer have access to the needed version of SQLite.


-Original Message- 
From: Levente Kovacs

Sent: Saturday, August 09, 2014 2:18 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] archive of database

Dear List,


Is it a recommended method to archive or copy a database just copying the
sqlite file, or should I make a dump of the database, and move the SQL
statements?

I mean... is SQLite designed to support the move of the sqlite file? What if
a new version of the library comes out? Will it be able to read older
version of database?

What is the most portable way?

Thanks,
Levente

--
Levente Kovacs
CTO, CSO
http://levente.logonex.eu



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.6 coming soon

[Tony Papadimitriou]

Unless I'm misusing it, I believe the shell readfile() function reads a file 
as text, not binary.  I haven't checked writefile() for the same problem on 
writing.


This is how I use it, is this not correct?

create table t1(a blob);
insert into  t1 values(readfile('binfile'));
select * from t1;

I always see text and I expect to see one of the following cases:
1. a hex sequence regardless of file type
2. text (if the file was text), or a hex sequence if it was binary.

Instead, I see text when dealing with text files, and truncated text (up to 
the first zero) when dealing with binary.


Am I misusing it?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Send parameter from batch file to sqlite

[Tony Papadimitriou]

Not possible directly from SQLite.

Some weeks ago I suggested a possible way to have this capability added to 
the shell version of SQLite3 but there seems to be zero interest from the 
developers.  So, don't hold your breath.  Better yet, write a Lua script or 
something to do it.  (If you do, maybe you'd like to share it.)


-Original Message- 
From: shweta gk


...
One of the queries in export.sql has where clause , to which i have to
send a value from batch file. Which syntax is used for this
functionality.

...

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to see timeline for given branch (or eventag)?

[Tony Papadimitriou]

Oops!  That was meant for the fossil group, sorry!

-Original Message- 
From: Tony Papadimitriou

Sent: Thursday, March 27, 2014 7:26 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Is it possible to see timeline for given branch (or 
eventag)?


When I give the command FOSSIL TIM it lists all changes in all branches (and
tags).

But what if I want to see changes in only, say, the TRUNK branch?  Is there
a way?  Or maybe items having a given tag?

TIA

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible to see timeline for given branch (or even tag)?

[Tony Papadimitriou]

When I give the command FOSSIL TIM it lists all changes in all branches (and 
tags).


But what if I want to see changes in only, say, the TRUNK branch?  Is there 
a way?  Or maybe items having a given tag?


TIA 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite clusters?

[Tony Papadimitriou]

A "temp" view, however, can access table from different DBs.

-Original Message- 
From: Gerry Snyder 

I was under the impression that a view is limited to the tables in the 
db file where it resides, and have received error messages whenever I 
tried to access another file's tables.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] quetion on editing a sqlite database

[Tony Papadimitriou]

See the REPLACE(x,y,z) function.  Use UPDATE table SET field = 
REPLACE(field,)


-Original Message- 
From: john white

Sent: Monday, September 16, 2013 1:30 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] quetion on editing a sqlite database

I have a bit of a problem.  I am moving a program (sickbeard) from windows 
to

ubuntu.  The program has an sqlite database.  It lists a variety of things
with their physical location on disk.  The problem is that ubuntu (linux)
location information is slightly different between windows and ubuntu so I
must change those locations.  Oh, there is a location column with this
information.  I can do them, one at a time, but that is going to take a LOT
of time.  I was hoping there would be a way to select them all and then
simply do a find and replace thing.

An example would be: "C:\TV\" which should now be '/TV/".  Even better would
be to change all the "\" to "/" and then just delete the leading "C:".

Thank you...

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[Tony Papadimitriou]

How about:

maybe(COLUMN LIKE '%pattern%',.95)

or (as percent using integer value in 0..100)

maybe(COLUMN LIKE '%pattern%',95)

with a default value of (possibly) 50% (or .5) for the optional second arg?

-Original Message- 
From: Richard Hipp 
Sent: Tuesday, September 10, 2013 10:26 PM 
To: General Discussion of SQLite Database 
Subject: [sqlite] Hints for the query planner 


The question for today is what to call this magic hint function:

(1)  unlikely(EXPR)
(2)  selective(EXPR)
(3)  seldom(EXPR)
(4)  seldom_true(EXPR)
(5)  usually_not_true(EXPR)

Please feel free to suggest other names if you think of any.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Tony Papadimitriou]

This is documented, I think.

Based on 2.1 (bullet 5) of http://www.sqlite.org/datatype3.html the default 
affinity is numeric.  Since STRING is not understood, it has numeric 
affinity.


-Original Message- 
The actual table definition is :
CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG 
String, BEMERKUNG String, ID_GUID String, IST_PAUSCHALIERT String, 
IST_KATEGORIE_ESK_BETRIEBSSTOF String);


SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select schluessel, typeof(schluessel)
  ...> from t_vwg_abfallverzeichnis
  ...> where id_guid in ('BEE7F6108F02416FA0C9D5DC777DB172', 
'6061A3864C2546C4A7DDA9FDB321459F');

1|integer
01 01|text
sqlite>

I *expected* to gain TEXT affinity through the use of System.Data.SQLite and 
it's strongly typed types (doppelt gemoppelt?), but apparently this is not 
totally effective. Maybe Joe Mistachkin can say something about that?


Bernd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in query planner / optimizer

[Tony Papadimitriou]

With the SQL query at the end of the dump like you say, try this:

SQLite3 -init dump.sql
Then, no matter how many times you repeat the query from within SQLite3 
prompt, it works!


As soon as you do ANALYZE, the query stops working...

Maybe this helps pin point the problem.

(Replacing "  SD_MAILSERVERID = ms_id" with "SD_MAILSERVERID IN (MS_ID)" 
also works but I don't know if it's a good temp solution for your case)


-Original Message- 
From: Klaus Keppler

Sent: Monday, July 08, 2013 6:06 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug in query planner / optimizer

But after I remove the assert(), I cannot reproduce your problem.  I 
always

get four result rows, respectively: x2, x3, x4, and x5.  I've tried this
with 3.7.15, 3.7.16, 3.7.16.2, 3.7.17, and the latest trunk.  And I've
tried it using your original database and after dropping the SQLITE_STAT1
table.  Do you have any further clues for me?


This is *really* strange.
I've created a dump of my test database, and just appended the SQL query
to the end of that dump (inspired by your debugging example).
When I now pipe that whole file into SQLite3, the query works. But when
I run the same query again on that (now existing) database, there are
*no* results any more. Maybe we have a timing problem with the journal here?

Here are two files, one containing the schema and one with the query:

http://download.liveconfig.com/tmp/test.schema
  (SHA1: f0e6f024d5f0d4dbcbfef9bb1c92b12042fd8cd2)
http://download.liveconfig.com/tmp/test.sql
  (SHA1: f56970e0343a7418ed2a89ab3ffa4e8c30986bae)

To reproduce:

  cat test.schema test.sql | ./sqlite3 tmp.db
  => returns the expected 4 result rows (x2/x3/x4/x5)

  cat test.sql | ./sqlite3 tmp.db
  => returns nothing

Tested right now with 3.7.17-trunk and 3.7.16.2

Meanwhile, one of our users sent us another bug report concerning an
"empty" query (another query, retuning no results even after executing
ANALYZE command). I'll check this too, and will try to simplify the
scheme by removing some columns.

Best regards

   -Klaus


--
__
Keppler IT GmbH - Die Hostingexperten.

Dipl.-Inf. Klaus KepplerTel. (09131) 691-480
Geschäftsführer Fax: (09131) 691-489

Am Weichselgarten 7 UStID.-Nr. DE259788698
91058 Erlangen  Amtsgericht Fürth, HRB 11477
www.keppler-it.de   Sitz d. Gesellschaft: Erlangen
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Tony Papadimitriou]

OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)


I'm sure there will be a SQL engine somewhere that will do it for you.


We're talking about SQLite here, aren't we?  If some other database can do 
it, then you should also consider that it may also be able to do what this 
'row' function.  (e.g. MySQL).  So, your point it moot.



So, why make it sound like I don't know what I'm talking about?

I think you beat me to it.


No comment!

-Original Message- 
From: Alex Bowden

Sent: Monday, July 01, 2013 6:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe 
rowid)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Tony Papadimitriou]

I agree with you completely!  It has to be relatively simple to implement. 
And certainly it adds a very important missing functionality (for those who 
can see it, that is).
Some kind of internal cursor has to iterate to return rows to the user after 
the query is completely executed.  This can add a counter (if it doesn't 
have it already to compare again a possible LIMIT).


The only real problem is how do you return this to the user.  If you put it 
in a function, you have to syntactically exclude that function from 
appearing anywhere except the select fields (not in WHERE, ORDER, GROUP or 
whatever else.)  I assume a special name or symbol (#, perhaps) would work. 
(e.g.., SELECT # FROM table _or_ SELECT # AS row,* FROM table -- to give it 
a name)


As for how useful it is, it depends on one's imagination!  If some can't see 
a reason for certain functionality, that's no reason for it not to be there. 
Some do see a reason, and (please) don't call it a whim!


One good example was mentioned in someone else's reply, you need to add 
ranks to a master table based on periodical results.  Yes, you could use 
temporary tables as intermediate storage but what if you have a table 100K 
rows or more?  Not very efficient, is it?


By the way, MySQL can do it very easily because of its variable manipulation 
ability.  (Something like SELECT @ROW:=@ROW+1 as ROW,* FROM table,(SELECT 
@ROW := 0) foo; )  By since SQLite has not such capability, a poor man's 
version could be implemented, as suggested.


Thanks to all for your contribution!

-Original Message- 
From: Gabriel Corneanu

Sent: Monday, July 01, 2013 4:54 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a way to return the row number? (NOT 
therowid)


As I don't think that the implementation is the problem, the real issues
are:
a) is it "lite" enough for the core? (I believe yes)
b) is a specific (non-standard) feature desired? (I don't have a
problem, but maintainers might have)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

[Tony Papadimitriou]

Thanks!  (At least some understand what I mean!)

And people giving examples of how it can be done in C (or Python, for me) or 
whatever language miss the point.  We're not talking how it can be 
programmatically.  This is easy!!!  How does one do it via pure SQL is the 
real question.


As for the temporary table idea, I had this one, too!  But it seems too much 
overhead for such a simple thing, so I was hoping for something like an 
internal symbol, or special function.


-Original Message- 
From: Gabriel Corneanu

Sent: Monday, July 01, 2013 2:10 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a way to return the row number? (NOT 
therowid)


I also needed this for some special update queries.
Without many details, it was some kind of "insert into xxx select
, otherfields from source order by ".
For this case there is a workaround, selecting first into a temporary
table with auto generated rowid and using it afterwards for insert.

There are lots of other cases where this would be handy, e.g. showing
ordinal of some results.

... 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Tony Papadimitriou]

Please!  Just because you can select something doesn't mean you have to be 
able to sort by it.  Can you sort by *
(select * by table sort by *)?  So, why make it sound like I don't know what 
I'm talking about?


-Original Message- 
From: Alex Bowden

Sent: Monday, July 01, 2013 2:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe 
rowid)



This would not be something you would sort by.


And what if I do?

It should be assigned a value only during final 'display' of the query 
after all 'sorts' of operations are done with.


Oh great.  So the user is supposed to understand the implementation, in 
order to understand what the results will be.



This would be just another nail in the coffin of relationality and 
simplicity, on a minor whim.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Tony Papadimitriou]

This would not be something you would sort by.  It should be assigned a 
value only during final 'display' of the query after all 'sorts' of 
operations are done with.


-Original Message- 
From: Alex Bowden

Sent: Monday, July 01, 2013 12:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOT 
therowid)



I can't wait to try

order by row_number desc


On 1 Jul 2013, at 10:33, Tony Papadimitriou <to...@acm.org> wrote:

Is there a function (or method), e.g., row(), to return the sequence 
number of the selected row?  This is not the same as ROWID.  row() should 
give a sequence number always starting from 1 up the to the number of rows 
returned by the view/select etc.


If not, then please add to the wish list.

TIA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is there a way to return the row number? (NOT the rowid)

[Tony Papadimitriou]

Is there a function (or method), e.g., row(), to return the sequence number 
of the selected row?  This is not the same as ROWID.  row() should give a 
sequence number always starting from 1 up the to the number of rows returned 
by the view/select etc.


If not, then please add to the wish list.

TIA 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users