cannot search for "subject OR subject" in a simple search field

[phluid61]

Searching for subjects in a simple search field using merge=ANY causes an SQL error.

Here is an illustrative example:

Step 1

Add the following search form to archive/cfg/cfg.d/search.pl :

$c->{search}->{errortest} = {
    search_fields => [
        { meta_fields => [ "title", "subjects" ], id => "q" },                                                                                        
    ],
    preamble_phrase => "cgi/search:preamble",
    title_phrase => "cgi/search:title",
    citation => "result",
    page_size => 20,
    order_methods => { "byyear" => "-date/creators_name/title" },
    order_menu => [ "byyear" ],
    default_order => "byyear",
    show_zero_results => 1,
};

Since the field searches both title and subjects, we have to enter the subject code instead of choosing it from a list.

Step 2

Search for: [any of] "070402 070402"

Note: searching for [all of] the above works and finds the appropriate subject.

Step 3:

In perl_lib/EPrints/Search.pm, in sub perform_search add the line: print STDERR $self->get_conditions->describe; before generating a new cache entry.

Outcome

In the error log we see:

AND(
        =($archive.metadata_visibility,"show") ... eprint,
        =($archive.eprint_status,"archive") ... eprint,
        OR(
                OrSubQuery(
                        in_subject($archive.subjects,"mathematics") ... eprint_subjects,
                        in_subject($archive.subjects,"accountancy") ... eprint_subjects
                ),
                OrSubQuery(
                        index($archive.title,"mathematic") ... eprint__rindex,
                        index($archive.title,"accountancy") ... eprint__rindex
                )
        )
)

and the following error:

DBD::mysql::db do failed: Unknown column '140549149986120subject_ancestors.ancestors' in 'where clause' at /usr/local/eprints/perl_lib/EPrints/Database.pm line 3213.
[myeprints 2013-01-22 10:06:24] SQL ERROR (do): INSERT INTO `cache8841073`(`pos`, `eprintid`) SELECT @i:=@i+1, `eprintid` FROM (SELECT `eprint`.`eprintid` FROM `eprint` LEFT JOIN `eprint__ordervalues_en` ON `eprint`.`eprintid`=`eprint__ordervalues_en`.`eprintid`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint_subjects` AS `eprint_subjects`, `subject_ancestors` AS `140549149756928subject_ancestors` WHERE `eprint`.`eprintid`=`eprint_subjects`.`eprintid` AND `eprint_subjects`.`subjects`=`140549149756928subject_ancestors`.`subjectid` AND (`140549149756928subject_ancestors`.`ancestors` = 'mathematics' OR `140549149986120subject_ancestors`.`ancestors` = 'accountancy') UNION SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND (`eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='mathematic' OR `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='accountancy')) AS `or_140549149716736` WHERE `eprint`.`eprintid`=`or_140549149716736`.`eprintid`) AS `and_140549150014272_0` WHERE `eprint`.`eprintid`=`and_140549150014272_0`.`eprintid` AND `eprint`.`metadata_visibility` = 'show' AND `eprint`.`eprint_status` = 'archive' GROUP BY `eprint`.`eprintid`, `eprint__ordervalues_en`.`date`, `eprint__ordervalues_en`.`creators_name`, `eprint__ordervalues_en`.`title` ORDER BY `eprint__ordervalues_en`.`date` DESC, `eprint__ordervalues_en`.`creators_name` ASC, `eprint__ordervalues_en`.`title` ASC) `S`
[myeprints 2013-01-22 10:06:24] SQL ERROR (do): Unknown column '140549149986120subject_ancestors.ancestors' in 'where clause' (#1054)

The significant portion is the left-hand side of the UNION, corresponding with the first OrSubQuery:

SELECT `eprint`.`eprintid` AS `eprintid`
FROM
    `eprint`,
    `eprint_subjects` AS `eprint_subjects`,
    `subject_ancestors` AS `140549149756928subject_ancestors`
WHERE `eprint`.`eprintid`=`eprint_subjects`.`eprintid`
AND `eprint_subjects`.`subjects`=`140549149756928subject_ancestors`.`subjectid`
AND (
    `140549149756928subject_ancestors`.`ancestors` = 'mathematics' OR
    `140549149986120subject_ancestors`.`ancestors` = 'accountancy'
)

Note that the first three instances of the aliased subject_ancestors table have a different prefix from the fourth. This is a direct consequence of the change at:

http://trac.eprints.org/eprints/changeset/6251/trunk/system/perl_lib/EPrints/Search/Condition/InSubject.pm

Note: Our repository is forked from ePrints 3.2, however based on code inspection I believe the same issue would occur in trunk, if anyone ever decided to simple-search for subjects.

[timbrody]

Please confirm that this resolves your issue. The trouble with search bugs is avoiding collateral damage ... given the complexity of generating the SQL.

[phluid61]

I confirm this patch fixes the error.
Thanks!