Is there any DB server that can optimize the following query?

Let's say I have the table my_table(id int not null primary key, datafield varchar(100)). Query SELECT * from my_table where id = 100 performs an index seek. If I change it to

SELECT * from my_table where id+1 = 101

it scans the whole index (index scan) (at least it does it in SQL Server and Mysql). Is there any DB server which 'understands' that id +1 = 101 is the same as id = 101-1 ? I do realize that i开发者_开发技巧t's not a typical database operation, and server doesn't have to perform any math in such cases, but I wonder if it's implemented anywhere?

Thanks

UPDATE

So far I've tried SQL Server 2008 Enterprise, Mysql 5.1, 5.5. SQL Server shows clustered index seek and clustered index scan respectively. Mysql explain shows ref:const, key:primary, rows:1 and ref:null, key:null,rows: #total number of rows in the table

id +1 = 101 is the same as id = 101-1

No it isn't. What if the +1 overflows the id?

I tried this with PostgreSQL 9.0 and it does not use an index unless I create one on (id - 1).

So with the following index definition

create index idx_minus on my_table ( (id - 1) );

PostgreSQL uses an index for the query

select *
from my_table
where id - 1 = 12345

Interesting.

You can add Oracle Release 10.2.0.1.0 to your list (not able to rewrite the query).

create table t(
   id
  ,x
  ,padding
  ,primary key (id)
) as
select rownum              as id
      ,'x'                 as x
      ,lpad('x', 100, 'x') as padding
  from dual
 connect by level <= 50000;

Query 1.

select id
  from t
 where id = 100 + 1;

----------------------------------------+
| Id  | Operation         | Name        |
-----------------------------------------
|   0 | SELECT STATEMENT  |             |
|*  1 |  INDEX UNIQUE SCAN| SYS_C006659 |
-----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------    
   1 - access("ID"=101)

Query 2.

select id
  from t
 where id + 1 = 101;

--------------------------------------------
| Id  | Operation            | Name        |
--------------------------------------------
|   0 | SELECT STATEMENT     |             |
|*  1 |  INDEX FAST FULL SCAN| SYS_C006659 |
--------------------------------------------    

Predicate Information (identified by operation id):
---------------------------------------------------    
   1 - filter("ID"+1=101)

Query 3.

select x
  from t
 where id + 1 = 101;

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |
------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------    
   1 - filter("ID"+1=101)

Why not just do this instead (assuming you don't want the server to do the math for calculating the actual ID you're looking for)?

SELECT * FROM my_table WHERE id = (101 - 1)