Re: [sqlite] Using SQLite internal recognizers eg: SQLITE_PRIVATE int sqlite3AtoF()
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()
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()
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()
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()
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);
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
> 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
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);
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
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
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
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
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
> 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);
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
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);
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, &pMem->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 && &zNum[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
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
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);
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);
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
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);
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
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);
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
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