issue 95 (Millisecond handling in Timestamp fields)

[Maxime Lévesque]

 Hi Michael, I moved the thread in squeryl-contributors,
there might indeed be another issue, my assertion of H2 handling
milliseconds correctly is only as good as my test ! ;-)

But as you suggest, there could be a problem in the record adapter as well,
I'll refine the tests in squeryl, and fix whatever I find broken in the treatment of milliseconds
and let you know the results.

I'll also test the timestamp handling for each DB with
raw JDBC, to see what are the exact precision limit for each DB.

Thanks

On Sun, Jan 16, 2011 at 4:05 PM, Michael Gottschalk <migott...@googlemail.com> wrote:

Hi Max,

thanks for reproducing it in the Squeryl test cases.
I was already starting to try it myself when I saw that you had been
faster :-)

You say that H2 handles the milliseconds correctly. However, I have
used H2 (in memory, with the following connect string:
"jdbc:h2:mem:testSquerylRecordDB;DB_CLOSE_DELAY=-1") and nevertheless
had the problem.
Then it's maybe still another problem that I had?

On 16 Jan., 20:17, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
> Michael, the test case was incomplete, I refined it to verify this :
>
>  https://github.com/max-l/Squeryl/issues/issue/95
>
> and it appears that not all DBs handle milliseconds properly,
> It remains to be seen if they require special treatment on the JDBC side, in
> which case
> the adapters could do somsthing about it.
> Some DBs work just fine like Postgres and H2, others trucate at the
> millisecond
> in either the where clause or in the resultset mapping.
> Anyhow it seem to be a Squeryl issue rather than one specific to the
> lift/squeryl/record module.
>
> Max
>

> 2011/1/16 Maxime Lévesque <maxime.leves...@gmail.com>

>
>
>
> > Hi Michael, could you look at this test case :
>

> >https://github.com/max-l/Squeryl/blob/master/src/test/scala/org/squer...

>
> > It is supposed to test all things related to Timestamp fields, so either
> > this test case is incomplete
> > or the bug is specific to the lift/record/squeryl adapter.
>
> > Thanks
>
> > On Sun, Jan 16, 2011 at 11:06 AM, Michael Gottschalk <

> > migottsch...@googlemail.com> wrote:
>
> >> Hi,
>
> >> I think I found a bug when trying to perform a select based on
> >> timestamp equality. It seems to me that milliseconds are not handled
> >> correctly.
>
> >> If I have the following code (using record type mode; "created" is a
> >> timestamp field):
>
> >> println("Company created: " + company.created.is.getTime)
> >> val company = companies.lookup(1).head
> >> val loadedCompany = from(companies)(c => where (c.created ===
> >> company.created) select(c)).head
>
> >> and I turn on query logging, I get the following result (I'm using H2
> >> database):
>
> >> Company created: Sun Jan 16 16:56:25 CET 2011
> >> Select
> >>  Company1.name as Company1_name,
> >>  Company1.id as Company1_id,
> >>  Company1.postCode as Company1_postCode,
> >>  Company1.description as Company1_description,
> >>  Company1.country as Company1_country,
> >>  Company1.created as Company1_created
> >> From
> >>  Company Company1
> >> Where
> >>  (Company1.created = ?)
> >> jdbcParams:[Sun Jan 16 16:56:25 CET 2011]
>
> >> I get an empty result set. I would assume that the company that is
> >> previously loaded should be returned.
> >> If I use ">" or ">=", the company is returned. So I think in the
> >> database, there must be some milliseconds value that is not set in the
> >> date field when the object is loaded from the database.
>
> >> Cheers,
> >> Michael
>
>

[Maxime Lévesque]

Ok, I refined the test case for millisecond handling :

https://github.com/max-l/Squeryl/blob/da64eb8b6ee84c0cf16e57dacd5f586b5b483580/src/test/scala/org/squeryl/tests/musicdb/MusicDb.scala#L565

so I'm more confident that it works (except on oracle, MsSql, and
MySql which don't support them),
so I'd be curious to see your test case that fails with H2.

On 16 jan, 21:10, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
>  Hi Michael, I moved the thread in squeryl-contributors,
> there might indeed be another issue, my assertion of H2 handling
> milliseconds correctly is only as good as my test ! ;-)
>
> But as you suggest, there could be a problem in the record adapter as well,
> I'll refine the tests in squeryl, and fix whatever I find broken in the
> treatment of milliseconds
> and let you know the results.
>
> I'll also test the timestamp handling for each DB with
> raw JDBC, to see what are the exact precision limit for each DB.
>
> Thanks
>
> On Sun, Jan 16, 2011 at 4:05 PM, Michael Gottschalk <
>

[Michael Gottschalk]

Hi Max,

I finally found the problem, it was indeed squeryl-record specific.

Saving and loading millisecond values worked without problems, as my
test cases for squeryl-record already showed.
However, queries by timestamp equality did not work. The problem was
the following implicit conversion in RecordTypeMode:

implicit def date2ScalarDate(f: MandatoryTypedField[Calendar]) =
fieldReference match {
case Some(e) => new SelectElementReference[Date](e)
(createOutMapperDateType) with DateExpression[Date] with
SquerylRecordNonNumericalExpression[Date]
case None => new ConstantExpressionNode[Date](f.is.getTime) with
BooleanExpression[Date] with SquerylRecordNonNumericalExpression[Date]
}

Date should have been Timestamp and createOutMapperTimestampType
should have been used. I suppose Time values were not taken into
account at all (not just milliseconds as I guessed).
I changed this and now my test with the timestamp query passes.

The time part worked for loading and saving, though, because in
RecordMetaDataFactory, the value type of DateTimeTypedField was
correctly defined as Timestamp already.

I will push the change to the lift master today.

Cheers,
Michael

On 17 Jan., 04:17, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
> Ok, I refined the test case for millisecond handling :
>

>  https://github.com/max-l/Squeryl/blob/da64eb8b6ee84c0cf16e57dacd5f586...