Re: [sqlite] Using SQLite internal recognizers eg: SQLITE_PRIVATE int sqlite3AtoF()

[petern]

I am drawing from parallel functionality in the existing API.Roughly,
the API sqlite3_buffer_numeric_type() would simply be the buffer input
version of the existing API sqlite3_value_numeric_type().  But instead of
operating on a sqlite3_value parameter, it would read from a pzBuffer
parameter (plus optional length and optional encoding) to return one of
SQLITE_FLOAT, SQLITE_INTEGER, or SQLITE_NULL by directly calling on the
internal recognizers.






On Tue, Jan 23, 2018 at 6:09 PM, Richard Hipp  wrote:

> On 1/23/18, petern  wrote:
> > Any chance of publishing a modest but hardened "int
> > sqlite3_numeric_buffer_type(const char*pBuffer,int length,int encoding)"
> > API that extensions can use?
>
> I'm not sure what "sqlite3_numeric_buffer_type()" is suppose to do?
> --
> 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] Using SQLite internal recognizers eg: SQLITE_PRIVATE int sqlite3AtoF()

[Richard Hipp]

On 1/23/18, petern  wrote:
> Any chance of publishing a modest but hardened "int
> sqlite3_numeric_buffer_type(const char*pBuffer,int length,int encoding)"
> API that extensions can use?

I'm not sure what "sqlite3_numeric_buffer_type()" is suppose to do?
-- 
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

Re: [sqlite] Using SQLite internal recognizers eg: SQLITE_PRIVATE int sqlite3AtoF()

[petern]

Any chance of publishing a modest but hardened "int
sqlite3_numeric_buffer_type(const char*pBuffer,int length,int encoding)"
API that extensions can use?

On Tue, Jan 23, 2018 at 4:43 PM, Richard Hipp  wrote:

> On 1/23/18, petern  wrote:
> > What is the fastest forward compatible way to gain use of the internal
> > buffer value recognizers such as "SQLITE_PRIVATE int sqlite3AtoF()" in
> > external C programs?
> >
>
> There is no forwards-compatible way to do that.  We reserve the right
> to change the design and/or behavior of all internal interfaces at any
> time and for any reason.  And we do.  Usually there are some internal
> interface changes on every release.  With a quick glance, I count 10
> separate, incompatble changes in the 3.22.0 release, with no telling
> how many others I have overlooked.
>
> Furthermore, the internal interfaces are not hardened for general use
> and are not general purpose.  They will typically have quirks and
> caveats and corner-cases that need to be avoided.
> --
> 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] Using SQLite internal recognizers eg: SQLITE_PRIVATE int sqlite3AtoF()

[Richard Hipp]

On 1/23/18, petern  wrote:
> What is the fastest forward compatible way to gain use of the internal
> buffer value recognizers such as "SQLITE_PRIVATE int sqlite3AtoF()" in
> external C programs?
>

There is no forwards-compatible way to do that.  We reserve the right
to change the design and/or behavior of all internal interfaces at any
time and for any reason.  And we do.  Usually there are some internal
interface changes on every release.  With a quick glance, I count 10
separate, incompatble changes in the 3.22.0 release, with no telling
how many others I have overlooked.

Furthermore, the internal interfaces are not hardened for general use
and are not general purpose.  They will typically have quirks and
caveats and corner-cases that need to be avoided.
-- 
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] Using SQLite internal recognizers eg: SQLITE_PRIVATE int sqlite3AtoF()

[petern]

What is the fastest forward compatible way to gain use of the internal
buffer value recognizers such as "SQLITE_PRIVATE int sqlite3AtoF()" in
external C programs?

The goal is to efficiently compute exactly how SQLite would taxonomically
classify {numeric,float,integer,...} a buffer string value if it were used
in a statement.

Obviously the buffer under test could simply be composed into a "SELECT
typeof('$buffer')" statement and the result string read back from the db
exection step().  However prepare() + bind() + step() is slow compared to
directly calling natively compiled recognizer functions.

The computationally faster alternatives are (1) patch out SQLITE_PRIVATE on
the recognizer functions in a custom build to export them or (2)
copy/paste/fixup the recognizer function snippets into the external
compilation unit and hope they don't change too much.

What other alternatives are possible?

Peter
___
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

[Jens Alfke]

> On Jan 22, 2018, at 10:12 PM, Cezary H. Noweta  wrote:
> 
> I suppose that 20 digits is taken from the fact: 64ln(2)/ln(10)=19.2..., 
> however, for 64bit mantissa (long double) it is not enough (to be represented 
> exactly), for 53bit mantissa it is too many. Besides that, 17 digits + one 
> rounding digit fit in i64, while 20 decimal digits do not.

I agree. A standard `double` value with a 53-bit mantissa is only accurate to 
17 decimal digits, so formatting it with more precision than that is 
unnecessary, and exposes annoying roundoff errors.

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

Re: [sqlite] Tcl binding: Quirk with Tcl variable reference in eval method

[Richard Hipp]

On 1/23/18, Rolf Ade  wrote:
>
> While being able to use Tcl variable references inside db eval SQL
> statements (as in
>
> set name "foo'bar"
> db eval {SELECT * FROM sometable WHERE somecolumn = $name}
>
> ) this does work only for "simple" Tcl variable references.

That is correct.

You can use an array variable, but the index part must be a constant.
For example:

set x(name) "whatever"
db eval {SELECT * FROM tab WHERE col = $x(name)}

As you observce, you cannot use another variable as the index to the array.

-- 
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

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

[Richard Hipp]

On 1/23/18, Petr Kubat  wrote:
> Still present in 3.22.0:
>
> ! e_expr-32.2.5 expected: [integer 9223372036854775807]
> ! e_expr-32.2.5 got:  [real 9.22337203685478e+18]
>
> Is there any more information I can provide to get this looked at?

Ralf provided the information I needed, which was a method of
reproducing the problem.  It is now fixed on trunk.  See
https://www.sqlite.org/src/info/1b02731962c21bb0 for the patch.

-- 
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

Re: [sqlite] unexpected row value error

[Dan Kennedy]

On 01/23/2018 07:55 PM, Mark Brand wrote:

Hi,

The 6th SELECT example below throws an error. This seems unexpected, 
especially given the contrast with example 3, which differs only in 
lacking a seemingly unrelated JOIN.  Am I overlooking something?


Removing the PRIMARY KEY from table x also avoids the error somehow.

Seen on version 3.22.0, and also on 3.19.3.

Mark



Hi Mark,

Thanks for looking into this one. Looks like it has been in since 3.15.0 
(when row-value support was added). Now fixed here:


  http://www.sqlite.org/src/info/14dfd96f9bca2df5

Dan.





CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
CREATE TABLE y ( a );
CREATE TABLE z ( a, b );

INSERT INTO x VALUES (1, 1), (1, 2);
INSERT INTO y VALUES (1);
INSERT INTO z VALUES (1, 1), (1, 2);

SELECT sqlite_version();

-- CASE 1: OK
SELECT * FROM x
WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );

-- CASE 2: OK
SELECT * FROM x
WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );

-- CASE 3: OK
SELECT * FROM x
WHERE (x.a, x.b) IN ( SELECT a, b FROM z );

-- CASE 4: OK
SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );

-- CASE 5: OK
SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );

-- CASE 6: ERROR
SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (x.a, x.b) IN ( SELECT a, b FROM z );

/*
sqlite_version()
3.22.0
a|b
1|1
1|2
a|b
1|1
1|2
a|b
1|1
1|2
a|b|a
1|1|1
1|2|1
a|b|a
1|1|1
1|2|1
Error: near line 34: sub-select returns 2 columns - expected 1
*/

___
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] Tcl binding: Quirk with Tcl variable reference in eval method

[Rolf Ade]

While being able to use Tcl variable references inside db eval SQL
statements (as in

set name "foo'bar"
db eval {SELECT * FROM sometable WHERE somecolumn = $name}

) this does work only for "simple" Tcl variable references.

This script shows this:

package require sqlite3
sqlite3 db ":memory:"
puts [db version]
db eval {
CREATE TABLE test(name text);
INSERT INTO test(name) VALUES('one');
}
set array(a) "one"
set key "a"
puts "\$array(\$key) has the value: '$array($key)'"
puts "Same as \$array(a): '$array(a)'"
db eval {
DELETE FROM test WHERE name = $array($key)
}
puts "State after first DELETE (using \$array(\$key)); nothing deleted"
puts [db eval {SELECT count(1) FROM test}]
puts [db eval {SELECT * FROM test}]
db eval {
DELETE FROM test WHERE name = $array(a)
}
puts "State after second DELETE (using (\$array(a)); now the row is deleted"
puts [db eval {SELECT count(1) FROM test}]
puts [db eval {SELECT * FROM test}]


While $array($key) is a perfect variable reference in Tcl scripts
(depending of course of the values of array and key) it isn't inside a
SQL script provided to the eval method.

I naively expected the sqlite3 SQL parser to revert to Tcl_ParseVar()
(or Tcl_ParseVarName()) to resolve Tcl variable references inside the
SQL - which would happily resolve $array($key) or even more complicated
constructs as $x([expr {$index + 1}]) - but it is obviously done in
another, simpler way.

I suspect there are good reasons for this "limitation" and this is no
big deal, the feature (with its current capabilities) is still very
helpful. Maybe I'm the only one being so bold to have expected
$array($key) to work inside the SQL statement. But perhaps a word of
warning in https://www.sqlite.org/tclsqlite.html#eval would prevent
others to have similar expectations.


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

Re: [sqlite] Bug in unique index

[petern]

The second UNIQUE(v2,v1) constraint is redundant and equivalent to
UNIQUE(v1,v2)

Also consider that {(1,2),(2,1)} has no duplicates:

sqlite> WITH test(v1,v2) AS (VALUES (1,2),(2,1)) SELECT DISTINCT * FROM
test;
v1,v2
1,2
2,1

Peter


On Tue, Jan 23, 2018 at 8:35 AM, Domingo Alvarez Duarte 
wrote:

> Hello !
>
> Maybe I found a bug in sqlite3 unique index, see example bellow:
>
> bug-unique.sql
>
> drop table if exists test;
> create table test(
> id integer primary key,
> v1 integer not null,
> v2 integer not null constraint not_equal check(v1 != v2),
> unique(v1, v2),
> unique(v2, v1)
> );
>
> insert into test values(1, 1, 2);
> insert into test values(2, 2, 1);
> select * from test;
>
> 
>
> sqlite3 < bug-unique.sql
>
> output
>
> 1|1|2
> 2|2|1
> 
>
> I was expecting to have an error trying to insert the second row but
> sqlite3 accepted the duplicated index without error.
>
> Cheers !
>
>
> ___
> 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] [EXTERNAL] Bug in unique index

[Hick Gunter]

Not a bug.

Unique(v1,v2) implies unique(v2,v1) which makes the second definition superflous

(1,2) is distinct from (2,1) (because tuples are ordered), no violation of 
unique.

If you want to exclude equivalent sets, your check needs to be v1 < v2.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Domingo Alvarez Duarte
Gesendet: Dienstag, 23. Jänner 2018 17:36
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Bug in unique index

Hello !

Maybe I found a bug in sqlite3 unique index, see example bellow:

bug-unique.sql

drop table if exists test;
create table test(
 id integer primary key,
 v1 integer not null,
 v2 integer not null constraint not_equal check(v1 != v2),
 unique(v1, v2),
 unique(v2, v1)
);

insert into test values(1, 1, 2);
insert into test values(2, 2, 1);
select * from test;



sqlite3 < bug-unique.sql

output

1|1|2
2|2|1


I was expecting to have an error trying to insert the second row but
sqlite3 accepted the duplicated index without error.

Cheers !


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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in unique index

[Simon Slavin]

> On 23 Jan 2018, at 4:35pm, Domingo Alvarez Duarte  wrote:
> 
> create table test(
> id integer primary key,
> v1 integer not null,
> v2 integer not null constraint not_equal check(v1 != v2),
> unique(v1, v2),
> unique(v2, v1)
> );
> 
> insert into test values(1, 1, 2);
> insert into test values(2, 2, 1); [snip]
> 
> I was expecting to have an error trying to insert the second row but sqlite3 
> accepted the duplicated index without error.

You are not seeing any duplicates.

You have two separate UNIQUE requirements (which are equivalent to one-another, 
so one is not needed).  One of them is seeing (1, 2) and then (2, 1).  The 
other is seeing (2, 1) and then (1, 2).  Neither of them is seeing a duplicate.

Simon.
___
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);

[Jim Callahan]

What locale?
The locale setting may influence character to numeric conversions at the C
language library level.

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.


Although U.S. dollar users are used to the currency symbol to the left of
the digits; in some countries
the currency symbol is to the right of the digits and sometimes there is a
space between the digits and the currency symbol.

Currencies are often represented as decimals (except when accounting
systems use integer pennies) and so a trailing space
in some or all locales may trigger an assumption of either a monetary or
floating point value.

The locale could impact something as low level as an atoi() or atol() C
conversion functions.
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rtref/itol.htm#itol

"These members are 1 if the currency_symbol or int_curr_symbol strings
should precede the value of a monetary amount, or 0 if the strings should
follow the value. The p_cs_precedes and int_p_cs_precedes members apply to
positive amounts (or zero), and the n_cs_precedes and int_n_cs_precedes members
apply to negative amounts."
...
"These members are 1 if a space should appear between the currency_symbol
 or int_curr_symbol strings and the amount, or 0 if no space should appear.
The p_sep_by_space and int_p_sep_by_space members apply to positive amounts
(or zero), and the n_sep_by_space and int_n_sep_by_space members apply to
negative amounts."
https://www.gnu.org/savannah-checkouts/gnu/libc/manual/html_node/Currency-Symbol.html#Currency-Symbol

"In many European countries such as France, Germany, Greece, Scandinavian
countries, the symbol is usually placed after the amount (e.g., 20,50 €)."
Note space between amount and Euro symbol.
https://en.wikipedia.org/wiki/Currency_symbol

Scientific Linux is used at CERN and ETHZ and other European facilities?

So, my guess would be that the space after the number, plus certain locales
would reproduce the issue.


Jim Callahan
Callahan Data Science LLC
Orlando, FL



On Tue, Jan 23, 2018 at 10:22 AM, Ralf Junker  wrote:

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

[sqlite] Bug in unique index

[Domingo Alvarez Duarte]

Hello !

Maybe I found a bug in sqlite3 unique index, see example bellow:

bug-unique.sql

drop table if exists test;
create table test(
    id integer primary key,
    v1 integer not null,
    v2 integer not null constraint not_equal check(v1 != v2),
    unique(v1, v2),
    unique(v2, v1)
);

insert into test values(1, 1, 2);
insert into test values(2, 2, 1);
select * from test;



sqlite3 < bug-unique.sql

output

1|1|2
2|2|1


I was expecting to have an error trying to insert the second row but 
sqlite3 accepted the duplicated index without error.


Cheers !


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

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

[Hick Gunter]

I think I may have found the problem:

The ToNumeric opcode calls sqlite3VdbeMemNumerify() which has (in 3.7.14.1) the 
following code:

if( 0==sqlite3Atoi64(pMem->z, >u.i, pMem->n, pMem->enc) ){
  MemSetTypeFlag(pMem, MEM_Int);
}else{
  pMem->r = sqlite3VdbeRealValue(pMem);
  MemSetTypeFlag(pMem, MEM_Real);
  sqlite3VdbeIntegerAffinity(pMem);
}

So the result is an integer only oif the sqlite3Atoi64 function returns 0

This function hast he following head:

int sqlite3Atoi64(const char *zNum, i64 *pNum, int length, u8 enc){
  int incr = (enc==SQLITE_UTF8?1:2);
  u64 u = 0;
  int neg = 0; /* assume positive */
  int i;
  int c = 0;
  const char *zStart;
  const char *zEnd = zNum + length;
...

Where the superflous space at the end of the string causes the following 
condition to be TRUE

  if( (c!=0 && [i]19*incr ){
/* zNum is empty or contains non-numeric text or is longer
** than 19 digits (thus guaranteeing that it is too large) */
return 1;

The conversion/counting loop inbetween needs to set zEnd to the last character 
scanned to fix this.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Ralf Junker
Gesendet: Dienstag, 23. Jänner 2018 16:22
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] sqlite 3.21.0 bug? SELECT CAST 
('9223372036854775807 ' AS NUMERIC);

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unexpected row value error

[petern]

Confirmed that way too.

CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
CREATE TABLE y ( a );

INSERT INTO x VALUES (1, 1), (1, 2);
INSERT INTO y VALUES (1);

SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a, x.b) IN (VALUES (1,2));
--Error: sub-select returns 2 columns - expected 1

SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a, x.b) IN ((VALUES (1,2)));
--a,b,a
--1,2,1

.version
--SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a


On Tue, Jan 23, 2018 at 7:12 AM, curmudgeon  wrote:

> Probably won't help but the final one works with SELECT in double brackets
>
> SELECT * FROM x
> JOIN y ON y.a = x.a
> WHERE (x.a, x.b) IN ( ( SELECT a, b FROM z ) );
> .
>
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] unexpected row value error

[petern]

Confirmed.  SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a

CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
CREATE TABLE y ( a );
CREATE TABLE z ( a, b );

INSERT INTO x VALUES (1, 1), (1, 2);
INSERT INTO y VALUES (1);
INSERT INTO z VALUES (1, 1), (1, 2);

SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a,2) IN (VALUES (1,2));
Error: sub-select returns 2 columns - expected 1

SELECT * FROM z JOIN y ON y.a = z.a WHERE (z.a,2) IN (VALUES (1,2));
a,b,a
1,1,1
1,2,1


On Tue, Jan 23, 2018 at 4:55 AM, Mark Brand  wrote:

> Hi,
>
> The 6th SELECT example below throws an error. This seems unexpected,
> especially given the contrast with example 3, which differs only in lacking
> a seemingly unrelated JOIN.  Am I overlooking something?
>
> Removing the PRIMARY KEY from table x also avoids the error somehow.
>
> Seen on version 3.22.0, and also on 3.19.3.
>
> Mark
>
> CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
> CREATE TABLE y ( a );
> CREATE TABLE z ( a, b );
>
> INSERT INTO x VALUES (1, 1), (1, 2);
> INSERT INTO y VALUES (1);
> INSERT INTO z VALUES (1, 1), (1, 2);
>
> SELECT sqlite_version();
>
> -- CASE 1: OK
> SELECT * FROM x
> WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );
>
> -- CASE 2: OK
> SELECT * FROM x
> WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );
>
> -- CASE 3: OK
> SELECT * FROM x
> WHERE (x.a, x.b) IN ( SELECT a, b FROM z );
>
> -- CASE 4: OK
> SELECT * FROM x
> JOIN y ON y.a = x.a
> WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );
>
> -- CASE 5: OK
> SELECT * FROM x
> JOIN y ON y.a = x.a
> WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );
>
> -- CASE 6: ERROR
> SELECT * FROM x
> JOIN y ON y.a = x.a
> WHERE (x.a, x.b) IN ( SELECT a, b FROM z );
>
> /*
> sqlite_version()
> 3.22.0
> a|b
> 1|1
> 1|2
> a|b
> 1|1
> 1|2
> a|b
> 1|1
> 1|2
> a|b|a
> 1|1|1
> 1|2|1
> a|b|a
> 1|1|1
> 1|2|1
> Error: near line 34: sub-select returns 2 columns - expected 1
> */
>
> ___
> 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] sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

[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] sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

[Joseph R. Justice]

On Jan 23, 2018 9:32 AM, "Richard Hipp"  wrote:

I'm still unable to reproduce this problem.  I've tried on every
32-bit platform I have at hand:

   * Ubuntu with -m32
   * Android
   * MacOS 10.6.8  with -m32
   * MinGW (32-bit) on Win7
   * MSVC (32-bit) on Win10

They all give the correct answer.   I'm sorry you are having problems.
But it is difficult for me to fix the problem if I am unable to
reproduce it.


Is it possible Mr. Lee or Mr. Kubat could provide a guest login to Dr. Hipp
on a system exhibiting this problem, so that he might be able to
investigate it on a system known to be having the problem?



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

Re: [sqlite] unexpected row value error

[curmudgeon]

Probably won't help but the final one works with SELECT in double brackets

SELECT * FROM x 
JOIN y ON y.a = x.a 
WHERE (x.a, x.b) IN ( ( SELECT a, b FROM z ) ); 
.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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);

[Richard Hipp]

I'm still unable to reproduce this problem.  I've tried on every
32-bit platform I have at hand:

   * Ubuntu with -m32
   * Android
   * MacOS 10.6.8  with -m32
   * MinGW (32-bit) on Win7
   * MSVC (32-bit) on Win10

They all give the correct answer.   I'm sorry you are having problems.
But it is difficult for me to fix the problem if I am unable to
reproduce it.

On 1/23/18, Petr Kubat  wrote:
> Still present in 3.22.0:
>
> ! e_expr-32.2.5 expected: [integer 9223372036854775807]
> ! e_expr-32.2.5 got:  [real 9.22337203685478e+18]
> ! e_expr-32.2.6 expected: [integer 9223372036854775807]
> ! e_expr-32.2.6 got:  [real 9.22337203685478e+18]
> ! e_expr-32.2.8 expected: [integer 901 integer
> 901 integer 901 integer 901
> integer 901 integer 901 integer
> 9223372036854775807 integer 9223372036854775807 integer 9223372036854775807
> real 9.22337203685478e+18 real 9.22337203685478e+18 integer
> 9223372036854775807 integer 9223372036854775807 integer -5 integer -5]
> ! e_expr-32.2.8 got:  [integer 901 real 9.0e+18 real
> 9.0e+18 real 9.0e+18 integer 901 real 9.0e+18 integer
> 9223372036854775807 real 9.22337203685478e+18 real 9.22337203685478e+18 real
> 9.22337203685478e+18 real 9.22337203685478e+18 real 9.22337203685478e+18
> real 9.22337203685478e+18 integer -5 integer -5]
>
> This seems to be happening whenever a string not ending with a number
> ('901x', '901 ',' 901.')
> gets cast to NUMERIC.
>
> Is there any more information I can provide to get this looked at?
>
> On 11/01/2017 01:07 PM, Petr Kubat wrote:
>> Encountering this in Fedora as well while trying to package latest
>> 3.21.0 version. Only on i386.
>>
>> Additionally the test suite fails on some architectures (aarch64,
>> ppc64, s390x) when running the test case fts3expr5-1.5:
>>
>> ! fts3expr5-1.5 expected: [1 {invalid matchinfo blob passed to
>> function rank()}]
>> ! fts3expr5-1.5 got:  [1 {wrong number of arguments to function
>> rank()}]
>>
>>
>> On 10/28/2017 12:43 AM, Timothy J. Lee wrote:
>>> sqlite 3.21.0, built from source on Scientific Linux 6
>>> (which is derived from Red Hat Enterprise Linux 6).
>>>
>>> configure options were:
>>> --enable-threadsafe --enable-threads-override-locks
>>> --enable-load-extension
>>>
>>> CFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_DISABLE_DIRSYNC=1
>>> -DSQLITE_ENABLE_FTS3=3 -DSQLITE_ENABLE_RTREE=1
>>> -DSQLITE_SECURE_DELETE=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 -Wall
>>> -fno-strict-aliasing"
>>>
>>> On i386:
>>>
>>> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
>>> 9.22337203685478e+18
>>> sqlite>
>>>
>>> On x86_64:
>>>
>>> sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
>>> 9223372036854775807
>>> sqlite>
>>>
>>> This difference causes self-tests e_expr-32.2.5 e_expr-32.2.6
>>> e_expr-32.2.8
>>> to fail on i386 (the output on x86_64 is the expected output).
>>> ___
>>> 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
>


-- 
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] unexpected row value error

[Mark Brand]

Hi,

The 6th SELECT example below throws an error. This seems unexpected, 
especially given the contrast with example 3, which differs only in 
lacking a seemingly unrelated JOIN.  Am I overlooking something?


Removing the PRIMARY KEY from table x also avoids the error somehow.

Seen on version 3.22.0, and also on 3.19.3.

Mark

CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
CREATE TABLE y ( a );
CREATE TABLE z ( a, b );

INSERT INTO x VALUES (1, 1), (1, 2);
INSERT INTO y VALUES (1);
INSERT INTO z VALUES (1, 1), (1, 2);

SELECT sqlite_version();

-- CASE 1: OK
SELECT * FROM x
WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );

-- CASE 2: OK
SELECT * FROM x
WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );

-- CASE 3: OK
SELECT * FROM x
WHERE (x.a, x.b) IN ( SELECT a, b FROM z );

-- CASE 4: OK
SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );

-- CASE 5: OK
SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );

-- CASE 6: ERROR
SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (x.a, x.b) IN ( SELECT a, b FROM z );

/*
sqlite_version()
3.22.0
a|b
1|1
1|2
a|b
1|1
1|2
a|b
1|1
1|2
a|b|a
1|1|1
1|2|1
a|b|a
1|1|1
1|2|1
Error: near line 34: sub-select returns 2 columns - expected 1
*/

___
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);

[Petr Kubat]

Still present in 3.22.0:

! e_expr-32.2.5 expected: [integer 9223372036854775807]
! e_expr-32.2.5 got:  [real 9.22337203685478e+18]
! e_expr-32.2.6 expected: [integer 9223372036854775807]
! e_expr-32.2.6 got:  [real 9.22337203685478e+18]
! e_expr-32.2.8 expected: [integer 901 integer 
901 integer 901 integer 901 
integer 901 integer 901 integer 
9223372036854775807 integer 9223372036854775807 integer 9223372036854775807 
real 9.22337203685478e+18 real 9.22337203685478e+18 integer 9223372036854775807 
integer 9223372036854775807 integer -5 integer -5]
! e_expr-32.2.8 got:  [integer 901 real 9.0e+18 real 
9.0e+18 real 9.0e+18 integer 901 real 9.0e+18 integer 
9223372036854775807 real 9.22337203685478e+18 real 9.22337203685478e+18 real 
9.22337203685478e+18 real 9.22337203685478e+18 real 9.22337203685478e+18 real 
9.22337203685478e+18 integer -5 integer -5]

This seems to be happening whenever a string not ending with a number 
('901x', '901 ',' 901.') 
gets cast to NUMERIC.


Is there any more information I can provide to get this looked at?

On 11/01/2017 01:07 PM, Petr Kubat wrote:
Encountering this in Fedora as well while trying to package latest 
3.21.0 version. Only on i386.


Additionally the test suite fails on some architectures (aarch64, 
ppc64, s390x) when running the test case fts3expr5-1.5:


! fts3expr5-1.5 expected: [1 {invalid matchinfo blob passed to 
function rank()}]
! fts3expr5-1.5 got:  [1 {wrong number of arguments to function 
rank()}]



On 10/28/2017 12:43 AM, Timothy J. Lee wrote:

sqlite 3.21.0, built from source on Scientific Linux 6
(which is derived from Red Hat Enterprise Linux 6).

configure options were:
--enable-threadsafe --enable-threads-override-locks 
--enable-load-extension


CFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_DISABLE_DIRSYNC=1 
-DSQLITE_ENABLE_FTS3=3 -DSQLITE_ENABLE_RTREE=1 
-DSQLITE_SECURE_DELETE=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 -Wall 
-fno-strict-aliasing"


On i386:

sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
9.22337203685478e+18
sqlite>

On x86_64:

sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC);
9223372036854775807
sqlite>

This difference causes self-tests e_expr-32.2.5 e_expr-32.2.6 
e_expr-32.2.8

to fail on i386 (the output on x86_64 is the expected output).
___
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] [EXTERNAL] Re: Can this be done with SQLite

[Hick Gunter]

You need to use a temporary table because by the time you select the key 1 
value it has already been overwritten.

BEGIN;
CREATE TEMP TABLE new_speed AS SELECT (key +4) % 5 AS key, speed FROM 
playYouTubeVideo;
UPDATE playYouTubeVideo SET speed = SELECT speed FROM new_speed WHERE 
new_speed.key = playYouTubeVideo.key;
DROP TABLE new_speed;
COMMIT;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Cecil Westerhof
Gesendet: Montag, 22. Jänner 2018 23:12
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Can this be done with SQLite

2018-01-22 23:07 GMT+01:00 Igor Tandetnik :

> On 1/22/2018 4:36 PM, Cecil Westerhof wrote:
>
>>
>> When I do this, I get:
>> sqlite> SELECT *
>> ...> FROM   playYouTubeVideo
>> ...> WHERE  key BETWEEN '1' AND '5'
>> ...> ;
>> 1|1.0
>> 2|2.0
>> 3|3.0
>> 4|4.0
>> 5|5.0
>>
>> [snip]
>>
>> sqlite> SELECT *
>> ...> FROM   playYouTubeVideo
>> ...> WHERE  key BETWEEN '1' AND '5'
>> ...> ;
>> 1|2.0
>> 2|3.0
>> 3|4.0
>> 4|5.0
>> 5|2.0
>>
>> But I want the last one needs to be 1.0.
>>
>
> Something along these lines, perhaps:
>
> update playYouTubeVideo set key=char(61440+unicode(key)); update
> playYouTubeVideo set key=case when key=char(61440+unicode('1')) then
> '5' else char(unicode(key)-61440-1) end;
>

​This also expects the values to be constant. But what I want is that the 
record with key 1 gets the value from key 2, with key 2 from key 3, …

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users