Re: [sqlite] Problem with WHEN expressions inside CASE statement in SELECT statement

[drh]

Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
> Robert Duff <[EMAIL PROTECTED]> wrote:
> 
> > I had a problem with inserting bad unicode characters into my database. 
> > ...
> > Returned by "SELECT locationsFK,variable,logindex, CASE units WHEN units 
> > ISNULL THEN '#!#!#!#!#!#!#!#!#!#!' WHEN units = '??F' THEN 
> > '' WHEN units != '??F' THEN '!!!' ELSE 
> > 'WHY DOES ONLY THE ELSE WORK???' END FROM data LIMIT 10;"
> > "1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "WHY DOES ONLY THE ELSE 
> > WORK???",
> > ...
> 
> I don't know if there's any other problem, but it looks like
> you don't understand how case expressions work. There are
> two forms, and you're confusing them. If you remove the
> first instance of 'units' in your query, it may work as you
> were expecting.
> 

Aha!  That's the problem.  Good catch, Kurt.  I thought for
a while that there was a bug in SQLite...

To clarify, you should say:

   CASE
  WHEN units ISNULL THEN 'a'
  WHEN units='??F' THEN 'b'
  WHEN units!='??F' THEN 'd'
  ELSE 'd'
   END

When you say this:

   CASE units
  WHEN units ISNULL THEN 'a'
  WHEN units='??F' THEN 'b'
  WHEN units!='??F' THEN 'd'
  ELSE 'd'
   END

It is evaluating each subexpression in between WHEN and THEN
into a boolean (an integer 0 or 1) then comparing that against
units.  Since units is a string, the comparison is always false
and you end up falling through into the ELSE case.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] sqlite3_column_text question

[Alexei Alexandrov]

Perhaps because it is returning UTF8 and that needs to be unsigned, not signed?



Hmm, why sqlite3_bind_text takes const char * then?

--
Alexei Alexandrov

Re: [sqlite] [solved] Problem with WHEN expressions inside CASE statement in SELECT statement

[Robert Duff]

Kurt Welgehausen wrote:


Robert Duff <[EMAIL PROTECTED]> wrote:

 

I had a problem with inserting bad unicode characters into my database. 
...
Returned by "SELECT locationsFK,variable,logindex, CASE units WHEN units 
ISNULL THEN '#!#!#!#!#!#!#!#!#!#!' WHEN units = '??F' THEN 
'' WHEN units != '??F' THEN '!!!' ELSE 
'WHY DOES ONLY THE ELSE WORK???' END FROM data LIMIT 10;"
"1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "WHY DOES ONLY THE ELSE 
WORK???",

...
   



I don't know if there's any other problem, but it looks like
you don't understand how case expressions work. There are
two forms, and you're confusing them. If you remove the
first instance of 'units' in your query, it may work as you
were expecting.

The SQLite documentation 
gives the correct grammar but doesn't explain the semantics.
You can probably find examples of the two variants in one of
the several introductions to SQL on the web.

Regards


 


Thank you, this solved the problem.

--

Robert J. Duff
EnergyPro Services

Re: [sqlite] Problem with WHEN expressions inside CASE statement in SELECT statement

[Kurt Welgehausen]

Robert Duff <[EMAIL PROTECTED]> wrote:

> I had a problem with inserting bad unicode characters into my database. 
> ...
> Returned by "SELECT locationsFK,variable,logindex, CASE units WHEN units 
> ISNULL THEN '#!#!#!#!#!#!#!#!#!#!' WHEN units = '??F' THEN 
> '' WHEN units != '??F' THEN '!!!' ELSE 
> 'WHY DOES ONLY THE ELSE WORK???' END FROM data LIMIT 10;"
> "1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "WHY DOES ONLY THE ELSE 
> WORK???",
> ...

I don't know if there's any other problem, but it looks like
you don't understand how case expressions work. There are
two forms, and you're confusing them. If you remove the
first instance of 'units' in your query, it may work as you
were expecting.

The SQLite documentation 
gives the correct grammar but doesn't explain the semantics.
You can probably find examples of the two variants in one of
the several introductions to SQL on the web.

Regards

Re: [sqlite] sqlite3_column_text question

[Will Leshner]

On 6/30/06, Alexei Alexandrov <[EMAIL PROTECTED]> wrote:

Why does it return *unsigned* char *? Just const char * would seem
more natural


Perhaps because it is returning UTF8 and that needs to be unsigned, not signed?

[sqlite] Problem with WHEN expressions inside CASE statement in SELECT statement

[Robert Duff]

I had a problem with inserting bad unicode characters into my database. 
In the proccess of running an upgrade, I thought I would filter units 
like '??F' into '°F'. I believe I have the right logic, but the WHEN 
expressions of my CASE statement never seem to fire, only the ELSE 
expression.


For example, the following is returned by "SELECT 
locationsFK,variable,logindex,units FROM data LIMIT 10;" (yes some 
fields are NULL)


"1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "??F",
"1", "NVE_AI_1Boiler1_SupplnvoValu", "5", "??F",
"1", "NVE_DO_2Pump3_EnabnvoValueDO", "5", "",
"1", "NVE_DO_1Pump2_EnabnvoValueDO", "5", "",
"1", "NVE_DO_0Pump1_EnabnvoValueDO", "5", "",
"1", "NVE_SCCPRGnvoSpaceTemp", "5", "??F",
"1", "NVE_DO_1Boiler2_EnanvoValueD", "5", "",
"1", "NVE_DO_1Boiler2_EnanviOvrdD", "5", "",
"1", "NVE_DO_0Boiler1_EnanvoValueD", "5", "",
"1", "NVE_DO_0Boiler1_EnanviOvrdD", "5", "",
"0", "", "0", "",

Returned by "SELECT locationsFK,variable,logindex, (substr(units,1,2) == 
'??') FROM data LIMIT 10;"

"1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "1",
"1", "NVE_AI_1Boiler1_SupplnvoValu", "5", "1",
"1", "NVE_DO_2Pump3_EnabnvoValueDO", "5", "0",
"1", "NVE_DO_1Pump2_EnabnvoValueDO", "5", "0",
"1", "NVE_DO_0Pump1_EnabnvoValueDO", "5", "0",
"1", "NVE_SCCPRGnvoSpaceTemp", "5", "1",
"1", "NVE_DO_1Boiler2_EnanvoValueD", "5", "0",
"1", "NVE_DO_1Boiler2_EnanviOvrdD", "5", "0",
"1", "NVE_DO_0Boiler1_EnanvoValueD", "5", "0",
"1", "NVE_DO_0Boiler1_EnanviOvrdD", "5", "0",
"0", "", "0", "0",

Returned by "SELECT locationsFK,variable,logindex, CASE units WHEN units 
ISNULL THEN '#!#!#!#!#!#!#!#!#!#!' WHEN units = '??F' THEN 
'' WHEN units != '??F' THEN '!!!' ELSE 
'WHY DOES ONLY THE ELSE WORK???' END FROM data LIMIT 10;"
"1", "NVE_AI_2Boiler2_SupplnvoValu", "5", "WHY DOES ONLY THE ELSE 
WORK???",
"1", "NVE_AI_1Boiler1_SupplnvoValu", "5", "WHY DOES ONLY THE ELSE 
WORK???",
"1", "NVE_DO_2Pump3_EnabnvoValueDO", "5", "WHY DOES ONLY THE ELSE 
WORK???",
"1", "NVE_DO_1Pump2_EnabnvoValueDO", "5", "WHY DOES ONLY THE ELSE 
WORK???",
"1", "NVE_DO_0Pump1_EnabnvoValueDO", "5", "WHY DOES ONLY THE ELSE 
WORK???",

"1", "NVE_SCCPRGnvoSpaceTemp", "5", "WHY DOES ONLY THE ELSE WORK???",
"1", "NVE_DO_1Boiler2_EnanvoValueD", "5", "WHY DOES ONLY THE ELSE 
WORK???",
"1", "NVE_DO_1Boiler2_EnanviOvrdD", "5", "WHY DOES ONLY THE ELSE 
WORK???",
"1", "NVE_DO_0Boiler1_EnanvoValueD", "5", "WHY DOES ONLY THE ELSE 
WORK???",
"1", "NVE_DO_0Boiler1_EnanviOvrdD", "5", "WHY DOES ONLY THE ELSE 
WORK???",

"0", "", "0", "",

I've tested in my program and from the command line tool. This is the 
explain from the last statement, but I've never tried to read one 
before. I thought I'd include it to aid anyone who attempts to help me.

addr|opcode|p1|p2|p3
0|Integer|10|0|
1|MustBeInt|0|0|
2|MemStore|0|0|
3|IfMemZero|0|50|
4|IfMemPos|0|8|
5|Pop|1|0|
6|MemInt|-1|1|
7|Goto|0|9|
8|MemStore|1|1|
9|Goto|0|51|
10|Integer|0|0|
11|OpenRead|0|549|
12|SetNumColumns|0|6|
13|Rewind|0|49|
14|Column|0|0|
15|Column|0|3|
16|Column|0|1|
17|Column|0|5|
18|Integer|1|0|
19|Column|0|5|
20|IsNull|1|22|
21|AddImm|-1|0|
22|Dup|1|1|
23|Ne|354|27|collseq(BINARY)
24|Pop|1|0|
25|String8|0|0|#!#!#!#!#!#!#!#!#!#!
26|Goto|0|45|
27|Column|0|5|
28|String8|0|0|??F
29|Eq|98|0|collseq(BINARY)
30|Dup|1|1|
31|Ne|354|35|collseq(BINARY)
32|Pop|1|0|
33|String8|0|0|
34|Goto|0|45|
35|Column|0|5|
36|String8|0|0|??F
37|Ne|98|0|collseq(BINARY)
38|Dup|1|1|
39|Ne|354|43|collseq(BINARY)
40|Pop|1|0|
41|String8|0|0|!!!
42|Goto|0|45|
43|Pop|1|0|
44|String8|0|0|WHY DOES ONLY THE ELSE WORK???
45|Callback|4|0|
46|MemIncr|-1|0|
47|IfMemZero|0|49|
48|Next|0|14|
49|Close|0|0|
50|Halt|0|0|
51|Transaction|0|0|
52|VerifyCookie|0|53|
53|Goto|0|10|
54|Noop|0|0|

If I replace '"WHY DOES ONLY THE ELSE WORK???"' with 'units', the 
correct units get spit out, so the case seems to go through the rows 
fine, but the comparisons just don't work there. Also, I've tried '=', 
'==', '<>', and '!=' as comparison operators, no changes. I have my 
string substitution code worked out nicely so all I need is the CASE 
statement to work. Thank you.


--

Robert J. Duff
EnergyPro Services

Re: [sqlite] database corruption

[Jay Sprenkle]

On 6/29/06, Jens Miltner <[EMAIL PROTECTED]> wrote:

Hi everybody,

We have encountered two corrupted databases so far at customers and
we have no idea how they could become corrupted (we haven't had any
corruption in house so far).


Check your hardware too. A lot of cheap commodity hardware is not
terribly reliable.  http://www.memtest86.org

[sqlite] sqlite3_column_text question

[Alexei Alexandrov]

Why does it return *unsigned* char *? Just const char * would seem
more natural - now I need to cast it on Windows. Also from the docs
it's not clear who owns the memory returned. I assume that SQLite owns
it until sqlite3_step is called.

--
Alexei Alexandrov

Re: [sqlite] update or insert.

[Jens Miltner]

Am 27.06.2006 um 22:15 schrieb [EMAIL PROTECTED]:

My brain does not seem to be able to function properly today. I can  
think

of many ways to do what I want to do but none of them I like.

I will describe the problem in the most symplistic form.

I have two tables.  The first table, has a row which includes an 'id'
pointing to a row of another table.

create table config (profile_id integer, other_configs int );

The second table is defined as follows:

create table profile (id integer primary key autoincrement, name
varchar(32) unique, magic_number int);

Every entry in 'profile' should be unique. I would like to be able to
update (or insert if it does not exist) profile with a new  
'magic_number'
while keeping the same 'id' or updating the new 'id' in 'config'  
table.


What is the simplest way (and most efficient) to achieve this ?


Assuming you know the profile id, just use an INSERT OR REPLACE query  
(see ):


 INSERT OR REPLACE INTO profile VALUES(...)

Will insert a new record if none with that id exists or replace the  
data of the record with the given ID if it already exists.


HTH,

Re: [sqlite] use of index in ORDER BY clause

[Jens Miltner]

Am 29.06.2006 um 17:17 schrieb Dennis Cote:


Jens Miltner wrote:




Is there any way to improve the ORDER BY performance for joined  
queries? From your answer that the intermediate results are  
sorted, I take it no index won't ever be used when using ORDER BY  
with a join query?


Is there a way to rewrite the queries so we don't take the penalty  
of sorting without an index?


In our case, the 'bar' table may have several 100,000 entries and  
the 'foo' table probably has much less (say order of thousand).


A minimal query (i.e. one where we don't return a minimal amount  
of data, so the pure data shuffling doesn't get in the way) with a  
'bar' table with 250,000 entries and a 'foo' table with around 10  
entries (so the 250,000 entries in the 'bar' table relate to only  
a few records in the 'foo' table), a query like the above takes  
10-20 minutes to run (depending on the ORDER BY clauses used)!


(Side-note: The table does have quite a few columns and  
apparently, the amount of data per row also massively affects the  
performance... when I tried with the same table with not all  
columns filled in - i.e. less data - performance was much better.  
I also tried increasing the page size, hoping that less paging  
would have happen, but this didn't really make a noticeable  
difference)


I would really appreciate any hints on how to improve performance  
with this kind of setup...




Jens,

If you create an index on your bar.something column, it will be  
used to do the ordering. The log below shows how this query will be  
executed before and after creating this index.


Doh! You're right... I could have sworn when I tried, it didn't show  
an index on bar(something) to be used...
I probably confuse this with our real schema & query, which is  
slightly more complex and which I couldn't get to use an index on the  
ORDER BY column(s)...
Maybe it's because there sometimes are more than one sort column,  
even from distinct tables. I guess in that case, I'm probably stuck  
without an index?


Thanks for your help so far,

Re: [sqlite] database corruption

[Jens Miltner]

Am 30.06.2006 um 00:01 schrieb [EMAIL PROTECTED]:


I have never yet found anything useful by analyzing a corrupt
database file.  Generally, the only way to fix this kind of problem
is come up with a reproducible test case.


Unfortunately, that may prove difficult, if not impossible :(
Anyway, thanks for your quick reply...





P.S.: the sqlite version used at the customer site was 3.3.4



What OS?  How did you compile SQLite (what compile-time options
are turned on?)  What run-time options are using using
(auto-vacuum, non-standard page size, etc)?  How large are the
database files when you first notice the corruption.  What
was the program doing at the time the corruption appeared?


- Mac OS X (not sure what the exact version at the client side was -  
10.3 or 10.4).


- configure options are: --enable-threadsafe --disable-shared -- 
enable-static CFLAGS="-g -O2 -Wno-long-double"


- we are building with a custom Xcode project (in this case using  
gcc-3.3 to get backward compatibility with 10.3). We build as a  
static link library (universal binary PowerPC & Intel). Compile time  
defines are:
OS_UNIX=1 SQLITE_OMIT_CURSOR THREADSAFE=1 HAVE_USLEEP=1  
SQLITE_THREAD_OVERRIDE_LOCK=-1 TEMP_STORE=1 NDEBUG


- we don't use any special runtime options, the only special thing we  
do when opening the database is to use "PRAGMA short_column_names=OFF"


- Our last schema update did run a VACUUM and did run some ALTER  
TABLE ADD COLUMN statements afterwards. (not sure whether this is  
relevant)


- The database file was about 16 MB when the corruption was noticed.

- I have no idea what the program was doing when the corruption  
appeared. It's a multithreaded server application, thus it's hard to  
say what was going on even if I knew the exact time when the  
corruption occured :(


Sorry, this is probably not a whole lot of useful information.

Assuming nobody (except for sqlite) touched the database or journal  
files, any idea what could be other reasons that may lead to a  
database corruption?


Thanks,

[sqlite] Virtual Table: Interface concerns

[Ralf Junker]

Hello,

I have a few remarks concerning the Virtual Table interface as it is currently 
defined in CVS. I am very enthusiastic about the Virtual Table concept but can 
see a few limitations. Time permitting, I kindly ask the core developers to 
give a short feedback that they have taken notice of this.

1. xCreate

Would it be possible to replace the following parameter in xCreate:

  char **argv  -->  sqlite3_value **

This would save applications to parse parameters strings for numbers, decode 
binary information, dequote strings, etc. Since this functionality is already 
buld into SQLite internally and is known to work reliably, it would be nice if 
applications would not have to reinvent the wheel and duplicate the parsing 
code for each virtual table module.

2. xBestIndex / xFilter / sqlite3_index_info

The sqlite3_index_info structure contains char *idxStr; which may be used to 
the application's liking. In addition, int needToFreeIdxStr; can be used to 
instruct SQLite3 to free idxStr.

Would it be possible that SQLite3 could pass idxStr back to the application 
when it needs to be freed instead of freeing it using sqlite3_free? This would 
allow to free complex memory structures which sqlite3_free can not handle. I 
would not mind if idxStr would just be void* so I can store to it whatever I 
need.

3. xRowID / xUpdate

As outlined in the Wiki, the 64bit integer RowID concept is not supported by 
all databases and many potential uses of Virtual Tables. I proposed to add a 
more generic interface to ensure reliable updates for those:

  xGetBookmark
  xFreeBookmark

The Virtual Table Methods wiki contains more details.

Best regards,

Ralf