Problem with REAL PRIMARY KEY

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem with REAL PRIMARY KEY

Manuel Rigger
Hi everyone,

Consider the following example:

CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;

I would expect the row to be fetched, which is not the case.

I confirmed that the real value stored is indeed equal to the constant:

sqlite> SELECT *, typeof(c1) FROM t1;
|5.76460752303423e+17|real

This is not only the case when using a literal, but also when querying the
value stored in the row in a sub query. So the following expression also
does not fetch the row:

SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1);

It seems that the PRIMARY KEY causes the bug. When it is removed (or one of
the columns), the row is returned as expected.

Best,
Manuel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with REAL PRIMARY KEY

Keith Medcalf

The actual value is 5.7646075230342348e+17 or 5.764607523034235e+17 (depending on compiler, floating point mode, FPU rounding settings, etc.).  This is a common problem with using = with floating point numbers ...

You can find the actual exact value using:

select printf('%!.20e', c1) from t1;

sqlite> select * from t1 not indexed where c1 = 5.764607523034234e+17;
QUERY PLAN
`--SCAN TABLE t1 (~262144 rows)
sqlite> select * from t1 not indexed where c1 = 5.76460752303423488e+17;
QUERY PLAN
`--SCAN TABLE t1 (~262144 rows)
|5.76460752303423e+17
sqlite> select * from t1 where c1 = 5.76460752303423488e+17;
QUERY PLAN
`--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~10 rows)

So the issue you found exists, but your example is bad because you are using the "wrong" floating point value ...

---
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-
>[hidden email]] On Behalf Of Manuel Rigger
>Sent: Saturday, 4 May, 2019 10:36
>To: SQLite mailing list
>Subject: [sqlite] Problem with REAL PRIMARY KEY
>
>Hi everyone,
>
>Consider the following example:
>
>CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
>INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
>SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>
>I would expect the row to be fetched, which is not the case.
>
>I confirmed that the real value stored is indeed equal to the
>constant:
>
>sqlite> SELECT *, typeof(c1) FROM t1;
>|5.76460752303423e+17|real
>
>This is not only the case when using a literal, but also when
>querying the
>value stored in the row in a sub query. So the following expression
>also
>does not fetch the row:
>
>SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1);
>
>It seems that the PRIMARY KEY causes the bug. When it is removed (or
>one of
>the columns), the row is returned as expected.
>
>Best,
>Manuel
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with REAL PRIMARY KEY

Richard Hipp-3
In reply to this post by Manuel Rigger
On 5/4/19, Manuel Rigger <[hidden email]> wrote:
> Hi everyone,
>
> Consider the following example:
>
> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>
> I would expect the row to be fetched, which is not the case.

But 0x7ffffffffffffff != 5.76460752303423e+17.  Try it:

   SELECT 0x7ffffffffffffff != 5.76460752303423e+17;

You should get back 0.

The rule of thumb is to never expect the == operator to give a
meaningful answer for floating-point numbers.  Only use <, <=, >, and
>=.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with REAL PRIMARY KEY

Richard Damon
In reply to this post by Manuel Rigger
On 5/4/19 12:36 PM, Manuel Rigger wrote:

> Hi everyone,
>
> Consider the following example:
>
> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>
> I would expect the row to be fetched, which is not the case.
>
> I confirmed that the real value stored is indeed equal to the constant:
>
> sqlite> SELECT *, typeof(c1) FROM t1;
> |5.76460752303423e+17|real
>
> This is not only the case when using a literal, but also when querying the
> value stored in the row in a sub query. So the following expression also
> does not fetch the row:
>
> SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1);
>
> It seems that the PRIMARY KEY causes the bug. When it is removed (or one of
> the columns), the row is returned as expected.
>
> Best,
> Manuel

5.76460752303423e+17 == 576460752303423000 which is even
0X7ffffffffffffff is odd
They can't be the same value!

Now, depending on how you convert things, they may be close enough to round to the same value if the calculation ends up being done as a real, but that is down in implementation details (a 'Double' has less than 59 significant bits, so can not express the value 0x7ffffffffffffff)

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with REAL PRIMARY KEY

Keith Medcalf
In reply to this post by Richard Hipp-3

There is, however, something weird:

SQLite version 3.29.0 2019-05-04 17:32:07
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .version
SQLite 3.29.0 2019-05-04 17:32:07 c2e439bccc40825e211bfa9a88e6a251ff066ca7453d4e7cb5eab56ce733alt2
zlib version 1.2.11
gcc-8.1.0
sqlite> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
sqlite> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
sqlite> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
sqlite> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1);
sqlite> SELECT ALL * FROM t1 WHERE c1 > (select c1 - 1 from t1);
sqlite> select c1 from t1;
5.76460752303423e+17
sqlite> select c1 - 1 from t1;
5.76460752303423e+17
sqlite>


---
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-
>[hidden email]] On Behalf Of Richard Hipp
>Sent: Saturday, 4 May, 2019 11:49
>To: SQLite mailing list
>Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
>
>On 5/4/19, Manuel Rigger <[hidden email]> wrote:
>> Hi everyone,
>>
>> Consider the following example:
>>
>> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
>> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
>> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>>
>> I would expect the row to be fetched, which is not the case.
>
>But 0x7ffffffffffffff != 5.76460752303423e+17.  Try it:
>
>   SELECT 0x7ffffffffffffff != 5.76460752303423e+17;
>
>You should get back 0.
>
>The rule of thumb is to never expect the == operator to give a
>meaningful answer for floating-point numbers.  Only use <, <=, >, and
>>=.
>
>--
>D. Richard Hipp
>[hidden email]
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with REAL PRIMARY KEY

Keith Medcalf

Ooopsie ... that should have been 1e17, and it appears to be fine, except that:

SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1);

does not work ever though the value returned from the subselect should be exactly the value in the index ...

A table scan does however work correctly ...

sqlite> SELECT ALL * FROM t1 not indexed WHERE c1 = (select c1 from t1);
|5.76460752303423e+17


---
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-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Saturday, 4 May, 2019 12:09
>To: SQLite mailing list
>Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
>
>
>There is, however, something weird:
>
>SQLite version 3.29.0 2019-05-04 17:32:07
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .version
>SQLite 3.29.0 2019-05-04 17:32:07
>c2e439bccc40825e211bfa9a88e6a251ff066ca7453d4e7cb5eab56ce733alt2
>zlib version 1.2.11
>gcc-8.1.0
>sqlite> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
>sqlite> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
>sqlite> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>sqlite> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1);
>sqlite> SELECT ALL * FROM t1 WHERE c1 > (select c1 - 1 from t1);
>sqlite> select c1 from t1;
>5.76460752303423e+17
>sqlite> select c1 - 1 from t1;
>5.76460752303423e+17
>sqlite>
>
>
>---
>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-
>>[hidden email]] On Behalf Of Richard Hipp
>>Sent: Saturday, 4 May, 2019 11:49
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
>>
>>On 5/4/19, Manuel Rigger <[hidden email]> wrote:
>>> Hi everyone,
>>>
>>> Consider the following example:
>>>
>>> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
>>> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
>>> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>>>
>>> I would expect the row to be fetched, which is not the case.
>>
>>But 0x7ffffffffffffff != 5.76460752303423e+17.  Try it:
>>
>>   SELECT 0x7ffffffffffffff != 5.76460752303423e+17;
>>
>>You should get back 0.
>>
>>The rule of thumb is to never expect the == operator to give a
>>meaningful answer for floating-point numbers.  Only use <, <=, >,
>and
>>>=.
>>
>>--
>>D. Richard Hipp
>>[hidden email]
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with REAL PRIMARY KEY

Manuel Rigger
Sorry, I should have anticipated that we get slightly different values.
Shouldn't the query "SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1);"
return a result though?

Best,
Manuel

On Sat, May 4, 2019 at 8:17 PM Keith Medcalf <[hidden email]> wrote:

>
> Ooopsie ... that should have been 1e17, and it appears to be fine, except
> that:
>
> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1);
>
> does not work ever though the value returned from the subselect should be
> exactly the value in the index ...
>
> A table scan does however work correctly ...
>
> sqlite> SELECT ALL * FROM t1 not indexed WHERE c1 = (select c1 from t1);
> |5.76460752303423e+17
>
>
> ---
> 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-
> >[hidden email]] On Behalf Of Keith Medcalf
> >Sent: Saturday, 4 May, 2019 12:09
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
> >
> >
> >There is, however, something weird:
> >
> >SQLite version 3.29.0 2019-05-04 17:32:07
> >Enter ".help" for usage hints.
> >Connected to a transient in-memory database.
> >Use ".open FILENAME" to reopen on a persistent database.
> >sqlite> .version
> >SQLite 3.29.0 2019-05-04 17:32:07
> >c2e439bccc40825e211bfa9a88e6a251ff066ca7453d4e7cb5eab56ce733alt2
> >zlib version 1.2.11
> >gcc-8.1.0
> >sqlite> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
> >sqlite> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
> >sqlite> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
> >sqlite> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1);
> >sqlite> SELECT ALL * FROM t1 WHERE c1 > (select c1 - 1 from t1);
> >sqlite> select c1 from t1;
> >5.76460752303423e+17
> >sqlite> select c1 - 1 from t1;
> >5.76460752303423e+17
> >sqlite>
> >
> >
> >---
> >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-
> >>[hidden email]] On Behalf Of Richard Hipp
> >>Sent: Saturday, 4 May, 2019 11:49
> >>To: SQLite mailing list
> >>Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
> >>
> >>On 5/4/19, Manuel Rigger <[hidden email]> wrote:
> >>> Hi everyone,
> >>>
> >>> Consider the following example:
> >>>
> >>> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
> >>> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
> >>> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
> >>>
> >>> I would expect the row to be fetched, which is not the case.
> >>
> >>But 0x7ffffffffffffff != 5.76460752303423e+17.  Try it:
> >>
> >>   SELECT 0x7ffffffffffffff != 5.76460752303423e+17;
> >>
> >>You should get back 0.
> >>
> >>The rule of thumb is to never expect the == operator to give a
> >>meaningful answer for floating-point numbers.  Only use <, <=, >,
> >and
> >>>=.
> >>
> >>--
> >>D. Richard Hipp
> >>[hidden email]
> >>_______________________________________________
> >>sqlite-users mailing list
> >>[hidden email]
> >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with REAL PRIMARY KEY

Tim Streater-3
In reply to this post by Richard Hipp-3
On 04 May 2019, at 18:49, Richard Hipp <[hidden email]> wrote:

> On 5/4/19, Manuel Rigger <[hidden email]> wrote:

>> Consider the following example:
>>
>> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
>> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
>> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>>
>> I would expect the row to be fetched, which is not the case.
>
> But 0x7ffffffffffffff != 5.76460752303423e+17.  Try it:
>
>    SELECT 0x7ffffffffffffff != 5.76460752303423e+17;
>
> You should get back 0.
>
> The rule of thumb is to never expect the == operator to give a
> meaningful answer for floating-point numbers. Only use <, <=, >, and >=.

My floating-point "don't do that" lesson occured in 1965 when trying to compute square roots by Newton's method. This was on an Elliott 803B.



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with REAL PRIMARY KEY

Keith Medcalf
In reply to this post by Manuel Rigger

I should think so yes ... The query only appears to work if the index on the real value is not used -- but there appears no way to do that when using the IN operator ...

QUERY PLAN
|--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~240 rows)
`--LIST SUBQUERY 1
   `--SCAN TABLE t1 (~1048576 rows)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     37    0                    00  Start at 37
1     OpenRead       2     3     0     k(3,,,)        02  root=3 iDb=0; sqlite_autoindex_t1_1
2     ColumnsUsed    2     0     0     3              00
3     Explain        3     0     0     SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~240 rows)  00            
4     Noop           0     0     0                    00  Begin WHERE-loop0: t1
5     Integer        21    2     0                    00  r[2]=21; return address
6     Once           0     21    0                    00
7     OpenEphemeral  3     1     0     k(1,B)         00  nColumn=1; Result of SELECT 1
8     OpenRead       1     2     0     2              00  root=2 iDb=0; t1
9     ColumnsUsed    1     0     0     2              00
10    Explain        10    0     0     SCAN TABLE t1 (~1048576 rows)  00
11    Noop           0     0     0                    00  Begin WHERE-loop0: t1
12    Rewind         1     20    0                    00
13      Noop           0     0     0                    00  Begin WHERE-core
14      Column         1     1     3                    00  r[3]=t1.c1
15      RealAffinity   3     0     0                    00
16      MakeRecord     3     1     4     C              00  r[4]=mkrec(r[3])
17      IdxInsert      3     4     3     1              00  key=r[4]
18      Noop           0     0     0                    00  End WHERE-core
19    Next           1     13    0                    01
20    Noop           0     0     0                    00  End WHERE-loop0: t1
21    Return         2     0     0                    00
22    Rewind         3     35    0                    00
23      Column         3     0     1                    00  r[1]=
24      IsNull         1     34    0                    00  if r[1]==NULL goto 34
25      SeekGE         2     34    1     1              00  key=r[1]
26        IdxGT          2     34    1     1              00  key=r[1]
27        Noop           0     0     0                    00  Begin WHERE-core
28        Column         2     1     5                    00  r[5]=t1.c0
29        Column         2     0     6                    00  r[6]=t1.c1
30        RealAffinity   6     0     0                    00
31        ResultRow      5     2     0                    00  output=r[5..6]
32        Noop           0     0     0                    00  End WHERE-core
33      Next           2     26    0                    00
34    Next           3     23    0                    00
35    Noop           0     0     0                    00  End WHERE-loop0: t1
36    Halt           0     0     0                    00
37    Transaction    0     0     1     0              01  usesStmtJournal=0
38    Goto           0     1     0                    00
sqlite>

sqlite> SELECT * FROM t1 NOT INDEXED WHERE c1 IN (SELECT c1 FROM t1);
QUERY PLAN
|--SCAN TABLE t1 (~983040 rows)
`--USING INDEX sqlite_autoindex_t1_1 FOR IN-OPERATOR
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     23    0                    00  Start at 23
1     OpenRead       0     2     0     2              00  root=2 iDb=0; t1
2     ColumnsUsed    0     0     0     3              00
3     Explain        3     0     0     SCAN TABLE t1 (~983040 rows)  00
4     Noop           0     0     0                    00  Begin WHERE-loop0: t1
5     Rewind         0     21    0                    00
6       Noop           0     0     0                    00  begin IN expr
7       Once           0     9     0                    00
8       OpenRead       2     3     0     k(3,,,)        00  root=3 iDb=0; sqlite_autoindex_t1_1
9       Column         0     1     1                    00  r[1]=t1.c1
10      RealAffinity   1     0     0                    00
11      IsNull         1     20    0                    00  if r[1]==NULL goto 20
12      Affinity       1     1     0     C              00  affinity(r[1])
13      NotFound       2     20    1     1              00  key=r[1]; end IN expr
14      Noop           0     0     0                    00  Begin WHERE-core
15      Column         0     0     2                    00  r[2]=t1.c0
16      Column         0     1     3                    00  r[3]=t1.c1
17      RealAffinity   3     0     0                    00
18      ResultRow      2     2     0                    00  output=r[2..3]
19      Noop           0     0     0                    00  End WHERE-core
20    Next           0     6     0                    01
21    Noop           0     0     0                    00  End WHERE-loop0: t1
22    Halt           0     0     0                    00
23    Transaction    0     0     1     0              01  usesStmtJournal=0
24    Goto           0     1     0                    00

sqlite> SELECT * FROM t1 not indexed WHERE c1 IN (SELECT c1 FROM t1 not indexed);
QUERY PLAN
|--SCAN TABLE t1 (~983040 rows)
`--USING INDEX sqlite_autoindex_t1_1 FOR IN-OPERATOR
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     23    0                    00  Start at 23
1     OpenRead       0     2     0     2              00  root=2 iDb=0; t1
2     ColumnsUsed    0     0     0     3              00
3     Explain        3     0     0     SCAN TABLE t1 (~983040 rows)  00
4     Noop           0     0     0                    00  Begin WHERE-loop0: t1
5     Rewind         0     21    0                    00
6       Noop           0     0     0                    00  begin IN expr
7       Once           0     9     0                    00
8       OpenRead       2     3     0     k(3,,,)        00  root=3 iDb=0; sqlite_autoindex_t1_1
9       Column         0     1     1                    00  r[1]=t1.c1
10      RealAffinity   1     0     0                    00
11      IsNull         1     20    0                    00  if r[1]==NULL goto 20
12      Affinity       1     1     0     C              00  affinity(r[1])
13      NotFound       2     20    1     1              00  key=r[1]; end IN expr
14      Noop           0     0     0                    00  Begin WHERE-core
15      Column         0     0     2                    00  r[2]=t1.c0
16      Column         0     1     3                    00  r[3]=t1.c1
17      RealAffinity   3     0     0                    00
18      ResultRow      2     2     0                    00  output=r[2..3]
19      Noop           0     0     0                    00  End WHERE-core
20    Next           0     6     0                    01
21    Noop           0     0     0                    00  End WHERE-loop0: t1
22    Halt           0     0     0                    00
23    Transaction    0     0     1     0              01  usesStmtJournal=0
24    Goto           0     1     0                    00
sqlite>

sqlite> SELECT * FROM t1 NOT INDEXED WHERE c1 == (SELECT c1 FROM t1);
QUERY PLAN
|--SCAN TABLE t1 (~262144 rows)
`--SCALAR SUBQUERY 1
   `--SCAN TABLE t1 (~1048576 rows)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     35    0                    00  Start at 35
1     OpenRead       0     2     0     2              00  root=2 iDb=0; t1
2     ColumnsUsed    0     0     0     3              00
3     Explain        3     0     0     SCAN TABLE t1 (~262144 rows)  00
4     Noop           0     0     0                    00  Begin WHERE-loop0: t1
5     Rewind         0     33    0                    00
6       Column         0     1     1                    00  r[1]=t1.c1
7       RealAffinity   1     0     0                    00
8       Integer        24    3     0                    00  r[3]=24; return address
9       Once           0     24    0                    00
10      Null           0     4     4                    00  r[4..4]=NULL; Init subquery result
11      Integer        1     5     0                    00  r[5]=1; LIMIT counter
12      OpenRead       1     2     0     2              00  root=2 iDb=0; t1
13      ColumnsUsed    1     0     0     2              00
14      Explain        14    0     0     SCAN TABLE t1 (~1048576 rows)  00
15      Noop           0     0     0                    00  Begin WHERE-loop0: t1
16      Rewind         1     23    0                    00
17        Noop           0     0     0                    00  Begin WHERE-core
18        Column         1     1     4                    00  r[4]=t1.c1
19        RealAffinity   4     0     0                    00
20        DecrJumpZero   5     24    0                    00  if (--r[5])==0 goto 24
21        Noop           0     0     0                    00  End WHERE-core
22      Next           1     17    0                    01
23      Noop           0     0     0                    00  End WHERE-loop0: t1
24      Return         3     0     0                    00
25      Ne             4     32    1     (BINARY)       53  if r[1]!=r[4] goto 32
26      Noop           0     0     0                    00  Begin WHERE-core
27      Column         0     0     6                    00  r[6]=t1.c0
28      Column         0     1     7                    00  r[7]=t1.c1
29      RealAffinity   7     0     0                    00
30      ResultRow      6     2     0                    00  output=r[6..7]
31      Noop           0     0     0                    00  End WHERE-core
32    Next           0     6     0                    01
33    Noop           0     0     0                    00  End WHERE-loop0: t1
34    Halt           0     0     0                    00
35    Transaction    0     0     1     0              01  usesStmtJournal=0
36    Goto           0     1     0                    00
|5.76460752303424e+17
sqlite>

sqlite> SELECT * FROM t1 WHERE c1 == (SELECT c1 FROM t1);
QUERY PLAN
|--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~10 rows)
`--SCALAR SUBQUERY 1
   `--SCAN TABLE t1 (~1048576 rows)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     35    0                    00  Start at 35
1     OpenRead       2     3     0     k(3,,,)        02  root=3 iDb=0; sqlite_autoindex_t1_1
2     ColumnsUsed    2     0     0     3              00
3     Explain        3     0     0     SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~10 rows)  00            
4     Noop           0     0     0                    00  Begin WHERE-loop0: t1
5     Integer        21    2     0                    00  r[2]=21; return address
6     Once           0     21    0                    00
7     Null           0     3     3                    00  r[3..3]=NULL; Init subquery result
8     Integer        1     4     0                    00  r[4]=1; LIMIT counter
9     OpenRead       1     2     0     2              00  root=2 iDb=0; t1
10    ColumnsUsed    1     0     0     2              00
11    Explain        11    0     0     SCAN TABLE t1 (~1048576 rows)  00
12    Noop           0     0     0                    00  Begin WHERE-loop0: t1
13    Rewind         1     20    0                    00
14      Noop           0     0     0                    00  Begin WHERE-core
15      Column         1     1     3                    00  r[3]=t1.c1
16      RealAffinity   3     0     0                    00
17      DecrJumpZero   4     21    0                    00  if (--r[4])==0 goto 21
18      Noop           0     0     0                    00  End WHERE-core
19    Next           1     14    0                    01
20    Noop           0     0     0                    00  End WHERE-loop0: t1
21    Return         2     0     0                    00
22    IsNull         3     33    0                    00  if r[3]==NULL goto 33
23    Affinity       3     1     0     E              00  affinity(r[3])
24    SeekGE         2     33    3     1              00  key=r[3]
25      IdxGT          2     33    3     1              00  key=r[3]
26      Noop           0     0     0                    00  Begin WHERE-core
27      Column         2     1     5                    00  r[5]=t1.c0
28      Column         2     0     6                    00  r[6]=t1.c1
29      RealAffinity   6     0     0                    00
30      ResultRow      5     2     0                    00  output=r[5..6]
31      Noop           0     0     0                    00  End WHERE-core
32    Next           2     25    0                    00
33    Noop           0     0     0                    00  End WHERE-loop0: t1
34    Halt           0     0     0                    00
35    Transaction    0     0     1     0              01  usesStmtJournal=0
36    Goto           0     1     0                    00
sqlite>


---
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-
>[hidden email]] On Behalf Of Manuel Rigger
>Sent: Saturday, 4 May, 2019 12:25
>To: SQLite mailing list
>Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
>
>Sorry, I should have anticipated that we get slightly different
>values.
>Shouldn't the query "SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM
>t1);"
>return a result though?
>
>Best,
>Manuel
>
>On Sat, May 4, 2019 at 8:17 PM Keith Medcalf <[hidden email]>
>wrote:
>
>>
>> Ooopsie ... that should have been 1e17, and it appears to be fine,
>except
>> that:
>>
>> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1);
>>
>> does not work ever though the value returned from the subselect
>should be
>> exactly the value in the index ...
>>
>> A table scan does however work correctly ...
>>
>> sqlite> SELECT ALL * FROM t1 not indexed WHERE c1 = (select c1 from
>t1);
>> |5.76460752303423e+17
>>
>>
>> ---
>> 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-
>> >[hidden email]] On Behalf Of Keith Medcalf
>> >Sent: Saturday, 4 May, 2019 12:09
>> >To: SQLite mailing list
>> >Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
>> >
>> >
>> >There is, however, something weird:
>> >
>> >SQLite version 3.29.0 2019-05-04 17:32:07
>> >Enter ".help" for usage hints.
>> >Connected to a transient in-memory database.
>> >Use ".open FILENAME" to reopen on a persistent database.
>> >sqlite> .version
>> >SQLite 3.29.0 2019-05-04 17:32:07
>> >c2e439bccc40825e211bfa9a88e6a251ff066ca7453d4e7cb5eab56ce733alt2
>> >zlib version 1.2.11
>> >gcc-8.1.0
>> >sqlite> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
>> >sqlite> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
>> >sqlite> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>> >sqlite> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1);
>> >sqlite> SELECT ALL * FROM t1 WHERE c1 > (select c1 - 1 from t1);
>> >sqlite> select c1 from t1;
>> >5.76460752303423e+17
>> >sqlite> select c1 - 1 from t1;
>> >5.76460752303423e+17
>> >sqlite>
>> >
>> >
>> >---
>> >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-
>> >>[hidden email]] On Behalf Of Richard Hipp
>> >>Sent: Saturday, 4 May, 2019 11:49
>> >>To: SQLite mailing list
>> >>Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
>> >>
>> >>On 5/4/19, Manuel Rigger <[hidden email]> wrote:
>> >>> Hi everyone,
>> >>>
>> >>> Consider the following example:
>> >>>
>> >>> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
>> >>> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
>> >>> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>> >>>
>> >>> I would expect the row to be fetched, which is not the case.
>> >>
>> >>But 0x7ffffffffffffff != 5.76460752303423e+17.  Try it:
>> >>
>> >>   SELECT 0x7ffffffffffffff != 5.76460752303423e+17;
>> >>
>> >>You should get back 0.
>> >>
>> >>The rule of thumb is to never expect the == operator to give a
>> >>meaningful answer for floating-point numbers.  Only use <, <=, >,
>> >and
>> >>>=.
>> >>
>> >>--
>> >>D. Richard Hipp
>> >>[hidden email]
>> >>_______________________________________________
>> >>sqlite-users mailing list
>> >>[hidden email]
>> >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>> >
>> >
>> >
>> >_______________________________________________
>> >sqlite-users mailing list
>> >[hidden email]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with REAL PRIMARY KEY

Manuel Rigger
It seems that this example now works as expected:

CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM t1);

I guess that this test case triggered the same issue as the one I later
reported in the email "Series of statements results in a malformed database
disk image" [1], which has been fixed (see
https://www.sqlite.org/src/tktview/6c1d3febc00b22d457c7). So I think that
this bug report can be considered a duplicate of the later one.

[1]
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-May/084483.html

On Sat, May 4, 2019 at 8:43 PM Keith Medcalf <[hidden email]> wrote:

>
> I should think so yes ... The query only appears to work if the index on
> the real value is not used -- but there appears no way to do that when
> using the IN operator ...
>
> QUERY PLAN
> |--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~240
> rows)
> `--LIST SUBQUERY 1
>    `--SCAN TABLE t1 (~1048576 rows)
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     37    0                    00  Start at 37
> 1     OpenRead       2     3     0     k(3,,,)        02  root=3 iDb=0;
> sqlite_autoindex_t1_1
> 2     ColumnsUsed    2     0     0     3              00
> 3     Explain        3     0     0     SEARCH TABLE t1 USING COVERING
> INDEX sqlite_autoindex_t1_1 (c1=?) (~240 rows)  00
> 4     Noop           0     0     0                    00  Begin
> WHERE-loop0: t1
> 5     Integer        21    2     0                    00  r[2]=21; return
> address
> 6     Once           0     21    0                    00
> 7     OpenEphemeral  3     1     0     k(1,B)         00  nColumn=1;
> Result of SELECT 1
> 8     OpenRead       1     2     0     2              00  root=2 iDb=0; t1
> 9     ColumnsUsed    1     0     0     2              00
> 10    Explain        10    0     0     SCAN TABLE t1 (~1048576 rows)  00
> 11    Noop           0     0     0                    00  Begin
> WHERE-loop0: t1
> 12    Rewind         1     20    0                    00
> 13      Noop           0     0     0                    00  Begin
> WHERE-core
> 14      Column         1     1     3                    00  r[3]=t1.c1
> 15      RealAffinity   3     0     0                    00
> 16      MakeRecord     3     1     4     C              00
> r[4]=mkrec(r[3])
> 17      IdxInsert      3     4     3     1              00  key=r[4]
> 18      Noop           0     0     0                    00  End WHERE-core
> 19    Next           1     13    0                    01
> 20    Noop           0     0     0                    00  End WHERE-loop0:
> t1
> 21    Return         2     0     0                    00
> 22    Rewind         3     35    0                    00
> 23      Column         3     0     1                    00  r[1]=
> 24      IsNull         1     34    0                    00  if r[1]==NULL
> goto 34
> 25      SeekGE         2     34    1     1              00  key=r[1]
> 26        IdxGT          2     34    1     1              00  key=r[1]
> 27        Noop           0     0     0                    00  Begin
> WHERE-core
> 28        Column         2     1     5                    00  r[5]=t1.c0
> 29        Column         2     0     6                    00  r[6]=t1.c1
> 30        RealAffinity   6     0     0                    00
> 31        ResultRow      5     2     0                    00
> output=r[5..6]
> 32        Noop           0     0     0                    00  End
> WHERE-core
> 33      Next           2     26    0                    00
> 34    Next           3     23    0                    00
> 35    Noop           0     0     0                    00  End WHERE-loop0:
> t1
> 36    Halt           0     0     0                    00
> 37    Transaction    0     0     1     0              01  usesStmtJournal=0
> 38    Goto           0     1     0                    00
> sqlite>
>
> sqlite> SELECT * FROM t1 NOT INDEXED WHERE c1 IN (SELECT c1 FROM t1);
> QUERY PLAN
> |--SCAN TABLE t1 (~983040 rows)
> `--USING INDEX sqlite_autoindex_t1_1 FOR IN-OPERATOR
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     23    0                    00  Start at 23
> 1     OpenRead       0     2     0     2              00  root=2 iDb=0; t1
> 2     ColumnsUsed    0     0     0     3              00
> 3     Explain        3     0     0     SCAN TABLE t1 (~983040 rows)  00
> 4     Noop           0     0     0                    00  Begin
> WHERE-loop0: t1
> 5     Rewind         0     21    0                    00
> 6       Noop           0     0     0                    00  begin IN expr
> 7       Once           0     9     0                    00
> 8       OpenRead       2     3     0     k(3,,,)        00  root=3 iDb=0;
> sqlite_autoindex_t1_1
> 9       Column         0     1     1                    00  r[1]=t1.c1
> 10      RealAffinity   1     0     0                    00
> 11      IsNull         1     20    0                    00  if r[1]==NULL
> goto 20
> 12      Affinity       1     1     0     C              00  affinity(r[1])
> 13      NotFound       2     20    1     1              00  key=r[1]; end
> IN expr
> 14      Noop           0     0     0                    00  Begin
> WHERE-core
> 15      Column         0     0     2                    00  r[2]=t1.c0
> 16      Column         0     1     3                    00  r[3]=t1.c1
> 17      RealAffinity   3     0     0                    00
> 18      ResultRow      2     2     0                    00  output=r[2..3]
> 19      Noop           0     0     0                    00  End WHERE-core
> 20    Next           0     6     0                    01
> 21    Noop           0     0     0                    00  End WHERE-loop0:
> t1
> 22    Halt           0     0     0                    00
> 23    Transaction    0     0     1     0              01  usesStmtJournal=0
> 24    Goto           0     1     0                    00
>
> sqlite> SELECT * FROM t1 not indexed WHERE c1 IN (SELECT c1 FROM t1 not
> indexed);
> QUERY PLAN
> |--SCAN TABLE t1 (~983040 rows)
> `--USING INDEX sqlite_autoindex_t1_1 FOR IN-OPERATOR
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     23    0                    00  Start at 23
> 1     OpenRead       0     2     0     2              00  root=2 iDb=0; t1
> 2     ColumnsUsed    0     0     0     3              00
> 3     Explain        3     0     0     SCAN TABLE t1 (~983040 rows)  00
> 4     Noop           0     0     0                    00  Begin
> WHERE-loop0: t1
> 5     Rewind         0     21    0                    00
> 6       Noop           0     0     0                    00  begin IN expr
> 7       Once           0     9     0                    00
> 8       OpenRead       2     3     0     k(3,,,)        00  root=3 iDb=0;
> sqlite_autoindex_t1_1
> 9       Column         0     1     1                    00  r[1]=t1.c1
> 10      RealAffinity   1     0     0                    00
> 11      IsNull         1     20    0                    00  if r[1]==NULL
> goto 20
> 12      Affinity       1     1     0     C              00  affinity(r[1])
> 13      NotFound       2     20    1     1              00  key=r[1]; end
> IN expr
> 14      Noop           0     0     0                    00  Begin
> WHERE-core
> 15      Column         0     0     2                    00  r[2]=t1.c0
> 16      Column         0     1     3                    00  r[3]=t1.c1
> 17      RealAffinity   3     0     0                    00
> 18      ResultRow      2     2     0                    00  output=r[2..3]
> 19      Noop           0     0     0                    00  End WHERE-core
> 20    Next           0     6     0                    01
> 21    Noop           0     0     0                    00  End WHERE-loop0:
> t1
> 22    Halt           0     0     0                    00
> 23    Transaction    0     0     1     0              01  usesStmtJournal=0
> 24    Goto           0     1     0                    00
> sqlite>
>
> sqlite> SELECT * FROM t1 NOT INDEXED WHERE c1 == (SELECT c1 FROM t1);
> QUERY PLAN
> |--SCAN TABLE t1 (~262144 rows)
> `--SCALAR SUBQUERY 1
>    `--SCAN TABLE t1 (~1048576 rows)
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     35    0                    00  Start at 35
> 1     OpenRead       0     2     0     2              00  root=2 iDb=0; t1
> 2     ColumnsUsed    0     0     0     3              00
> 3     Explain        3     0     0     SCAN TABLE t1 (~262144 rows)  00
> 4     Noop           0     0     0                    00  Begin
> WHERE-loop0: t1
> 5     Rewind         0     33    0                    00
> 6       Column         0     1     1                    00  r[1]=t1.c1
> 7       RealAffinity   1     0     0                    00
> 8       Integer        24    3     0                    00  r[3]=24;
> return address
> 9       Once           0     24    0                    00
> 10      Null           0     4     4                    00  r[4..4]=NULL;
> Init subquery result
> 11      Integer        1     5     0                    00  r[5]=1; LIMIT
> counter
> 12      OpenRead       1     2     0     2              00  root=2 iDb=0;
> t1
> 13      ColumnsUsed    1     0     0     2              00
> 14      Explain        14    0     0     SCAN TABLE t1 (~1048576 rows)  00
> 15      Noop           0     0     0                    00  Begin
> WHERE-loop0: t1
> 16      Rewind         1     23    0                    00
> 17        Noop           0     0     0                    00  Begin
> WHERE-core
> 18        Column         1     1     4                    00  r[4]=t1.c1
> 19        RealAffinity   4     0     0                    00
> 20        DecrJumpZero   5     24    0                    00  if
> (--r[5])==0 goto 24
> 21        Noop           0     0     0                    00  End
> WHERE-core
> 22      Next           1     17    0                    01
> 23      Noop           0     0     0                    00  End
> WHERE-loop0: t1
> 24      Return         3     0     0                    00
> 25      Ne             4     32    1     (BINARY)       53  if r[1]!=r[4]
> goto 32
> 26      Noop           0     0     0                    00  Begin
> WHERE-core
> 27      Column         0     0     6                    00  r[6]=t1.c0
> 28      Column         0     1     7                    00  r[7]=t1.c1
> 29      RealAffinity   7     0     0                    00
> 30      ResultRow      6     2     0                    00  output=r[6..7]
> 31      Noop           0     0     0                    00  End WHERE-core
> 32    Next           0     6     0                    01
> 33    Noop           0     0     0                    00  End WHERE-loop0:
> t1
> 34    Halt           0     0     0                    00
> 35    Transaction    0     0     1     0              01  usesStmtJournal=0
> 36    Goto           0     1     0                    00
> |5.76460752303424e+17
> sqlite>
>
> sqlite> SELECT * FROM t1 WHERE c1 == (SELECT c1 FROM t1);
> QUERY PLAN
> |--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (c1=?) (~10
> rows)
> `--SCALAR SUBQUERY 1
>    `--SCAN TABLE t1 (~1048576 rows)
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     35    0                    00  Start at 35
> 1     OpenRead       2     3     0     k(3,,,)        02  root=3 iDb=0;
> sqlite_autoindex_t1_1
> 2     ColumnsUsed    2     0     0     3              00
> 3     Explain        3     0     0     SEARCH TABLE t1 USING COVERING
> INDEX sqlite_autoindex_t1_1 (c1=?) (~10 rows)  00
> 4     Noop           0     0     0                    00  Begin
> WHERE-loop0: t1
> 5     Integer        21    2     0                    00  r[2]=21; return
> address
> 6     Once           0     21    0                    00
> 7     Null           0     3     3                    00  r[3..3]=NULL;
> Init subquery result
> 8     Integer        1     4     0                    00  r[4]=1; LIMIT
> counter
> 9     OpenRead       1     2     0     2              00  root=2 iDb=0; t1
> 10    ColumnsUsed    1     0     0     2              00
> 11    Explain        11    0     0     SCAN TABLE t1 (~1048576 rows)  00
> 12    Noop           0     0     0                    00  Begin
> WHERE-loop0: t1
> 13    Rewind         1     20    0                    00
> 14      Noop           0     0     0                    00  Begin
> WHERE-core
> 15      Column         1     1     3                    00  r[3]=t1.c1
> 16      RealAffinity   3     0     0                    00
> 17      DecrJumpZero   4     21    0                    00  if (--r[4])==0
> goto 21
> 18      Noop           0     0     0                    00  End WHERE-core
> 19    Next           1     14    0                    01
> 20    Noop           0     0     0                    00  End WHERE-loop0:
> t1
> 21    Return         2     0     0                    00
> 22    IsNull         3     33    0                    00  if r[3]==NULL
> goto 33
> 23    Affinity       3     1     0     E              00  affinity(r[3])
> 24    SeekGE         2     33    3     1              00  key=r[3]
> 25      IdxGT          2     33    3     1              00  key=r[3]
> 26      Noop           0     0     0                    00  Begin
> WHERE-core
> 27      Column         2     1     5                    00  r[5]=t1.c0
> 28      Column         2     0     6                    00  r[6]=t1.c1
> 29      RealAffinity   6     0     0                    00
> 30      ResultRow      5     2     0                    00  output=r[5..6]
> 31      Noop           0     0     0                    00  End WHERE-core
> 32    Next           2     25    0                    00
> 33    Noop           0     0     0                    00  End WHERE-loop0:
> t1
> 34    Halt           0     0     0                    00
> 35    Transaction    0     0     1     0              01  usesStmtJournal=0
> 36    Goto           0     1     0                    00
> sqlite>
>
>
> ---
> 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-
> >[hidden email]] On Behalf Of Manuel Rigger
> >Sent: Saturday, 4 May, 2019 12:25
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
> >
> >Sorry, I should have anticipated that we get slightly different
> >values.
> >Shouldn't the query "SELECT * FROM t1 WHERE c1 IN (SELECT c1 FROM
> >t1);"
> >return a result though?
> >
> >Best,
> >Manuel
> >
> >On Sat, May 4, 2019 at 8:17 PM Keith Medcalf <[hidden email]>
> >wrote:
> >
> >>
> >> Ooopsie ... that should have been 1e17, and it appears to be fine,
> >except
> >> that:
> >>
> >> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1);
> >>
> >> does not work ever though the value returned from the subselect
> >should be
> >> exactly the value in the index ...
> >>
> >> A table scan does however work correctly ...
> >>
> >> sqlite> SELECT ALL * FROM t1 not indexed WHERE c1 = (select c1 from
> >t1);
> >> |5.76460752303423e+17
> >>
> >>
> >> ---
> >> 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-
> >> >[hidden email]] On Behalf Of Keith Medcalf
> >> >Sent: Saturday, 4 May, 2019 12:09
> >> >To: SQLite mailing list
> >> >Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
> >> >
> >> >
> >> >There is, however, something weird:
> >> >
> >> >SQLite version 3.29.0 2019-05-04 17:32:07
> >> >Enter ".help" for usage hints.
> >> >Connected to a transient in-memory database.
> >> >Use ".open FILENAME" to reopen on a persistent database.
> >> >sqlite> .version
> >> >SQLite 3.29.0 2019-05-04 17:32:07
> >> >c2e439bccc40825e211bfa9a88e6a251ff066ca7453d4e7cb5eab56ce733alt2
> >> >zlib version 1.2.11
> >> >gcc-8.1.0
> >> >sqlite> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
> >> >sqlite> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
> >> >sqlite> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
> >> >sqlite> SELECT ALL * FROM t1 WHERE c1 = (select c1 from t1);
> >> >sqlite> SELECT ALL * FROM t1 WHERE c1 > (select c1 - 1 from t1);
> >> >sqlite> select c1 from t1;
> >> >5.76460752303423e+17
> >> >sqlite> select c1 - 1 from t1;
> >> >5.76460752303423e+17
> >> >sqlite>
> >> >
> >> >
> >> >---
> >> >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-
> >> >>[hidden email]] On Behalf Of Richard Hipp
> >> >>Sent: Saturday, 4 May, 2019 11:49
> >> >>To: SQLite mailing list
> >> >>Subject: Re: [sqlite] Problem with REAL PRIMARY KEY
> >> >>
> >> >>On 5/4/19, Manuel Rigger <[hidden email]> wrote:
> >> >>> Hi everyone,
> >> >>>
> >> >>> Consider the following example:
> >> >>>
> >> >>> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
> >> >>> INSERT INTO t1(c1) VALUES (0X7ffffffffffffff);;
> >> >>> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
> >> >>>
> >> >>> I would expect the row to be fetched, which is not the case.
> >> >>
> >> >>But 0x7ffffffffffffff != 5.76460752303423e+17.  Try it:
> >> >>
> >> >>   SELECT 0x7ffffffffffffff != 5.76460752303423e+17;
> >> >>
> >> >>You should get back 0.
> >> >>
> >> >>The rule of thumb is to never expect the == operator to give a
> >> >>meaningful answer for floating-point numbers.  Only use <, <=, >,
> >> >and
> >> >>>=.
> >> >>
> >> >>--
> >> >>D. Richard Hipp
> >> >>[hidden email]
> >> >>_______________________________________________
> >> >>sqlite-users mailing list
> >> >>[hidden email]
> >> >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >> >
> >> >
> >> >
> >> >_______________________________________________
> >> >sqlite-users mailing list
> >> >[hidden email]
> >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users