Re: [sqlite] strange behavior of "<" and ">" operator

[Andrea Federico Grisotto]

postgres=# SELECT * from np WHERE (42>=fromId) AND (42<=toId);
fromid | toid | serviceid
+--+---
40 |   43 | 3
(1 row)



please forget it,this an error.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] strange behavior of "<" and ">" operator

[davep]

>> You will be told MySQL doesn't set the standard :-)
> the same query in postgresql have have same result of Mysql.
And nor does Postgresql.

> Mysql and postgresql have the same behavior and sqlite is different.
>
> what do you think about this?
> where I am mistaking?

You are making a mistake in thinking that MySQL and Postgresql are 'right'
and sqlite is wrong. There are no strict rules governing this and sqlite
defines quite clearly in its documentation the comparison operator rules
for fields declared as integer, or string type.

If you want numeric/integer comparison in sqlite, you need to define the
field as a numeric/integer type.

In MySQL and postgresql, they both, if the field is detected by content to
be numeric, do a numeric comparison before doing a string comparison.

sqlite has "stricter" typing rules.

Dave.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] strange behavior of "<" and ">" operator

[Andrea Federico Grisotto]

[EMAIL PROTECTED] wrote:

  fromIdVARCHAR(6),
  toId  VARCHAR(6),
  

excuse me, this was a stupid question:
*fromId* and *toId* are VARCHAR, if I use *int* the behavior is correct,
excuse me again,  but remain the difference behavior between sqlite and
Mysql.



You will be told MySQL doesn't set the standard :-)

the same query in postgresql have have same result of Mysql.


postgres=# SELECT * from np WHERE (42>=fromId) AND (42<=toId);
fromid | toid | serviceid
+--+---
40 |   43 | 3
(1 row)



Mysql and postgresql have the same behavior and sqlite is different.

what do you think about this?
where I am mistaking?

bye Andrea.

Re: [sqlite] strange behavior of "<" and ">" operator

[Mario Frasca]

Andrea Federico Grisotto wrote:


*fromId* and *toId* are VARCHAR, if I use *int* the behavior is correct,
excuse me again,  but remain the difference behavior between sqlite 
and Mysql.


just food for thought: when you're asking an ambiguous question (like in 
the case of comparing apples with potatoes) the answer is a question of 
interpretation.


since you have declared fromId and toId to be VARCHAR, it is correct to 
compare them with other VARCHAR values, like here...


SELECT * from np WHERE ('42'>=fromId) AND ('42'<=toId);

in this case you are clearing stating what you want and you get the same 
answer from both engines.


in the expression (42 >= fromId) mysql converts fromId to integer and 
performs an integer comparison, while sqlite3 converts the 42 to string 
and performs a string comparison.  as said, a question of interpretation.


hth,
MF

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] strange behavior of "<" and ">" operator

[davep]

>
>>   fromIdVARCHAR(6),
>>   toId  VARCHAR(6),
>
> excuse me, this was a stupid question:
> *fromId* and *toId* are VARCHAR, if I use *int* the behavior is correct,
> excuse me again,  but remain the difference behavior between sqlite and
> Mysql.

You will be told MySQL doesn't set the standard :-)

Dave.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] strange behavior of "<" and ">" operator

[Andrea Federico Grisotto]

  fromIdVARCHAR(6),
  toId  VARCHAR(6),


excuse me, this was a stupid question:
*fromId* and *toId* are VARCHAR, if I use *int* the behavior is correct,
excuse me again,  but remain the difference behavior between sqlite and 
Mysql.


bye Andrea.

[sqlite] strange behavior of "<" and ">" operator

[Andrea Federico Grisotto]

I have a table like this:

CREATE TABLE np (
  fromIdVARCHAR(6),
  toId  VARCHAR(6),
  serviceId INTEGER,
  PRIMARY KEY (fromId, toId)
);

and I insert these values:

INSERT INTO np VALUES (  0,  5, 2);
INSERT INTO np VALUES ( 40, 43, 3);
INSERT INTO np VALUES (440,499, 3);
INSERT INTO np VALUES (500,599, 1);

I want to list only the rows who have, for a particular value, for 
example *42*

a min bound "fromId" and max bound "toId".

for example:
if I run this query in *sqlite version 3.3.6* I obtain (two rows):


sqlite> SELECT * from np WHERE (42>=fromId) AND (42<=toId);
fromId   toId serviceId
---  ---  ---
052
40   43   3


and if I run the same query in Mysql I obtain (one row):


mysql> SELECT * from np WHERE (42>=fromId) AND (42<=toId);
++--+---+
| fromId | toId | serviceId |
++--+---+
| 40 | 43   | 3 |
++--+---+
1 row in set (0.03 sec)

this is correct because  *40 <= 42 <= 43*

I have the same result using *BETWEEN* operator.


can you help me to to explain to me this "strange" (for me)
behavior?


bye Andrea.