Re: [sqlite] Re: SQLite version 3 design question: '500'=500?

[Doug Currie]

Firebird 1.5

SQL CREATE TABLE test1(a VARCHAR(100));
SQL INSERT INTO test1 VALUES('501');
SQL INSERT INTO test1 VALUES('  502  ');
SQL SELECT * FROM test1 WHERE a=501;

A
===
501

SQL SELECT * FROM test1 WHERE a=502;

A
===
  502

SQL SELECT * FROM test1 WHERE a'502';

A
===
501
  502

SQL CREATE TABLE test2(b INTEGER);
SQL INSERT INTO test2 VALUES(503);
SQL INSERT INTO test2 VALUES(504);
SQL SELECT * FROM test2 WHERE b='503';

   B


 503

SQL SELECT * FROM test2 WHERE b'503';

   B


 504

e

Thursday, May 13, 2004, 4:42:24 AM, DRH wrote:

 George Ionescu wrote:
 
 However, wanting to test how the engine compares strings and numbers:
 
 SELECT 'match' WHERE '500' = 500;
 
 returns 'match'; also, the following statements return the same result:
 
 SELECT 'match' WHERE '500' = 500;
 SELECT 'match' WHERE '500' = 499 + 1;
 

 Who can tell me what other SQL database engines do with
 the following?

 CREATE TABLE test1(a VARCHAR(100));
 INSERT INTO test1 VALUES('501');
 INSERT INTO test1 VALUES('  502  ');
 SELECT * FROM test1 WHERE a=501;
 SELECT * FROM test1 WHERE a=502;
 SELECT * FROM test1 WHERE a'502';

 Or how about this:

 CREATE TABLE test2(b INTEGER);
 INSERT INTO test2 VALUES(503);
 INSERT INTO test2 VALUES(504);
 SELECT * FROM test2 WHERE b='503';
 SELECT * FROM test2 WHERE b'503';




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Re: SQLite version 3 design question: '500'=500?

[Peter Pistorius]

I think it should return 0.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Re: SQLite version 3 design question: '500'=500?

[Jarosaw Nozderko]

Sybase ASE 12.5.1:

CREATE TABLE test1(a VARCHAR(100))
INSERT INTO test1 VALUES('501')
INSERT INTO test1 VALUES('  502  ')
SELECT * FROM test1 WHERE a=501
SELECT * FROM test1 WHERE a=502
SELECT * FROM test1 WHERE a'502'

Result: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.

CREATE TABLE test2(b INTEGER)
INSERT INTO test2 VALUES(503)
INSERT INTO test2 VALUES(504)
SELECT * FROM test2 WHERE b='503'
SELECT * FROM test2 WHERE b'503'

Same as above. It requires explicit use of 'convert' function.

Regards,
Jarek

Jaroslaw Nozderko
GSM +48 601131870 / Kapsch (22) 6075013
[EMAIL PROTECTED]
IT/CCBS/RS - Analyst Programmer
 

 -Original Message-
 From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
 Sent: Thursday, May 13, 2004 10:42 AM
 To: [EMAIL PROTECTED]
 Subject: Re: [sqlite] Re: SQLite version 3 design question: '500'=500?
 
 
 George Ionescu wrote:
  
  However, wanting to test how the engine compares strings 
 and numbers:
  
  SELECT 'match' WHERE '500' = 500;
  
  returns 'match'; also, the following statements return the 
 same result:
  
  SELECT 'match' WHERE '500' = 500;
  SELECT 'match' WHERE '500' = 499 + 1;
  
 
 Who can tell me what other SQL database engines do with
 the following?
 
 CREATE TABLE test1(a VARCHAR(100));
 INSERT INTO test1 VALUES('501');
 INSERT INTO test1 VALUES('  502  ');
 SELECT * FROM test1 WHERE a=501;
 SELECT * FROM test1 WHERE a=502;
 SELECT * FROM test1 WHERE a'502';
 
 Or how about this:
 
 CREATE TABLE test2(b INTEGER);
 INSERT INTO test2 VALUES(503);
 INSERT INTO test2 VALUES(504);
 SELECT * FROM test2 WHERE b='503';
 SELECT * FROM test2 WHERE b'503';
 
 
 -- 
 D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
 
 
 -
 To unsubscribe, e-mail: [EMAIL PROTECTED]
 For additional commands, e-mail: [EMAIL PROTECTED]
 
 

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Re: SQLite version 3 design question: '500'=500?

[Peter Boehm]

 Who can tell me what other SQL database engines do with
 the following?

CREATE TABLE test1(a VARCHAR(100));
INSERT INTO test1 VALUES('501');
INSERT INTO test1 VALUES('  502  ');
SELECT * FROM test1 WHERE a=501;
SELECT * FROM test1 WHERE a=502;
SELECT * FROM test1 WHERE a'502';
Gupta's SQLBase:

TABLE CREATED

1 ROW INSERTED

1 ROW INSERTED

A

501
1 ROW SELECTED

A

0 ROWS SELECTED

A

501
  502
2 ROWS SELECTED

 Or how about this:

CREATE TABLE test2(b INTEGER);
INSERT INTO test2 VALUES(503);
INSERT INTO test2 VALUES(504);
SELECT * FROM test2 WHERE b='503';
SELECT * FROM test2 WHERE b'503';


Gupta's SQLBase:

TABLE CREATED

1 ROW INSERTED

1 ROW INSERTED

  B
===
503
1 ROW SELECTED

  B
===
504
1 ROW SELECTED

Peter

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]