Re: [sqlite] Using with clause in update statement

[Clemens Ladisch]

x wrote:
> If I replace the following statement
>
> UPDATE SomeTable SET SomeCol = Col1 * (LongWindedFormala) + Col2 * 
> (LongWindedFormala)  + ...
>
> with
>
> WITH CTE(X) AS (SELECT LongWindedFormula)
> UPDATE SomeTable SET SomeCol = Col1 * (SELECT X FROM CTE) + Col2 * (SELECT X 
> FROM CTE) + .
>
> Is ‘(SELECT X FROM CTE)’ the briefest way of accessing LongWindedFormula?

If LongWindedFormula returns a single value and does not access any column of 
the
table, then this is correct.

(In a SELECT query, you could join with the CTE and then directly access the
column as "X", but UPDATE does not support joins.)

> Sent from Mail for Windows 10

Please do not send ads.


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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Clemens Ladisch]

Ziemowit Laski wrote:
> Visual C++

Which one?

> correctly catches this.

Oh?  What exactly is illegal about this?

>   struct IcuScalar {
> const char *zName;/* Function name */
> int nArg; /* Number of arguments */
> int enc;  /* Optimal text encoding */
> void *pContext;   /* sqlite3_user_data() context 
> */
> void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
>   } scalars[] = {
> ...
> {"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
>   };
>
> should read
>
>} scalars[] = {
>   ...
>   { "icu_load_collation",  2, SQLITE_UTF8, 0, icuLoadCollation }
>};
>
>scalars[11].pContext = (void*)db;


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

[sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Ziemowit Laski]

Visual C++ correctly catches this.  The fragment

  struct IcuScalar {
const char *zName;/* Function name */
int nArg; /* Number of arguments */
int enc;  /* Optimal text encoding */
void *pContext;   /* sqlite3_user_data() context */
void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
  } scalars[] = {
{"regexp", 2, SQLITE_ANY|SQLITE_DETERMINISTIC,  0, icuRegexpFunc},

{"lower",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, icuCaseFunc16},
{"lower",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, icuCaseFunc16},
{"upper",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, icuCaseFunc16},
{"upper",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, icuCaseFunc16},

{"lower",  1, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuCaseFunc16},
{"lower",  2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuCaseFunc16},
{"upper",  1, SQLITE_UTF8|SQLITE_DETERMINISTIC,  (void*)1, icuCaseFunc16},
{"upper",  2, SQLITE_UTF8|SQLITE_DETERMINISTIC,  (void*)1, icuCaseFunc16},

{"like",   2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc},
{"like",   3, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc},

{"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
  };

  int rc = SQLITE_OK;
  int i;

should read

   struct IcuScalar {
  const char *zName;/* Function name */
  int nArg; /* Number of arguments 
*/
  int enc;  /* Optimal text 
encoding */
  void *pContext;   /* sqlite3_user_data() 
context */
  void(*xFunc)(sqlite3_context*, int, sqlite3_value**);
   } scalars[] = {
  { "regexp", 2, SQLITE_ANY | SQLITE_DETERMINISTIC,  0, 
icuRegexpFunc },

  { "lower",  1, SQLITE_UTF16 | SQLITE_DETERMINISTIC,0, 
icuCaseFunc16 },
  { "lower",  2, SQLITE_UTF16 | SQLITE_DETERMINISTIC,0, 
icuCaseFunc16 },
  { "upper",  1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, (void*)1, 
icuCaseFunc16 },
  { "upper",  2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, (void*)1, 
icuCaseFunc16 },

  { "lower",  1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
icuCaseFunc16 },
  { "lower",  2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
icuCaseFunc16 },
  { "upper",  1, SQLITE_UTF8 | SQLITE_DETERMINISTIC,  (void*)1, 
icuCaseFunc16 },
  { "upper",  2, SQLITE_UTF8 | SQLITE_DETERMINISTIC,  (void*)1, 
icuCaseFunc16 },

  { "like",   2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
icuLikeFunc },
  { "like",   3, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
icuLikeFunc },

  { "icu_load_collation",  2, SQLITE_UTF8, 0, icuLoadCollation }
   };

   int rc = SQLITE_OK;
   int i;

   scalars[11].pContext = (void*)db;

Thank you,

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

[sqlite] Using with clause in update statement

[x]

If I replace the following statement

UPDATE SomeTable SET SomeCol = Col1 * (LongWindedFormala) + Col2 * 
(LongWindedFormala)  + ...

with

WITH CTE(X) AS (SELECT LongWindedFormula)
UPDATE SomeTable SET SomeCol = Col1 * (SELECT X FROM CTE) + Col2 * (SELECT X 
FROM CTE) + .

Is ‘(SELECT X FROM CTE)’ the briefest way of accessing LongWindedFormula? Not 
that I’m complaining by the way. The WITH is a big help regardless.

Sent from Mail for Windows 10

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

Re: [sqlite] Creating indexes on non-existent columns with double quotes

[Ersin Akinci]

On Wed, Jan 25, 2017 at 9:55 AM, Simon Slavin  wrote:
>
> On 25 Jan 2017, at 5:45pm, Ersin Akinci  wrote:
>
>> Thanks Richard and Simon for your insights. I think I'm still missing
>> a few things, though.
>>
>> 1. What does it mean when SQLite tries to create an index on a string?
>> Simon suggested that it's creating a calculated index, but I'm not
>> sure what means. (Does it just mean an "index" literally just on the
>> word "yearz_doesnt_exist"? That seems quite silly.)
>
> SQLite supports indexes on calculations.  Or other expressions.  So you 
> should be able to do things like
>
> CREATE INDEX fred ON MyTable (CASE WHEN stock < 0 THEN 0 ELSE stock END)
>
> Since a constant string is an expression, and single quotes must be used to 
> quote strings, this means you can do something like
>
> CREATE INDEX mabel ON MyTable ('hello mom')
>
> It won’t do anything useful, but it should work.
>
>> 2. And regardless, why is there a different result when using single
>> quotes vs. double quotes?
>
> The two quotes mean completely different things to SQLite.  Single quotes are 
> used to quote strings.  Double quotes are used to quote entity names (like 
> column names, table names, etc.).  Properly speaking the version of your 
> command with double quotes should be rejected for the reason you expected 
> but, as DRH explained, for historical reasons if there’s no column with the 
> given name SQLite thinks you want the contents of the quotes as a string.  
> And that’s what it’s doing.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Aha. Makes sense. And as you noted in your bug report, Simon, the
single quoted version should create a computed index, as well, but for
some reason it doesn't.

Thanks for the clarification!

-Ersin

-- 
Ersin Y. Akinci -- ersinakinci.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating indexes on non-existent columns with double quotes

[Simon Slavin]

On 25 Jan 2017, at 5:45pm, Ersin Akinci  wrote:

> Thanks Richard and Simon for your insights. I think I'm still missing
> a few things, though.
> 
> 1. What does it mean when SQLite tries to create an index on a string?
> Simon suggested that it's creating a calculated index, but I'm not
> sure what means. (Does it just mean an "index" literally just on the
> word "yearz_doesnt_exist"? That seems quite silly.)

SQLite supports indexes on calculations.  Or other expressions.  So you should 
be able to do things like

CREATE INDEX fred ON MyTable (CASE WHEN stock < 0 THEN 0 ELSE stock END)

Since a constant string is an expression, and single quotes must be used to 
quote strings, this means you can do something like

CREATE INDEX mabel ON MyTable ('hello mom')

It won’t do anything useful, but it should work.

> 2. And regardless, why is there a different result when using single
> quotes vs. double quotes?

The two quotes mean completely different things to SQLite.  Single quotes are 
used to quote strings.  Double quotes are used to quote entity names (like 
column names, table names, etc.).  Properly speaking the version of your 
command with double quotes should be rejected for the reason you expected but, 
as DRH explained, for historical reasons if there’s no column with the given 
name SQLite thinks you want the contents of the quotes as a string.  And that’s 
what it’s doing.

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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Richard Hipp]

On 1/25/17, Warren Young  wrote:
>
> stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;
>
> Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into the
> string.

Not quite.  You cannot have parameters embedded in the middle of
strings.  The whole string is replaced by a parameter.

   stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"

Then the application has to prepend and append the "%" on the strings
before binding.  Or, if your application does not want to do that:

   stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
LIKE ('%' || ?2 || '%')"

Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
|| is the string concatenation operator in SQL.)

-- 
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, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Warren Young]

On Jan 25, 2017, at 8:50 AM, Clyde Eisenbeis  wrote:
> 
> Are there code examples similar to the following (OLE DB)?

Code examples?  Maybe, but the second link I gave you is pretty clear.  The 
bits you want are even in bold text.

> stCmdString += " AND " + stLikeFieldName + " LIKE '%" +
> liststLikeFieldValue[iii] + "%’";

stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;

Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into the 
string.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating indexes on non-existent columns with double quotes

[Ersin Akinci]

Thanks Richard and Simon for your insights. I think I'm still missing
a few things, though.

1. What does it mean when SQLite tries to create an index on a string?
Simon suggested that it's creating a calculated index, but I'm not
sure what means. (Does it just mean an "index" literally just on the
word "yearz_doesnt_exist"? That seems quite silly.) My assumption is
that a calculated index is just an index on a view or some other kind
of dynamically rendered column, but according to
http://sqlite.1065341.n5.nabble.com/Computed-column-or-create-index-on-a-view-td89442.html,
SQLite doesn't support indexes on views.

2. And regardless, why is there a different result when using single
quotes vs. double quotes?

-- 
Ersin Y. Akinci -- ersinakinci.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding the largest TOTAL() of numerous GROUP totals

[R Smith]

On 2017/01/25 6:15 PM, Simon Slavin wrote:

On 25 Jan 2017, at 4:14pm, R Smith  wrote:


Maybe I'm misunderstanding some fundamental principle, but I don't see how 
TOTAL(count) is equivalent to TOTAL(*)? COUNT(some_field_name) and COUNT(*) 
might be equivalent, but I'm wondering how total would know which column you 
need the total of when using * ?

Nope.  I had a bad day and confused the two functions.  Please ignore my 
earlier suggestion.  Thanks for pointing it out, R.


Shame on you Simon, you made me feel stupid! I was expecting a smart 
explanation of some special SQL standard feature like "It uses the first 
column" or "it totals all the suitable columns" or some such that I 
wasn't aware of.  Thanks for clearing it up!


Ryan


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

Re: [sqlite] application function value in sql where

[R Smith]

On 2017/01/25 3:18 PM, Dominique Devienne wrote:

On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp  wrote:


On 1/25/17, Richard Hipp  wrote:

On 1/25/17, dspub...@freemail.hu  wrote:

I get weird sql result with subselect too
select * from (select row_number(name) as id,name from example ) t where
id<=5


SQLite is invoking your row_number() function twice for each row -
once for the return value and a second time when evaluating the "id<5"
expression.

Further information:

The query optimizer is transforming your nested query into a single
query.  You wrote:

 SELECT * FROM (SELECT func(name) AS id, name FROM example) WHERE id<5;

Evaluated directly, this would require two separate queries.  For
improved performance, SQLite "flattens" the inner query into the
second, like this:

 SELECT func(name), name FROM example WHERE func(name)<5;


Hi Richard,

Would SQLite invoke the function only once though, had the function been
declared "deterministic"?
I.e. when compiling that "flattened" query into VDBE, it would use a
"register" to avoid calling it twice?


Not only would it avoid calling it twice, the QP might even cache it for 
future iterations with the same parameter... The immediate problem here 
is that his function is specifically NOT deterministic, it returns an 
ever-growing result upon each call (from the looks of it - untested).


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

Re: [sqlite] Finding the largest TOTAL() of numerous GROUP totals

[Simon Slavin]

On 25 Jan 2017, at 4:14pm, R Smith  wrote:

> Maybe I'm misunderstanding some fundamental principle, but I don't see how 
> TOTAL(count) is equivalent to TOTAL(*)? COUNT(some_field_name) and COUNT(*) 
> might be equivalent, but I'm wondering how total would know which column you 
> need the total of when using * ?

Nope.  I had a bad day and confused the two functions.  Please ignore my 
earlier suggestion.  Thanks for pointing it out, R.

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

Re: [sqlite] Finding the largest TOTAL() of numerous GROUP totals

[R Smith]

On 2017/01/25 1:36 PM, Simon Slavin wrote:

On 25 Jan 2017, at 5:13am, Jeffrey Mattox  wrote:


  (1) SELECT TOTAL(count) as grandTotalCount FROM History

Please try replacing TOTAL(count) with TOTAL(*).  Nothing to do with your 
question but it may lead to faster results with less processing.


Maybe I'm misunderstanding some fundamental principle, but I don't see 
how TOTAL(count) is equivalent to TOTAL(*)? COUNT(some_field_name) and 
COUNT(*) might be equivalent, but I'm wondering how total would know 
which column you need the total of when using * ?
(I suppose I could test it when I get home, but was hoping someone may 
have the quick answer...)


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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

I should clarify ... I am currently using:

string stCmdString = "SELECT" + stFieldNames + "FROM " +
stTableName + " WHERE " + stLikeFieldName + " LIKE '%" +
liststLikeFieldValue[0] + "%'";

for (int iii = 1; iii < liststLikeFieldValue.Count; iii++)
  stCmdString += " AND " + stLikeFieldName + " LIKE '%" +
liststLikeFieldValue[iii] + "%'";

which does not handle the cases where a ' is included in the field value.


On Wed, Jan 25, 2017 at 9:50 AM, Clyde Eisenbeis  wrote:
> Thanks for the prompt response!
>
> Are there code examples similar to the following (OLE DB)?
>
>oledbCmd.CommandText = "SELECT" + stFieldNames + "FROM " +
> stTableName + " WHERE " + stLikeFieldName + " LIKE @p0";
>
> for (int iii = 1; iii < liststLikeFieldValue.Count; iii++)
>   oledbCmd.CommandText += stLikeFieldName + " AND " +
> stLikeFieldName + " LIKE @p" + (iii).ToString();
>
> On Wed, Jan 25, 2017 at 9:45 AM, Warren Young  wrote:
>> On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis  wrote:
>>>
>>> The use of .Parameters in OLE DB fixes this problem.  Is there an
>>> equivalent for SQLite?
>>
>> You’re looking for prepared statements with parameters:
>>
>>https://sqlite.org/c3ref/stmt.html
>>https://sqlite.org/lang_expr.html#varparam
>>
>> ___
>> 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, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

Thanks for the prompt response!

Are there code examples similar to the following (OLE DB)?

   oledbCmd.CommandText = "SELECT" + stFieldNames + "FROM " +
stTableName + " WHERE " + stLikeFieldName + " LIKE @p0";

for (int iii = 1; iii < liststLikeFieldValue.Count; iii++)
  oledbCmd.CommandText += stLikeFieldName + " AND " +
stLikeFieldName + " LIKE @p" + (iii).ToString();

On Wed, Jan 25, 2017 at 9:45 AM, Warren Young  wrote:
> On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis  wrote:
>>
>> The use of .Parameters in OLE DB fixes this problem.  Is there an
>> equivalent for SQLite?
>
> You’re looking for prepared statements with parameters:
>
>https://sqlite.org/c3ref/stmt.html
>https://sqlite.org/lang_expr.html#varparam
>
> ___
> 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, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Warren Young]

On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis  wrote:
> 
> The use of .Parameters in OLE DB fixes this problem.  Is there an
> equivalent for SQLite?

You’re looking for prepared statements with parameters:

   https://sqlite.org/c3ref/stmt.html
   https://sqlite.org/lang_expr.html#varparam

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

[sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Clyde Eisenbeis]

Using SQLite, how can I search for chars that include a ', similar to
OLE DB .Parameters?

This works to find bruce and baseball:

string stCmdString = "SELECT fstInfo FROM PadTable WHERE fstInfo
LIKE '%bruce%' AND fstInfo LIKE '%baseball%'"

Not sure how to find bruce's and baseball.

I know it can be done by using two single quote marks ... but this is
a bit of a hassle to read every char of all new entries (or searches)
to the database before storing the.

The use of .Parameters in OLE DB fixes this problem.  Is there an
equivalent for SQLite?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] _SQLITEINT_H_ vs SQLITEINT_H

[Simon Slavin]

On 25 Jan 2017, at 3:14pm, Clyde Eisenbeis  wrote:

> It's not clear how I post questions.  Do I just send to this mailing list?

Although some questions get very fast replies, it is just a coincidence that 
one of the development team happened to be reading the list when the question 
is posted.

Your question is sensible and someone should post a reply of some sort in the 
next day or two.

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

Re: [sqlite] _SQLITEINT_H_ vs SQLITEINT_H

[Clyde Eisenbeis]

It's not clear how I post questions.  Do I just send to this mailing list?

On Wed, Jan 25, 2017 at 6:59 AM, Theo Veenker  wrote:
> Hi all,
>
> Just downloaded the 3.16.2 amalgamation (used 3.11.1 before). I then wanted
> to apply the userauth extension. The resulting sqlite3.c doesn't compile
> however because userauth.c contains this fragment:
> #ifndef _SQLITEINT_H_
> # include "sqliteInt.h"
> #endif
> Apparently _SQLITEINT_H_ should now be SQLITEINT_H. Maybe this needs a fix?
>
> A grep in sqlite3.c shows one more (harmless however) occurrence of
> _SQLITEINT_H_ for json1.c.
>
> Theo
>
> --
> Theo Veenker  |  Beexy - Behavioral Experiment Software
> +31(0)524-541531  |  +31(0)6-42525777 mobile
> theo.veen...@beexy.nl  |  www.beexy.nl
> ___
> 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] application function value in sql where

[Dominique Devienne]

On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp  wrote:

> On 1/25/17, Richard Hipp  wrote:
> > On 1/25/17, dspub...@freemail.hu  wrote:
> >>
> >> I get weird sql result with subselect too
> >> select * from (select row_number(name) as id,name from example ) t where
> >> id<=5
> >>
> >
> > SQLite is invoking your row_number() function twice for each row -
> > once for the return value and a second time when evaluating the "id<5"
> > expression.
>
> Further information:
>
> The query optimizer is transforming your nested query into a single
> query.  You wrote:
>
> SELECT * FROM (SELECT func(name) AS id, name FROM example) WHERE id<5;
>
> Evaluated directly, this would require two separate queries.  For
> improved performance, SQLite "flattens" the inner query into the
> second, like this:
>
> SELECT func(name), name FROM example WHERE func(name)<5;


Hi Richard,

Would SQLite invoke the function only once though, had the function been
declared "deterministic"?
I.e. when compiling that "flattened" query into VDBE, it would use a
"register" to avoid calling it twice?

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

Re: [sqlite] Matching wildcards in Lemon

[Ben]

> On 24 Jan 2017, at 14:47, Richard Hipp  wrote:
> 
> On 1/24/17, Ben  wrote:
>> 
>> Just for a heads up, this isn't documented at:
>> http://www.hwaci.com/sw/lemon/lemon.html
>> 
>> 
> 
> Documentation patches gladly accepted :-)

Looks like it's already written up at 
https://www.sqlite.org/src/doc/trunk/doc/lemon.html 
 , but just not on the 
link I found before.

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

[sqlite] _SQLITEINT_H_ vs SQLITEINT_H

[Theo Veenker]

Hi all,

Just downloaded the 3.16.2 amalgamation (used 3.11.1 before). I then wanted to apply the 
userauth extension. The resulting sqlite3.c doesn't compile however because userauth.c 
contains this fragment:

#ifndef _SQLITEINT_H_
# include "sqliteInt.h"
#endif
Apparently _SQLITEINT_H_ should now be SQLITEINT_H. Maybe this needs a fix?

A grep in sqlite3.c shows one more (harmless however) occurrence of 
_SQLITEINT_H_ for json1.c.

Theo

--
Theo Veenker  |  Beexy - Behavioral Experiment Software
+31(0)524-541531  |  +31(0)6-42525777 mobile
theo.veen...@beexy.nl  |  www.beexy.nl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[Richard Hipp]

On 1/25/17, Richard Hipp  wrote:
> On 1/25/17, dspub...@freemail.hu  wrote:
>>
>> I get weird sql result with subselect too
>> select * from (select row_number(name) as id,name from example ) t where
>> id<=5
>>
>
> SQLite is invoking your row_number() function twice for each row -
> once for the return value and a second time when evaluating the "id<5"
> expression.

Further information:

The query optimizer is transforming your nested query into a single
query.  You wrote:

SELECT * FROM (SELECT func(name) AS id, name FROM example) WHERE id<5;

Evaluated directly, this would require two separate queries.  For
improved performance, SQLite "flattens" the inner query into the
second, like this:

SELECT func(name), name FROM example WHERE func(name)<5;

-- 
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] Possible bug when creating a calculated index

[Simon Slavin]

Bug is as follows:

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.

sqlite> CREATE TABLE reports (a INT, b TEXT);

sqlite> CREATE INDEX index_reports_quotes ON reports ("yearz_doesnt_exist");

sqlite> PRAGMA index_xinfo('index_reports_quotes');
0|-2||0|BINARY|1
1|-1||0|BINARY|0

DRH says that, having failed to find a column called 'yearz_doesnt_exist' 
SQLite is interpreting that name as a fixed text string and creating a 
calculated index.  This one isn’t useful but it’s legal.  So far so good.  Now 
continue.

sqlite> CREATE INDEX index_reports_apostrophes ON reports
   ...> ('yearz_doesnt_exist');
Error: no such column: yearz_doesnt_exist

Here I am intentionally trying to create the same calculated index.  I have 
explicitly quoted the string 'yearz_doesnt_exist', which means I am explicitly 
supplying an expression.  Why doesn’t SQLite understand this ?

If this is a bug, credit for spotting it belongs to Ersin Akinci.

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

Re: [sqlite] application function value in sql where

[Richard Hipp]

On 1/25/17, dspub...@freemail.hu  wrote:
>
> I get weird sql result with subselect too
> select * from (select row_number(name) as id,name from example ) t where
> id<=5
>

SQLite is invoking your row_number() function twice for each row -
once for the return value and a second time when evaluating the "id<5"
expression.

-- 
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] Creating indexes on non-existent columns with double quotes

[Simon Slavin]

On 23 Jan 2017, at 9:33pm, Ersin Akinci  wrote:

> CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
> (yearz_doesnt_exist);
> CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
> ('yearz_doesnt_exist');
> CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
> ("yearz_doesnt_exist");
> 
> The first two of these statements will fail with the error 'Error: no such
> column: yearz_doesnt_exist', but the last one goes through.

The first one is doing what you said.

In the second index you are, technically, asking it to create a computed index 
on a constant string (that’s what the apostrophes mean).  I don’t understand 
why this doesn’t work for me.  I’m using SQLite 3.14.0.

The third one /should/ do what the first one does, since double-quotes indicate 
entity-names.

> After executing
> the last statement, if we look at the output of index_list and index_info,
> we get:
> 
> sqlite> PRAGMA index_list(reports);
> 0|index_reports_on_yearz_doesnt_exist|0|c|0
> sqlite> PRAGMA index_info(index_reports_on_yearz_doesnt_exist);
> 0|-2|

I don’t get that.  Instead I get

sqlite> CREATE INDEX index_reports_quotes ON reports
   ...> ("yearz_doesnt_exist");
sqlite> PRAGMA index_info('index_reports_quotes');
0|-2|
sqlite> PRAGMA index_xinfo('index_reports_quotes');
0|-2||0|BINARY|1
1|-1||0|BINARY|0

The "-2" in each case is undocumented.  I suspect that that it means 
'calculated column' and it’s just not documented yet.

[later] I see DRH has cleared this up.  SQLite failed to find a column with 
that name and is interpreting the string as a text string.  Therefore it is 
indeed creating a calculated index.

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

Re: [sqlite] Finding the largest TOTAL() of numerous GROUP totals

[Simon Slavin]

On 25 Jan 2017, at 5:13am, Jeffrey Mattox  wrote:

>  (1) SELECT TOTAL(count) as grandTotalCount FROM History

Please try replacing TOTAL(count) with TOTAL(*).  Nothing to do with your 
question but it may lead to faster results with less processing.

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

Re: [sqlite] Creating indexes on non-existent columns with double quotes

[Richard Hipp]

On 1/23/17, Ersin Akinci  wrote:
> CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
> ("yearz_doesnt_exist");
>
>
> I understand that the double quotation syntax is used to indicate
> identifiers.  Why am I allowed to create an index on a non-existent column
> when I use double quotes?
>

SQLite has a (mis-)feature that double-quoted names that cannot be
resolved to a table or column name are treated as strings.  This was a
very early design decision, made long before SQLite went viral and
found itself running in everything device on the planet, and was
intended to make SQLite more compatible with MySQL, which at the time
was the most widely deployed database engine in the world.  I regret
that choice now, but I cannot undo it without breaking backwards
compatibility.

On the other hand, the ability to index an expression is a relatively
new feature.  Maybe we can disable the double-quote-to-string
mis-feature for that one isolated case.  Maybe.  I'll look into it.

-- 
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] application function value in sql where

[dspublic]

Hi Everyone,

I would like to report a probably bug, what I found in sqlite (3.16.2)
I want to make and use some application defined function (ADF). If I use ADF 
value in sql where expression, then the server result is incorrect.

  example data: 
'1_one','2_two','3_three','4_four','5_five','6_six','7_seven','8_eight','9_nine'

  with t (id,name) as (select row_number(name),name from example )
select * from t order by id

   expected result: [(1, '1_one'), (2, '2_two'), (3, '3_three'), (4, '4_four'), 
(5, '5_five'), (6, '6_six'), (7, '7_seven'), (8, '8_eight'), (9, '9_nine')] 
   real result: same, CORRECT


  with t (id,name) as (select row_number(name),name from example )
   select * from t WHERE ID<=5 order by id

   expected result: [(1, '1_one'), (2, '2_two'), (3, '3_three'), (4, '4_four'), 
(5, '5_five')] 
   real result: [(2, '1_one'), (4, '2_two'), (6, '3_three')] INCORRECT

I get weird sql result with subselect too
select * from (select row_number(name) as id,name from example ) t where id<=5


I've made a python script for testing and issue reproduction:

import sqlite3

row_number_buffer=0;
def row_number(v):
  print('row_number called:',v);
  global row_number_buffer;
  row_number_buffer+=1;
  return row_number_buffer;


if __name__ == '__main__':
  db = sqlite3.connect(':memory:');
  db.create_function("row_number", 1, row_number);

  c = db.cursor();
  c.execute('''CREATE TABLE example(name)''');

  for i in 
('1_one','2_two','3_three','4_four','5_five','6_six','7_seven','8_eight','9_nine'):
c.execute('''INSERT INTO example(name) VALUES(?)''', (i,));
  db.commit();

  print('Test1 OK');
  #call a "with" statement without sql where expression
  c.execute("""with t (id,data) as (select row_number(name),name from example ) 
select * from t order by id""");
  print('Correct result (without ID filter):',c.fetchall());

  print('Test2 NOK');
  row_number_buffer=0;
  #call a "with" statement with application sql function filter ID<=5
  c.execute("""with t (id,name) as (select row_number(name),name from example ) 
select * from t WHERE ID<=5 order by id""");
  print('Incorrect result (WHERE ID<=5):',c.fetchall());

  db.close();

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

[sqlite] SQLITE Massif diagram

[Ali Sarlak]

Hi all,

I wrote a simple app that is use sqlite 3.8.2 as below:

//
// Name: test.cpp
// Author  : Ali
// Version :
// Copyright   : GPLv3
// Description : Sqlite Test
// SQLITE Version : 3.8.2
//

#include 
#include 
#include "sqlite3.h"

#define ERROR -1
#define MY_DB "test.db"

int myCallBackFunction(void* notUsed, int columnNo, char **argv,
char **azcolName)
{
static bool flag = true;
if (flag)
{
printf("%s%s%s\n", azcolName[0], azcolName[1], 
azcolName[2]);
printf("\n");
flag = false;
}
{
printf("%s%s%s$\n", argv[0], argv[1], argv[2]);
}
return 0;
}

int main()
{
sqlite3 *dbConnection;
char *sqlite_errMsg;

int result = ERROR;
result = sqlite3_open(MY_DB, );

if (result != SQLITE_OK)
{
printf("Error on SQLITE\n");
sqlite3_close(dbConnection);
return ERROR;
}

printf("%s DB Was successfully created!\n", MY_DB);

char *sqlCommand =
"DROP TABLE IF EXISTS Cars;"
"CREATE TABLE Cars(Id INT, Name TEXT, Price INT);"
"INSERT INTO Cars VALUES(1, 'Audi', 52642);"
"INSERT INTO Cars VALUES(2, 'Mercedes', 57127);"
"INSERT INTO Cars VALUES(3, 'Skoda', 9000);"
"INSERT INTO Cars VALUES(4, 'Volvo', 29000);"
"INSERT INTO Cars VALUES(5, 'Bentley', 35);"
"INSERT INTO Cars VALUES(6, 'Citroen', 21000);"
"INSERT INTO Cars VALUES(7, 'Hummer', 41400);"
"INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);";

result = sqlite3_exec(dbConnection, sqlCommand, 0, 0, _errMsg);

if (result != SQLITE_OK)
{
printf("SQL error: %s\n", sqlite_errMsg);
sqlite3_free(sqlite_errMsg);
sqlite3_close(dbConnection);
return ERROR;
}

sqlite3_stmt *stmt;
char *sqlSelect = "SELECT * FROM Cars";

result = 
sqlite3_exec(dbConnection,sqlSelect,myCallBackFunction,0,_errMsg);

if (result != SQLITE_OK )
{
printf("Failed to select data\n");
printf("SQL error: %s\n", sqlite_errMsg);
sqlite3_free(sqlite_errMsg);
sqlite3_close(dbConnection);
return ERROR;
}

sqlite3_close(dbConnection);
return 0;
}


When I use Massif as a heap profiler the diagram shows non freed memory but 
without Sqlite the diagram starts with 0 byte and end in 0 byte?




Yours,
Ali




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

[sqlite] Creating indexes on non-existent columns with double quotes

[Ersin Akinci]

I'm trying to track down a behavior in SQLite that I don't fully understand
and was hoping to get some help with. Here are three CREATE INDEX
statements for a table called reports that does NOT have a column called
yearz_doesnt_exist:

CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
(yearz_doesnt_exist);
CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
('yearz_doesnt_exist');
CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports
("yearz_doesnt_exist");

The first two of these statements will fail with the error 'Error: no such
column: yearz_doesnt_exist', but the last one goes through. After executing
the last statement, if we look at the output of index_list and index_info,
we get:

sqlite> PRAGMA index_list(reports);
0|index_reports_on_yearz_doesnt_exist|0|c|0
sqlite> PRAGMA index_info(index_reports_on_yearz_doesnt_exist);
0|-2|

I understand that the double quotation syntax is used to indicate
identifiers. Functionally, shouldn't it be equivalent to using no quotes in
this example? Why am I allowed to create an index on a non-existent column
when I use double quotes?

(Using SQLite 3.14.0 on macOS 10.12.1.)

Thanks,
Ersin

-- 
Ersin Y. Akinci -- ersinakinci.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[Cecil Westerhof]

2017-01-23 23:20 GMT+01:00 Ben Newberg :

> what i've done in the past is append a character to the value and make use
> of "cast":
>
> ​​
> update desktops set indexNo = indexNo || '_';
> update desktops set indexNo = cast(indexNo as integer) + 1;
>
> then:
> insert into desktops values (new row with index = 1);
>
> from the docs, which i hope i'm not misreading:
>
> http://sqlite.org/lang_expr.html#castexpr
> "When casting a TEXT value to INTEGER, the longest possible prefix of the
> value that can be interpreted as an integer number is extracted from the
> TEXT value and the remainder ignored."
>
> has worked for me for years but ymmv
>

​It looks very promising. Would also be very useful when the order is
changed. One strange quirk: the first statement does not work in
sqlitebrowser, but the second does. The first does work in the commandline
tool.

When I am building my GUI I am going to play with it.


The program I am using this table:

https://github.com/CecilWesterhof/PythonScripts/blob/master/startPrograms.py
​


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