Re: [sqlite] Help with assigning default values...

[jose isaias cabrera]

Thanks.  Yes, saw it.

-Original Message- 
From: Simon Slavin

Sent: Tuesday, December 20, 2016 11:54 PM
To: SQLite mailing list ; jic...@barrioinvi.net
Subject: Re: [sqlite] Help with assigning default values...


On 20 Dec 2016, at 9:37pm, jose isaias cabrera  
wrote:


Trying to learn or understand constraints.  Will you please look at the 
following...


Answers were posted to the mailing list.  If you’re not seeing them there’s 
something wrong with your subscription.


Simon. 


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

Re: [sqlite] Help with assigning default values...

[Simon Slavin]

On 20 Dec 2016, at 9:37pm, jose isaias cabrera  wrote:

> Trying to learn or understand constraints.  Will you please look at the 
> following...

Answers were posted to the mailing list.  If you’re not seeing them there’s 
something wrong with your subscription.

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

Re: [sqlite] Incorrect calculation in sqlite3VdbeAllocUnpackedRecord?

[Richard Hipp]

On 12/20/16, Alexey Romanov  wrote:
> It looks to me like the test in line 3512 of
> http://www.sqlite.org/src/annotate?filename=src/vdbeaux.c&checkin=8165f88bb1d40693
> should use `szSpace - off` instead of +, because that's the remaining space
> after &pSpace[off] used in the else branch.
>

I think you may be correct.  Thanks.

On the other hand, it appears that nOff is always zero on current
compilers.  So this may not an urgent problem.  I will investigate
further and perhaps check in some changes.
-- 
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] Help with assigning default values...

[jose isaias cabrera]

Trying to learn or understand constraints.  Will you please look at the 
following...



sqlite> PRAGMA foreign_keys=1;

sqlite> BEGIN TRANSACTION;

sqlite> DROP TABLE IF EXISTS Years;

sqlite> CREATE TABLE Years

   ...> (

   ...> IDYear INTEGER PRIMARY KEY,

   ...> Year TEXT DEFAULT '1980'

   ...> );

sqlite> INSERT INTO Years VALUES(1,'1982');

sqlite> INSERT INTO Years VALUES(2,'1992');

sqlite> INSERT INTO Years VALUES(3,'2016');

sqlite> INSERT INTO Years VALUES(4);

Error: table YEARS has 2 columns but 1 values were supplied

sqlite> COMMIT;





I also tried just a plain insert, 



sqlite> INSERT INTO Years VALUES();

Error: near ")": syntax error



How can I make DEFAULT work?  Thanks.



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

[sqlite] Incorrect calculation in sqlite3VdbeAllocUnpackedRecord?

[Alexey Romanov]

It looks to me like the test in line 3512 of
http://www.sqlite.org/src/annotate?filename=src/vdbeaux.c&checkin=8165f88bb1d40693
should use `szSpace - off` instead of +, because that's the remaining space
after &pSpace[off] used in the else branch.

I am unsure about this because I would expect such a bug to be caught by
tests or by users. But I was unable to persuade myself it's correct and a
teammate familiar with SQLite code agrees that it looks wrong.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Assigning default values...

[Jens Alfke]

FYI, the syntax of the INSERT statement is described here: 
http://www.sqlite.org/lang_insert.html  
and this paragraph describes your situation exactly:

• The first form (with the "VALUES" keyword) creates one or more new 
rows in an existing table. If the column-name list after table-name is omitted 
then the number of values inserted into each row must be the same as the number 
of columns in the table. In this case the result of evaluating the left-most 
expression from each term of the VALUES list is inserted into the left-most 
column of each new row, and so forth for each subsequent expression. If a 
column-name list is specified, then the number of values in each term of the 
VALUE list must match the number of specified columns. Each of the named 
columns of the new row is populated with the results of evaluating the 
corresponding VALUES expression. Table columns that do not appear in the column 
list are populated with the default column value (specified as part of the 
CREATE TABLE statement), or with NULL if no default value is specified.

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

Re: [sqlite] Assigning default values...

[Simon Slavin]

On 20 Dec 2016, at 8:19pm, jic...@yahoo.com wrote:

> sqlite> INSERT INTO Years VALUES(4);
> Error: table YEARS has 2 columns but 1 values were supplied
> 
> […]
> 
> How can I make DEFAULT work?

INSERT INTO Years (IDYear) VALUES (4);

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

[sqlite] Assigning default values...

[jicman]

Trying to learn or understand constraints.  Please take a look at the following…

sqlite> PRAGMA foreign_keys=1;
sqlite> BEGIN TRANSACTION;
sqlite> DROP TABLE IF EXISTS Years;
sqlite> CREATE TABLE Years
   ...> (
   ...> IDYear INTEGER PRIMARY KEY,
   ...> Year TEXT DEFAULT '1980'
   ...> );
sqlite> INSERT INTO Years VALUES(1,'1982');
sqlite> INSERT INTO Years VALUES(2,'1992');
sqlite> INSERT INTO Years VALUES(3,'2016');
sqlite> INSERT INTO Years VALUES(4);
Error: table YEARS has 2 columns but 1 values were supplied
sqlite> COMMIT;


I also tried just a plain insert, 

sqlite> INSERT INTO Years VALUES();
Error: near ")": syntax error

How can I make DEFAULT work?  Thanks.

josé



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

Re: [sqlite] Performance degradation 3.10->3.13

[Richard Hipp]

On 12/20/16, Tobel, Ralf  wrote:
> Hello Everybody,
>
> I’m facing a strange performance degradation from SQLite 3.10 to 3.13.
> The code posted below is around 10 times slower in a release build for
> 64bit.
> Two Visual Studio projects where the problem can be reproduced can be
> downloaded here:
> https://drive.google.com/open?id=0B0DoU-QPegjkcDdkSVl6Rmp6cHM
>
> Does anyone have an idea about the cause?

A performance problems for in-memory databases was introduced by
check-in (https://www.sqlite.org/src/timeline?c=06c1e27ca868) and
fixed by check-in
(https://www.sqlite.org/src/timeline?c=9675518b33e8d407).  The
"Prerelease Snapshot" on the Download page
(https://www.sqlite.org/download.html) includes the fix.
-- 
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] Weird chars inserted

[Kevin Youren]

Ariel, Keith, Rowan, 

apologies, I re-ran the queries a few times, and I decided to include
"rowid" to keep track of the changes.



The experiments were conducted by cut-and-paste of the í character from
the email, hence UTF8, and using x'...' for inserts and concats.

Note, I use sqlite3 shell by preference, but I use both the Firefox
addon and "DB Browser for Sqlite" for GUI convenience - however, for
inserts and updates I use the sqlite3 shell or the C programming
interface. 

In C, I use int rather than char -

FILE *pinfile = NULL;
...
pinfile = fopen(argv[1],"rb");



int ch = fgetc (pinfile); 
/* changed from char to int to allow >127 & UTF */


Also, I use .mode csv and then a spreadsheet quite a lot.

Note, at the end, I added typeof( ) - and most were BLOBs and a couple
as TEXT.




kevin@kevin-Aspire-V5-571G:~$ sqlite3 dir_md5sum_db.sqlite
SQLite version 3.15.2 2016-11-28 19:13:37
Enter ".help" for usage hints.
sqlite> SELECT * FROM dir_md5sum
   ...> where rowid >= 194576;
kev|
kev2|
kev3|
kev4|
sqlite> insert into dir_md5sum values ( 'kev5', x'C3AD');
sqlite> SELECT * FROM dir_md5sum where rowid >= 194576;
kev|
kev2|
kev3|
kev4|
kev5|í
sqlite> .schema
CREATE TABLE dir_md5sum (dir_name text, dir_md5sum text);
sqlite> SELECT rowid, dir_name, hex(dir_name), dir_md5sum,
hex(dir_md5sum), unicode(dir_md5sum)  FROM dir_md5sum where rowid >=
194576;
194576|kev|6B6576|�|EE|65533
194577|kev2|6B657632|�|EE|65533
194578|kev3|6B657633|�|EE|65533
194579|kev4|6B657634|�|ED|65533
194580|kev5|6B657635|í|C3AD|237
sqlite> insert into dir_md5sum values ( 'kev6', 'a' || x'C3AD' || 'b'
);
sqlite> SELECT rowid, dir_name, hex(dir_name), dir_md5sum,
hex(dir_md5sum), unicode(dir_md5sum)  FROM dir_md5sum where rowid >=
194576;
194576|kev|6B6576|�|EE|65533
194577|kev2|6B657632|�|EE|65533
194578|kev3|6B657633|�|EE|65533
194579|kev4|6B657634|�|ED|65533
194580|kev5|6B657635|í|C3AD|237
194581|kev6|6B657636|aíb|61C3AD62|97
sqlite> insert into dir_md5sum values ( 'kev7', 'c' || x'00ED' || 'd'
);
sqlite> SELECT rowid, dir_name, hex(dir_name), dir_md5sum,
hex(dir_md5sum), unicode(dir_md5sum)  FROM dir_md5sum where rowid >=
194576;
194576|kev|6B6576|�|EE|65533
194577|kev2|6B657632|�|EE|65533
194578|kev3|6B657633|�|EE|65533
194579|kev4|6B657634|�|ED|65533
194580|kev5|6B657635|í|C3AD|237
194581|kev6|6B657636|aíb|61C3AD62|97
194582|kev7|6B657637|c|6300ED64|99
sqlite> insert into dir_md5sum values ( 'kev8',  x'00ED'  );
sqlite> SELECT rowid, dir_name, hex(dir_name), dir_md5sum,
hex(dir_md5sum), unicode(dir_md5sum)  FROM dir_md5sum where rowid >=
194576;
194576|kev|6B6576|�|EE|65533
194577|kev2|6B657632|�|EE|65533
194578|kev3|6B657633|�|EE|65533
194579|kev4|6B657634|�|ED|65533
194580|kev5|6B657635|í|C3AD|237
194581|kev6|6B657636|aíb|61C3AD62|97
194582|kev7|6B657637|c|6300ED64|99
194583|kev8|6B657638||00ED|
sqlite> .mode csv
sqlite> .once /home/kevin/Martin.csv
sqlite> SELECT rowid, dir_name, hex(dir_name), dir_md5sum,
hex(dir_md5sum), unicode(dir_md5sum)  FROM dir_md5sum where rowid >=
194576;
sqlite> SELECT rowid, dir_name, hex(dir_name), dir_md5sum,
hex(dir_md5sum), unicode(dir_md5sum), typeof(dir_md5sum)  FROM
dir_md5sum where rowid >= 194576;
194576,kev,6B6576,"�",EE,65533,blob
194577,kev2,6B657632,"�",EE,65533,blob
194578,kev3,6B657633,"�",EE,65533,blob
194579,kev4,6B657634,"�",ED,65533,blob
194580,kev5,6B657635,"í",C3AD,237,blob
194581,kev6,6B657636,"aíb",61C3AD62,97,text
194582,kev7,6B657637,c,6300ED64,99,text
194583,kev8,6B657638,"",00ED,,blob
sqlite> 

regs,

Kev


Date: Mon, 19 Dec 2016 11:12:59 +0800
From: Rowan Worth 
To: SQLite mailing list 
Subject: Re: [sqlite] Weird chars inserted
Message-ID:

Content-Type: text/plain; charset=UTF-8

On 19 December 2016 at 08:24, Kevin  wrote:

> Hi Martin,
>
> I had a go using a terminal session, with default encoding UTF-8.
>
> Try using the hex( ) and unicode( ) functions to check what is
actually
> stored in the sqlite table.
>
> I put a couple of rows at the end of an existing simple table
>
> kevin@kevin-Aspire-V5-571G:~$ sqlite3
/home/kevin/dir_md5sum_db.sqlite
> SQLite version 3.15.2 2016-11-28 19:13:37
> Enter ".help" for usage hints.
> sqlite> SELECT dir_name, hex(dir_name), dir_md5sum, hex(dir_md5sum),
> unicode(dir_md5sum)  FROM dir_md5sum
>...> where rowid >= 194576;
> 194576|kev|6B6576|í|C3AD|237
> 194577|kev2|6B657632|�|ED|65533
> sqlite> .quit
> kevin@kevin-Aspire-V5-571G:~$
>

Hi Kevin,

The problem here lies in whatever inserted these rows. sqlite just
stores
what it is given - it is up to the application to take care of encoding
issues.

In this case the "kev" row has been inserted using utf-8 encoding, so
when
you retrieve this value sqlite emits the bytes 0xC3 0xAD (exactly as
they
were stored), which your terminal interprets as utf-8 and renders the
character í.

The "kev2" row however is not utf-8 encoded. The dir_md5sum column
contains
a single byte 0xED, which is not valid utf-8 (the encoding specifies
that
when the highest bit is on, the

[sqlite] Performance degradation 3.10->3.13

[Tobel, Ralf]

Hello Everybody,

I’m facing a strange performance degradation from SQLite 3.10 to 3.13.
The code posted below is around 10 times slower in a release build for 64bit.
Two Visual Studio projects where the problem can be reproduced can be 
downloaded here: https://drive.google.com/open?id=0B0DoU-QPegjkcDdkSVl6Rmp6cHM

Does anyone have an idea about the cause?

Best Regards,
Ralf

PS: I know that the code is not optimal. It’s just an example where the 
performance problem can be reproduced…


int main()
{
sqlite3* db;

int rc = sqlite3_open(":memory:", &db);
if (rc != SQLITE_OK)
return 1;

const HANDLE  hProc = GetCurrentProcess();
__int64  a, b, c, d;

GetProcessTimes(hProc, (FILETIME*) &a, (FILETIME*) &b, (FILETIME*) &c, 
(FILETIME*) &d);

const double startTime = double(c+d) / 1000.0;

const char createStmt[] = "create table tTest (UsrRowID Text, Column2 
Integer, Column3 Real, Column4 Text)";

sqlite3_stmt* pLocalStmt;
const char*   zTail;
rc = sqlite3_prepare_v2(db, createStmt, -1, &pLocalStmt, &zTail);
if (rc != SQLITE_OK)
{
puts(sqlite3_errmsg(db));
return 1;
}

rc = sqlite3_step(pLocalStmt);
if (rc != SQLITE_DONE)
return 1;

sqlite3_finalize(pLocalStmt);

const char insertStmt[] = "insert into tTest values (?1, ?2, ?3, ?4)";

for (int i = 0; i < 40; i++)
{
rc = sqlite3_prepare_v2(db, insertStmt, -1, &pLocalStmt, 
&zTail);
if (rc != SQLITE_OK)
return 1;

rc = sqlite3_bind_text(pLocalStmt, 1, "UsrRow_", -1, 
SQLITE_TRANSIENT);
rc = sqlite3_bind_int(pLocalStmt, 2, 1);
rc = sqlite3_bind_double(pLocalStmt, 3, 8.5);
rc = sqlite3_bind_text(pLocalStmt, 4, "ExampleText_", -1, 
SQLITE_TRANSIENT);

rc = sqlite3_step(pLocalStmt);
if (rc != SQLITE_DONE)
return 1;

sqlite3_finalize(pLocalStmt);
}

sqlite3_close_v2(db);

GetProcessTimes(hProc, (FILETIME*) &a, (FILETIME*) &b, (FILETIME*) &c, 
(FILETIME*) &d);

const double processTime = double(c+d) / 1000.0 - startTime;
printf("Process Time = %f\n", processTime);

puts("Press  to exit");
getchar();

return 0;
}


-
Siemens Industry Software GmbH; Anschrift: Franz-Geuer-Str. 10, 50823 Köln; 
Gesellschaft mit beschränkter Haftung; Geschäftsführer: Urban August, Daniel 
Trebes; Sitz der Gesellschaft: Köln; Registergericht: Amtsgericht Köln, HRB 
84564
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY

[petern]

Keith.  You are correct.  The online help is the only design document.

Furthermore, from my observations about this forum, the intrepid Dr. Hipp
will often never weigh in to disclose the full design or thought process of
the intended product behavior.

You can put the arbitrary outer column expression scoping implementation
for ORDER BY in the same category as the even more capricious outer column
expression scoping implementation of the LIMIT clause.

There are more examples.

Another good one was the lack of general delimiter character setting
somebody needed for wrangling mainframe data with the shell program.  The
proposed solution was to have the user tweak their local build so the
improvement would never be propagated into the product for others to use!

Still another recent good one was the lack of any explanation for a
peculiar edge case of the equality operator when dealing with boolean 0/1
expressions and conversions from text.  The guy who pointed out the problem
gave some great examples including the one that doesn't work sensibly.
Those examples ought to be in the online help.

From this forum you'll definitely get a lot of replies about how something
currently works in the SQLite language but not why the implementation was
chosen or even if it is only by accident that it currently works in a
certain peculiar non-orthogonal or non-symmetrical way.

FYI, there is a workaround for the expression scoping limitations of ORDER
BY.  You may cast the desired ordering information into a synthetic column
of an intermediate table and then pick up only the payload column of that
table.

Below I've restated your example in CTE form and added the requisite
synthetic column "myorder" to an intermediate table.

WITH t1(x,y) AS (VALUES (2,1),(3,2),(6,4)), t2(z) AS (VALUES (4))
SELECT (SELECT y FROM (SELECT y,abs(x-z)myorder FROM t1 ORDER BY myorder
LIMIT 1))y FROM t2;

Enjoy.

Thanks for posting such a clearly stated problem.
Peter


On Sun, Dec 18, 2016 at 12:23 PM, Keith Maxwell 
wrote:

> Keith
>
> Brilliant! Thank you.
>
> > Correlated (outer) columns are not permitted in the ORDER BY clause
>
> At least I've learnt a new term [1]. Reading the documentation again
> [2], I think a
> change to the paragraph below would make this clearer:
>
> 3. Otherwise, if the ORDER BY expression is any other expression, it is
> evaluated and the returned value used to order the output rows. If the
> SELECT
> statement is a simple SELECT, then an ORDER BY may contain any arbitrary
> expressions. However, if the SELECT is a compound SELECT, then ORDER BY
> expressions that are not aliases to output columns must be exactly the
> same as
> an expression used as an output column.
>
> Maybe add a sentence: "If the SELECT is a correlated inner subquery then
> the
> ORDER BY expression may not include columns from the outer query."
>
> I'm also not sure if it would help to drop the word "any" from "any
> arbitrary
> expressions"?
>
> I'm afraid I didn't get very far trying to look up a standard.
>
> I appreciate your help! Thanks again.
>
> Kind Regards
>
> Keith Maxwell
>
> [1] https://en.wikipedia.org/wiki/Correlated_subquery
> [2] https://www.sqlite.org/lang_select.html#order-by
> ___
> 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] Failed tests on ppc64

[Petr Kubat]

Hi everyone,

I hit some failures in fts3conf.test while buidling latest version of 
sqlite for the ppc64 architecture:


! fts3conf-3.1 expected: [X'01000200']
! fts3conf-3.1 got:  [X'00010002']
! fts3conf-3.2 expected: [X'02000300']
! fts3conf-3.2 got:  [X'00020003']
! fts3conf-3.3 expected: [X'02000500']
! fts3conf-3.3 got:  [X'00020005']
! fts3conf-3.4 expected: [X'01000600']
! fts3conf-3.4 got:  [X'00010006']
! fts3conf-3.5 expected: [X'01000600']
! fts3conf-3.5 got:  [X'00010006']
! fts3conf-3.6 expected: [X'01000200']
! fts3conf-3.6 got:  [X'00010002']
! fts3conf-3.8 expected: [X'02000200']
! fts3conf-3.8 got:  [X'00020002']

From what I understand the issue seems to be caused by how the blob is 
stored in memory on a big-endian architecture and since the query 
function just dumps the value into a string, it differs from what is 
expected.
This seems like an issue that needs to be fixed in the test suite rather 
than in the code, but would like to make sure before writing any 
patches. Does sqlite's test suite even support arch-specific results?


Thanks for any help with this.

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