openerp-india team mailing list archive

["Hannes \(Neobis\)" <1155623@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

When searching records by a datetime field, using a date value as search
argument, the following happens:

search arguments:

[('datetime_field_1', '>=', '2013-01-01'),  ('datetime_field_1', '<',
'2013-01-31'), ]

Is rendered to SQL:

select x.id from "x"
where (x.datetime_field_1 >= '2013-01-01 00:00:00') 
AND (x.datetime_field_1 < '2013-01-31 23:59:59') 

This is done by code in expression.py (search for "00:00" in the server
branch).

There is the following code found:

                    if operator in ('>', '>='):
                        self.__exp[i][2] += ' 00:00:00'
                    elif operator in ('<', '<='):
                        self.__exp[i][2] += ' 23:59:59'

Of course, the problem is caused by the developer who searches with DATE strings instead of DATETIME strings.
OpenERP clearly wants to cast DATE values.
I think this piece of code shouldn't exist there. It should either raise an exception, or, delegate the casting to python / postgres.

Besides the casting-or-not issue, there is a real bug in the openerp-
casting.

I think it should be:

                    if operator in ('<', '>='):
                        self.__exp[i][2] += ' 00:00:00'
                    if operator in ('>', '<='):
                        self.__exp[i][2] += ' 23:59:59'

or just:

                    if operator in ('>', '<', '>=', '<='):
                        self.__exp[i][2] += ' 00:00:00'

Think about it: 
When searching for records with datetime smaller than (<) 2013-01-05 you don't expect records with datetime 2013-01-05 23:00:00, do you?
And when searching for records with datetime larger than (>) 2013-01-05 you don't expect records with datetime 2013-01-05 01:00:00, do you?

I think a lot of software cast DATE 2013-01-05 to DATETIME 2013-01-05
00:00:00.


At the same time I would like to warn you, because this is a very fundamental change which might affect various users in an unexpected way. 

I think the best solution is a step-by-step approach through various OpenERP server versions:
1st: send WARNINGS to the server log when casting takes place
2nd: make the OpenERP cast DEPRECATED
3rd: raise Exceptions (if possible only for developers)
4th: remove entire piece of code.

I hope I could help

Hannes Smit

** Affects: openobject-server
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of OpenERP
Indian Team, which is subscribed to OpenERP Server.
https://bugs.launchpad.net/bugs/1155623

Title:
  search expression datetime cast incorrect

Status in OpenERP Server:
  New

Bug description:
  When searching records by a datetime field, using a date value as
  search argument, the following happens:

  search arguments:

  [('datetime_field_1', '>=', '2013-01-01'),  ('datetime_field_1', '<',
  '2013-01-31'), ]

  Is rendered to SQL:

  select x.id from "x"
  where (x.datetime_field_1 >= '2013-01-01 00:00:00') 
  AND (x.datetime_field_1 < '2013-01-31 23:59:59') 

  This is done by code in expression.py (search for "00:00" in the
  server branch).

  There is the following code found:

                      if operator in ('>', '>='):
                          self.__exp[i][2] += ' 00:00:00'
                      elif operator in ('<', '<='):
                          self.__exp[i][2] += ' 23:59:59'

  Of course, the problem is caused by the developer who searches with DATE strings instead of DATETIME strings.
  OpenERP clearly wants to cast DATE values.
  I think this piece of code shouldn't exist there. It should either raise an exception, or, delegate the casting to python / postgres.

  Besides the casting-or-not issue, there is a real bug in the openerp-
  casting.

  I think it should be:

                      if operator in ('<', '>='):
                          self.__exp[i][2] += ' 00:00:00'
                      if operator in ('>', '<='):
                          self.__exp[i][2] += ' 23:59:59'

  or just:

                      if operator in ('>', '<', '>=', '<='):
                          self.__exp[i][2] += ' 00:00:00'

  Think about it: 
  When searching for records with datetime smaller than (<) 2013-01-05 you don't expect records with datetime 2013-01-05 23:00:00, do you?
  And when searching for records with datetime larger than (>) 2013-01-05 you don't expect records with datetime 2013-01-05 01:00:00, do you?

  I think a lot of software cast DATE 2013-01-05 to DATETIME 2013-01-05
  00:00:00.

  
  At the same time I would like to warn you, because this is a very fundamental change which might affect various users in an unexpected way. 

  I think the best solution is a step-by-step approach through various OpenERP server versions:
  1st: send WARNINGS to the server log when casting takes place
  2nd: make the OpenERP cast DEPRECATED
  3rd: raise Exceptions (if possible only for developers)
  4th: remove entire piece of code.

  I hope I could help

  Hannes Smit

To manage notifications about this bug go to:
https://bugs.launchpad.net/openobject-server/+bug/1155623/+subscriptions