[sqlite] union, order by, and a phantom record

[R Smith]

Definitely a bug: I distilled the OP's code into an easy repeatable test 
case -

---
create table t(id integer primary key autoincrement, a, b, c);

insert into t values
  (3,1 ,'name','Imogen')
,(5,1 ,'gender'  ,'female')
,(6,1 ,'son' ,'Guiderius')
,(7,1 ,'son' ,'Arvirargus');

select distinct t.*
   from t
   join t as t1 on t.a=t1.a and t1.a = 1
  where t.b = 'name'
union
select distinct t.*
   from t
   join t as t1 on t.a=t1.a and t1.a = 1
  where t.b = 'gender'
  order by t.id

   -- Expected
   --  id |  a  | b| c
   -- --- | --- |  | 
   --  3  |  1  | name | Imogen
   --  5  |  1  | gender   | female

   -- Returned
   --  id |  a  | b| c
   -- --- | --- |  | 
   --  3  |  1  | name | Imogen
   --  5  |  1  | gender   | female
   --  5  |  1  | 1| female

drop table t;

---

If you change almost anything, it works. Remove the "t.a=t1.a and" from 
the first select, and it works.
Remove the order clause and it works.

Cheers,
Ryan


On 2016/02/08 11:12 PM, Poor Yorick wrote:
> The following query produces a third phantom record on my system:
>
>
> = start script =
> package require sqlite3
>
> sqlite3 [namespace current]::db :memory:
>
> db eval {
> create table if not exists eav (
> id integer primary key autoincrement
> ,entity numeric
> ,attribute text
> ,value
> )
> ; insert into eav values
> (3 ,1 ,'name','Imogen')
> ,(5 ,1 ,'gender'  ,'female')
> ,(6,1 ,'son' ,'Guiderius')
> ,(7,1 ,'son' ,'Arvirargus')
> }
>
> set pattern0 1
> set report2 name
> set report3 gender
>
> puts [db eval {
> select distinct eav.* from eav
> join eav as eav0 on eav.entity == eav0.entity
> and eav0.entity == :pattern0 where eav.attribute == :report2
> union
> select distinct eav.* from eav
> join eav as eav0 on eav.entity == eav0.entity
> and eav0.entity == :pattern0 where eav.attribute == :report3
> order by eav.id
> }]
> = end script =
>
> The result is:
>
> 3 1 name Imogen 5 1 gender female 5 1 1 female
>

[sqlite] union, order by, and a phantom record

[Richard Hipp]

On 2/8/16, Richard Hipp  wrote:
> On 2/8/16, Poor Yorick  wrote:
>> The following query produces a third phantom record on my system:
>
> Running "PRAGMA automatic_index=OFF;" might alleviate the symptoms
> your are experiencing, until we can get a proper fix published.
>

The bug appears to be quite a bit more serious.  A proposed fix has
been checked into trunk.  Please try out the trunk to see if that
works better for you.  We will continue to analyze the problem and
write tests in the meantime.

The latest "snapshot" at https://www.sqlite.org/download.html contains
the proposed fix.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] union, order by, and a phantom record

[Poor Yorick]

On 2016-02-08 19:15, Richard Hipp wrote:
> On 2/8/16, Richard Hipp  wrote:
>> On 2/8/16, Poor Yorick  wrote:
>>> The following query produces a third phantom record on my system:
>> 
>> Running "PRAGMA automatic_index=OFF;" might alleviate the symptoms
>> your are experiencing, until we can get a proper fix published.
>> 
> 
> The bug appears to be quite a bit more serious.  A proposed fix has
> been checked into trunk.  Please try out the trunk to see if that
> works better for you.  We will continue to analyze the problem and
> write tests in the meantime.
> 
> The latest "snapshot" at https://www.sqlite.org/download.html contains
> the proposed fix.

My working code has already morphed into something that isn't triggering 
the
bug, but it's gratifying to see a proposed fix so quickly, as I'm likely 
to hit
it again during this project.

-- 
Yorick

[sqlite] Can't create LEFT or RIGHT functions with sqlite3_create_function()

[Eric Hill]

That's fair.  Thanks for looking into it.  I can create JLEFT and JRIGHT or 
something, or just direct people to SUBSTR.  There are reasonable workarounds.

Thanks,

Eric

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Monday, February 08, 2016 3:39 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Can't create LEFT or RIGHT functions with 
sqlite3_create_function()

On 2/8/16, Eric Hill  wrote:
> Hey,
>
> It appears that LEFT and RIGHT are treated as reserved words by 
> SQLite, so my attempts to use sqlite3_create_function() to create my 
> own LEFT and RIGHT SQL functions have been unsuccessful (I'm using 
> 3.8.11.1).  Several databases (SQL Server, MySQL) define their own 
> LEFT and RIGHT functions for performing sub-string functions, 
> apparently without conflicting with LEFT JOIN/RIGHT JOIN syntax.  Is that 
> just not possible for SQLite?

Not easily, it seems.  There is a list of keywords that can fallback to be 
identifiers
(https://www.sqlite.org/src/artifact/d7bff41d4?ln=220,233) if they cannot be 
parsed as their original keyword value.  I tried adding JOIN_KW to that list.  
(JOIN_KW is a compound keyword that includes "LEFT", "RIGHT", "NATURAL", 
"CROSS", "FULL", "INNER", and "OUTER".) The result compiled, but lots of tests 
failed.  I don't know if that is something that would be easy to fix or not.

Even if it were possible to fix it, I am not in a big rush to do so.
By allowing LEFT and RIGHT to be identifiers, we would be making a promise that 
they can be identifiers in all future versions of SQLite, which puts an 
additional constraint on future changes.  I'm not sure this is worth it.

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can't create LEFT or RIGHT functions with sqlite3_create_function()

[Eric Hill]

Hey,

It appears that LEFT and RIGHT are treated as reserved words by SQLite, so my 
attempts to use sqlite3_create_function() to create my own LEFT and RIGHT SQL 
functions have been unsuccessful (I'm using 3.8.11.1).  Several databases (SQL 
Server, MySQL) define their own LEFT and RIGHT functions for performing 
sub-string functions, apparently without conflicting with LEFT JOIN/RIGHT JOIN 
syntax.  Is that just not possible for SQLite?

SQLite does support its own quite powerful SUBSTR SQL function allowing 
obtaining substrings from either the left or the right, which is great, but I 
am trying to support SQL syntax that users of my product are already familiar 
with.  But if it just has to be this way, I'll live.

Thanks,

Eric

[sqlite] union, order by, and a phantom record

[Richard Hipp]

On 2/8/16, Poor Yorick  wrote:
> The following query produces a third phantom record on my system:

Running "PRAGMA automatic_index=OFF;" might alleviate the symptoms
your are experiencing, until we can get a proper fix published.

>
>
> = start script =
> package require sqlite3
>
> sqlite3 [namespace current]::db :memory:
>
> db eval {
>  create table if not exists eav (
>  id integer primary key autoincrement
>  ,entity numeric
>  ,attribute text
>  ,value
>  )
>  ; insert into eav values
>  (3 ,1 ,'name','Imogen')
>  ,(5 ,1 ,'gender'  ,'female')
>  ,(6,1 ,'son' ,'Guiderius')
>  ,(7,1 ,'son' ,'Arvirargus')
> }
>
> set pattern0 1
> set report2 name
> set report3 gender
>
> puts [db eval {
>  select distinct eav.* from eav
>  join eav as eav0 on eav.entity == eav0.entity
>  and eav0.entity == :pattern0 where eav.attribute == :report2
>  union
>  select distinct eav.* from eav
>  join eav as eav0 on eav.entity == eav0.entity
>  and eav0.entity == :pattern0 where eav.attribute == :report3
>  order by eav.id
> }]
> = end script =
>
> The result is:
>
> 3 1 name Imogen 5 1 gender female 5 1 1 female
>
> --
> Poor Yorick
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] How to enter Unicode character?

[Dominique Devienne]

On Mon, Feb 8, 2016 at 5:43 PM, Dominique Devienne 
wrote:

> sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from
> t?;
> c?|length(c?)|length(cast(c? as blob))|unicode(c?)
> ??|1|2|252
> ?|1|1|129
>
> sqlite> .schema
>>
> CREATE TABLE t? (c?);
>>
>
What's surprising is that the second row/value is text, and it's supposed
to be UTF-8 encoded,
yet as far as I know, if not value UTF-8 (0x81 is 129, which should be
encoded on two bytes).

sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?),
typeof(c?), quote(cast(c? as blob)) from t?;
c?|length(c?)|length(cast(c? as blob))|unicode(c?)|typeof(c?)|quote(cast(c?
as blob))
??|1|2|252|text|X'C3BC'
?|1|1|129|text|X'81'

OK, I retried with latest sqlite3.exe, and the results are different:

C:\Users\DDevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t? (c?);
sqlite> insert into t? (c?) values (char(252));
sqlite> insert into t? (c?) values ('?');
sqlite> .header on
sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?),
typeof(c?), quote(cast(c? as blob)) from t?;
c?|length(c?)|length(cast(c? as blob))|unicode(c?)|typeof(c?)|quote(cast(c?
as blob))
?|1|2|252|text|X'C3BC'
?|1|2|129|text|X'C281'
sqlite>

but still not quite the same, despite the inserted characters being
logically the same (I think).
x'C3BC' is the correct encoding of lower-case umlaut in UTF-8.
x'C281' OTOH is the UTF-8 encoding of char(129), so the code page was not
taken into account (I think), and the byte taken "as-is".

No? --DD

[sqlite] How to enter Unicode character?

[Dominique Devienne]

On Mon, Feb 8, 2016 at 5:33 PM, Dominique Devienne 
wrote:

> On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot  wrote:
>
>> On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne 
>> wrote:
>> What I mean is the following:
>>
>> sqlite> CREATE TABLE abc();
>>
>> In that line '' should be the German character which look like the
>> Greek letter "beta".
>> ...
>
> In the good old DOS days I would probably just do ALT+NUMPAD2,2,0,
>> but that will most likely won't work here.
>>
>
> It does appear to work for me. I used
> https://en.wikipedia.org/wiki/Code_page_437 as a guide.
>
> C:\Users\DDevienne>chcp
> Active code page: 437
>
> ALT129 (keep pressing ALT, then press successively on keypad 1, 2, 9)
> C:\Users\DDevienne>echo ?
> ?
>
> ALT225
> C:\Users\DDevienne>echo ?
> ?
>
> C:\Users\DDevienne>sqlite3
> SQLite version 3.8.9 2015-04-08 12:16:33
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t? (c?);
> sqlite> insert into t? (c?) values (char(220));
> sqlite> insert into t? (c?) values (?);
> Error: no such column: ?
> sqlite> insert into t? (c?) values ('?');
> sqlite> .header on
> sqlite> select * from t?;
> c?
> ??
> ?
>

Sorry, slight copy/paste issue on the last message.
there are two rows as expect, but only the second ALT129 entered

Above I made the mistake of using char(220), which is the upper-case U
umlaut.
Below I use char(252) (Unicode code point), and ALT129 (Windows CP437),
which are logically the same letter (lower-case u umlaut), but don't show
up the same.
(see https://en.wikipedia.org/wiki/%C3%9C)

sqlite> create table t? (c?);
sqlite> insert into t? (c?) values (char(252));
sqlite> insert into t? (c?) values ('?');
sqlite> .header on
sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from
t?;
c?|length(c?)|length(cast(c? as blob))|unicode(c?)
??|1|2|252
?|1|1|129

sqlite> .schema
>
CREATE TABLE t? (c?);
>

[sqlite] How to enter Unicode character?

[Dominique Devienne]

On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot  wrote:

> On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne 
> wrote:
> What I mean is the following:
>
> sqlite> CREATE TABLE abc();
>
> In that line '' should be the German character which look like the
> Greek letter "beta".
> ...

In the good old DOS days I would probably just do ALT+NUMPAD2,2,0,
> but that will most likely won't work here.
>

It does appear to work for me. I used
https://en.wikipedia.org/wiki/Code_page_437 as a guide.

C:\Users\DDevienne>chcp
Active code page: 437

ALT129 (keep pressing ALT, then press successively on keypad 1, 2, 9)
C:\Users\DDevienne>echo ?
?

ALT225
C:\Users\DDevienne>echo ?
?

C:\Users\DDevienne>sqlite3
SQLite version 3.8.9 2015-04-08 12:16:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t? (c?);
sqlite> insert into t? (c?) values (char(220));
sqlite> insert into t? (c?) values ('?');
sqlite> .header on
sqlite> select * from t?;
c?
??
?sqlite> .schema
CREATE TABLE t? (c?);

But you'll notice sqlite3.exe (not the latest, perhaps it matters) doesn't
show the UTF-8 encoded text value correctly,
but does show the table and column name correctly. But does it convert the
Windows CodePage console entered
text to unicode internally, to make the DB portable to another OS? My guess
is that it stores the bytes as-is, which
is why they show up correctly, otherwise why properly convert idents, but
not text values? Pure conjecture though. --DD

PS: Hopefully the above umlaut and ss above, which look correct in Chrome,
will make it across correctly.

[sqlite] How to enter Unicode character?

[Graham Holden]

In case it helps...

In a command-prompt, ALT 156 (hold ALT while pressing 156 on the NUMERIC 
keypad) uses the current code-page (British pound sign - ? - for me). ?Using 
ALT 0163 (i.e. preceding the character-code with a zero) uses Unicode (also a 
?).

I don't have an sqlite3 shell to hand to test with.

Graham.


Sent from Samsung Mobile

 Original message 
From: Dominique Devienne  
Date: 08/02/2016  16:43  (GMT+00:00) 
To: SQLite mailing list  
Subject: Re: [sqlite] How to enter Unicode character? 

On Mon, Feb 8, 2016 at 5:33 PM, Dominique Devienne 
wrote:

> On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot  wrote:
>
>> On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne 
>> wrote:
>> What I mean is the following:
>>
>> sqlite> CREATE TABLE abc();
>>
>> In that line '' should be the German character which look like the
>> Greek letter "beta".
>> ...
>
> In the good old DOS days I would probably just do ALT+NUMPAD2,2,0,
>> but that will most likely won't work here.
>>
>
> It does appear to work for me. I used
> https://en.wikipedia.org/wiki/Code_page_437 as a guide.
>
> C:\Users\DDevienne>chcp
> Active code page: 437
>
> ALT129 (keep pressing ALT, then press successively on keypad 1, 2, 9)
> C:\Users\DDevienne>echo ?
> ?
>
> ALT225
> C:\Users\DDevienne>echo ?
> ?
>
> C:\Users\DDevienne>sqlite3
> SQLite version 3.8.9 2015-04-08 12:16:33
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t? (c?);
> sqlite> insert into t? (c?) values (char(220));
> sqlite> insert into t? (c?) values (?);
> Error: no such column: ?
> sqlite> insert into t? (c?) values ('?');
> sqlite> .header on
> sqlite> select * from t?;
> c?
> ??
> ?
>

Sorry, slight copy/paste issue on the last message.
there are two rows as expect, but only the second ALT129 entered

Above I made the mistake of using char(220), which is the upper-case U
umlaut.
Below I use char(252) (Unicode code point), and ALT129 (Windows CP437),
which are logically the same letter (lower-case u umlaut), but don't show
up the same.
(see https://en.wikipedia.org/wiki/%C3%9C)

sqlite> create table t? (c?);
sqlite> insert into t? (c?) values (char(252));
sqlite> insert into t? (c?) values ('?');
sqlite> .header on
sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from
t?;
c?|length(c?)|length(cast(c? as blob))|unicode(c?)
??|1|2|252
?|1|1|129

sqlite> .schema
>
CREATE TABLE t? (c?);
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] union, order by, and a phantom record

[Richard Hipp]

On 2/8/16, R Smith  wrote:
> Definitely a bug: I distilled the OP's code into an easy repeatable test
> case -

https://www.sqlite.org/src/tktview/d06a25c84454a372be4e4c970c3c4d4363197219

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar

[Darren Duncan]

David, unless you're wanting to use SQLite's built-in datetime operators, then 
just encode yours somehow and put them in another field type, and decode them 
on 
retrieval into your own datetime types.  Depending what you encode them as, 
pick 
the appropriate built-in type. -- Darren Duncan

On 2016-02-08 5:00 PM, david at andl.org wrote:
> Having read and understood the documentation on Sqlite data types, I'm really 
> just looking for a single recommendation on which choices to make.
>
> I need to store generic data in 5 types: bool, binary, number/decimal, 
> text/nvarchar, time/date/datetime. Decimal has more than 15 digits of 
> precision. Text is Unicode. Time is years - with fractional seconds.
>
> For each type I need to choose:
>
> 1. Type name (string) to use in CREATE TABLE.
> 2. Affinity.
> 3. Datatype that will be returned by C API calls
> 4. Which C API call to use to get and put data values.
>
> My current choices are:
> Bool: TINYINT, INTEGER, value_int
> Binary: BLOB, BLOB, value_blob
> Number: NUMERIC, NUMERIC, value_???
> Text: TEXT, TEXT, Encoding utf-8, value_text
> Time: DATETIME, NUMERIC, value_???.
>
> The first two are easy enough. No problems.
>
> Number: should I choose text functions like sqlite3_value_text() and do all 
> my own conversions, or is there benefit in using a different function 
> according to the storage class/data type? Or is sqlite3_value_numeric_type() 
> the way to go?
>
> Text: I assume this just works, and all text values are UTF-8 by default?
>
> Time: the only supported DATETIME format seems to be ISO8601, which has no 
> explicit C API support? That looks like a lot of conversion overhead for 
> something that is easily stored in a 64-bit integer. What would 
> sqlite3_value_numeric_type() do?
>
> [Has there been any consideration of extending the range of types to include 
> decimal and a binary datetime?]
>
> Sorry if it's a bit scrappy, but I just need to make some choices and then go 
> away and write the code.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org

[sqlite] How to enter Unicode character?

[Dominique Devienne]

On Mon, Feb 8, 2016 at 4:28 PM, Dominique Devienne 
wrote:

> On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot  wrote:
>
>> Now my question is: is it possible to enter a Unicode character
>>
>
> Yes. Just use the char() built-in function. --DD
>

Oh, by "SQLite table contains" you meant the "table name". Sorry, I
misunderstood.
OT, but below shows about unicode chars (German umlaut) in column values,
and not identifiers, FWIW. --DD

sqlite> create table t (c);
sqlite> insert into t values (char(220));
sqlite> select * from t;
??
sqlite> select length(c) from t;
1
sqlite> select length(cast (c as blob)) from t;
2
sqlite> select unicode(c) from t;
220

[sqlite] How to enter Unicode character?

[Stephan Beal]

On Mon, Feb 8, 2016 at 4:27 PM, Igor Korot  wrote:

> At the moment I'm using Win 8.1.
> And the sqlite3.exe shell tool.
>
> So if I go with option 2, I will copy the character and the paste it
> into the command.
> Something like :
>
> sqlite3> CREATE TABLE abc();
>
> right?
>

That's what Unix would do. So... on Windows, probably not ;).


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

[sqlite] How to enter Unicode character?

[Dominique Devienne]

On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot  wrote:

> Now my question is: is it possible to enter a Unicode character
>

Yes. Just use the char() built-in function. --DD

[sqlite] How to enter Unicode character?

[Stephan Beal]

On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot  wrote:

>  Hi, ALL,
> I live in US and therefore have an English-based laptop with an
> English-based keyboard.
>
> I am also a programmer and would like to test what happen if I have a
> SQLite table
> which contains a Unicode character.
>

It depends entirely on your environment/shell. You have multiple options:

- add German a secondary keyboard layout and switch keys as needed. Doh -
your keyboard is US, so you won't know where the 'sharp s' is :/. (My
keyboard is physically German but it's mapped to a US layout.)

- Google for "utf8 character tables" and copy/paste them.


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

[sqlite] Can't create LEFT or RIGHT functions with sqlite3_create_function()

[Richard Hipp]

On 2/8/16, Eric Hill  wrote:
> Hey,
>
> It appears that LEFT and RIGHT are treated as reserved words by SQLite, so
> my attempts to use sqlite3_create_function() to create my own LEFT and RIGHT
> SQL functions have been unsuccessful (I'm using 3.8.11.1).  Several
> databases (SQL Server, MySQL) define their own LEFT and RIGHT functions for
> performing sub-string functions, apparently without conflicting with LEFT
> JOIN/RIGHT JOIN syntax.  Is that just not possible for SQLite?

Not easily, it seems.  There is a list of keywords that can fallback
to be identifiers
(https://www.sqlite.org/src/artifact/d7bff41d4?ln=220,233) if they
cannot be parsed as their original keyword value.  I tried adding
JOIN_KW to that list.  (JOIN_KW is a compound keyword that includes
"LEFT", "RIGHT", "NATURAL", "CROSS", "FULL", "INNER", and "OUTER".)
The result compiled, but lots of tests failed.  I don't know if that
is something that would be easy to fix or not.

Even if it were possible to fix it, I am not in a big rush to do so.
By allowing LEFT and RIGHT to be identifiers, we would be making a
promise that they can be identifiers in all future versions of SQLite,
which puts an additional constraint on future changes.  I'm not sure
this is worth it.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] EBCDIC tester needed

[John McKown]

On Mon, Feb 8, 2016 at 1:45 PM, Richard Hipp  wrote:

> The latest SQLite check-ins contain changes to the SQL language
> tokenizer.  These changes have been tested on Unicode systems, but we
> do not have access to any systems running EBCDIC in order to test
> there.  If you are deploying SQLite on a computer that uses the EBCDIC
> character set, we would appreciate your help in validating the latest
> code.
>
> The "snapshot" at https://www.sqlite.org/download.html contains the
> new code, so you can just download and test that.  Or, contact me via
> private email message if you need further assistance.
>
> Thanks.
>
> --
> D. Richard Hipp
> drh at sqlite.org


?I downloaded the code to my Linux/Intel box to unzip it. I then uploaded
those files to a z/OS 2.2 system, which is definitely EBCDIC based! I was
able to successfully compile (eventually) and did some minor tests. Since I
was running a session to z/OS from Linux, I was able to capture my input
and output using the Linux "script" command. A redacted transcript is below:

===

Script started on Mon 08 Feb 2016 03:04:36 PM CST
Connected to redacted.
IBM
Licensed Material - Property of IBM
5650-ZOS Copyright IBM Corp. 1993, 2015
(C) Copyright Mortice Kern Systems, Inc., 1985, 1996.
(C) Copyright Software Development Group, University of Waterloo, 1989.

U.S. Government Users Restricted Rights -
Use,duplication or disclosure restricted by
GSA ADP Schedule Contract with IBM Corp.

IBM is a registered trademark of the IBM Corp.

$
cd projects/sqlite-test
~/projects/sqlite-test$
cat source-cflags
export CFLAGS='-O3 -V -DSQLITE_MAX_MMAPSIZE=1048576 -qTARG=zOSV1R13 '
export CFLAGS="${CFLAGS} -qLANG=EXTC99 -qFLOAT=IEEE -qnolist -qnosource "
export CFLAGS="${CFLAGS} -D_POSIX_C_SOURCE=200112L -D_XOPEN_SOURCE=600"
export CFLAGS="${CFLAGS} -DSQLITE_ENABLE_COLUMN_METADATA"
export CFLAGS="${CFLAGS} -DHAVE_POSIX_FALLOCATE=0"
~/projects/sqlite-test$
. ./source-cflags
~/projects/sqlite-test$
xlc ${CFLAGS} -o sqlite3 sqlite3.o shell.c

FSUMI  Utility(xlc)  Level(D150318.1512)
XL_CONFIG=/bin/../usr/lpp/cbclib/xlc/etc/xlc.cfg:xlc
./ ./shell.c *.c
"CMDOPTS(DEFINE(errno=(*__errno())),NOTEST,-qoe,-qargparse,-qexecops,-qflag=i,-qhalt=16,-qnodebug,-qnolsearch,-qredir,-qlocale=POSIX,-qlongname,-qmaxmem=*,-qmemory,-qnestinc=255,-qnoexpmac,-qnoexportall,-qnogonumber,-qtarget=le,-qnolibansi,-qlist=/dev/fd1,-qnolist,-qnomargins,-qnooffset,-qnosequence,-qnoshowinc,-qsource=/dev/fd1,-qnosource,-qnoxref,-qterminal,-qnooptimize,-qplist=host,-qspill=128,-qstart,-qnoipa,DEFINE(_OPEN_DEFAULT=1),-qansialias,-qcpluscmt,-qlanglvl=extended,-qnoupconv,-qnoaggregate,-qnoalias,-qnoinfo,-qnoevents,-qrent,-qinline=auto:noreport:100:1000,-qnoinline)"
"object(./shell.o)" "OPT(3)" "DEF(SQLITE_MAX_MMAPSIZE=1048576)"
-qTARG=zOSV1R13 -qLANG=EXTC99 -qFLOAT=IEEE -qnolist -qnosource
"DEF(_POSIX_C_SOURCE=200112L)" "DEF(_XOPEN_SOURCE=600)"
"DEF(SQLITE_ENABLE_COLUMN_METADATA)" "DEF(HAVE_POSIX_FALLOCATE=0)" NOPPONLY
STEPLIB=NONE
_C89_ACCEPTABLE_RC=4
-v -o./sqlite3 sqlite3.o shell.o
_C89_ACCEPTABLE_RC=4
_C89_PVERSION=0x4202
_C89_PSYSIX=
_C89_PSYSLIB=CEE.SCEEOBJ:CEE.SCEECPP
_C89_LSYSLIB=CEE.SCEELKEX:CEE.SCEELKED:CBC.SCCNOBJ:SYS1.CSSLIB
FSUMI  Utility(c89)  Level(D150318.1512)
//* c89

//LINKEDIT  EXEC  PGM=LINKEDIT,
//  PARM='AMODE=31,RMODE=ANY,TERM=YES,
//  DYNAM=DLL,ALIASES=NO,UPCASE=NO,
//  LIST=OFF,MAP=NO,XREF=NO,INFO=NO,MSGLEVEL=4,
//  REUS=RENT,EDIT=YES,AC=0,CALL=YES,CASE=MIXED'
//SYSLIB   DD  DSN='CEE.SCEELKEX',DISP=SHR,DCB=DSORG=DIR
// DD  DSN='CEE.SCEELKED',DISP=SHR,DCB=DSORG=DIR
// DD  DSN='CBC.SCCNOBJ',DISP=SHR,DCB=DSORG=DIR
// DD  DSN='SYS1.CSSLIB',DISP=SHR,DCB=DSORG=DIR
//C8920DD  UNIT=SYSDA,SPACE=(32000,(30,30)),
// STORCLAS=,MGMTCLAS=,DATACLAS=,DSNTYPE=,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//C8961DD  DSN='CEE.SCEEOBJ',DISP=SHR,DCB=DSORG=DIR
//C8962DD  DSN='CEE.SCEECPP',DISP=SHR,DCB=DSORG=DIR
//SYSPRINT DD  PATH='/dev/fd1',
// PATHOPTS=(ORDWR,OCREAT,OAPPEND),FILEDATA=TEXT,
// PATHMODE=(SIROTH,SIRGRP,SIRUSR,SIWOTH,SIWGRP,SIWUSR)
//SYSTERM  DD  PATH='/dev/fd2',
// PATHOPTS=(ORDWR,OCREAT,OAPPEND),FILEDATA=TEXT,
// PATHMODE=(SIROTH,SIRGRP,SIRUSR,SIWOTH,SIWGRP,SIWUSR)
//SYSLMOD  DD  PATH='./sqlite3',
// PATHOPTS=(OWRONLY,OCREAT),
// PATHMODE=(SIRWXO,SIRWXG,SIRWXU)
//SYSLIN   DD  *
  INCLUDE C8920
  INCLUDE './sqlite3.o'

  INCLUDE './shell.o'

  AUTOCALL C8961
  AUTOCALL C8962
/*
~/projects/sqlite-test$
sqlite3 test.db3
SQLite version 3.11.0 2016-02-05 14:11:12
Enter ".help" for usage hints.
sqlite> .tables
sqlite> create table one(one int);
sqlite> insert into one values(1);
sqlite> create table two (two text);
sqlite> insert into two values('two');
sqlite> select * from one;
1
sqlite> select * from two
   ...> ;
two
sqlite> insert into one va

[sqlite] EBCDIC tester needed

[Richard Hipp]

The latest SQLite check-ins contain changes to the SQL language
tokenizer.  These changes have been tested on Unicode systems, but we
do not have access to any systems running EBCDIC in order to test
there.  If you are deploying SQLite on a computer that uses the EBCDIC
character set, we would appreciate your help in validating the latest
code.

The "snapshot" at https://www.sqlite.org/download.html contains the
new code, so you can just download and test that.  Or, contact me via
private email message if you need further assistance.

Thanks.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] union, order by, and a phantom record

[Poor Yorick]

The following query produces a third phantom record on my system:


= start script =
package require sqlite3

sqlite3 [namespace current]::db :memory:

db eval {
 create table if not exists eav (
 id integer primary key autoincrement
 ,entity numeric
 ,attribute text
 ,value
 )
 ; insert into eav values
 (3 ,1 ,'name','Imogen')
 ,(5 ,1 ,'gender'  ,'female')
 ,(6,1 ,'son' ,'Guiderius')
 ,(7,1 ,'son' ,'Arvirargus')
}

set pattern0 1
set report2 name
set report3 gender

puts [db eval {
 select distinct eav.* from eav
 join eav as eav0 on eav.entity == eav0.entity
 and eav0.entity == :pattern0 where eav.attribute == :report2
 union
 select distinct eav.* from eav
 join eav as eav0 on eav.entity == eav0.entity
 and eav0.entity == :pattern0 where eav.attribute == :report3
 order by eav.id
}]
= end script =

The result is:

3 1 name Imogen 5 1 gender female 5 1 1 female

-- 
Poor Yorick

[sqlite] What's the reason for alias names not being visible in subqueries?

[Paul]

Not really, then I would have to select child table with which to JOIN on 
condition, based on the value of parent table.


CREATE TABLE parent(
  id INTEGER PRIMARY KEY,
  child_type INTEGER,
  CHECK(child_type IN (1, 2))
);

CREATE TABLE child_1(
  id INTEGER PRIMARY KEY,
  my_value INTEGER,
  ...
);

CREATE TABLE child_2(
  id INTEGER PRIMARY KEY,
  my_value INTEGER,
  my_other value INTEGER,
  ...
);

CREATE TABLE parent_child_1_link(
  parent_id INTEGER PRIMARY KEY,
  child_1_id INTEGER,
  FOREIGN KEY(parent_id) REFERENCES parent(id),
  FOREIGN KEY(child_1_id) REFERENCES child_1(id)
);

CREATE TABLE parent_child_2_link(
  parent_id INTEGER PRIMARY KEY,
  child_2_id INTEGER,
  FOREIGN KEY(parent_id) REFERENCES parent(id),
  FOREIGN KEY(child_2_id) REFERENCES child_2(id)
);


now, depending on the child_type in the parent I want to select 
 * child_type
 * child_id
 * my_value of specific child
 * some other values of specific child
 * some other values of parent

I cannot JOIN obviously, so I decided to first fetch specific ID of a child, 
alias it and then use it
in selection of properties of specific child. This way i would avoid querying 
parent_child_1_link
or parent_child_2_link tables for each property of specific child.

But i think 2 queries will work more efficiently.

(SIDE NOTE: I know about necessity of indices for FKs, I decided 
to omit them because they are meaningless in this example)

8 February 2016, 13:42:04, by "J Decker" :

>   On Mon, Feb 8, 2016 at 3:38 AM, Paul  wrote:
> > I see, thank you for pointing out.
> >
> > I wanted to use it on table with conditional relations with 3 different 
> > child tables.
> > Though I could use a trick and fit data selection into one query, 
> > efficiently.
> > Alas I am forced to stick to 2 queries.
> >
> 
> Might still be able to collapse it into one CTE query but I don't
> know what the other part is to demo that...
> 
> https://www.sqlite.org/lang_with.html
> 
> > Thank you!
> >
> > 8 February 2016, 12:08:26, by "Clemens Ladisch" :
> >
> >>   Paul wrote:
> >> > I am curious what is a particular reason that aliased columns in a query 
> >> > not visible to sub-queries?
> >>
> >> Because the SQL standard says so.
> >>
> >> > Of course it is possible to duplicate expression in sub-query ...
> >> > But this would not be as efficient as to access result of already 
> >> > evaluated expression.
> >>
> >> An alias refers not to an expression's value but to the expression
> >> itself, so it would not be any more efficient.  (SQLite's optimizer
> >> typically is not smart enough to detect and remove the duplication.)
> >>
> >>
> >> As a workaround, you have to move the expression into a subquery in
> >> the FROM clause:
> >>
> >> SELECT super_id,
> >>(SELECT qux FROM baz WHERE id = super_id)
> >> FROM (SELECT id as super_id FROM foo);
> >>
> >>
> >> Regards,
> >> Clemens
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What's the reason for alias names not being visible in subqueries?

[Paul]

I see, thank you for pointing out.

I wanted to use it on table with conditional relations with 3 different child 
tables.
Though I could use a trick and fit data selection into one query, efficiently.
Alas I am forced to stick to 2 queries.

Thank you!

8 February 2016, 12:08:26, by "Clemens Ladisch" :

>   Paul wrote:
> > I am curious what is a particular reason that aliased columns in a query 
> > not visible to sub-queries?
> 
> Because the SQL standard says so.
> 
> > Of course it is possible to duplicate expression in sub-query ...
> > But this would not be as efficient as to access result of already evaluated 
> > expression.
> 
> An alias refers not to an expression's value but to the expression
> itself, so it would not be any more efficient.  (SQLite's optimizer
> typically is not smart enough to detect and remove the duplication.)
> 
> 
> As a workaround, you have to move the expression into a subquery in
> the FROM clause:
> 
> SELECT super_id,
>(SELECT qux FROM baz WHERE id = super_id)
> FROM (SELECT id as super_id FROM foo);
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What's the reason for alias names not being visible in subqueries?

[Clemens Ladisch]

Paul wrote:
> CREATE TABLE parent(
> CREATE TABLE child_1(
> CREATE TABLE child_2(
> CREATE TABLE parent_child_1_link(
> CREATE TABLE parent_child_2_link(
>
> now, depending on the child_type in the parent I want to select ...

Show your query.  Or at least example data and the desired result.


Regards,
Clemens

[sqlite] How to enter Unicode character?

[Igor Korot]

Dominique,

On Mon, Feb 8, 2016 at 11:43 AM, Dominique Devienne  
wrote:
> On Mon, Feb 8, 2016 at 5:33 PM, Dominique Devienne 
> wrote:
>
>> On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot  wrote:
>>
>>> On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne 
>>> wrote:
>>> What I mean is the following:
>>>
>>> sqlite> CREATE TABLE abc();
>>>
>>> In that line '' should be the German character which look like the
>>> Greek letter "beta".
>>> ...
>>
>> In the good old DOS days I would probably just do ALT+NUMPAD2,2,0,
>>> but that will most likely won't work here.
>>>
>>
>> It does appear to work for me. I used
>> https://en.wikipedia.org/wiki/Code_page_437 as a guide.
>>
>> C:\Users\DDevienne>chcp
>> Active code page: 437
>>
>> ALT129 (keep pressing ALT, then press successively on keypad 1, 2, 9)
>> C:\Users\DDevienne>echo ?
>> ?
>>
>> ALT225
>> C:\Users\DDevienne>echo ?
>> ?
>>
>> C:\Users\DDevienne>sqlite3
>> SQLite version 3.8.9 2015-04-08 12:16:33
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> create table t? (c?);
>> sqlite> insert into t? (c?) values (char(220));
>> sqlite> insert into t? (c?) values (?);
>> Error: no such column: ?
>> sqlite> insert into t? (c?) values ('?');
>> sqlite> .header on
>> sqlite> select * from t?;
>> c?
>> ??
>> ?
>>
>
> Sorry, slight copy/paste issue on the last message.
> there are two rows as expect, but only the second ALT129 entered
>
> Above I made the mistake of using char(220), which is the upper-case U
> umlaut.
> Below I use char(252) (Unicode code point), and ALT129 (Windows CP437),
> which are logically the same letter (lower-case u umlaut), but don't show
> up the same.
> (see https://en.wikipedia.org/wiki/%C3%9C)
>
> sqlite> create table t? (c?);
> sqlite> insert into t? (c?) values (char(252));
> sqlite> insert into t? (c?) values ('?');
> sqlite> .header on
> sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from
> t?;
> c?|length(c?)|length(cast(c? as blob))|unicode(c?)
> ??|1|2|252
> ?|1|1|129
>
> sqlite> .schema
>>
> CREATE TABLE t? (c?);

Thank you.
I will try it tonight when I get home.


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

[sqlite] What's the reason for alias names not being visible in subqueries?

[Clemens Ladisch]

Paul wrote:
> I am curious what is a particular reason that aliased columns in a query not 
> visible to sub-queries?

Because the SQL standard says so.

> Of course it is possible to duplicate expression in sub-query ...
> But this would not be as efficient as to access result of already evaluated 
> expression.

An alias refers not to an expression's value but to the expression
itself, so it would not be any more efficient.  (SQLite's optimizer
typically is not smart enough to detect and remove the duplication.)


As a workaround, you have to move the expression into a subquery in
the FROM clause:

SELECT super_id,
   (SELECT qux FROM baz WHERE id = super_id)
FROM (SELECT id as super_id FROM foo);


Regards,
Clemens

[sqlite] What's the reason for alias names not being visible in subqueries?

[Paul]

Hello!

I am curious what is a particular reason that aliased columns in a query not 
visible to sub-queries?

CREATE TABLE foo(
id INTEGER,
bar INTEGER
);

INSERT INTO foo VALUES(1, 2), (3, 4);

SELECT 1 as super_id, (SELECT bar FROM foo WHERE id = super_id);

Gives an error:
Error: no such column: super_id

On the other hand, we can easily refer to values of a table that participates 
in a query.

CREATE TABLE baz(
id INTEGER,
qux INTEGER
);

INSERT INTO baz VALUES(1, 6), (2, 8);

SELECT id, (SELECT qux FROM baz WHERE id = foo.id) FROM foo;

id  (SELECT qux FROM baz WHERE id = foo.id)
--  ---
1   6  
3   

But still not though alias:

SELECT id as super_id, (SELECT qux FROM baz WHERE id = super_id) FROM foo;
Error: no such column: super_id



Why would this be useful?

Sometimes you need a sub-select by a result of an expression.

SELECT ..., , (SELECT ... FROM FOO where id = ) ..;

Of course it is possible to duplicate expression in sub-query

SELECT ..., , (SELECT ... FROM FOO where id = ) ..;

But this would not be as efficient as to access result of already evaluated 
expression.
Especially if  itself is a sub-select. Also imagine a chain of 
sub-queries.

[sqlite] How to enter Unicode character?

[Igor Korot]

Dominique, Stehan,

On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne  
wrote:
> On Mon, Feb 8, 2016 at 4:28 PM, Dominique Devienne 
> wrote:
>
>> On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot  wrote:
>>
>>> Now my question is: is it possible to enter a Unicode character
>>>
>>
>> Yes. Just use the char() built-in function. --DD
>>
>
> Oh, by "SQLite table contains" you meant the "table name". Sorry, I
> misunderstood.
> OT, but below shows about unicode chars (German umlaut) in column values,
> and not identifiers, FWIW. --DD
>
> sqlite> create table t (c);
> sqlite> insert into t values (char(220));
> sqlite> select * from t;
> ??
> sqlite> select length(c) from t;
> 1
> sqlite> select length(cast (c as blob)) from t;
> 2
> sqlite> select unicode(c) from t;
> 220

What I mean is the following:

sqlite> CREATE TABLE abc();

In that line '' should be the German character which look like the
Greek letter "beta".

Inserting the Unicode character in the table is the next step and it
is easy with some SQLite function,
but inserting the Unicode character into the table name or the field
name, for that matter, is not.

In the good old DOS days I would probably just do ALT+NUMPAD2,2,0, but
that will most likely
won't work here.

Thank you.

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

[sqlite] How to enter Unicode character?

[Igor Korot]

Hi,

On Mon, Feb 8, 2016 at 10:21 AM, Stephan Beal  wrote:
> On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot  wrote:
>
>>  Hi, ALL,
>> I live in US and therefore have an English-based laptop with an
>> English-based keyboard.
>>
>> I am also a programmer and would like to test what happen if I have a
>> SQLite table
>> which contains a Unicode character.
>>
>
> It depends entirely on your environment/shell. You have multiple options:
>
> - add German a secondary keyboard layout and switch keys as needed. Doh -
> your keyboard is US, so you won't know where the 'sharp s' is :/. (My
> keyboard is physically German but it's mapped to a US layout.)
>
> - Google for "utf8 character tables" and copy/paste them.

At the moment I'm using Win 8.1.
And the sqlite3.exe shell tool.

So if I go with option 2, I will copy the character and the paste it
into the command.
Something like :

sqlite3> CREATE TABLE abc();

right?

Thank you.

>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to enter Unicode character?

[Adam Devita]

Good day,
What OS are you using?
Have  you read up on this?
http://www.fileformat.info/tip/microsoft/enter_unicode.htm

Also, Notepad++ is a good text editor for displaying / saving them. (I
assume that you are wanting to generate some text files as scripts)

regards,
Adam DeVita

On Mon, Feb 8, 2016 at 10:17 AM, Igor Korot  wrote:
>  Hi, ALL,
> I live in US and therefore have an English-based laptop with an
> English-based keyboard.
>
> I am also a programmer and would like to test what happen if I have a
> SQLite table
> which contains a Unicode character.
>
> Now my question is: is it possible to enter a Unicode character
> (umlaut symbol, german 'ss'
> character or maybe even something from Chinese alphabet) inside the
> sqlite3.exe in
> order to test my program?
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] How to enter Unicode character?

[Igor Korot]

 Hi, ALL,
I live in US and therefore have an English-based laptop with an
English-based keyboard.

I am also a programmer and would like to test what happen if I have a
SQLite table
which contains a Unicode character.

Now my question is: is it possible to enter a Unicode character
(umlaut symbol, german 'ss'
character or maybe even something from Chinese alphabet) inside the
sqlite3.exe in
order to test my program?

Thank you.

[sqlite] issue: % in expression with binding paramter.

[László Zoltán Buza]

Hello,



Today, i ?ve used the following expression, but I?ve got unexpected results:

((@korte % 2) + 1))



I binded ?@korte? to 5 as integer, using sqlite3_bind_parameter_index and
sqlite3_bind_int.

And I?ve got 6 as result, instead of 2.

It does the same with numbered paramters as well.

I?ve tried to use parentheses around the paramter but doesn?t help.

If I wrote the value directly into the query, it works fine.

I made a workaround: I?m giving +1 parameter with the value of the
expression. (it works either)



My sqlite version is:
3.8.11.1



Building with VS2015 (14.00), for x86 (32 bit, Windows).



Regards,



L?szl? Buza

[sqlite] Use of __builtin_expect in SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/02/16 22:39, Matthias-Christian Ott wrote:
> Amdahl's law is not applicable here and describes a completely
> different problem. SQLite does not involve concurrency.

Amdahl's law very much applies, and doesn't explicitly only involve
concurrency.  It is about relating speedups of individual pieces and
how that affects the whole.  For example:

Lets say that processing a representative query involves ten different
places in the SQLite code, and that each one of those takes about ten
percent of the total execution time.  And then lets say
likely/unlikely speeds up one of those by ten percent.  The overall
whole improvement will then be 1%.  To get an overall improvement of
10% each of the ten different pieces would need to get about 10%
faster.  That would be a huge amount of work, and the nature of each
of those places would have be that they could be sped up that way.

> SQLite does not involve concurrency.

http://sqlite.org/pragma.html#pragma_threads  :-)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAla4u9gACgkQmOOfHg372QScKACeKaDcRUmtllIaCtLrvQOXYAoy
tPsAoNH+TKDtsWsE9XeJHTVwKQ24MjJu
=sPJZ
-END PGP SIGNATURE-

[sqlite] Use of __builtin_expect in SQLite

[Scott Hess]

On Sun, Feb 7, 2016 at 10:39 PM, Matthias-Christian Ott 
wrote:

> On 2016-02-08 04:31, Roger Binns wrote:
> > On 07/02/16 00:56, Dominique Pell? wrote:
> >> I'm curious about the outcome on SQLite benchmarks.
> >
> > About a year ago I tried them out on some tight code (non-SQLite) that
> > absolutely had to use less CPU time.  I couldn't get them to make any
> > difference outside the bounds of measurement error.  Since SQLite has
> > lots of "tight code" places, the instrumentation would have to help in
> > most of them to make a difference (Amdahl's law).
>
> Amdahl's law is not applicable here and describes a completely different
> problem. SQLite does not involve concurrency.
>

"Amdahl's law _In computer architecture, Amdahl's law (or Amdahl's
argument[1]) gives the theoretical speedup in latency of the execution of a
task at fixed workload that can be expected of a system whose resources are
improved."

You _can_ use it to analyze why parallelizing an algorithm across N CPUs
won'y cause the runtime to become 1/N, but that's not the only use.

In typical scenarios SQLite is also limited by the number of IOPS and
> optimization to improve branch predictability have no measurable effect
> and you just waste time with them. Just use PCIe SSDs that are able to
> saturate the bus and all of you performance problems with SQLite are
> gone if you use SQLite on typical computer.
>

In fact, your argument here is Amdahl's Law.  If your performance is
primarily limited by I//O latency, then improving CPU efficiency won't help
much.

-scott

[sqlite] Use of __builtin_expect in SQLite

[Matthias-Christian Ott]

On 2016-02-08 04:31, Roger Binns wrote:
> On 07/02/16 00:56, Dominique Pell? wrote:
>> I'm curious about the outcome on SQLite benchmarks.
> 
> About a year ago I tried them out on some tight code (non-SQLite) that
> absolutely had to use less CPU time.  I couldn't get them to make any
> difference outside the bounds of measurement error.  Since SQLite has
> lots of "tight code" places, the instrumentation would have to help in
> most of them to make a difference (Amdahl's law).

Amdahl's law is not applicable here and describes a completely different
problem. SQLite does not involve concurrency.

In typical scenarios SQLite is also limited by the number of IOPS and
optimization to improve branch predictability have no measurable effect
and you just waste time with them. Just use PCIe SSDs that are able to
saturate the bus and all of you performance problems with SQLite are
gone if you use SQLite on typical computer.

- Matthias-Christian

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 181 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160208/2dd38072/attachment.pgp>

[sqlite] What's the reason for alias names not being visible in subqueries?

[Keith Medcalf]

select *
  from parent P, parent_child_1_link L, child_1 C
 where P.id = L.parent_id
   and C.id = L.child_id
   and P.child_type = 1
union
select *
  from parent P, parent_child_2_link L, child_2 C
 where P.id = L.parent_id
   and C.id = L.child_id
   and P.child_type = 2

It is a very straightforward join.


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Paul
> Sent: Monday, 8 February, 2016 04:57
> To: SQLite mailing list
> Subject: Re: [sqlite] What's the reason for alias names not being visible
> in subqueries?
> 
> Not really, then I would have to select child table with which to JOIN on
> condition, based on the value of parent table.
> 
> 
> CREATE TABLE parent(
>   id INTEGER PRIMARY KEY,
>   child_type INTEGER,
>   CHECK(child_type IN (1, 2))
> );
> 
> CREATE TABLE child_1(
>   id INTEGER PRIMARY KEY,
>   my_value INTEGER,
>   ...
> );
> 
> CREATE TABLE child_2(
>   id INTEGER PRIMARY KEY,
>   my_value INTEGER,
>   my_other value INTEGER,
>   ...
> );
> 
> CREATE TABLE parent_child_1_link(
>   parent_id INTEGER PRIMARY KEY,
>   child_1_id INTEGER,
>   FOREIGN KEY(parent_id) REFERENCES parent(id),
>   FOREIGN KEY(child_1_id) REFERENCES child_1(id)
> );
> 
> CREATE TABLE parent_child_2_link(
>   parent_id INTEGER PRIMARY KEY,
>   child_2_id INTEGER,
>   FOREIGN KEY(parent_id) REFERENCES parent(id),
>   FOREIGN KEY(child_2_id) REFERENCES child_2(id)
> );
> 
> 
> now, depending on the child_type in the parent I want to select
>  * child_type
>  * child_id
>  * my_value of specific child
>  * some other values of specific child
>  * some other values of parent
> 
> I cannot JOIN obviously, so I decided to first fetch specific ID of a
> child, alias it and then use it
> in selection of properties of specific child. This way i would avoid
> querying parent_child_1_link
> or parent_child_2_link tables for each property of specific child.
> 
> But i think 2 queries will work more efficiently.
> 
> (SIDE NOTE: I know about necessity of indices for FKs, I decided
> to omit them because they are meaningless in this example)
> 
> 8 February 2016, 13:42:04, by "J Decker" :
> 
> >   On Mon, Feb 8, 2016 at 3:38 AM, Paul  wrote:
> > > I see, thank you for pointing out.
> > >
> > > I wanted to use it on table with conditional relations with 3
> different child tables.
> > > Though I could use a trick and fit data selection into one query,
> efficiently.
> > > Alas I am forced to stick to 2 queries.
> > >
> >
> > Might still be able to collapse it into one CTE query but I don't
> > know what the other part is to demo that...
> >
> > https://www.sqlite.org/lang_with.html
> >
> > > Thank you!
> > >
> > > 8 February 2016, 12:08:26, by "Clemens Ladisch" :
> > >
> > >>   Paul wrote:
> > >> > I am curious what is a particular reason that aliased columns in a
> query not visible to sub-queries?
> > >>
> > >> Because the SQL standard says so.
> > >>
> > >> > Of course it is possible to duplicate expression in sub-query ...
> > >> > But this would not be as efficient as to access result of already
> evaluated expression.
> > >>
> > >> An alias refers not to an expression's value but to the expression
> > >> itself, so it would not be any more efficient.  (SQLite's optimizer
> > >> typically is not smart enough to detect and remove the duplication.)
> > >>
> > >>
> > >> As a workaround, you have to move the expression into a subquery in
> > >> the FROM clause:
> > >>
> > >> SELECT super_id,
> > >>(SELECT qux FROM baz WHERE id = super_id)
> > >> FROM (SELECT id as super_id FROM foo);
> > >>
> > >>
> > >> Regards,
> > >> Clemens
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users at mailinglists.sqlite.org
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] issue: % in expression with binding paramter.

[Richard Hipp]

On 2/8/16, L?szl? Zolt?n Buza  wrote:
> Hello,
>
>
>
> Today, i ?ve used the following expression, but I?ve got unexpected results:
>
> ((@korte % 2) + 1))
>
>
>
> I binded ?@korte? to 5 as integer, using sqlite3_bind_parameter_index and
> sqlite3_bind_int.
>
> And I?ve got 6 as result, instead of 2.
>

I get 2 when I try it:

#include "sqlite3.h"
#include 
int main(int argc, char **argv){
  sqlite3 *db;
  sqlite3_stmt *pStmt;
  sqlite3_open(":memory:", &db);
  sqlite3_prepare_v2(db, "SELECT ((@korte%2)+1);", -1, &pStmt, 0);
  sqlite3_bind_int(pStmt, sqlite3_bind_parameter_index(pStmt, "@korte"), 5);
  sqlite3_step(pStmt);
  printf("result = %d\n", sqlite3_column_int(pStmt, 0));
  return 0;
}


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Use of __builtin_expect in SQLite

[Simon Slavin]

On 8 Feb 2016, at 3:31am, Roger Binns  wrote:

> Taking a step back, the reasons why it had no measureable effect are
> simple.  The processors are getting better at branch prediction,
> better at mitigating mispredicted branches, getting even faster
> compared to memory.  The compilers are getting better all the time too
> at the same kind of things.

This is the other argument against optimization (of a certain kind).  Computers 
are far better at optimization than humans are, and they always do it.  No need 
for you to do it too.

Simon.

[sqlite] What's the reason for alias names not being visible in subqueries?

[J Decker]

On Mon, Feb 8, 2016 at 3:38 AM, Paul  wrote:
> I see, thank you for pointing out.
>
> I wanted to use it on table with conditional relations with 3 different child 
> tables.
> Though I could use a trick and fit data selection into one query, efficiently.
> Alas I am forced to stick to 2 queries.
>

Might still be able to collapse it into one CTE query but I don't
know what the other part is to demo that...

https://www.sqlite.org/lang_with.html

> Thank you!
>
> 8 February 2016, 12:08:26, by "Clemens Ladisch" :
>
>>   Paul wrote:
>> > I am curious what is a particular reason that aliased columns in a query 
>> > not visible to sub-queries?
>>
>> Because the SQL standard says so.
>>
>> > Of course it is possible to duplicate expression in sub-query ...
>> > But this would not be as efficient as to access result of already 
>> > evaluated expression.
>>
>> An alias refers not to an expression's value but to the expression
>> itself, so it would not be any more efficient.  (SQLite's optimizer
>> typically is not smart enough to detect and remove the duplication.)
>>
>>
>> As a workaround, you have to move the expression into a subquery in
>> the FROM clause:
>>
>> SELECT super_id,
>>(SELECT qux FROM baz WHERE id = super_id)
>> FROM (SELECT id as super_id FROM foo);
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What's the reason for alias names not being visible in subqueries?

[J Decker]

On Mon, Feb 8, 2016 at 12:47 AM, Paul  wrote:
> Hello!
>
> I am curious what is a particular reason that aliased columns in a query not 
> visible to sub-queries?
>
> CREATE TABLE foo(
> id INTEGER,
> bar INTEGER
> );
>
> INSERT INTO foo VALUES(1, 2), (3, 4);
>
> SELECT 1 as super_id, (SELECT bar FROM foo WHERE id = super_id);
>
This looks like a simple select

select 1 as super_id,bar from foo where id=super_id

> Gives an error:
> Error: no such column: super_id
>
> On the other hand, we can easily refer to values of a table that participates 
> in a query.
>
> CREATE TABLE baz(
> id INTEGER,
> qux INTEGER
> );
>
> INSERT INTO baz VALUES(1, 6), (2, 8);
>
> SELECT id, (SELECT qux FROM baz WHERE id = foo.id) FROM foo;
>
this looks like a join

select foo.id,qux from foo join baz on baz.id=foo.id

> id  (SELECT qux FROM baz WHERE id = foo.id)
> --  ---
> 1   6
> 3
>
> But still not though alias:
>
> SELECT id as super_id, (SELECT qux FROM baz WHERE id = super_id) FROM foo;
> Error: no such column: super_id

And this
select foo.id as super_id,qux from foo join baz on baz.id=super_id


>
> 
>
> Why would this be useful?
>
> Sometimes you need a sub-select by a result of an expression.
>
> SELECT ..., , (SELECT ... FROM FOO where id =  expression X>) ..;
>
> Of course it is possible to duplicate expression in sub-query
>
> SELECT ..., , (SELECT ... FROM FOO where id = ) 
> ..;
>

and both of those

> But this would not be as efficient as to access result of already evaluated 
> expression.
> Especially if  itself is a sub-select. Also imagine a chain of 
> sub-queries.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem on Windows using filenames with non-ASCII characters

[Holger Jakobs]

Changing to code page 1200 or 1201 results in an error of the chcp command. 
These code pages are invalid.?


--
Holger Jakobs
M?lheimer Str. 133, 51469 Bergisch Gladbach



 Urspr?ngliche Nachricht Von: Simon Slavin 
 Datum:07.02.2016  22:49  (GMT+01:00) 
An: SQLite mailing list  
Betreff: Re: [sqlite] problem on Windows using filenames with 
non-ASCII
characters 

On 7 Feb 2016, at 9:46pm, Holger Jakobs  wrote:

> Unfortunately, it doesn't help.
> 
> see http://plausibolo.de/tmp/utf8_cmd.png
> 
> Codepage ist 65001 (UTF-8), there already was a db called
> 103_Beethovenstra?e_Stammdaten.etv; trying to open it creates a new one
> called 103_Beethovenstra?e_Stammdaten.etv

I don't know how to do it, but can you please try codepage 1200 and codepage 
1201 ?



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