Re: [Monotone-devel] [PATCH] new selectors: "earlier or equal" and ?later"

[Nathaniel Smith]

On Thu, May 12, 2005 at 08:18:48AM +0200, rghetta wrote:
> Ok, I've taken care of the tabs and rewritten the tests using RAW_MONOTONE.
> On the normdate function: without it the date comparison simply doesn't
> work.
> As an example try a db execute of
> 'SELECT id FROM revision_certs WHERE name="date" AND unbase64(value) >
> "2005-05-13T00:00:00"'
> 
> it should return no rows, instead returns all the certs on the database,
> and changing the string has no effect on the result (using <, however,
> yields no rows)
> This time I digged deeper, trying to understand why the comparison
> doesn't work.
> The problem is unbase64(): it does return (correctly IMHO) a blob and
> apparently sqlite don't allows mixing blobs and strings.  Substring
> searching works correctly, put ordering operators not.
> I guess sqlite encodes something in the blob making it "greater" than a
> string.
> Perhaps sqlite devs should made aware of this behaviour; for now you
> need a sql function to handle these comparisons.

Ah, I see.  Nice analysis.  It turns out this is all well-documented
at:
  http://www.sqlite.org/datatype3.html
As it says, "A TEXT value is less than a BLOB value"; that would explain
our problem.

It also explains why it's showing up in our case:
  "Values specified as literals as part of SQL statements are
  assigned storage class TEXT if they are enclosed by single or double
  quotes...Literals with storage class BLOB are specified using the
  X'ABCD' notation."
and
  "Values supplied using the sqlite3_bind_* APIs are assigned the
  storage class that most closely matches the native type bound (i.e.
  sqlite3_bind_blob() binds a value with storage class BLOB)."

So, either we're using the wrong sqlite3_bind_ function, or we need to
insert an X before our ''.  (With a comment noting what's going on in
either case, presumably.)

> One of such functions is normdate() of my previous patch. Another is an
> unbase64_text() identical to unbase64() but returning a text.
> While the latter has a better syntax and could be generally useful, it
> potentially confusing, since it's not obvious which unbase* function to use.
> Besides, if this behaviour is a bug of sqlite as I suspect, sooner or
> later the function will be useless.
> Thus I still favor the normdate() approach, but either one will be fine
> and attached you will find TWO patches: one with normdate(), and one
> with unbase64_text().  Choose as you wish :)

Hmm, can I choose door C? ;-)

Sorry to be such a pain :-).

-- Nathaniel

-- 
"Of course, the entire effort is to put oneself
 Outside the ordinary range
 Of what are called statistics."
  -- Stephan Spender