SEARCH FAILURE BY DATE AND HOUR
Description
Environment
It was tested with version 9.0 of iDempiere using a PostgreSQL database and on test.idempiere.org.
Attachments
is caused by
Activity
Marcos Favaretto February 16, 2022 at 2:41 PM
Thanks for your feedback
Carlos Ruiz February 16, 2022 at 2:38 PM
Thanks - I’m precisely working on this.
The issue is caused because we made postgresql native the default, it can still be disabled and use the convert layer.
I’m checking and the problem is more extended, for example when importing translations the Updated value on the _Trl table is being saved without time, because of this same problem.
Pull request coming in a few minutes.
Marcos Favaretto February 16, 2022 at 2:21 PMEdited
Carlos, the code at the picture solves the problem, but i cannot say the impact for the system.
Studing the system behavior, I notice that the setence “TO_DATE” was always converted to “TO_TIMESTAMP” on 6.2.
Carlos Ruiz February 14, 2022 at 9:18 AM
Probably caused by IDEMPIERE-4962
Marcos Favaretto February 13, 2022 at 11:41 PM
I'm sorry, I forgot to tell how to reproduce that error:
1) Create some records that has a date+time field and is present at the find window.
2) Open the find window and try to search for the records of the day you provided at the first step, making a period search, for example:
from: 2022/02/13 at 00:00:00 a.m.
to: 2022/02/13 at 12:00:00 p.m.
3) When you run the search, you will notice that the records are not found.
4) If you add one more day for the period, you will notice that the records are finally found.
Scenario
When trying to find all records of a specific date, it is necessary to complete it within a period of time, for example: “all records from 00:00:00 a.m. at the day X until 11:59:59 p.m. at the same day.” It is expected that it shows all the records referenced on that day (day X), as the date and hour provided covers the entire period.
Failure
When the search demonstrated in the scenario above is complete, the system cannot find any results. To provide all the expected results, it is necessary to add at least one day to the end of the specified search period before.
Failure cause
The failure happens from the way the search is made and handled on code. When building the WHERE clause that will run on the database, the function “TO_DATE” is used to convert data and time already filled by the user. The Image 1 is a demonstration of the query being built and produced.
The fact of using that function mentioned before makes it ignore the ‘Times’ informed into the Query. That way, the search is being executed only between the dates. Image 2 shows that same Query on another version, due to the use of the function “TO_DATE”, it executes the same search on the database.
Comparison between iDempiere 6.2 and 9.0 versions
During tests, it was noticed that the failure doesn not occur at the version 6.2. That occurs due a treatment about a generated Query, which in the older version (6.2), converts all the function occurrences “TO_DATE” to “TO_TIMESTAMP” before the SQL being executed, that makes the Times being considered and the records be founded, that does not happen at the version 9.0.