Bug 14861 - Accession date comparison does not work in advanced search
Summary: Accession date comparison does not work in advanced search
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Barton Chittenden
QA Contact: Testopia
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-09-18 22:03 UTC by Barton Chittenden
Modified: 2017-01-12 05:56 UTC (History)
9 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:


Attachments
Bug 14861: Change acqdate to allow date ranges. (931 bytes, patch)
2015-09-30 21:16 UTC, Barton Chittenden
Details | Diff | Splinter Review
Bug 14861: Change acqdate to allow date ranges. (1.02 KB, patch)
2015-10-01 01:11 UTC, David Cook
Details | Diff | Splinter Review
Bug 14861 - [ALTERNATE] Accession date comparison does not work in advanced search (1.52 KB, patch)
2015-10-06 00:45 UTC, David Cook
Details | Diff | Splinter Review
Bug 14861 - [SIGNED OFF] Accession date comparison does not work in advanced search (1.59 KB, patch)
2015-10-08 13:14 UTC, Barton Chittenden
Details | Diff | Splinter Review
Bug 14861 - [SIGNED OFF] Accession date comparison does not work in advanced search (1.66 KB, patch)
2015-10-15 09:34 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Barton Chittenden 2015-09-18 22:03:25 UTC
According to the documentation here:

http://manual.koha-community.org/3.18/en/searchguide.html#searchguide-syntax

A CCL search may be constructed as follows:

(paraphrasing)

  Index(,search type)=Search String

The documentation uses the following as a date search:

  yr,st-numeric=>2000

Following the same pattern, I used the following to search for a normalize date:

  acqdate,st-date-normalized=>2015-08-01

This returned

  Results of search for 'acqdate,st-date-normalized: >2015-08-01'
  No results found!



To re-create:

Add an item with the acquisition date set to 2015-08-01 (and let the fast indexer pick it up).

Put the string

  acqdate,st-date-normalized=>2015-08-01

In the 'search the catalog' search box and run the serch. You will see

  Results of search for 'acqdate,st-date-normalized: >2015-08-01'
  No results found!
Comment 1 David Cook 2015-09-21 02:44:06 UTC
Hey Barton, I can explain the cause (and solution).

Check out "ccl.properties":

Date-of-publication 1=31 r=r
yr Date-of-publication
pubdate Date-of-publication

Date-of-acquisition 1=32
acqdate Date-of-acquisition

st-numeric  4=109 r=o
st-name-normalized  4=101

If you look at the Zebra docs, you'll see that r=o and r=r are special attributes for doing range searches (http://www.indexdata.com/yaz/doc/tools.html). 

I wrote about this last year on http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12897 but I didn't go any further with it as it would be nice to make some more logical changes across the board... but feel free to make a patch to fix this particular case.

--

Locally, I've done the following to solve this problem:

Date-of-acquisition 1=Date-of-acquisition r=o
acqdate Date-of-acquisition
Comment 2 Barton Chittenden 2015-09-28 15:36:21 UTC
(In reply to David Cook from comment #1)
> Hey Barton, I can explain the cause (and solution).
> 
> Check out "ccl.properties":
> 
> Date-of-publication 1=31 r=r
> yr Date-of-publication
> pubdate Date-of-publication
> 
> Date-of-acquisition 1=32
> acqdate Date-of-acquisition
> 
> st-numeric  4=109 r=o
> st-name-normalized  4=101
> 
> If you look at the Zebra docs, you'll see that r=o and r=r are special
> attributes for doing range searches
> (http://www.indexdata.com/yaz/doc/tools.html). 
> 
> I wrote about this last year on
> http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12897 but I didn't
> go any further with it as it would be nice to make some more logical changes
> across the board... but feel free to make a patch to fix this particular
> case.
> 
> --
> 
> Locally, I've done the following to solve this problem:
> 
> Date-of-acquisition 1=Date-of-acquisition r=o
> acqdate Date-of-acquisition

Ok, this is from my ccl.properties:

Date-of-acquisition 1=32
Date-of-acquisition 1=Date-of-acquisition r=o
acqdate Date-of-acquisition

Given an item where acqdate is 2011-09-07,

  search.pl?idx=acqdate%2Cst-date-normalized&q=2011-09-07

works, but querying the entire month from 2011-09-01 to 2011-09-30 does not:

  search.pl?idx=acqdate%2Cst-date-normalized&q="2011-09-01+-+2011-09-30"

Returns no results.
Comment 3 David Cook 2015-09-29 00:06:28 UTC
(In reply to Barton Chittenden from comment #2)
> Ok, this is from my ccl.properties:
> 
> Date-of-acquisition 1=32
> Date-of-acquisition 1=Date-of-acquisition r=o
> acqdate Date-of-acquisition
> 
> Given an item where acqdate is 2011-09-07,
> 
>   search.pl?idx=acqdate%2Cst-date-normalized&q=2011-09-07
> 
> works, but querying the entire month from 2011-09-01 to 2011-09-30 does not:
> 
>   search.pl?idx=acqdate%2Cst-date-normalized&q="2011-09-01+-+2011-09-30"
> 
> Returns no results.

Barton, are you using the advanced search or the regular search box? It looks like the advanced search to me.

The problem I see is that your query is being wrapped in double quotation marks.

No results:
/cgi-bin/koha/catalogue/search.pl?idx=acqdate%2Cst-date-normalized&q="2010-01-01+-+2015-01-01"&idx=kw&idx=kw&sort_by=relevance

Lots of results:
/cgi-bin/koha/catalogue/search.pl?idx=acqdate%2Cst-date-normalized&q=2010-01-01+-+2015-01-01&idx=kw&idx=kw&sort_by=relevance

Here are two examples of it working in the staff client and opac when using free-form CCL queries:
/cgi-bin/koha/catalogue/search.pl?q=acqdate%2Cst-date-normalized%3A2010-01-01+-+2015-01-01
/cgi-bin/koha/opac-search.pl?q=acqdate%2Cst-date-normalized%3A2010-01-01+-+2015-01-01&branch_group_limit=

--

I've seen this sort of problem before on Bywater Koha sites...

I just tried using the Advanced Search in Koha master and I got the following URL, which matches my working URL above.
/cgi-bin/koha/catalogue/search.pl?idx=acqdate%2Cst-date-normalized&q=2010-01-01+-+2015-01-01&idx=kw&idx=kw&sort_by=relevance

Are you trying this query on the Koha used by Bywater or the Koha master branch? I'm guessing Bywater has a local patch which quotes values and that's creating the problem.

I'm going to poke around a bit more with yaz-client to see if I can find out more...
Comment 4 David Cook 2015-09-29 00:15:19 UTC
Z> f acqdate,st-date-normalized=2010-01-01 - 2015-01-01
Sent searchRequest.
Received SearchResponse.
Search was a success.
Number of hits: 9644, setno 2
SearchResult-1: term=2010-01-01 cnt=11758, term=2015-01-01 cnt=36059
records returned: 0
Elapsed: 0.179575

Z> f acqdate,st-date-normalized="2010-01-01 - 2015-01-01"
Sent searchRequest.
Received SearchResponse.
Search was a success.
Number of hits: 0, setno 3
SearchResult-1: term=2010-01-01 - 2015-01-01 cnt=0
records returned: 0
Elapsed: 0.000754

It's because "2010-01-01 - 2015-01-01" is being treated as a single term when it's supposed to be a range according to the CCL grammar. 

http://www.indexdata.com/yaz/doc/tools.html#ccl.syntax

Elements ::= '(' CCL-Find ')'
                | Set
                | Terms
                | Qualifiers Relation Terms
                | Qualifiers Relation '(' CCL-Find ')'
                | Qualifiers '=' string '-' string
      -- Elements is either a recursive definition, a result set reference, a
      -- list of terms, qualifiers followed by terms, qualifiers followed
      -- by a recursive definition or qualifiers in a range (lower - upper).

A syntactically correct query would be the following:

acqdate,st-date-normalized=2010-01-01 - 2015-01-01

Because it's: qualifiers '=' string '-' string
Comment 5 David Cook 2015-09-29 00:50:03 UTC
You might notice different tokenizing behaviours depending on your searches, and that's down to default.idx and using different "charmap" files for different index types (ie registers). 

We've already observed that "acqdate,st-date-normalized" with double quotes uses the value as a single token.

Z> f acqdate,st-date-normalized="2010-01-01 - 2015-01-01"
Sent searchRequest.
Received SearchResponse.
Search was a success.
Number of hits: 0, setno 38
SearchResult-1: term=2010-01-01 - 2015-01-01 cnt=0
records returned: 0
Elapsed: 0.000856

Z> f acqdate,st-date-normalized=2010-01-01 - 2015-01-01
Sent searchRequest.
Received SearchResponse.
Search was a success.
Number of hits: 9644, setno 39
SearchResult-1: term=2010-01-01 cnt=11758, term=2015-01-01 cnt=36059
records returned: 0
Elapsed: 0.029800

However, "pubdate,st-numeric" tokenizes everything when using double quotes.

Z> f pubdate,st-numeric="2010 - 2015"
Sent searchRequest.
Received SearchResponse.
Search was a success.
Number of hits: 0, setno 36
SearchResult-1: term=2010 cnt=1059, term=- cnt=167, term=2015 cnt=730
records returned: 0
Elapsed: 0.033589

Z> f pubdate,st-numeric=2010 - 2015
Sent searchRequest.
Received SearchResponse.
Search was a success.
Number of hits: 5066, setno 37
SearchResult-1: term=2010 cnt=5068, term=2015 cnt=24660
records returned: 0
Elapsed: 0.093974

--

In the first two searches, we're using the date register:

# Date
# Used if structure=date (@attr 4=5)
index d
completeness 0
charmap @

Whereas with the second one, we're using the numeric register:

# Numeric index
# Used if structure=numeric (@attr 4=109)
index n
completeness 0
charmap numeric.chr

As per the docs at http://www.indexdata.com/zebra/doc/character-map-files.html, "charmap @" means no normalization and no tokenization.

This isn't just specific to the date register. It's also the case for "y" and "0".
Comment 6 David Cook 2015-09-29 00:50:56 UTC
Just noticed that you had two different URLs...

search.pl?idx=acqdate%2Cst-date-normalized&q=2011-09-07
search.pl?idx=acqdate%2Cst-date-normalized&q="2011-09-01+-+2011-09-30"

Either you added the double quotes or they're inserted by your Koha?
Comment 7 Barton Chittenden 2015-09-29 11:56:45 UTC
(In reply to David Cook from comment #3)
> (In reply to Barton Chittenden from comment #2)
> > Ok, this is from my ccl.properties:
> > 
> > Date-of-acquisition 1=32
> > Date-of-acquisition 1=Date-of-acquisition r=o
> > acqdate Date-of-acquisition
> > 
> > Given an item where acqdate is 2011-09-07,
> > 
> >   search.pl?idx=acqdate%2Cst-date-normalized&q=2011-09-07
> > 
> > works, but querying the entire month from 2011-09-01 to 2011-09-30 does not:
> > 
> >   search.pl?idx=acqdate%2Cst-date-normalized&q="2011-09-01+-+2011-09-30"
> > 
> > Returns no results.
> 
> Barton, are you using the advanced search or the regular search box? It
> looks like the advanced search to me.
> 
> The problem I see is that your query is being wrapped in double quotation
> marks.
> 
> No results:
> /cgi-bin/koha/catalogue/search.pl?idx=acqdate%2Cst-date-normalized&q="2010-
> 01-01+-+2015-01-01"&idx=kw&idx=kw&sort_by=relevance
> 
> Lots of results:
> /cgi-bin/koha/catalogue/search.pl?idx=acqdate%2Cst-date-normalized&q=2010-01-
> 01+-+2015-01-01&idx=kw&idx=kw&sort_by=relevance
> 
> Here are two examples of it working in the staff client and opac when using
> free-form CCL queries:
> /cgi-bin/koha/catalogue/search.pl?q=acqdate%2Cst-date-normalized%3A2010-01-
> 01+-+2015-01-01
> /cgi-bin/koha/opac-search.pl?q=acqdate%2Cst-date-normalized%3A2010-01-01+-
> +2015-01-01&branch_group_limit=
> 
> --
> 
> I've seen this sort of problem before on Bywater Koha sites...
> 
> I just tried using the Advanced Search in Koha master and I got the
> following URL, which matches my working URL above.
> /cgi-bin/koha/catalogue/search.pl?idx=acqdate%2Cst-date-normalized&q=2010-01-
> 01+-+2015-01-01&idx=kw&idx=kw&sort_by=relevance
> 
> Are you trying this query on the Koha used by Bywater or the Koha master
> branch? I'm guessing Bywater has a local patch which quotes values and
> that's creating the problem.
> 
> I'm going to poke around a bit more with yaz-client to see if I can find out
> more...

Actually, I put the quotes in the URL by hand. When I search using the advanced search screen, the CCL query

acqdate,st-date-normalized=2010-01-01 - 2015-01-01

Is parsed to

   search.pl?idx=kw&q=acqdate%2Cst-date-normalized%3D2010-01-01+-+2015-01-01&op=and&idx=kw&op=and&idx=kw&sort_by=relevance

And 

acqdate,st-date-normalized="2010-01-01 - 2015-01-01"

becomes

   search.pl?idx=kw&q=acqdate%2Cst-date-normalized%3D%222010-01-01+-+2015-01-01%22&op=and&idx=kw&op=and&idx=kw&sort_by=relevance

I was essentially trying to short-circuit the parser by composing the URLs by hand.

The quotes in the URL are a red herring anyway:

Neither

  search.pl?idx=acqdate%2Cst-date-normalized&q="2011-09-01+-+2011-09-30"

nor

  search.pl?idx=acqdate%2Cst-date-normalized&q=2011-09-01+-+2011-09-30

return results.

I'm going to try applying this patch to master to see if I get different results.
Comment 8 Barton Chittenden 2015-09-29 13:37:47 UTC
I just checked the virtual machine that I have this running on -- it *is* running on master -- version 3.21.00.028.

I've only made a single change against that:

diff --git a/etc/zebradb/ccl.properties b/etc/zebradb/ccl.properties
index 93ae8ec..fdd8eec 100644
--- a/etc/zebradb/ccl.properties
+++ b/etc/zebradb/ccl.properties
@@ -344,6 +344,7 @@ pubdate Date-of-publication
 #Date-acquisition       32  The date when a document was    541$d
 #                           acquired.
 Date-of-acquisition 1=32
+Date-of-acquisition 1=Date-of-acquisition r=o
 acqdate Date-of-acquisition
 
 #Date/time added to   1011  The date and time that a        008/00-05
Comment 9 David Cook 2015-09-30 05:03:30 UTC
(In reply to Barton Chittenden from comment #7)
> The quotes in the URL are a red herring anyway:
> 

I can assure you that they're not. The quotes are a problem too.

> Neither
> 
>   search.pl?idx=acqdate%2Cst-date-normalized&q="2011-09-01+-+2011-09-30"
> 
> nor
> 
>   search.pl?idx=acqdate%2Cst-date-normalized&q=2011-09-01+-+2011-09-30
> 
> return results.
> 
> I'm going to try applying this patch to master to see if I get different
> results.

Well, that's rather strange. It's definitely working on a number of 3.14 installs.

Hmm... you're right... it's not working in master. It seemed to be working when I had an acqdate with 2011-09-01, but then I changed it to 2011-09-02 in the item, and now it's clearly not working.

The query works in yaz-client, so it's clear that Koha is doing something naughty.

*looks at the logs*

Holy... Barton, I think you've uncovered a much larger bug.

Let me guess... you're not using the first box in the advanced search, are you?

It works for me when I use the first box as "Acquisition date (yyyy-mm-dd)", but if I leave the first two boxes as "Keyword" and I use the third box as "Acquisition date (yyyy-mm-dd)", it won't work. 

Check your logs to see what's actually coming out of buildQuery.


--

Also, a note on your first comment:

Results of search for 'acqdate,st-date-normalized: >2015-08-01'
No results found!

If you check your logs, you'll see that it says the following:

WARNING: query problem with acqdate,st-date-normalized=> 2011-08-01  ZOOM error 10014 "CCL parsing error" (addinfo: "Bad relation") from diag-set 'ZOOM'

While the documentation uses "yr,st-numeric=>2000", that's actually a bad example as it produces the same error:

WARNING: query problem with yr,st-numeric=>2000 ZOOM error 10014 "CCL parsing error" (addinfo: "Bad relation") from diag-set 'ZOOM'

I keep meaning to re-write the search documentation for Koha... it's really not very accurate...

You know what would work in Koha?

ccl=acqdate,st-date-normalized > 2011-08-30

Or in yaz-client:
acqdate,st-date-normalized > 2011-08-30


The relation doesn't work properly in Koha, because we're not parsing the query properly. "buildQuery" is a nightmare which butchers queries and turns them into pseudo-CCL.
Comment 10 David Cook 2015-09-30 05:04:37 UTC
Actually, the problems I'm encountering with the advanced search are different based on the URLs you're showing me... give me a few more minutes...
Comment 11 David Cook 2015-09-30 05:08:02 UTC
(In reply to Barton Chittenden from comment #8)
> I just checked the virtual machine that I have this running on -- it *is*
> running on master -- version 3.21.00.028.
> 
> I've only made a single change against that:
> 
> diff --git a/etc/zebradb/ccl.properties b/etc/zebradb/ccl.properties
> index 93ae8ec..fdd8eec 100644
> --- a/etc/zebradb/ccl.properties
> +++ b/etc/zebradb/ccl.properties
> @@ -344,6 +344,7 @@ pubdate Date-of-publication
>  #Date-acquisition       32  The date when a document was    541$d
>  #                           acquired.
>  Date-of-acquisition 1=32
> +Date-of-acquisition 1=Date-of-acquisition r=o
>  acqdate Date-of-acquisition
>  
>  #Date/time added to   1011  The date and time that a        008/00-05

Looking at this again... that doesn't look right.

You're defining the "Date-of-acquisition" alias twice.

It should be:

Date-of-acquisition 1=Date-of-acquisition r=o
acqdate Date-of-acquisition

Not:

Date-of-acquisition 1=32
Date-of-acquisition 1=Date-of-acquisition r=o
acqdate Date-of-acquisition

That said, I just did a test and it seems like it doesn't matter... it appears that the second alias definition overrides the first.
Comment 12 David Cook 2015-09-30 05:17:01 UTC
Sorry for all the comments!

I definitely have this working in master, when I use the following URL:

search.pl?idx=acqdate%2Cst-date-normalized&q=2011-09-01+-+2011-09-30

I didn't need to restart Zebra or re-index either to make it work. I just change the ccl.properties file.

Here's the entry for the working query in my logs:

[Wed Sep 30 15:08:25 2015] search.pl: QUERY:acqdate,st-date-normalized=2011-09-01 - 2011-09-30

Another reason it might not be working... are you sure that you actually have anything indexed into acqdate? 

I suppose it must have something in it if the following URL works:

search.pl?idx=acqdate%2Cst-date-normalized&q=2011-09-07

--

Let's see... remaining guesses... perhaps you're not changing the correct ccl.properties file.

My advice would be to do the following:

1) Check your logs to see what buildQuery is producing
2) Try your query in yaz-client (following the advice at http://wiki.koha-community.org/wiki/Troubleshooting_Zebra#Manually_searching_with_yaz-client)

--

I assure you that this should work. I think it's just a configuration problem on your end.

If you're using a Gitified Koha, you'll have problems, because Gitified Koha doesn't handle Zebra changes I believe. If you're using a Gitified Koha... you would need to change /etc/koha/zebradb/ccl.properties because it won't be using the ccl.properties within your Git since it's not installed anywhere unless you've changed the koha-conf.xml for that instance.

Sorry again for all the words. I don't use Gitify or Debian for Koha, so I do things a bit differently, but the Zebra configuration works.
Comment 13 David Cook 2015-09-30 06:21:18 UTC
Just fired up my old Debian VM and I'm noticing something mighty peculiar...

[Wed Sep 30 15:34:26 2015] [error] [client ::1] [Wed Sep 30 15:34:26 2015] search.pl: QUERY:2014-03-24 - 2014-03-27  at /home/david/koha/C4/Search.pm line 1609., referer: http://localhost:2051/cgi-bin/koha/catalogue/search.pl
[Wed Sep 30 15:34:26 2015] [error] [client ::1] [Wed Sep 30 15:34:26 2015] search.pl: QUERY CGI:idx=acqdate%2Cst-date-normalized&q=2014-03-24%20-%202014-03-27 at /home/david/koha/C4/Search.pm line 1610., referer: http://localhost:2051/cgi-bin/koha/catalogue/search.pl
[Wed Sep 30 15:34:26 2015] [error] [client ::1] [Wed Sep 30 15:34:26 2015] search.pl: QUERY DESC:acqdate,st-date-normalized: 2014-03-24 - 2014-03-27 at /home/david/koha/C4/Search.pm line 1611., referer: http://localhost:2051/cgi-bin/koha/catalogue/search.pl

I also wound up at the following URL:
search.pl?idx=acqdate&q=2014-03-24+-+2014-03-27&idx=kw&idx=kw&sort_by=relevance

Sure enough... it's not working even with the ccl.properties modification. However, I am able to get it to work in yaz-client, so I'm thinking it's something weird in Koha...

That's an old verison of Koha though... I'll try master...

By the way, I had to add "SetEnv DEBUG 1" in order to get any of this in my logs...

--

I'm trying to update my Git, but I have 1.5 years worth of commits to download, so it's taking a while...
Comment 14 David Cook 2015-09-30 07:32:10 UTC
(In reply to Barton Chittenden from comment #8)
> I just checked the virtual machine that I have this running on -- it *is*
> running on master -- version 3.21.00.028.
> 
> I've only made a single change against that:
> 
> diff --git a/etc/zebradb/ccl.properties b/etc/zebradb/ccl.properties
> index 93ae8ec..fdd8eec 100644
> --- a/etc/zebradb/ccl.properties
> +++ b/etc/zebradb/ccl.properties
> @@ -344,6 +344,7 @@ pubdate Date-of-publication
>  #Date-acquisition       32  The date when a document was    541$d
>  #                           acquired.
>  Date-of-acquisition 1=32
> +Date-of-acquisition 1=Date-of-acquisition r=o
>  acqdate Date-of-acquisition
>  
>  #Date/time added to   1011  The date and time that a        008/00-05

Ok, since I'm insatiably curious, I spun up a Debian Wheezy virtual machine to run a Gitified Koha.

It's on version 3.21.00.030.

In /etc/koha/zebradb/ccl.properties I have the following entry:

Date-of-acquisition 1=32 r=o
acqdate Date-of-acquisition

NOTE: I added the r=o

Here's the URL I'm getting:

http://localhost:2051/cgi-bin/koha/catalogue/search.pl?idx=acqdate%2Cst-date-normalized&q=2014-03-24+-+2014-03-27&idx=kw&idx=kw&sort_by=relevance

I have two bib records each with an item with a Date Acquired within that date range, and I'm getting search results that include both those bib records.


--

Now that I look at your original post again... editing "etc/zebradb/ccl.properties" in the Git directory won't do anything. That file doesn't get used at all. If you're on Debian and you're using a Gitified install, you want "/etc/koha/zebradb/ccl.properties". It's the configuration file used by the packages, but that's what a Gitified Koha relies upon.

If you dont' want to modify that, you're going to have to do a manual install of Koha using the "single" option and then do a "make upgrade" every time you make changes to "etc/zebradb/ccl.properties" in your Git.

Anyway, if you change the "/etc/koha/zebradb/ccl.properties" file, I bet it'll work for you.
Comment 15 Barton Chittenden 2015-09-30 21:16:55 UTC Comment hidden (obsolete)
Comment 16 Barton Chittenden 2015-09-30 21:35:15 UTC
(In reply to Barton Chittenden from comment #15)
> Created attachment 42980 [details] [review] [review]
> Bug 14861: Change acqdate to allow date ranges.
> 
> Use 'r=o' to in ccl.properties enable acqudate date range searches
> of the form
> 
> YYYY-MM-DD - yyyy-mm-dd

Test plan

Prerequisites: Make sure that you have an item with a valid dateaccessioned, and that the bib is indexed in zebra. For the purposes of explanation, I'm going to use the date '2011-09-07'

1) Go to advanced search in the staff client and choose 'Acquisition date (yyyy-mm-dd)'
2) Enter 2011-09-07 (or the date of your choice).
3) Click the search button - you should get your item in the search results.
4) Return to the advanced search screen and select Acquisition date again.
5) Enter a start and end date in the text field separated by ' - ', e.g.

2011-09-01 - 2011-09-30

6) Click the search button -- this will return no results.
7) Apply the patch and copy etc/zebradb/ccl.properties to whatever directory is specified by $KOHA_CONF

8) Re-try step 5 -- this should return results.
Comment 17 David Cook 2015-10-01 01:11:06 UTC
I don't think we needed to change "1=32" to "1=Date-of-acquisition", but it's a trivial difference, so no worries.

Sign off to follow...
Comment 18 David Cook 2015-10-01 01:11:27 UTC Comment hidden (obsolete)
Comment 19 Jonathan Druart 2015-10-01 08:16:35 UTC
I am not familiar with ccl.properties, but there is a
  st-numeric  4=109 r=o

Does
- st-date-normalized  4=5
+ st-date-normalized  4=5 r=o
could do the trick for all date indexes?
Comment 20 David Cook 2015-10-01 23:22:42 UTC
(In reply to Jonathan Druart from comment #19)
> I am not familiar with ccl.properties, but there is a
>   st-numeric  4=109 r=o
> 
> Does
> - st-date-normalized  4=5
> + st-date-normalized  4=5 r=o
> could do the trick for all date indexes?

Yes! That is probably a better idea! I was suggesting on bug 12897 that we use ranges for all date indexes, and I think that would be the way to do it. 

What's the best way to move forward? Should I provide an alternative patch? Perhaps Barton can sign that one off, and Jonathan you can still QA?
Comment 21 Barton Chittenden 2015-10-02 01:43:29 UTC
(In reply to David Cook from comment #20)
> (In reply to Jonathan Druart from comment #19)
> > I am not familiar with ccl.properties, but there is a
> >   st-numeric  4=109 r=o
> > 
> > Does
> > - st-date-normalized  4=5
> > + st-date-normalized  4=5 r=o
> > could do the trick for all date indexes?
> 
> Yes! That is probably a better idea! I was suggesting on bug 12897 that we
> use ranges for all date indexes, and I think that would be the way to do it. 
> 
> What's the best way to move forward? Should I provide an alternative patch?
> Perhaps Barton can sign that one off, and Jonathan you can still QA?

That works by me.

--Barton
Comment 22 Jonathan Druart 2015-10-02 08:59:58 UTC
Yep, if it works, let's do it for all date indexes :)
Comment 23 David Cook 2015-10-06 00:40:39 UTC
(In reply to Jonathan Druart from comment #22)
> Yep, if it works, let's do it for all date indexes :)

I suppose I should clarify this a bit...

In ccl.properties, we create the following mapping:

"st-date-normalized  4=5"

This associates a CCL qualifier name with a Bib-1 structure attribute.

Internally in Zebra, the Bib-1 attribute "4=5" is mapped to the Date "d" register.
http://www.indexdata.com/zebra/doc/querymodel-zebra.html#querymodel-pqf-apt-mapping

If we search "biblio-zebra-indexdefs.xsl" for MARC21, we find that "Date-of-acquisition" is actually the only index defined in the Date "d" register. It's a little better for UNIMARC, as it also has "onloan" added to the "d" register.

What that means is that "st-date-normalized" can really only be used for "Date-of-acquisition" with MARC21/NORMARC, and "Date-of-acquisition" and "onloan" for UNIMARC.

--

Of course, there are lots of other MARC fields where we store ISO formatted dates, but we put them in the "w" register, so that they can be found using "kw,wrdl" queries it seems.

We probably under-use the various registers available. The only index using the "y" register is "pubdate" in MARC21. UNIMARC has a few more although I'm not sure whether they're used correctly as I'm not familiar with the format. The "y" register is only suitable for "YYYY" strings. The only way to access the "y" register is by using "st-year" as well.

"st-numeric" accesses the "n" register which seems to be relied upon a lot in Koha. 

--

In any case, I'll add a patch to add "r=o" to "st-date-normalized", even though it will only really affect the advanced search for acquisition date (and any CCL queries that are formed by hand). 

In the future, we could look at adding other indexes to the "d" register if necessary...
Comment 24 David Cook 2015-10-06 00:45:09 UTC Comment hidden (obsolete)
Comment 25 Barton Chittenden 2015-10-08 13:14:39 UTC Comment hidden (obsolete)
Comment 26 Jonathan Druart 2015-10-15 09:34:53 UTC
Created attachment 43420 [details] [review]
Bug 14861 - [SIGNED OFF] Accession date comparison does not work in advanced search

_Test plan_

Prerequisites: Make sure that you have an item with a valid dateaccessioned,
and that the bib is indexed in zebra.

For the purposes of explanation, I'm going to use the date '2011-09-07'

1) Go to advanced search in the staff client and choose 'Acquisition date (yyyy-mm-dd)'
2) Enter 2011-09-07 (or the date of your choice).
3) Click the search button - you should get your item in the search results.
4) Return to the advanced search screen and select Acquisition date again.
5) Enter a start and end date in the text field separated by ' - '.

For example:

2011-09-01 - 2011-09-30

6) Click the search button -- this will return no results.
7) Apply the patch and copy etc/zebradb/ccl.properties to whatever directory is specified
by the koha-conf.xml referenced by $KOHA_CONF.
8) Try the search again -- this will return the expected results

Signed-off-by: Barton Chittenden <barton@bywatersolutions.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 27 Tomás Cohen Arazi 2015-10-19 14:56:30 UTC
Patch pushed to master.

Thanks David!
Comment 28 Chris Cormack 2015-10-23 09:20:38 UTC
Pushed to 3.20.x will be in 3.20.5
Comment 29 Liz Rea 2015-11-02 21:04:54 UTC
Pushed to 3.18.x, is in 3.18.12.
Comment 30 Katrin Fischer 2016-12-27 09:46:03 UTC
Hm, tested this on a 3.22 database with the example from the test plan:

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 seems to work correctly. You can also truncate with *.

Also filed this as a new bug report: bug 17815.
Comment 31 David Cook 2017-01-12 05:56:25 UTC
(In reply to Katrin Fischer from comment #30)
> Hm, tested this on a 3.22 database with the example from the test plan:
> 
> 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 seems to work correctly. You can also truncate
> with *.
> 
> Also filed this as a new bug report: bug 17815.

I'll comment there. Sounds interesting!