date:20151105

[sqlite] PRAGMA integrity_check says row 2 missing from index

2015-11-05 Thread Zsbán Ambrus

Hello.

The PRAGMA integrity_check is telling me that "row 2 missing from
index tbvk".  This is reproducible with the following commands.
Should I be getting this message?  Or is it a bug?

SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE tb(k INTEGER PRIMARY KEY, v);
sqlite> CREATE INDEX tbvk ON tb(v, k);
sqlite> INSERT INTO tb(k, v) VALUES (8, 356282677878746339);
sqlite> INSERT INTO tb(k, v) VALUES (7, 356282677878746339.0);
sqlite> INSERT INTO tb(k, v) VALUES (6, 356282677878746340);
sqlite> DELETE FROM tb WHERE k = 7;
sqlite> SELECT k, v, typeof(v) FROM tb ORDER BY v, k;
6|356282677878746340|integer
8|356282677878746339|integer
sqlite> PRAGMA integrity_check;
row 2 missing from index tbvk
sqlite> DROP INDEX tbvk;
sqlite> SELECT k, v, typeof(v) FROM tb ORDER BY v, k;
8|356282677878746339|integer
6|356282677878746340|integer
sqlite> PRAGMA compile_options;
OMIT_LOAD_EXTENSION
SYSTEM_MALLOC
THREADSAFE=1
sqlite> .quit

I am using sqlite 3.9.2 built from the amalgamation on linux x86_64
with gcc 4.8.1 with the following command line:

gcc -pthread -O2 -DSQLITE_OMIT_LOAD_EXTENSION=1 -o sqlite3 sqlite3.c shell.c

-- Ambrus

[sqlite] Easiest way to pass SQL query parameters in command line?

2015-11-05 Thread Clemens Ladisch

Yuri wrote:
> I am looking for a way to have a parametrized query, when parameter is passed 
> through the command line.

Isn't the query itself passed through the command line?  Show some example.


Regards,
Clemens

[sqlite] Easiest way to pass SQL query parameters in command line?

2015-11-05 Thread Simon Slavin

> On 5 Nov 2015, at 10:13pm, Yuri  wrote:
> 
> In my case script is like this:
> #!/bin/sh
> (cat $1 && echo ";") | sqlite3 my-db.sqlite
> 
> Command to run it:
> ./my-sql-run sqls/my-query.sql
> 
> I need to pass some parameter, like "select * from table where 
> kind=%%MYPARAM%%"

Use echo or to put the command you want into a text file and feed that text 
file to sqlite3:

sqlite3 my-db.sqlite < commands.txt

Alternatively make your command file with the variable in, then use the 'sed' 
command to replace the parameter with your desired value.

sed -i '.bak' 's/%%MYPARAM%%/17/g' commands.txt
sqlite3 my-db.sqlite < commands.txt

Simon.

[sqlite] Non-transitive numeric equality

2015-11-05 Thread R Smith



On 2015/11/05 4:55 PM, Richard Hipp wrote:
> On 11/5/15, Zsb?n Ambrus  wrote:
>> Dear SQLite,
>>
>> It seems that equality of numeric values isn't transitive, when both
>> integers and reals are involved.  Here's an example output from the
>> shell, which shows that the numeric value in the 'c' row is equal to
>> both the value in the 'b' and the 'd' rows, but the value in the 'b'
>> row isn't equal to the value in the 'd' row.  Neither null values nor
>> collations seem to be involved here.
>>
>> SQLite version 3.9.2 2015-11-02 18:31:45
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> create table tb(n, v);
>> sqlite> insert into tb(n, v) values ('b', 1<<58), ('c', (1<<58) +
>> 1e-7), ('d', (1<<58) + 1);
>> sqlite> select n, v, typeof(v) from tb;
>> b|288230376151711744|integer
>> c|2.88230376151712e+17|real
>> d|288230376151711745|integer
>> sqlite> select l.n, r.n, l.v = r.v from tb as l, tb as r;
>> b|b|1
>> b|c|1
>> b|d|0
>> c|b|1
>> c|c|1
>> c|d|1
>> d|b|0
>> d|c|1
>> d|d|1
>> sqlite> .quit
> The following C program gives the same answer (using gcc 4.8.4 on ubuntu):
>
> #include 
> typedef long long int i64;
> int main(int argc, char **argv){
>i64 b = 1LL << 58;
>double c = (1LL << 58) + 1e-7;
>i64 d = (1LL << 58) + 1;
>printf("b==b: %d\n", b==b);
>printf("b==c: %d\n", b==c);
>printf("b==d: %d\n", b==d);
>printf("c==b: %d\n", c==b);
>printf("c==c: %d\n", c==c);
>printf("c==d: %d\n", c==d);
>printf("d==b: %d\n", d==b);
>printf("d==c: %d\n", d==c);
>printf("d==d: %d\n", d==d);
>return 0;
> }
>
>
>> Can this cause problems with indexes,
>> sorting or grouping by?
>>
> You should not compare floating-point numbers for equality.
> Floating-point numbers are, by definition, approximations.  When you
> compare floating-point numbers, therefore, you get an approximate
> answer.
>

fwiw - I don't get the same result, here is the same script running in 
Win32-SQLitespeed-via-SQLite-3.9.1-DLL (the standard pre-compiled one 
from sqlite.org) on an in-memory Database, This one seems to magically 
get it right:

   -- 2015-11-05 16:41:54.666  |  [Info]   Script Initialized, 
Started executing...
   -- 


create table tb(n, v);

insert into tb(n, v) values ('b', 1<<58), ('c', (1<<58) + 1e-7), ('d', 
(1<<58) + 1);

select n, v, typeof(v) from tb;

   --   n   | v  | typeof(v)
   -- - | -- | -
   --   b   | 288230376151711744 | integer
   --   c   | 2.88230376151712e+17   | real
   --   d   | 288230376151711745 | integer


select l.n, r.n, l.v = r.v from tb as l, tb as r;

   --   n   |   n   | l.v = r.v
   -- - | - | -
   --   b   |   b   | 1
   --   b   |   c   | 1
   --   b   |   d   | 0
   --   c   |   b   | 1
   --   c   |   c   | 1
   --   c   |   d   | 0
   --   d   |   b   | 0
   --   d   |   c   | 0
   --   d   |   d   | 1

   -- 2015-11-05 16:41:54.675  |  [Success]Script Success.


And to answer the OP's other question - This doesn't matter, a Primary 
key using FLOAT values is rather risky, but any float that isn't 
represented exactly the same as another will have a different bit 
pattern. Mixing floats and ints in a PK however, might be disastrous.

[sqlite] Non-transitive numeric equality

2015-11-05 Thread Stephan Beal

On Thu, Nov 5, 2015 at 3:36 PM, Zsb?n Ambrus  wrote:

> It seems that equality of numeric values isn't transitive, when both
> integers and reals are involved.


See this really, really, really, really long thread on that topic from a
couple weeks ago for far, far, far more information than you could possible
want on the reason:

http://www.mail-archive.com/sqlite-users at 
mailinglists.sqlite.org/msg04466.html


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Non-transitive numeric equality

2015-11-05 Thread Zsbán Ambrus

Dear SQLite,

It seems that equality of numeric values isn't transitive, when both
integers and reals are involved.  Here's an example output from the
shell, which shows that the numeric value in the 'c' row is equal to
both the value in the 'b' and the 'd' rows, but the value in the 'b'
row isn't equal to the value in the 'd' row.  Neither null values nor
collations seem to be involved here.

SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table tb(n, v);
sqlite> insert into tb(n, v) values ('b', 1<<58), ('c', (1<<58) +
1e-7), ('d', (1<<58) + 1);
sqlite> select n, v, typeof(v) from tb;
b|288230376151711744|integer
c|2.88230376151712e+17|real
d|288230376151711745|integer
sqlite> select l.n, r.n, l.v = r.v from tb as l, tb as r;
b|b|1
b|c|1
b|d|0
c|b|1
c|c|1
c|d|1
d|b|0
d|c|1
d|d|1
sqlite> .quit

Is this behavior by design?  Can this cause problems with indexes,
sorting or grouping by?

I believe the cause of this behavior is the sqlite3MemCompare private
function in the sqlite3 implementation, which compares an integer to a
real by converting the integer to a real.  The conversion can lose
precision of the integer, and as a result, the value in the 'd' row
compares equal to the value in the 'c' row, despite that the numeric
values they represent isn't equal.  Sadly, comparing an integer to a
floating point number is not easy, so I don't know an easy fix.

I ran the test above with sqlite 3.9.2 built from the amalgamation
source on windows x86_64 with gcc 4.8.3 and the following compiler
options:

gcc -march=core2 -mavx -mvzeroupper -pthread -O2 -g
-DSQLITE_OMIT_LOAD_EXTENSION=1 -o sqlite3 sqlite3.c shell.c

-- Ambrus

[sqlite] Easiest way to pass SQL query parameters in command line?

2015-11-05 Thread Yuri

On 11/05/2015 14:00, Clemens Ladisch wrote:
> Isn't the query itself passed through the command line?  Show some example.


In my case script is like this:
#!/bin/sh
(cat $1 && echo ";") | sqlite3 my-db.sqlite

Command to run it:
./my-sql-run sqls/my-query.sql

I need to pass some parameter, like "select * from table where 
kind=%%MYPARAM%%"

Currently one choice is to add my own shell code to modify the query on 
the fly, and to substitute %%MYPARAM%% with the argument passed to the 
shell script. But I think it would be easier if sqlite3 command itself 
could bind parameters. If sql could contain "kind=?", and sqlite3 could 
have for example --bind command to bind supplied values. Especially so 
if to consider that sqlite already supports prepared statements and binding.

Yuri

[sqlite] Easiest way to pass SQL query parameters in command line?

2015-11-05 Thread Yuri

I am looking for a way to have a parametrized query, when parameter is 
passed through the command line. Much like a prepared statement and '?' 
parameters.

Is this possible? I can't find anything like this in the sqlite3 man page.
Another way would be if sqlite had a function to access the environment 
variable, but such function doesn't seem to exist either.

Yuri

[sqlite] Non-transitive numeric equality

2015-11-05 Thread Richard Hipp

On 11/5/15, Zsb?n Ambrus  wrote:
> Dear SQLite,
>
> It seems that equality of numeric values isn't transitive, when both
> integers and reals are involved.  Here's an example output from the
> shell, which shows that the numeric value in the 'c' row is equal to
> both the value in the 'b' and the 'd' rows, but the value in the 'b'
> row isn't equal to the value in the 'd' row.  Neither null values nor
> collations seem to be involved here.
>
> SQLite version 3.9.2 2015-11-02 18:31:45
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table tb(n, v);
> sqlite> insert into tb(n, v) values ('b', 1<<58), ('c', (1<<58) +
> 1e-7), ('d', (1<<58) + 1);
> sqlite> select n, v, typeof(v) from tb;
> b|288230376151711744|integer
> c|2.88230376151712e+17|real
> d|288230376151711745|integer
> sqlite> select l.n, r.n, l.v = r.v from tb as l, tb as r;
> b|b|1
> b|c|1
> b|d|0
> c|b|1
> c|c|1
> c|d|1
> d|b|0
> d|c|1
> d|d|1
> sqlite> .quit

The following C program gives the same answer (using gcc 4.8.4 on ubuntu):

#include 
typedef long long int i64;
int main(int argc, char **argv){
  i64 b = 1LL << 58;
  double c = (1LL << 58) + 1e-7;
  i64 d = (1LL << 58) + 1;
  printf("b==b: %d\n", b==b);
  printf("b==c: %d\n", b==c);
  printf("b==d: %d\n", b==d);
  printf("c==b: %d\n", c==b);
  printf("c==c: %d\n", c==c);
  printf("c==d: %d\n", c==d);
  printf("d==b: %d\n", d==b);
  printf("d==c: %d\n", d==c);
  printf("d==d: %d\n", d==d);
  return 0;
}


>
> Can this cause problems with indexes,
> sorting or grouping by?
>

You should not compare floating-point numbers for equality.
Floating-point numbers are, by definition, approximations.  When you
compare floating-point numbers, therefore, you get an approximate
answer.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] sign bug on HP-UX IPF 64bit

2015-11-05 Thread H.Merijn Brand

I am not a list member, so please Cc me if you reply

I was asked to post the problem here by charsbar, after I noted that
DBD::SQLite started to fail on HP-UX 64bit IPF (Itanium 64bit)

charsbar asked me to build sqlite3 from source and retry the fail with
plain SQL. I built from scratch using HP C-ANSI-C in 64bitall mode

 -Ae -AC99 -O2 +Onolimit +Z -z +DD64

$ wget http://sqlite.org/2015/sqlite-autoconf-3090200.tar.gz
$ tgz x sqlite-autoconf-3090200.tbz
$ cd sqlite-autoconf-3090200/
$ configure --prefix=/pro/local
$ make
$ ./sqlite3
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table foo (c_foo integer, foo varchar (4));
sqlite> insert into foo values (-2147483646, "foo");
sqlite> select * from foo;
2147483650|foo
sqlite>


--- Some of the compilation warnings ? (the 51880 one might be important)
"sqlite3.c", line 9564: warning #2047-D: incompatible redefinition of macro
  "MIN" (declared at line 442 of "/usr/include/sys/param.h")
  #define MIN(A,B) ((A)<(B)?(A):(B))
  ^

"sqlite3.c", line 9565: warning #2047-D: incompatible redefinition of macro
  "MAX" (declared at line 443 of "/usr/include/sys/param.h")
  #define MAX(A,B) ((A)>(B)?(A):(B))
  ^

"sqlite3.c", line 25014: warning #4069-D: floating point overflow exception
result = 1e308*1e308*s;  /* Infinity */
  ^

"sqlite3.c", line 51880: warning #4232-D: conversion from "volatile ht_slot *"
  to a more strictly aligned type "u32 *" may cause misaligned access
  nEntry = (int)((u32*)aHash - (u32*)aPgno);
 ^

"sqlite3.c", line 80887: warning #2191-D: type qualifier is meaningless on
  cast type
const u8 * const p1 = (const u8 * const)pKey1;
   ^

"sqlite3.c", line 80888: warning #2191-D: type qualifier is meaningless on
  cast type
const u8 * const p2 = (const u8 * const)pKey2;
   ^

"sqlite3.c", line 80928: warning #2191-D: type qualifier is meaningless on
  cast type
const u8 * const p1 = (const u8 * const)pKey1;
   ^

"sqlite3.c", line 80929: warning #2191-D: type qualifier is meaningless on
  cast type
const u8 * const p2 = (const u8 * const)pKey2;
   ^

"sqlite3.c", line 156586: warning #4232-D: conversion from "int *" to a more
  strictly aligned type "RtreeDValue *" may cause misaligned access
aDistance = (RtreeDValue *)[n];
^



-- 
H.Merijn Brand  http://tux.nl   Perl Monger  http://amsterdam.pm.org/
using perl5.00307 .. 5.23   porting perl5 on HP-UX, AIX, and openSUSE
http://mirrors.develooper.com/hpux/http://www.test-smoke.org/
http://qa.perl.org   http://www.goldmark.org/jeff/stupid-disclaimers/
-- next part --
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 490 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20151105/8debea4e/attachment.pgp>

[sqlite] sign bug on HP-UX IPF 64bit

2015-11-05 Thread Richard Hipp

On 11/5/15, H.Merijn Brand  wrote:
> I am not a list member, so please Cc me if you reply
>
> I was asked to post the problem here by charsbar, after I noted that
> DBD::SQLite started to fail on HP-UX 64bit IPF (Itanium 64bit)
>
> charsbar asked me to build sqlite3 from source and retry the fail with
> plain SQL. I built from scratch using HP C-ANSI-C in 64bitall mode
>
>  -Ae -AC99 -O2 +Onolimit +Z -z +DD64

Can you get me temporary ssh access to the HP-UX 64bit IPF machine so
that I can debug the problem?

>
> $ wget http://sqlite.org/2015/sqlite-autoconf-3090200.tar.gz
> $ tgz x sqlite-autoconf-3090200.tbz
> $ cd sqlite-autoconf-3090200/
> $ configure --prefix=/pro/local
> $ make
> $ ./sqlite3
> SQLite version 3.9.2 2015-11-02 18:31:45
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table foo (c_foo integer, foo varchar (4));
> sqlite> insert into foo values (-2147483646, "foo");
> sqlite> select * from foo;
> 2147483650|foo
> sqlite>
>
>
> --- Some of the compilation warnings ? (the 51880 one might be important)
> "sqlite3.c", line 9564: warning #2047-D: incompatible redefinition of macro
>   "MIN" (declared at line 442 of "/usr/include/sys/param.h")
>   #define MIN(A,B) ((A)<(B)?(A):(B))
>   ^
>
> "sqlite3.c", line 9565: warning #2047-D: incompatible redefinition of macro
>   "MAX" (declared at line 443 of "/usr/include/sys/param.h")
>   #define MAX(A,B) ((A)>(B)?(A):(B))
>   ^
>
> "sqlite3.c", line 25014: warning #4069-D: floating point overflow exception
> result = 1e308*1e308*s;  /* Infinity */
>   ^
>
> "sqlite3.c", line 51880: warning #4232-D: conversion from "volatile ht_slot
> *"
>   to a more strictly aligned type "u32 *" may cause misaligned
> access
>   nEntry = (int)((u32*)aHash - (u32*)aPgno);
>  ^
>
> "sqlite3.c", line 80887: warning #2191-D: type qualifier is meaningless on
>   cast type
> const u8 * const p1 = (const u8 * const)pKey1;
>^
>
> "sqlite3.c", line 80888: warning #2191-D: type qualifier is meaningless on
>   cast type
> const u8 * const p2 = (const u8 * const)pKey2;
>^
>
> "sqlite3.c", line 80928: warning #2191-D: type qualifier is meaningless on
>   cast type
> const u8 * const p1 = (const u8 * const)pKey1;
>^
>
> "sqlite3.c", line 80929: warning #2191-D: type qualifier is meaningless on
>   cast type
> const u8 * const p2 = (const u8 * const)pKey2;
>^
>
> "sqlite3.c", line 156586: warning #4232-D: conversion from "int *" to a
> more
>   strictly aligned type "RtreeDValue *" may cause misaligned access
> aDistance = (RtreeDValue *)[n];
> ^
>
>
>
> --
> H.Merijn Brand  http://tux.nl   Perl Monger  http://amsterdam.pm.org/
> using perl5.00307 .. 5.23   porting perl5 on HP-UX, AIX, and openSUSE
> http://mirrors.develooper.com/hpux/http://www.test-smoke.org/
> http://qa.perl.org   http://www.goldmark.org/jeff/stupid-disclaimers/
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] PRAGMA integrity_check says row 2 missing from index

[sqlite] Easiest way to pass SQL query parameters in command line?

[sqlite] Easiest way to pass SQL query parameters in command line?

[sqlite] Non-transitive numeric equality

[sqlite] Non-transitive numeric equality

[sqlite] Non-transitive numeric equality

[sqlite] Easiest way to pass SQL query parameters in command line?

[sqlite] Easiest way to pass SQL query parameters in command line?

[sqlite] Non-transitive numeric equality

[sqlite] sign bug on HP-UX IPF 64bit

[sqlite] sign bug on HP-UX IPF 64bit

11 matches

Site Navigation

Mail list logo

Footer information