[postmodern-devel] Bug report: Incorrect handling of SIMPLE-DATE types in S-SQL

[Ivan Boldyrev]

This doesn't work:

,----
| (postmodern:query
|  (:select (:- :localtimestamp
|               (simple-date:encode-interval :hour 2 :minute 10))))
| 
| Database error 22007: invalid input syntax for type timestamp: "2 hours 10 minutes "
| Query: (SELECT (localtimestamp - '2 hours 10 minutes '))
`----

This does work:

,----
| (postmodern:query
|  (:select (:- :localtimestamp (:raw "interval '2 hours 10 minutes'"))))
`----

It was tested with PostgreSQL 8.0.13 and 8.2.4.

SQL-IZE methods for SIMPLE-DATE objects should return escaped strings
like "interval '2 hours 10 minutes'", not just "2 hours 10 minutes", and
second value of SQL-IZE must be NIL:

b
,----
|   (:method ((arg simple-date:date))
|       (multiple-value-bind (year month day) (simple-date:decode-date arg)
|         (format nil "date ~A"
|                 (sql-escape-string (format nil "~4,'0d-~2,'0d-~2,'0d" year month day)))))
`----

(Untested!)

Perhaps, sql-escape-string can be omitted: the output has no dangerous
characters anyway.

,----
|  (:method ((arg simple-date:date))
|     (multiple-value-bind (year month day) (simple-date:decode-date arg)
|       (format nil "date '~4,'0d-~2,'0d-~2,'0d'" year month day)))
`----

-- 
Ivan Boldyrev

Violets are red, Roses are blue. //
I'm schizophrenic, And so am I.