Re: [sqlite] WHERE clause doesn't seem to work right
On Mar 13, 2009, at 10:10 PM, Fitter Man wrote:
>
> @John: Is that documented anywhere? I ask because there are some
> cases I'd
> like to understand better without going through a lot of testing to
> determine how it works. I'm inferring from your example the rule is
> all
> numerics come first, with integer and floats interleaved according
> to their
> numeric sequence, and the remaining values would be treated as
> strings and
> sorted accordingly. (Null values get stuck somewhere: I think MySQL
> puts
> them at the beginning, but again I'll have to fiddle with this to
> figure it
> out, hence my request for a document reference.) Thanks for the prompt
> reply.
Everything you need is in the one you already read:
http://www.sqlite.org/datatype3.html
Section 3 describes how comparisons are made. Blobs are larger than
strings which are larger than numbers which are larger than NULL. No
exceptions.
Try this:
sqlite> SELECT 5 > '4';
It returns 0, because the string '4' is always greater than the number
5.
However, SQLite does what it calls "applying affinity" to values in some
circumstances. "Applying affinity" is fancy term for changing a text
value
that looks like a number to a number, or changing a number to a text
field.
Affinity may be applied in two circumstances:
* When inserting a value into a table. The affinity applied (if any)
depends on the declaration of the column type. Say you do:
CREATE TABLE t1(a INTEGER, b TEXT);
INSERT INTO t1 VALUES('10', 10);
Despite what it looks like, column a now contains a number and
column
b contains a text field. If I tried to insert a text value like
'abc'
into column a, the attempt to convert to a number would fail and
the
value stored would be a text field.
* When you compare something to a column value, the affinity of the
column
may be applied to the "something" before the comparison takes
place. Say
you now do:
SELECT a>'9' FROM t1
Because the literal text '9' is being compared to a column with
numeric
affinity, it is transformed to a number before the comparison
takes place.
10 is greater than 9 so this query returns "1". Exactly when
affinities
are applied is described in section 3 of the datatype3.html
document.
Check out the examples in datatype3.html. They should make everything
clear.
Dan.
> --
> View this message in context:
> http://www.nabble.com/WHERE-clause-doesn%27t-seem-to-work-right-tp22497543p22498479.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] WHERE clause doesn't seem to work right
See section 3.0 http://www.sqlite.org/datatypes.html Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fitter Man Sent: Friday, March 13, 2009 11:10 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] WHERE clause doesn't seem to work right @John: Is that documented anywhere? I ask because there are some cases I'd like to understand better without going through a lot of testing to determine how it works. I'm inferring from your example the rule is all numerics come first, with integer and floats interleaved according to their numeric sequence, and the remaining values would be treated as strings and sorted accordingly. (Null values get stuck somewhere: I think MySQL puts them at the beginning, but again I'll have to fiddle with this to figure it out, hence my request for a document reference.) Thanks for the prompt reply. -- View this message in context: http://www.nabble.com/WHERE-clause-doesn%27t-seem-to-work-right-tp22497543p22498479.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] WHERE clause doesn't seem to work right
@John: Is that documented anywhere? I ask because there are some cases I'd like to understand better without going through a lot of testing to determine how it works. I'm inferring from your example the rule is all numerics come first, with integer and floats interleaved according to their numeric sequence, and the remaining values would be treated as strings and sorted accordingly. (Null values get stuck somewhere: I think MySQL puts them at the beginning, but again I'll have to fiddle with this to figure it out, hence my request for a document reference.) Thanks for the prompt reply. -- View this message in context: http://www.nabble.com/WHERE-clause-doesn%27t-seem-to-work-right-tp22497543p22498479.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE clause doesn't seem to work right
Fitter Man wrote: > I tried something for kicks and it worked. I flipped all the column > definitions from VARCHAR(255) to INTEGER and my query, above, works > properly. I did a little reading and found this described my problem: > http://www.sqlite.org/datatypes.html . > > > Q: Is their some inverse problem now that all the columns are defined as > INTEGER? Is there anything that might go wrong with a casual query of mostly > or all string values in a column that's declared INTEGER? > > If you define the column as integer, you get a sorting order that is alphanumeric but which respects numeric sequencing: b a 300 30a 9 sorts as: 9 300 30a a b HTH John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE clause doesn't seem to work right
I tried something for kicks and it worked. I flipped all the column definitions from VARCHAR(255) to INTEGER and my query, above, works properly. I did a little reading and found this described my problem: http://www.sqlite.org/datatypes.html . Q: Is their some inverse problem now that all the columns are defined as INTEGER? Is there anything that might go wrong with a casual query of mostly or all string values in a column that's declared INTEGER? -- View this message in context: http://www.nabble.com/WHERE-clause-doesn%27t-seem-to-work-right-tp22497543p22497796.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE clause doesn't seem to work right
Fitter Man wrote: > I've just started using Sqlite and something seems wrong. I'm sure I'm making > a mistake, but can't spot it. > > I have a process that loads data from an RSS feed into a database. All the > columns are defined as VARCHAR(255) but I figure that's irrelevant here > based on what I've read about dynamic type support. > > If I do this query: > > > select name, servings from recipe where servings >= 100 > > > I get back dozens of records rather than the 3 I ought to get. As you can > see none of these records have "servings" over 100. Here are some examples: > Actually, from my own personal experience, it does matter. If you define the servings column as a varchar, then the comparison is done as alphanumeric, in which case '3' is indeed greater than '100'. If you define the servings column as integer, the comparison should be done as a numeric comparison, in which case 3 is less than 100. HTH, John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WHERE clause doesn't seem to work right
I've just started using Sqlite and something seems wrong. I'm sure I'm making a mistake, but can't spot it. I have a process that loads data from an RSS feed into a database. All the columns are defined as VARCHAR(255) but I figure that's irrelevant here based on what I've read about dynamic type support. If I do this query: select name, servings from recipe where servings >= 100 I get back dozens of records rather than the 3 I ought to get. As you can see none of these records have "servings" over 100. Here are some examples: Barbeque Beef Sandwiches|8 Green Turkey Chili|4 Thai Chicken Legs|4 Hungarian Beef Stew|6 But if I do it like this select name, servings from recipe where (1 * servings) >= 100 the right records back. Doing an EXPLAIN on the queries shows a MUL and INT operation, so I assume it's something to do with type conversions. Am I misunderstanding how the dynamic type conversion is supposed to work? I've tried both 3.4.0 and 3.6.11 with the same results. I've also tried creating a small example with three records (same column definitions, numeric values inserted as string literals) but it won't reproduce the problem. Thanks for any help you can provide. -- View this message in context: http://www.nabble.com/WHERE-clause-doesn%27t-seem-to-work-right-tp22497543p22497543.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
