Bug 17815 - Problems with search for dateaccessioned
Summary: Problems with search for dateaccessioned
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Galen Charlton
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-12-27 09:45 UTC by Katrin Fischer
Modified: 2017-01-19 00:54 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Katrin Fischer 2016-12-27 09:45:34 UTC
Tested this as described in bug 14861:

Search from advanced search by accession date:
2011-09-01 - 2011-09-30 
9461 results

Searched with SQL: 
select distinct biblionumber from items where dateaccessioned between '2011-09-01' and '2011-09-30'
805 results

The date range search doesn't seem to work right.

The search for a single date appears to work correctly. 
It's also possible to truncate dates using *.
Comment 1 David Cook 2017-01-12 05:58:27 UTC
I don't have time to look at this one at the moment, but it sounds interesting...

Can you give us any more data?

I trust with the SQL that the dateaccessioned would be between '2011-09-01' and '2011-09-30', but what about those 9461 results? If you look at a sample of them, do they all have dateaccessioned within that range, or do they have different data?

Can you share a link to your results?
Comment 2 Barton Chittenden 2017-01-17 21:37:22 UTC
Numeric searches in Koha are possible using the syntax

    NNN-MMM

Where NNN and MMM are numeric, and there are no spaces between the numerals and the dashes... so

    1970-1980

will work as a numeric search, but I'm not sure about searching for date ranges in other formats.
Comment 3 Katrin Fischer 2017-01-17 21:53:57 UTC
> Can you give us any more data?
> 
> I trust with the SQL that the dateaccessioned would be between '2011-09-01'
> and '2011-09-30', but what about those 9461 results? If you look at a sample
> of them, do they all have dateaccessioned within that range, or do they have
> different data?

They have different data. I verified the results with SQL, the Zebra results were clearly wrong. I was not able to verify which items actually showed up in the search.

> Can you share a link to your results?

It won't help. I filed a separate bug for the dateaccessioned search not working by entering the index name in keyword search and the search option is only available from the staff side of Koha. It would be interesting tho if someone can see similar (wrong) results on a different data set.
Comment 4 Katrin Fischer 2017-01-17 21:56:38 UTC
(In reply to Barton Chittenden from comment #2)
> Numeric searches in Koha are possible using the syntax
> 
>     NNN-MMM
> 
> Where NNN and MMM are numeric, and there are no spaces between the numerals
> and the dashes... so
> 
>     1970-1980
> 
> will work as a numeric search, but I'm not sure about searching for date
> ranges in other formats.

The help on the search option states to enter a date in the format of YYYY-MM-DD and there are examples with this syntax in the test plan on bug 14861. Would be good to get to the bottom of this somehow, but not sure where to start.
Comment 5 Barton Chittenden 2017-01-18 15:38:22 UTC
(In reply to Katrin Fischer from comment #4)
> (In reply to Barton Chittenden from comment #2)
> > Numeric searches in Koha are possible using the syntax
> > 
> >     NNN-MMM
> > 
> > Where NNN and MMM are numeric, and there are no spaces between the numerals
> > and the dashes... so
> > 
> >     1970-1980
> > 
> > will work as a numeric search, but I'm not sure about searching for date
> > ranges in other formats.
> 
> The help on the search option states to enter a date in the format of
> YYYY-MM-DD and there are examples with this syntax in the test plan on bug
> 14861. Would be good to get to the bottom of this somehow, but not sure
> where to start.

The available CCL attributes are documented at http://www.indexdata.com/yaz/doc/tools.html#ccl.qualifiers in "Table 7.2. Special attribute combos". We use "r=r" for numeric searches, creating the behavior above.
Comment 6 David Cook 2017-01-19 00:41:03 UTC
(In reply to Katrin Fischer from comment #4)
> The help on the search option states to enter a date in the format of
> YYYY-MM-DD and there are examples with this syntax in the test plan on bug
> 14861. Would be good to get to the bottom of this somehow, but not sure
> where to start.

The search help is often quite incorrect. I've been meaning to update it for a while.
Comment 7 David Cook 2017-01-19 00:50:14 UTC
As for bug 14861, you'll notice the CCL qualifiers are "acqdate,st-date-normalized". That st-date-normalized gives it a structure attribute of "date".

If we follow the spec for "date", we find: 

The day, month, year and time when a
        (normalized)            transaction or event takes place.  The
                                date search term structure is as defined
                                for Generalized Time in ASN.1 (ISO 8824)
                                except that the only mandatory portion of
                                the string is the four-digit representation
                                of the year.


http://www.loc.gov/z3950/agency/bib1.html

Some Googling suggests ISO 8824 might use the following format:

Format: YYYY[MM[DD[HHMM[SS[.S[S[S[S]]]]]]]][+/-ZZZZ]^

Of course, I don't know how ICU and CHR normalization rules factor in with the "2011-09-01 - 2011-09-30" search string...
Comment 8 David Cook 2017-01-19 00:53:44 UTC
(In reply to Katrin Fischer from comment #3)
> > Can you give us any more data?
> > 
> > I trust with the SQL that the dateaccessioned would be between '2011-09-01'
> > and '2011-09-30', but what about those 9461 results? If you look at a sample
> > of them, do they all have dateaccessioned within that range, or do they have
> > different data?
> 
> They have different data. I verified the results with SQL, the Zebra results
> were clearly wrong. I was not able to verify which items actually showed up
> in the search.
> 
> > Can you share a link to your results?
> 
> It won't help. I filed a separate bug for the dateaccessioned search not
> working by entering the index name in keyword search and the search option
> is only available from the staff side of Koha. It would be interesting tho
> if someone can see similar (wrong) results on a different data set.

It would be nice to see even a small selection of data to see if anything jumps out at me, but OK.

I don't have time to work on this, but maybe Barton can make some headway?

I'd try to reproduce the situation, and then maybe use zebra::snippet to see why I'm getting matches which don't make sense for our understanding of the query. 

Could be that the staff client search template isn't using st-date-normalized, or that the query is processed differently than we expect, or a bug in YAZ/Zebra..
Comment 9 David Cook 2017-01-19 00:54:02 UTC
Oh that might be useful to know.

Katrin: What version of Zebra and YAZ are you using?