Skip to Content
0
Former Member
Nov 06, 2007 at 02:04 PM

"System error: Move error" on comparing DATE rows with literal timestamps

11 Views

say, I have a table consisting of a DATE and a TIMESTAMP column:

  create table test (
  d date,
  ts timestamp
  )

now the problem:

when I try to retrieve values using a statement like this (using MaxDB's SQL Studio or via JDBC):

  select * from test
  where  d >= {ts '2007-06-18 12:19:45'}

it produces the following error:

General error;-9111 POS(36) System error: Move error

So, I am not able to compare DATE columns with timestamp values?

In contrast, the following combinations all work flawlessly:

  select * from test
  where  d >= {ts '2007-06-18 12:19:45'}

  select * from test
  where ts >= {d '2007-06-18'}

  select * from test
  where  ts >= {ts '2007-06-18 12:19:45'}

Thus, the opposite direction (comparing a TIMESTAMP column to a literal date value) apparently poses no problem to the MaxDB database engine.

Unfortunately, I cannot just resort to "truncating" the timestamp values and using the {d ...}-Syntax, because I am using Apache's Torque object-relational mapper which generates the statement like this and feeds the JDBC API with it. Anyway, I deem this a bug in MaxDB, especially with respect to the quite obscure error message.

I can reproduce this issue on both MaxDB 7.5 and 7.6 server, using client software (SQL Studio), version 7.5 and 7.6 as well.

Does anybody know this problem?

TIA,

Alex