Re: [sqlite] Is this a bug?

[Roman Fleysher]

Oh, Thank you Barry.

I am glad it is not a bug. Bug is in my head.

Thank you,

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry [smith.bar...@gmail.com]
Sent: Tuesday, June 18, 2019 7:59 PM
To: SQLite mailing list
Subject: Re: [sqlite] Is this a bug?

Your nested select statement wants to return many rows. Technically I think
this is illegal SQL (?), but it looks like SQLite is being lax about this
as usual and doing what it normally does in this sort of situation: picks a
row at random from the inner select.

Your statement is more or less equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher 
wrote:

> Dear SQLiters,
>
>
> I can not figure out what I am doing wrong. In testing, I simplified to
> the following:
>
> CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);
>
> SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY
> refVolume;
>
> refVolume   CAST(10*max(cosSquared) AS INT)
> --  ---
> 2   9
> 3   9
> 4   9
> 5   9
> .
> 31  9
> 32  9
> 33  9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> --
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&reserved=0
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug?

[Barry]

Your nested select statement wants to return many rows. Technically I think
this is illegal SQL (?), but it looks like SQLite is being lax about this
as usual and doing what it normally does in this sort of situation: picks a
row at random from the inner select.

Your statement is more or less equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher 
wrote:

> Dear SQLiters,
>
>
> I can not figure out what I am doing wrong. In testing, I simplified to
> the following:
>
> CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);
>
> SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY
> refVolume;
>
> refVolume   CAST(10*max(cosSquared) AS INT)
> --  ---
> 2   9
> 3   9
> 4   9
> 5   9
> .
> 31  9
> 32  9
> 33  9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> --
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a bug?

[Roman Fleysher]

Dear SQLiters,


I can not figure out what I am doing wrong. In testing, I simplified to the 
following:

CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);

SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY 
refVolume;

refVolume   CAST(10*max(cosSquared) AS INT)
--  ---
2   9
3   9
4   9
5   9
.
31  9
32  9
33  9

That is, we see that for refVolumes between 2 and 33, the value of the CAST() 
is always 9. Thus, I expect the following statement to output the same list of 
refVolumes. But it does not:

SELECT DISTINCT refVolume FROM cosSquared
 WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM 
cosSquared);

refVolume
--
2

What am I doing wrong? I am using version 3.16.

Thank you for your help,

Roman

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

Re: [sqlite] Is this a bug with expression evaluation?

[Clemens Ladisch]

Tony Papadimitriou wrote:
> I really don't know what the standard says, but here are two different
> opinions in implementation.
>
> MySQL example:

You know that the "SQL" in "MySQL" is actually the abbreviation of
"something quite loose"?  ;-)

Anyway, it appears even MySQL conforms to SQL-92 subclause 6.12:

| 1) If the data type of both operands of a dyadic arithmetic opera-
|tor is exact numeric, then the data type of the result is exact
|numeric, with precision and scale determined as follows:
|[...]
|d) The precision and scale of the result of division is
|   implementation-defined.


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

Re: [sqlite] Is this a bug with expression evaluation?

[Peter Da Silva]

On 12/14/17, 12:08 PM, "sqlite-users on behalf of Simon Slavin" 
 wrote:
> Just to remind you that if something is not documented it can change.  The 
> next version of SQLite might decide that 1 / 2 is 0.  So don’t write code 
> that depends on it.

I think it already does:

sqlite> select 1/2;
0
sqlite> 


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

Re: [sqlite] Is this a bug with expression evaluation?

[Richard Hipp]

On 12/14/17, Tony Papadimitriou  wrote:
>
> MySQL example:
> mysql> select 1/2;
> ++
> | 1/2|
> ++
> | 0.5000 |
> ++
> 1 row in set (0.13 sec)

MySQL is the only database engine that behaves this way.  All others
do integer arithmetic on integer values.

This is probably the reason that MySQL has the separate "DIV" operator
for integer division, whereas everybody else makes due with the
standard "/" operator.
-- 
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] Is this a bug with expression evaluation?

[Simon Slavin]

On 14 Dec 2017, at 5:03pm, Tony Papadimitriou  wrote:

> SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has 
> no affinity. "
> It seems that 'no affinity' gets translated to integer affinity, then.

Just to remind you that if something is not documented it can change.  The next 
version of SQLite might decide that 1 / 2 is 0.  So don’t write code that 
depends on it.

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

Re: [sqlite] Is this a bug with expression evaluation?

[Tony Papadimitriou]

-Original Message- 
From: J. King


Someone please correct me if I'm wrong, but I believe it's mandated by the 
SQL standard that integer division is used when both operands are integers.


I really don't know what the standard says, but here are two different 
opinions in implementation.


MySQL example:
mysql> select 1/2;
++
| 1/2|
++
| 0.5000 |
++
1 row in set (0.13 sec)

PostgreSQL example:
psql=# select 1/2;
?column?
--
   0
(1 row)


Your synthetic example doesn't use a fixed table, but if it did the easiest 
solution for you would probably be to define any columns where you need 
arbitrary precision as REAL rather than INTEGER, and SQLite column 
>affinity would do the rest.


SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has 
no affinity. "

It seems that 'no affinity' gets translated to integer affinity, then.

Is there a way to default to float?

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

Re: [sqlite] Is this a bug with expression evaluation?

[J. King]

Someone please correct me if I'm wrong, but I believe it's mandated by the SQL 
standard that integer division is used when both operands are integers. 

Your synthetic example doesn't use a fixed table, but if it did the easiest 
solution for you would probably be to define any columns where you need 
arbitrary precision as REAL rather than INTEGER, and SQLite column affinity 
would do the rest.

Otherwise, yes, I believe you would need to cast. 

On December 14, 2017 11:36:19 AM EST, Tony Papadimitriou  wrote:
>I’ve noticed this (very annoying) behavior:
>
>select column1*(24/100) wrong from (values(100));
>
>Removing the parentheses yields the correct result:
>
>select column1*24/100 correct from (values(100));
>
>This obviously behaves like integer math is used and (24/100) gets
>truncated to zero.
>
>If I add a dot to either number (e.g., 24. or 100.) I get the correct
>result.
>But, with named fields, it’s not as easy as adding a dot:
>
>select column1*(column2/column3) wrong from (values(100,24,100));
>select column1*column2/column3 correct from (values(100,24,100));
>
>So, to get correct answer I have to use a cast for either field?
>
>select column1*(cast(column2 as float)/column3) correct from
>(values(100,24,100));
>
>In this example removing the parentheses is a simple solution.
>But if the expression was column1*(1+column2/column3) a cast is the
>only way?  (Hope not!)
>
>Anyway, if all this happens to be so by design, is there at least some
>way to default to float math rather than integer?
>
>Thanks.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug with expression evaluation?

[Marc L. Allen]

I just multiply by 1.0

Select column1*(column2 * 1.0 / column3)...

Removing the parentheses only provide the correct results in your example.  
It's still using integer math, it's just performing the multiply first, as per 
order of operations.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Tony Papadimitriou
Sent: Thursday, December 14, 2017 11:36 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Is this a bug with expression evaluation?

I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to 
zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100)); select 
column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from 
(values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  
(Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to 
default to float math rather than integer?

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


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a bug with expression evaluation?

[Tony Papadimitriou]

I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to 
zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100));
select column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from 
(values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  
(Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to 
default to float math rather than integer?

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

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Kees Nuyt]

On Fri, 21 Aug 2015 22:58:51 +0200, "sqlite-mail"
 wrote:

> That's why I think that expose a basic SQL ANSI catalog would make this kind
> of work and others a lot easier.  

That would be nice to have in some development environment (you
could develop one, if nobody else has done it), but in my
opinion it does not belong in the core engine that SQLite is.
It's lite for good reasons.

It even would be fine to me if all ALTER TABLE features would be
removed.

-- 
Regards, 

Kees Nuyt

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Tim Streater]

On 22 Aug 2015 at 11:01, Kees Nuyt  wrote: 

> It even would be fine to me if all ALTER TABLE features would be
> removed.

I disagree with this, although I'd be satisfied if there were a separate 
library and API for such things. With an evolving application I need to have 
ALTER TABLE. Even the reduced version we have now is adequate.

--
Cheers  --  Tim

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

That's why I think that expose a basic SQL ANSI catalog would make this kind
of work and others a lot easier.  

?  
>  Fri Aug 21 2015 22:13:00 CEST from "Simon Slavin"  
>Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>dependencies ?
>
>  On 21 Aug 2015, at 9:07pm, Scott Hess  wrote:
> 
>  
>>That said, writing code to do this manually has potential to be error
>> prone. It might make sense under an ENABLE flag. It feels like an obvious
>> thing to have, not something out-of-scope like embedding a new language or
>> support for a new storage methodology.
>> 

>  To do it correctly you'd have to identify all table names used by each
>trigger and view. Which means you have to parse the VIEWs and TRIGGERs and
>look in all the positions where you'd expect to find a table name.
> 
> I don't know whether the existing SQLite statement parser could be used for
>this but it does seem to be a complicated task.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Simon Slavin]

On 21 Aug 2015, at 9:07pm, Scott Hess  wrote:

> That said, writing code to do this manually has potential to be error
> prone.  It might make sense under an ENABLE flag.  It feels like an obvious
> thing to have, not something out-of-scope like embedding a new language or
> support for a new storage methodology.

To do it correctly you'd have to identify all table names used by each trigger 
and view.  Which means you have to parse the VIEWs and TRIGGERs and look in all 
the positions where you'd expect to find a table name.

I don't know whether the existing SQLite statement parser could be used for 
this but it does seem to be a complicated task.

Simon.

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Simon Slavin]

On 21 Aug 2015, at 8:13pm, Scott Hess  wrote:

> Since renameTriggerFunc()
> follows renameParentFunc(), my guess is that triggers are also handled. 

The documentation says that statements within TRIGGERs are not changed 
correctly:



Perhaps renameParentFunc() suggests that you can rename a TRIGGER, not that the 
commands within TRIGGERs are changed.

I do not know whether the current version of SQLite correctly changes TRIGGERs 
or not.  Since it would require complete parsing of the action text, I suspect 
not.

Simon.

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Thank you for your attention !  

I'm pointing this here because postgresql do manage this case properly !  

And I'm creating a tool to prototype database applications and I'm using
sqlite as the primary database, when we are prototyping things can change
drastically at any point and if we already have a lot of views/triggers it's
a pain in the ass to fix it (postgresql does it fine).  

Also that we got to this point would be nice if sqlite implemented a basic
SQL ANSI data dictioanry,? I mean sqlite already provide most of the info
for a basic data dictionary but in a non compliant way "PRAGMAS", although is
better than nothing we can not use that info on sql statements like
views/joins.  

Would be nice if sqlite provides virtual ansi views (like postgresql does on
top of it's pg_catalog) for :  

- check_constraints  

?  

- collations  

- columns  

- key_column_usage  

- referential_constraints  

- sequences  

- table_constraints  

- tables  

- triggered_update_columns  

- triggers  

- user_defined_types  

- views


?  

Thanks in advance for your time, attention and great work !  

Cheers !   

?  
>  Fri Aug 21 2015 19:11:03 CEST from "Richard Hipp"  
>Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>dependencies ?
>
>  On 8/21/15, sqlite-mail  wrote:
>  
>>Then do you think this is a bug ?
>> 

>  The word "bug" means it gets the wrong answer.
> 
> The absence of a feature is not a bug, unless that feature is a
> required part of the specification for the program. The ability to
> rename tables and all dependencies is not a required feature in this
> case.
> -- 
> 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] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Thanks for your attention!  

Only to remark on this I tested on postgresql and somehow it knows how deal
with it ! "so few (none?)"  

Cheers !  

?  
>  Fri Aug 21 2015 19:08:58 CEST from "J Decker"   Subject:
>Re: [sqlite] Is this a bug ? How to rename a table and all dependencies ?
>
>  On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail 
>wrote:
>  
>>Then do you think this is a bug ?
>>  
>>>Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" 
>>> Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>>> dependencies ?

>>  

>  while it may be considered a nice thing; it's not common practice to
> rename tables, so few (none?) sql implementations automatically update
> references to tables when they are renamed. Not a bug; more like out
> of scope.
> 
> It wouldn't know if you were moving a source table for archival
> purposes and going to replace it with another empty one or moving
> because you didn't like your original schema.
> 
>  
>>  
>>>On 21 Aug 2015, at 12:20pm, sqlite-mail  wrote:
>>> 
>>> 
>>>  
>>>>Does anybody knows how to rename a table and all it's dependencies in one
>>>> go
>>>> ?
>>>> 
>>>> 

>>>  Can't be done. Sorry.
>>> 
>>> Simon.
>>> ___
>>> 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] Is this a bug ? How to rename a table and all dependencies ?

[Simon Slavin]

On 21 Aug 2015, at 7:02pm, sqlite-mail  wrote:

> I'm pointing this here because postgresql do manage this case properly !

If you want postgres, you know where to find it.

Please don't forget that SQLite has to run on your smartphone and your SatNav 
device and your TV.  It has different design objectives to the big 
client/server database systems.

Simon.

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Then do you think this is a bug ?  
>  Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin"  
>Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>dependencies ?
>
>  On 21 Aug 2015, at 12:20pm, sqlite-mail  wrote:
> 
>  
>>Does anybody knows how to rename a table and all it's dependencies in one
>>go
>> ? 
>> 

>  Can't be done. Sorry.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Simon Slavin]

On 21 Aug 2015, at 12:20pm, sqlite-mail  wrote:

> Does anybody knows how to rename a table and all it's dependencies in one go
> ? 

Can't be done.  Sorry.

Simon.

[sqlite] Is this a bug ? Pragmas do not accept qualified names !

[sqlite-mail]

Thank you !  

That's what I want and looking back in the sqlite documentation I can see now
that I was misinterpreting it, in reality I was applying the same principle
used on other places to qualify/prefix tables/views/... objects but with your
help I could realize that pragmas are an exception to the genral rule.  

?  

Again thank you !  

?  
>  Fri Aug 21 2015 16:42:02 CEST from "Scott Hess"  
>Subject: Re: [sqlite] Is this a bug ? Pragmas do not accept qualified names !
>
>  I think you wanted:
> PRAGMA attached_db.table_info(one_table);
> 
> -scott
> 
> On Fri, Aug 21, 2015 at 7:35 AM, sqlite-mail 
> wrote:
> 
>  
>>Hello !
>> 
>> Today I'm working with sqlite3 with attached databases and when I tried to
>> get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas
>> do
>> not work with qualified/prefixed names like:
>> 
>> PRAGMA table_info(attached_db.one_table)
>> 
>> 
>> 
>> Is this a bug ?
>> 
>> Cheers !
>> ___
>> 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] Is this a bug ? Pragmas do not accept qualified names !

[sqlite-mail]

Hello !  

Today I'm working with sqlite3 with attached databases and when I tried to
get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas do
not work with qualified/prefixed names like:  

PRAGMA table_info(attached_db.one_table)  

?  

Is this a bug ?  

Cheers !

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[John McKown]

On Fri, Aug 21, 2015 at 1:02 PM, sqlite-mail 
wrote:

> Thank you for your attention !
>
> I'm pointing this here because postgresql do manage this case properly !
>

?And is significantly larger and harder to install. PostgreSQL is not
"lite"! I know. I use it and love it. ?



>
> And I'm creating a tool to prototype database applications and I'm using
> sqlite as the primary database, when we are prototyping things can change
> drastically at any point and if we already have a lot of views/triggers
> it's
> a pain in the ass to fix it (postgresql does it fine).
>

?Which is why I keep all my definitions in a file. I edit them there, then
use the ".read" in sqlite3 to bring them all in. Granted this doesn't help
if you have a lot of data in the data base. In that case, I don't rename. I
create the new table and populate it with the data in the old table via a
INSERT INTO ... SELECT ... ?



>
> Also that we got to this point would be nice if sqlite implemented a basic
> SQL ANSI data dictioanry,  I mean sqlite already provide most of the info
> for a basic data dictionary but in a non compliant way "PRAGMAS", although
> is
> better than nothing we can not use that info on sql statements like
> views/joins.
>

?OK, implement one. This would be an _excellent_ "add on" product. Write a
program which reads the data base schema. Using that internal schema,
determine how to update all the dependencies. To implement, send all the
required commands to the sqlite3 API. ?

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Hello !  

Here I'm fixing some typos and I also tested on postgresql and there all
views are updated properly then I'll say is a bug in sqlite.  

Does anybody knows how to rename a table and all it's dependencies in one go
 ? 

 ? 

 The problem: a database has several tables and views that reference/use
other
 tables something simplified like this: 

  

 CREATE TABLE a(id integer primary key, name text); 

 CREATE TABLE b(id integer primary key, a_id integer references a(id), name
text); 

 CREATE VIEW aview AS select * from a; 

 CREATE VIEW bview AS select b.*, a.name as aname from b left join a on
b.a_id =a.id; 

 === 

 ? 

 Now if we do "alter table a rename to a2;" actually sqlite only rename the
 "a" all foreign key references from "a" to "a2" but leaves all views broken.



 ===schema after "alter table a rename to a2;" 

 CREATE TABLE "a2"(id integer primary key, name text); 

 CREATE TABLE b(id integer primary key, a_id integer references "a2"(id),
name text); 

 CREATE VIEW aview AS select * from a; 

 CREATE VIEW bview AS select b.*, a.name as aname from b left join a on
b.a_id =a.id; 

 === 

 Is this a bug ? 

 Cheers !   

?

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Hello !  

Does anybody knows how to rename a table and all it's dependencies in one go
?  

?  

The problem: a database has several tables and views that reference/use other
tables something simplified like this:  

  

CREATE TABLE a(id integer primary key, name text);  

CREATE TABLE b(id integer primary key, a_id integer references a(id), name
text);  

CREATE VIEW aview AS select * from a;  

CREATE VIEW bview AS select b.*, a.name from b left join a on b.a_id =aid;  

===  

?  

Now if we do "alter table a rename to a2;" actually sqlite only rename the
"a" all foreign key references from "a" to "a2" but leaves all views broken. 


===schema after "alter table a rename to a2;"  

CREATE TABLE "a2"(id integer primary key, name text);  

CREATE TABLE b(id integer primary key, a_id integer references "a2"(id), name
text);  

CREATE VIEW aview AS select * from a;  

CREATE VIEW bview AS select b.*, a.name from b left join a on b.a_id =aid;  

===  

Is this a bug ?  

Cheers !  

?

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Richard Hipp]

On 8/21/15, sqlite-mail  wrote:
> Then do you think this is a bug ?

The word "bug" means it gets the wrong answer.

The absence of a feature is not a bug, unless that feature is a
required part of the specification for the program.  The ability to
rename tables and all dependencies is not a required feature in this
case.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Scott Hess]

On Fri, Aug 21, 2015 at 1:02 PM, Simon Slavin  wrote:

> On 21 Aug 2015, at 8:13pm, Scott Hess  wrote:
> > Since renameTriggerFunc()
> > follows renameParentFunc(), my guess is that triggers are also handled.
>
> The documentation says that statements within TRIGGERs are not changed
> correctly:
>
> 
>
> Perhaps renameParentFunc() suggests that you can rename a TRIGGER, not
> that the commands within TRIGGERs are changed.
>
> I do not know whether the current version of SQLite correctly changes
> TRIGGERs or not.  Since it would require complete parsing of the action
> text, I suspect not.


Hmm.  My impression of both triggers and views is that they don't really
have independent existence, instead they are inlined into relevant
statements.  So that would make sense as an omission.

That said, writing code to do this manually has potential to be error
prone.  It might make sense under an ENABLE flag.  It feels like an obvious
thing to have, not something out-of-scope like embedding a new language or
support for a new storage methodology.

-scott

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Scott Hess]

On Fri, Aug 21, 2015 at 11:06 AM, Simon Slavin  wrote:

> On 21 Aug 2015, at 7:02pm, sqlite-mail  wrote:
> > I'm pointing this here because postgresql do manage this case properly !
>
> If you want postgres, you know where to find it.
>
> Please don't forget that SQLite has to run on your smartphone and your
> SatNav device and your TV.  It has different design objectives to the big
> client/server database systems.


SQLite already handles this correctly for index definitions.  It also
handles it correctly for foreign keys IFF you have run "PRAGMA foreign_keys
= ON;" implemented by renameParentFunc().  Since renameTriggerFunc()
follows renameParentFunc(), my guess is that triggers are also handled.  So
having views not be handled maybe is just an unintended omission?

-scott

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Scott Doctor]

(oops glitched my send)

Try using a database modeling  program such as

TMS Software's Data Modeler
or
SQL Maestro Group's SQLite Maestro

These handle the changes nicely. You use the programs for 
designing your databases, queries, views and such. It spits out 
SQL to run on your system that handles setting up and changes. 
Or you can connect directly to your SQLite database and it will 
handle making all the changes for you.


Scott Doctor
scott at scottdoctor.com
--

On 8/21/2015 11:54 AM, Scott Doctor wrote:
>
> 
> Scott Doctor
> scott at scottdoctor.com
> --
>
> On 8/21/2015 10:37 AM, sqlite-mail wrote:
>> Thanks for your attention!
>>
>> Only to remark on this I tested on postgresql and somehow it 
>> knows how deal
>> with it ! "so few (none?)"
>>
>> Cheers !
>>
>>>   Fri Aug 21 2015 19:08:58 CEST from "J Decker" 
>>>   Subject:
>>> Re: [sqlite] Is this a bug ? How to rename a table and all 
>>> dependencies ?
>>>
>>>   On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail 
>>> 
>>> wrote:
>>>> Then do you think this is a bug ?
>>>>> Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" 
>>>>> 
>>>>> Subject: Re: [sqlite] Is this a bug ? How to rename a 
>>>>> table and all
>>>>> dependencies ?
>>>   while it may be considered a nice thing; it's not common 
>>> practice to
>>> rename tables, so few (none?) sql implementations 
>>> automatically update
>>> references to tables when they are renamed. Not a bug; more 
>>> like out
>>> of scope.
>>>
>>> It wouldn't know if you were moving a source table for archival
>>> purposes and going to replace it with another empty one or 
>>> moving
>>> because you didn't like your original schema.
>>>
>>>>> On 21 Aug 2015, at 12:20pm, sqlite-mail 
>>>>>  wrote:
>>>>>
>>>>>
>>>>>> Does anybody knows how to rename a table and all it's 
>>>>>> dependencies in one
>>>>>> go
>>>>>> ?
>>>>>>
>>>>>>
>>>>>   Can't be done. Sorry.
>>>>>
>>>>> Simon.
>>>>> ___
>>>>> 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
>
>
>

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Scott Doctor]

Scott Doctor
scott at scottdoctor.com
--

On 8/21/2015 10:37 AM, sqlite-mail wrote:
> Thanks for your attention!
>
> Only to remark on this I tested on postgresql and somehow it knows how deal
> with it ! "so few (none?)"
>
> Cheers !
>
> 
>>   Fri Aug 21 2015 19:08:58 CEST from "J Decker"   
>> Subject:
>> Re: [sqlite] Is this a bug ? How to rename a table and all dependencies ?
>>
>>   On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail > dev.dadbiz.es>
>> wrote:
>>   
>>> Then do you think this is a bug ?
>>>   
>>>> Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" 
>>>> Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>>>> dependencies ?
>>>   
>>   while it may be considered a nice thing; it's not common practice to
>> rename tables, so few (none?) sql implementations automatically update
>> references to tables when they are renamed. Not a bug; more like out
>> of scope.
>>
>> It wouldn't know if you were moving a source table for archival
>> purposes and going to replace it with another empty one or moving
>> because you didn't like your original schema.
>>
>>   
>>>   
>>>> On 21 Aug 2015, at 12:20pm, sqlite-mail  
>>>> wrote:
>>>>
>>>>
>>>>   
>>>>> Does anybody knows how to rename a table and all it's dependencies in one
>>>>> go
>>>>> ?
>>>>>
>>>>>
>>>>   Can't be done. Sorry.
>>>>
>>>> Simon.
>>>> ___
>>>> 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] Is this a bug ? How to rename a table and all dependencies ?

[J Decker]

On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail  
wrote:
> Then do you think this is a bug ?
>>  Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" 
>>Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>>dependencies ?
while it may be considered a nice thing; it's not common practice to
rename tables, so few (none?) sql implementations automatically update
references to tables when they are renamed.  Not a bug; more like out
of scope.

It wouldn't know if you were moving a source table for archival
purposes and going to replace it with another empty one or moving
because you didn't like your original schema.
>>
>>  On 21 Aug 2015, at 12:20pm, sqlite-mail  
>> wrote:
>>
>>
>>>Does anybody knows how to rename a table and all it's dependencies in one
>>>go
>>> ?
>>>
>
>>  Can't be done. Sorry.
>>
>> Simon.
>> ___
>> 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] Is this a bug ? Pragmas do not accept qualified names !

[Scott Hess]

I think you wanted:
  PRAGMA attached_db.table_info(one_table);

-scott

On Fri, Aug 21, 2015 at 7:35 AM, sqlite-mail 
wrote:

> Hello !
>
> Today I'm working with sqlite3 with attached databases and when I tried to
> get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas
> do
> not work with qualified/prefixed names like:
>
> PRAGMA table_info(attached_db.one_table)
>
>
>
> Is this a bug ?
>
> Cheers !
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

[Paul Sanderson]

Thanks for the link Bernard
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 16 November 2014 16:07, Bernardo Sulzbach  wrote:
> You are supposing that INT PRIMARY KEY == INTEGER PRIMARY KEY. Which,
> clearly, is not true.
> http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite
> see this link for more on the subject.
>
> 2014-11-16 13:56 GMT-02:00 Igor Tandetnik :
>
>> On 11/16/2014 10:51 AM, Paul Sanderson wrote:
>>
>>> AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
>>>
>>
>> No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY.
>> Which part of the error message do you find unclear?
>>
>> For details, see http://www.sqlite.org/autoinc.html
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Bernardo Sulzbach
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

[Bernardo Sulzbach]

You are supposing that INT PRIMARY KEY == INTEGER PRIMARY KEY. Which,
clearly, is not true.
http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite
see this link for more on the subject.

2014-11-16 13:56 GMT-02:00 Igor Tandetnik :

> On 11/16/2014 10:51 AM, Paul Sanderson wrote:
>
>> AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
>>
>
> No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY.
> Which part of the error message do you find unclear?
>
> For details, see http://www.sqlite.org/autoinc.html
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

[Igor Tandetnik]

On 11/16/2014 10:51 AM, Paul Sanderson wrote:

AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY


No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY. 
Which part of the error message do you find unclear?


For details, see http://www.sqlite.org/autoinc.html

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

[sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

[Paul Sanderson]

CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT,
temp text UNIQUE NOT NULL);

works OK

CREATE TABLE IF NOT EXISTS test (id INT PRIMARY KEY AUTOINCREMENT,
temp text UNIQUE NOT NULL);

gives error

AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug? Can't tell from docs....

[Jay A. Kreibich]

On Fri, Nov 18, 2011 at 05:30:20PM -0800, Yang Zhang scratched on the wall:
> I just got bit by some peculiar behavior in sqlite where
> 
>  id int primary key
> is different from:
>  id integer primary key
> In particular, sqlite will generate values for the latter but not the former:

  Well, yes...  They're different types.  "FLOAT" and "FLOATING POINT"
  have very different behaviors as well (although not for the reasons
  you might guess).

> I couldn't find in http://www.sqlite.org/autoinc.html

  To quote:

"If a table contains a column of type INTEGER PRIMARY KEY, then that 
 column becomes an alias for the ROWID. [...]  When a new row is
 inserted into an SQLite table, the ROWID can either be specified
 as part of the INSERT statement or it can be assigned automatically
 by the database engine."

  The docs are quite specific that this is a "column of type..." and
  not just any column with an integer storage class.  The phrase
  INTEGER PRIMARY KEY is also a link, which takes you to a section
  of the CREATE TABLE docs that includes this:

"Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER"
 or "UNSIGNED INTEGER" causes the primary key column to behave as
 an ordinary table column..."
  
> or http://www.sqlite.org/datatype3.html any mention of this
> peculiardistinguishing behavior. 

  Again, the phrase INTEGER PRIMARY KEY appears on this page as a link
  to the above information that clearly states the column must be
  defined as an "INTEGER".

  You seem to be assuming the defined type "INT" and the type "INTEGER"
  should be equivalent.

>  Anyway, if this is intentional (as
> I'm guessing), I wouldn't have been able to tell from the docs -
> perhaps this would warrant special mention?

  Almost every use of the phrase "INTEGER PRIMARY KEY" in the
  documentation is a link that brings you right to this information.
  It seems like it would be difficult to make this any more accessible
  without copying the information to several different places on the
  website, thus bulking up the docs so much people don't bother to read
  them and making it much more difficult to maintain.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug? Can't tell from docs....

[Peter Aronson]

Actually, it is documented, on the CREATE TABLE page, near the bottom, in
the section titled "ROWIDs and the INTEGER PRIMARY KEY".  Not that this is
an exactly obvious place to look for it...

Best regards,

Peter

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Yang Zhang
> Sent: Friday, November 18, 2011 6:30 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Is this a bug? Can't tell from docs
> 
> I just got bit by some peculiar behavior in sqlite where
> 
>  id int primary key
> is different from:
>  id integer primary key
> In particular, sqlite will generate values for the latter but not the
> former:
> sqlite> create table a (a integer primary key, b integer);sqlite>
> insert into a (b) values (0);sqlite> select * from a;1|0sqlite> create
> table b (a int primary key, b integer);sqlite> insert into b (b)
> values (0);sqlite> select * from b;|0
> I couldn't find in http://www.sqlite.org/autoinc.html
> orhttp://www.sqlite.org/datatype3.html any mention of this
> peculiardistinguishing behavior.  Anyway, if this is intentional (as
> I'm guessing), I wouldn't have been able to tell from the docs -
> perhaps this would warrant special mention?  Just thought I'd bring
> this to your attention.
> --
> Yang Zhang
> http://yz.mit.edu/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Is this a bug? Can't tell from docs....

[Yang Zhang]

I just got bit by some peculiar behavior in sqlite where

 id int primary key
is different from:
 id integer primary key
In particular, sqlite will generate values for the latter but not the former:
sqlite> create table a (a integer primary key, b integer);sqlite>
insert into a (b) values (0);sqlite> select * from a;1|0sqlite> create
table b (a int primary key, b integer);sqlite> insert into b (b)
values (0);sqlite> select * from b;|0
I couldn't find in http://www.sqlite.org/autoinc.html
orhttp://www.sqlite.org/datatype3.html any mention of this
peculiardistinguishing behavior.  Anyway, if this is intentional (as
I'm guessing), I wouldn't have been able to tell from the docs -
perhaps this would warrant special mention?  Just thought I'd bring
this to your attention.
-- 
Yang Zhang
http://yz.mit.edu/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

[Stephan Beal]

On Tue, Jun 28, 2011 at 8:50 PM, thilo  wrote:

> > use SINGLE quotes, not double quotes.
> bummer,  Thanks a lot
>

i PROMISE that you won't find such an obvious bug in sqlite3 ;).

sqlite uses single quotes because that's what ANSI SQL specifies. MySQL uses
(or can use) double quotes, but that is an unportable SQL extension.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

[thilo]

On 6/28/2011 8:45 PM, Stephan Beal wrote:
> On Tue, Jun 28, 2011 at 8:42 PM, thilo  wrote:
>
>> sqlite> select (select v from t1 where n="a") wrong,*  from a1;
>>
>
> use SINGLE quotes, not double quotes.
bummer,  Thanks a lot

thilo


-- 
Dipl. Ing. Thilo Jeremias
Zur Rabenwiese 14
27239 Twistringen
T: +49 15782492240
T: +49 4243941633

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

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

[Black, Michael (IS)]

Use single quotes instead of double



sqlite> select (select v from t1 where n='a') wrong,*  from a1;
wrong|a|b
2000|123|456
2000|999|999



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of thilo [th...@nispuk.com]
Sent: Tuesday, June 28, 2011 1:42 PM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Is this a BUG or am I wrong ? sub-select returns nothing 
when column id matches the inner query

Hello there!

I found a strange behavior while doing a
select with a sub select that has a where clause with a value (here
'a') which is the same as a column id:

What am I missing here ?


SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode column
sqlite> .header on

sqlite> select (select v from t1 where n="a") wrong,*  from a1;

wrong   a   b
--  --  --
123 456
999 999
sqlite>
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a1 (a int,b int);
INSERT INTO "a1" VALUES(123,456);
INSERT INTO "a1" VALUES(999,999);
CREATE TABLE "t1" (n text primary key on conflict replace,v integer);
INSERT INTO "t1" VALUES('good',1000);
INSERT INTO "t1" VALUES('a',2000);
COMMIT;
sqlite>

Any light to the problem (especially if it is already fixed) is very
much appreciated.

My OS is NetBSD,

cheers thilo


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

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

[Jim Morris]

string literals are enclose in single quotes not double quotes"

select (select v from t1 where n='a') wrong,*  from a1;



On 6/28/2011 11:42 AM, thilo wrote:
> select (select v from t1 where n="a") wrong,*  from a1;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

[Stephan Beal]

On Tue, Jun 28, 2011 at 8:42 PM, thilo  wrote:

> sqlite> select (select v from t1 where n="a") wrong,*  from a1;
>


use SINGLE quotes, not double quotes.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

[thilo]

Hello there!

I found a strange behavior while doing a
select with a sub select that has a where clause with a value (here
'a') which is the same as a column id:

What am I missing here ?


SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode column
sqlite> .header on

sqlite> select (select v from t1 where n="a") wrong,*  from a1;

wrong   a   b
--  --  --
123 456  
999 999  
sqlite>
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a1 (a int,b int);
INSERT INTO "a1" VALUES(123,456);
INSERT INTO "a1" VALUES(999,999);
CREATE TABLE "t1" (n text primary key on conflict replace,v integer);
INSERT INTO "t1" VALUES('good',1000);
INSERT INTO "t1" VALUES('a',2000);
COMMIT;
sqlite>

Any light to the problem (especially if it is already fixed) is very
much appreciated.

My OS is NetBSD,

cheers thilo


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

Re: [sqlite] is this a bug?

[Jay A. Kreibich]

On Fri, Dec 18, 2009 at 07:23:24PM -0700, Valerio Aimale scratched on the wall:
> Hello all,
> 
> I've run into an interesting situation; when duplicating parenthesis 
> around a 'in ()' subquery, only the first row is returned.

> Why only one value returned when parenthesis are duplicated?

  Wrapping a sub-SELECT in parenthesis turns it into an expression, rather
  than a result-set.  This is done by returning the first value.

  See the diagram here:   http://www.sqlite.org/lang_expr.html


  In your specific case, the "IN" operator is defined as:

 IN (  |  )

  In other words, it allows either a SELECT -or- one or more expressions.

  If it is a sub-SELECT that has one column, the IN operator is smart
  enough to consider the returned column to be an expression set.

  However, when you wrap the sub-SELECT in parenthesis, it becomes a
  scalar expression of only one value (the first row), so you're IN
  test-set has only one value, and returns only one row in the
  super-SELECT.  If you're using a sub-SELECT, the IN operator must see
  it directly.

> Same with triple parenthesis enclosing:

  An expression in parenthesis is still just an expression.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is this a bug?

[Virgilio Alexandre Fornazin]

It´s good to try to reproduce all conditions that this problem happens, to
help with creating test-cases
and with bug fix.

[]'s

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor
Sent: sábado, 19 de dezembro de 2009 00:38
To: General Discussion of SQLite Database
Subject: Re: [sqlite] is this a bug?

On Fri, Dec 18, 2009 at 8:23 PM, Valerio Aimale  wrote:
> Hello all,
>
> I've run into an interesting situation; when duplicating parenthesis
> around a 'in ()' subquery, only the first row is returned.
>
> This is not my real-life query, but a test that replicates the problem.
>
> Thanks,
>
> Valerio
>
> $ sqlite3 --version
> 3.6.16
>
> prepare some dummy data:
>
> create table test ( id INT );
> insert into test VALUES(1);
> insert into test VALUES(2);
> insert into test VALUES(3);
> insert into test VALUES(4);
> insert into test VALUES(5);
> insert into test VALUES(6);
> insert into test VALUES(7);
> insert into test VALUES(8);
> insert into test VALUES(9);
> insert into test VALUES(10);
>
> sqlite> select id from test where (id > 5);
> 6
> 7
> 8
> 9
> 10
> [Good]
>
> sqlite> select id from test where id in (select id from test where (id >
> 5));
> 6
> 7
> 8
> 9
> 10
> [Still Good]
>
> Now let's duplicate parenthesis around the subquery:
>
> sqlite> select id from test where id in ((select id from test where (id
>  > 5)));
> 6
>
> Why only one value returned when parenthesis are duplicated?
>
> Same with triple parenthesis enclosing:
>
> sqlite> select id from test where id in (((select id from test where (id
>  > 5;
> 6
>


Given the above table

sqlite> SELECT id FROM test WHERE id IN (6, 7, 8, 9, 10);
id
--
6
7
8
9
10
sqlite> SELECT id FROM test WHERE id IN ((6, 7, 8, 9, 10));
SQL error: near ",": syntax error
sqlite>

Seems like IN expects a comma separated list, and nothing else within
a single set of parens.




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

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

Re: [sqlite] is this a bug?

[P Kishor]

On Fri, Dec 18, 2009 at 8:23 PM, Valerio Aimale  wrote:
> Hello all,
>
> I've run into an interesting situation; when duplicating parenthesis
> around a 'in ()' subquery, only the first row is returned.
>
> This is not my real-life query, but a test that replicates the problem.
>
> Thanks,
>
> Valerio
>
> $ sqlite3 --version
> 3.6.16
>
> prepare some dummy data:
>
> create table test ( id INT );
> insert into test VALUES(1);
> insert into test VALUES(2);
> insert into test VALUES(3);
> insert into test VALUES(4);
> insert into test VALUES(5);
> insert into test VALUES(6);
> insert into test VALUES(7);
> insert into test VALUES(8);
> insert into test VALUES(9);
> insert into test VALUES(10);
>
> sqlite> select id from test where (id > 5);
> 6
> 7
> 8
> 9
> 10
> [Good]
>
> sqlite> select id from test where id in (select id from test where (id >
> 5));
> 6
> 7
> 8
> 9
> 10
> [Still Good]
>
> Now let's duplicate parenthesis around the subquery:
>
> sqlite> select id from test where id in ((select id from test where (id
>  > 5)));
> 6
>
> Why only one value returned when parenthesis are duplicated?
>
> Same with triple parenthesis enclosing:
>
> sqlite> select id from test where id in (((select id from test where (id
>  > 5;
> 6
>


Given the above table

sqlite> SELECT id FROM test WHERE id IN (6, 7, 8, 9, 10);
id
--
6
7
8
9
10
sqlite> SELECT id FROM test WHERE id IN ((6, 7, 8, 9, 10));
SQL error: near ",": syntax error
sqlite>

Seems like IN expects a comma separated list, and nothing else within
a single set of parens.




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

Re: [sqlite] is this a bug?

[Virgilio Alexandre Fornazin]

This is probably a bug in SQL parser, that doesn't reduced to one
parenthesis, 
causing the side effect in VDBE engine, that doesn't returned all ID´s to IN

evaluator in first select.

This should be easily reproduced, but the fix you must wait for Richard or
other
that have knowledge on VDBE instruction debugging.


I've tested the same thing on SQL Server 2008, correct results appeared:

---
create database x
go

use x
go

create table test (id int not null, primary key(id))
go

insert into test values (1)
insert into test values (2)
insert into test values (3)
insert into test values (4)
insert into test values (5)
insert into test values (6)
insert into test values (7)
insert into test values (8)
insert into test values (9)
insert into test values (10)
go

select id from test where id in (select id from test where id > 5)
go

id
---
6
7
8
9
10

(5 row(s) affected)

select id from test where id in (select id from test where (id > 5))
go

id
---
6
7
8
9
10

(5 row(s) affected)

select id from test where id in ((select id from test where (id > 5)))
go

id
---
6
7
8
9
10

(5 row(s) affected)


[]'s


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Valerio Aimale
Sent: sábado, 19 de dezembro de 2009 00:23
To: sqlite-users@sqlite.org
Subject: [sqlite] is this a bug?

Hello all,

I've run into an interesting situation; when duplicating parenthesis 
around a 'in ()' subquery, only the first row is returned.

This is not my real-life query, but a test that replicates the problem.

Thanks,

Valerio

$ sqlite3 --version
3.6.16

prepare some dummy data:

create table test ( id INT );
insert into test VALUES(1);
insert into test VALUES(2);
insert into test VALUES(3);
insert into test VALUES(4);
insert into test VALUES(5);
insert into test VALUES(6);
insert into test VALUES(7);
insert into test VALUES(8);
insert into test VALUES(9);
insert into test VALUES(10);

sqlite> select id from test where (id > 5);
6
7
8
9
10
[Good]

sqlite> select id from test where id in (select id from test where (id > 
5));
6
7
8
9
10
[Still Good]

Now let's duplicate parenthesis around the subquery:

sqlite> select id from test where id in ((select id from test where (id 
 > 5)));
6

Why only one value returned when parenthesis are duplicated?

Same with triple parenthesis enclosing:

sqlite> select id from test where id in (((select id from test where (id 
 > 5;
6


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

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

[sqlite] is this a bug?

[Valerio Aimale]

Hello all,

I've run into an interesting situation; when duplicating parenthesis 
around a 'in ()' subquery, only the first row is returned.

This is not my real-life query, but a test that replicates the problem.

Thanks,

Valerio

$ sqlite3 --version
3.6.16

prepare some dummy data:

create table test ( id INT );
insert into test VALUES(1);
insert into test VALUES(2);
insert into test VALUES(3);
insert into test VALUES(4);
insert into test VALUES(5);
insert into test VALUES(6);
insert into test VALUES(7);
insert into test VALUES(8);
insert into test VALUES(9);
insert into test VALUES(10);

sqlite> select id from test where (id > 5);
6
7
8
9
10
[Good]

sqlite> select id from test where id in (select id from test where (id > 
5));
6
7
8
9
10
[Still Good]

Now let's duplicate parenthesis around the subquery:

sqlite> select id from test where id in ((select id from test where (id 
 > 5)));
6

Why only one value returned when parenthesis are duplicated?

Same with triple parenthesis enclosing:

sqlite> select id from test where id in (((select id from test where (id 
 > 5;
6


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

Re: [sqlite] Is this a bug?

[Griggs, Donald]

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J. R. Westmoreland

Is there a way to get an autoincrement field without being a primary
key?


 Yes, you can create a trigger to do this.  (This also allows you great
flexibility, since you can, for instance, use an increment other than 1,
include a check digit, etc., but it's not "built-in" as the one for
primary key can be.)

Hope this helps,
   Donald

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

Re: [sqlite] Is this a bug?

[J. R. Westmoreland]

Is there a way to get an autoincrement field without being a primary key?


J. R. Westmoreland
E-mail: j...@jrw.org


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Friday, January 23, 2009 1:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is this a bug?

J. R. Westmoreland  wrote:
> I have the following table creation statements and get an error.
>
> Functioning statement
>
> CREATE TABLE xxx (ID INTEGERPRIMARY KEY AUTOINCREMENT, .)
>
> If I remove the PRIMARY KEY" part of the statement, according to the
> docs on SQL that should be still a valid statement, I get an error.
>
> Brken statement:
>
> CREATE TABLE zzz (ID INTEGER AUTOINCREMENT .);

SQLite only supports AUTOINCREMENT together with PRIMARY KEY. See

http://sqlite.org/lang_createtable.html

> But, if I change "AUTOINCREMENT  "AUTO_INCREMENT" it works
>
> Functioning statement:
>
> CREATE TABLE zzz (ID INTEGER AUTO_INCREMENT .);

AUTO_INCREMENT has no special meaning in the syntax. It works for the 
same reason this works:

CREATE TABLE zzz (ID WHATEVER I WANT);

Neither makes ID an autoincrement field.

Igor Tandetnik 



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

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

Re: [sqlite] Is this a bug?

[Igor Tandetnik]

J. R. Westmoreland  wrote:
> I have the following table creation statements and get an error.
>
> Functioning statement
>
> CREATE TABLE xxx (ID INTEGERPRIMARY KEY AUTOINCREMENT, .)
>
> If I remove the PRIMARY KEY" part of the statement, according to the
> docs on SQL that should be still a valid statement, I get an error.
>
> Brken statement:
>
> CREATE TABLE zzz (ID INTEGER AUTOINCREMENT .);

SQLite only supports AUTOINCREMENT together with PRIMARY KEY. See

http://sqlite.org/lang_createtable.html

> But, if I change "AUTOINCREMENT  "AUTO_INCREMENT" it works
>
> Functioning statement:
>
> CREATE TABLE zzz (ID INTEGER AUTO_INCREMENT .);

AUTO_INCREMENT has no special meaning in the syntax. It works for the 
same reason this works:

CREATE TABLE zzz (ID WHATEVER I WANT);

Neither makes ID an autoincrement field.

Igor Tandetnik 



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

Re: [sqlite] Is this a bug?

[Jay A. Kreibich]

On Fri, Jan 23, 2009 at 12:51:07PM -0700, J. R. Westmoreland scratched on the 
wall:
> I have the following table creation statements and get an error. 
> 
> Functioning statement
> CREATE TABLE xxx (ID INTEGERPRIMARY KEY AUTOINCREMENT, .)

> If I remove the PRIMARY KEY" part of the statement, according to the docs on
> SQL that should be still a valid statement, I get an error.

  AUTOINCREMENT is only a valid modifier to INTEGER PRIMARY KEY.


> Brken statement:
> CREATE TABLE zzz (ID INTEGER AUTOINCREMENT .);
> 
> But, if I change "AUTOINCREMENT  "AUTO_INCREMENT" it works

  Yes, but it doesn't mean anything.  You've defined a user-type of
  "INTEGER AUTO_INCREMENT", which is about as meaningful to SQLite
  as "INTEGER PINEAPPLE" (also valid).



  http://sqlite.org/autoinc.html

  Also, see the syntax diagram here:

  http://sqlite.org/syntaxdiagrams.html#column-constraint



   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a bug?

[J. R. Westmoreland]

I have the following table creation statements and get an error. 

Scenario:

 

Functioning statement

CREATE TABLE xxx (ID INTEGERPRIMARY KEY AUTOINCREMENT, .)

 

If I remove the PRIMARY KEY" part of the statement, according to the docs on
SQL that should be still a valid statement, I get an error.

 

Brken statement:

CREATE TABLE zzz (ID INTEGER AUTOINCREMENT .);

 

But, if I change "AUTOINCREMENT  "AUTO_INCREMENT" it works

 

Functioning statement:

CREATE TABLE zzz (ID INTEGER AUTO_INCREMENT .);

 

Did this get changed in SQL and I'm just not aware of it? Should I use
"AUTO_INCRMENT" verywhere now?

If it has change shouldn't the parser be consistant about it? 

 

Thanks,

J. R.

 



J. R. Westmoreland

E-mail: j...@jrw.org

 

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

Re: [sqlite] Is this a bug?

[Alex Katebi]

I am not sure about the email server. You're probably correct.
I spent couple of painful hours on this issue. I just moved the error printf
after finalize now.

Thanks Roger!
-Alex

On Fri, Jul 4, 2008 at 7:03 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Alex Katebi wrote:
> > I was able to get the attachment myself. It could be your email server.
>
> Are you sure?  I was looking at the actual raw message.  My mail server
> is postfix with messages funneled through clamassassin and spamassassin.
>  It could be the SQLite mailing list manager that stripped out the
> attachment when adding the list trailer part.
>
> >   sqlite3_step(pStmt);
> >   printf("%s\n", sqlite3_errmsg(db));
>
> If you add sqlite3_reset(pStmt) or sqlite3_finalize(pStmt) after the
> step then you get the correct error message.  Also note that you should
> use sqlite3_prepare_v2.
>
> In the "olden days" sqlite3_step() used to only return SQLITE_ERROR and
> then you had to call reset or finalize to get the actual error code and
> message.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIbqw6mOOfHg372QQRAmeFAKChAns1ELMHkCNdlAoBajWmQE4MnQCeJE9V
> qbl2fYgpqCwcbeCe1WAvEKU=
> =608O
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Alex Katebi wrote:
> I was able to get the attachment myself. It could be your email server.

Are you sure?  I was looking at the actual raw message.  My mail server
is postfix with messages funneled through clamassassin and spamassassin.
 It could be the SQLite mailing list manager that stripped out the
attachment when adding the list trailer part.

>   sqlite3_step(pStmt);
>   printf("%s\n", sqlite3_errmsg(db));

If you add sqlite3_reset(pStmt) or sqlite3_finalize(pStmt) after the
step then you get the correct error message.  Also note that you should
use sqlite3_prepare_v2.

In the "olden days" sqlite3_step() used to only return SQLITE_ERROR and
then you had to call reset or finalize to get the actual error code and
message.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIbqw6mOOfHg372QQRAmeFAKChAns1ELMHkCNdlAoBajWmQE4MnQCeJE9V
qbl2fYgpqCwcbeCe1WAvEKU=
=608O
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug?

[Alex Katebi]

I was able to get the attachment myself. It could be your email server.
Below is the the code.
Thanks,
-Alex


#include 

void bitwise_and(sqlite3_context* pContext, int N, sqlite3_value** ppValue)
{
  int dest_bytes = sqlite3_value_bytes(ppValue[0]);
  int mask_bytes = sqlite3_value_bytes(ppValue[1]);
  if(dest_bytes != mask_bytes)
{
  sqlite3_result_error(pContext, "unequal blob lengths", -1);
  return;
}
  char* dest = (char*) sqlite3_value_blob(ppValue[0]);
  char* mask = (char*) sqlite3_value_blob(ppValue[1]);
  char* result = sqlite3_malloc(dest_bytes);
  int i; for(i = 0 ; i < dest_bytes ; i++) result[i] = dest[i] & mask[i];
  sqlite3_result_blob(pContext, result, dest_bytes, SQLITE_TRANSIENT);
  sqlite3_free(result);
}


int main()
{
  sqlite3* db;
  sqlite3_open(":memory:", &db);
  sqlite3_create_function(db, "bitand", 2, SQLITE_UTF8, 0, bitwise_and, 0,
0);

  char* errmsg;
  char* zSql = "select bitand(x'01',x'0101');";
  int rc = sqlite3_exec(db, zSql, 0, 0, &errmsg);
  printf("%s\n", errmsg);

  sqlite3_stmt* pStmt;
  sqlite3_prepare(db,zSql, -1, &pStmt, 0);
  printf("%s\n", sqlite3_errmsg(db));

  sqlite3_step(pStmt);
  printf("%s\n", sqlite3_errmsg(db));

  sqlite3_finalize(pStmt);

  sqlite3_close(db);

}


On Fri, Jul 4, 2008 at 4:49 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Alex Katebi wrote:
> > How about this one?
>
> Still no attachment.  It is a mime message with two parts.  The first is
>  your message and the second is a mailing list information trailer.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIboznmOOfHg372QQRAr6ZAJ9cEfod2s7rOwzg9cKXXxyJFxfymACfW6OB
> NLZdqmlmDRWb6IMXvaoOgds=
> =PaSo
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Alex Katebi wrote:
> How about this one?

Still no attachment.  It is a mime message with two parts.  The first is
 your message and the second is a mailing list information trailer.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIboznmOOfHg372QQRAr6ZAJ9cEfod2s7rOwzg9cKXXxyJFxfymACfW6OB
NLZdqmlmDRWb6IMXvaoOgds=
=PaSo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug?

[Alex Katebi]

How about this one?

On Thu, Jul 3, 2008 at 9:39 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Alex Katebi wrote:
> > sqlite3_errmsg(db) does not provide the correct error message but
> > sqlite_exec(...) does.
>
> The message gets cleared on various calls so you'll want to get it as
> early as possible after knowing there is an error.  For example before
> 3.5.9 sqlite3_clear_bindings used to clear the message.
>
> > I have attached my test code.
>
> It isn't in the message sent to list members :-)
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIbX9JmOOfHg372QQRAnT+AJ9+y5+H+4w8R3SG8nG4vOnZTPb/9gCfWfSA
> kmDrsFz0u516u1b+QF7pDHA=
> =EJN7
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Alex Katebi wrote:
> sqlite3_errmsg(db) does not provide the correct error message but
> sqlite_exec(...) does.

The message gets cleared on various calls so you'll want to get it as
early as possible after knowing there is an error.  For example before
3.5.9 sqlite3_clear_bindings used to clear the message.

> I have attached my test code. 

It isn't in the message sent to list members :-)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIbX9JmOOfHg372QQRAnT+AJ9+y5+H+4w8R3SG8nG4vOnZTPb/9gCfWfSA
kmDrsFz0u516u1b+QF7pDHA=
=EJN7
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a bug?

[Alex Katebi]

sqlite3_errmsg(db) does not provide the correct error message but
sqlite_exec(...) does.
I have attached my test code. You can compile it by doing below:

gcc x.c -lsqlite3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] is this a bug?

[Jay]

I type very fast and my fingers added the semicolon on the end
because they're used to doing it:

sqlite> .dump people_event;
BEGIN TRANSACTION;
COMMIT;

sqlite> .dump people_event
BEGIN TRANSACTION;
CREATE TABLE People_Event
  (
PeopleINTEGER NOT NULL,
Event INTEGER NOT NULL
  );
INSERT INTO "People_Event" VALUES(1, 2);
INSERT INTO "People_Event" VALUES(2, 3);
COMMIT;




__ 
Do you Yahoo!? 
Yahoo! Mail - 250MB free storage. Do more. Manage less. 
http://info.mail.yahoo.com/mail_250