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

[Former Member]

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

[holger_becker]

Hi,

this is a new bug and we will fix it with one of the next versions of MaxDB.

You can watch the proceeding in our internal bug tracking system:

http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1151609

Thank you for reporting it.

Kind regards

Holger