Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[Alexey Pechnikov]

Of cource you may report your bug! But I'm not sure about possibility
of the bugfix in upstream and so I speak about patch to SQLite binding
for your language.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[romtek]

Alexey, I am not clear about whether you reported my bug or yours. Do
you mind giving me a link to it so that I could see if I need to
report mine and let it go?



On Sun, Dec 25, 2011 at 11:16 AM, Alexey Pechnikov
 wrote:
> I think there are some problems with dynamic datatypes in sqlite.
> This may be redesigned in SQLite4 but not in SQLite3.
>
> P.S. Datatypes recognized differently after Shift-Insert SQLite3
> commands into Tcl shell. This is similar to problem with datatypes
> in triggers. I did report bug but for backward compability problem
> wil not be resolved by upstream. So I did write patch for myself.
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] Bug in trigger: when comparing a value of an int column to a quoted value

[Alexey Pechnikov]

I think there are some problems with dynamic datatypes in sqlite.
This may be redesigned in SQLite4 but not in SQLite3.

P.S. Datatypes recognized differently after Shift-Insert SQLite3
commands into Tcl shell. This is similar to problem with datatypes
in triggers. I did report bug but for backward compability problem
wil not be resolved by upstream. So I did write patch for myself.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[romtek]

So, you also think this is a bug? I will submit it to the SQLite bug database.


On Wed, Dec 21, 2011 at 7:42 AM, Alexey Pechnikov
 wrote:
> The problem can be fixed by variables bindings patch:
> http://sqlite.mobigroup.ru/wiki?name=tclsqlite
> I think, you can do same for you lang.
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] Bug in trigger: when comparing a value of an int column to a quoted value

[Alexey Pechnikov]

The problem can be fixed by variables bindings patch:
http://sqlite.mobigroup.ru/wiki?name=tclsqlite
I think, you can do same for you lang.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[romtek]

On Mon, Dec 19, 2011 at 11:23 PM, Roger Binns  wrote:
>
>
> On 19/12/11 20:31, romtek wrote:
> > As you can see, isActive is declared as an integer in table2,
>
> > This type of thing worked for years with an older version of SQLite
> > library
>
> Are you sure?

Absolutely. I have the same DB file (same schema but different data)
on my dev server and production server. Both were using PHP 5.2.x
until several days ago, and everything was working fine on both of
them. For years! When I switched to PHP 5.3.8 on my dev server, the
trigger was no longer working, and as soon as I switched to PHP 5.3.8
on the production server, the same thing happened. The fix was to
change the comparison to an int instead of to a string in the WHEN
clause.


> > .. that is used in PHP 5.2.17 ..
>
> PHP used to use SQLite 2.  The internals of SQLite 2 were that everything
> was stored as a string where this kind of comparison would work.

Old versions -- yes, but the PDO SQLite extension I was using was
using version 3.x. PHP 5.2.17 was using exactly SQLite lib 3.3.7. So,
maybe that version also stored everything as a string?


> Anyway this shows that you definitely don't get strings and numbers being
> equal to each other:

I do in SELECT statements. Consider this working example:

CREATE TABLE [table3] (
  [name] VARCHAR(20),
  [age] INTEGER);

The following two queries produce the same results in SQLite version 3.7.9:

SELECT * FROM table3 WHERE age=10;
SELECT * FROM table3 WHERE age='10';

So, comparisons in SELECT statements aren't performed the same way
they are in triggers. Triggers apparently use stricter type checking.
This has changed in some version in the 3.x line, and I am concerned
that this change was yelled about.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 19/12/11 20:31, romtek wrote:
> As you can see, isActive is declared as an integer in table2,

That declaration only does type coercion on the value stored in the
database (if appropriate).

> ... when I expect it to be activated ...

Why would you expect it to be activated?  You stored an integer and are
comparing it to a string.  They really aren't the same thing.

> This type of thing worked for years with an older version of SQLite
> library

Are you sure?

> .. that is used in PHP 5.2.17 ..

PHP used to use SQLite 2.  The internals of SQLite 2 were that everything
was stored as a string where this kind of comparison would work.

Anyway this shows that you definitely don't get strings and numbers being
equal to each other:

sqlite> select 1='1';
0
sqlite> select '1'=1;
0
sqlite> create table foo(x);
sqlite> insert into foo values(1);
sqlite> select * from foo where x='1';
sqlite> select * from foo where x=1;
1

> I've always thought that because SQLite didn't enforce data types,

It does.  It just doesn't require that the value stored in a column has
the same type for all rows.

Some of the operators will do type coercion though.  For example '+' will
convert its operands to integers and if the conversion fails treat them as
zero.  String concatenation coerces to string etc.

sqlite> select '1'+'1'+x'aa';
2
sqlite> select 3 || 4;
34

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

iEYEARECAAYFAk7wG+UACgkQmOOfHg372QQ3ywCgwr+5/I2IvpwgCsYRcq+hBJf2
NDgAoNSRYvYcioP+9fbf3DOPGVhUKERK
=9llt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[Nico Williams]

On Mon, Dec 19, 2011 at 10:31 PM, romtek  wrote:
> I've always thought that because SQLite didn't enforce data types, I could
> do what I have in the example, and this has worked! So, is this a bug in
> more recent versions of SQLite or an intended change that I am unaware of?

SQLite3 doesn't do automatic the type conversions that you expect.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[romtek]

Hi,

I would like to know if I am dealing with a bug or a backward incompatible
change that I am not aware of.

Consider this example:

CREATE TABLE [table2] (
  [id] INTEGER NOT NULL ON CONFLICT FAIL PRIMARY KEY ON CONFLICT FAIL
AUTOINCREMENT,
  [isActive] INTEGER NOT NULL ON CONFLICT FAIL DEFAULT (0));

CREATE TRIGGER [update_table1]
AFTER INSERT
ON [table2]
FOR EACH ROW
WHEN new.isActive='1'
BEGIN
INSERT INTO table1 (name) VALUES("dog");
END;

As you can see, isActive is declared as an integer in table2, and if I
specify the comparison in the WHEN clause of the trigger as
new.isActive='1' (with the quote characters), the trigger doesn't get
activated when I expect it to be activated (when I insert a record into the
table with 1 as a value in column isActive).

This type of thing worked for years with an older version of SQLite library
(specifically, 3.3.7, and possible older, that is used in PHP 5.2.17), but
doesn't work with version 3.7.7.1 (and possible some earlier versions too)
or newer.

I've always thought that because SQLite didn't enforce data types, I could
do what I have in the example, and this has worked! So, is this a bug in
more recent versions of SQLite or an intended change that I am unaware of?

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