View source for POD/Bugzilla::Search

This is totally rewritten Bugzilla4Intranet search engine.

== NAME ==

This is totally rewritten Bugzilla4Intranet search engine.

Original Bugzilla::Search was ugly and stupid. It contained a lot of legacy code and often generated queries very complex for the DBMS, which leads to awful performance on big databases.

Yet in Bugzilla 4.0, although we could expect the authors to refactor it, they've just decomposed overlong subroutines into small ones.

So in Bugzilla4Intranet, I (Vitaliy Filippov) have rewritten it totally.

License is dual-license GPL 3.0+ or MPL 1.1+ (&quot;+&quot; means &quot;or any later version&quot;), to be compatible with both GPL and Bugzilla code.

Most of the functionality remains unchanged, but the internals are totally different, as well as the query performance (tested on MySQL) :)

== BOOLEAN CHARTS ==

A boolean chart is a way of representing the terms in a logical expression. Bugzilla builds SQL queries depending on how you enter terms into the boolean chart. Boolean charts are represented in urls as three-tuples of (chart, row, column) = (field, operator, value). The query form (query.cgi) may contain an arbitrary number of boolean charts where each chart represents a condition in SQL query.

The expressions represented by columns are ORed together. The expressions represented by rows are ANDed together and can be negated. The expressions represented by each chart are ORed together. So, boolean charts specify a logical expression in the form of:
<pre>    OR(AND(OR()), NOT(AND(OR())), ...)</pre>

Original boolean charts in Bugzilla 2.x really consisted of tables with rows and columns. Now, there is no tables, but three levels of charts remain.

Original boolean charts also were ANDed together, not ORed. I.e. the expression was AND(AND(OR())), not OR(AND(OR())).

Original boolean charts also were &quot;constraints on a single piece of data&quot;. I.e. '(cc = foo) and (cc = bar)' within one boolean chart would match nothing in original Bugzilla, as this would run the check on a single user, which could not be 'foo' and 'bar' simultaneously. But, '(cc = foo)' and '(cc = bar)' as two separate charts would match bugs which have 'foo' as one CC AND 'bar' as another.

Now, this magic is only enabled for bug comments, bug changes, flags and attachments. See &quot;CORRELATED SEARCH TERMS&quot; below.

FIXME: Add support for manual selection of match target for such terms. I.e. the user should be able to select &quot;comment 1 author = ...&quot; AND &quot;comment 2 author = ...&quot; search terms by hand, without thinking about any magic. Now, it's also the single piece of functionality which is lost compared to the old search engine.

== QUERY OPTIMISATION ==

The two main problems of Bugzilla query complexity on big databases were:  
; '''OR operators inside query which stop DBMS from using indexes for query speed-up.''' : I.e. for example &quot;cc=X or commenter=X&quot; was translated to 2 LEFT JOINs - to cc and longdescs tables - and a term like &quot;cc.who=X or longdescs.who=X&quot;. DBMS can't do an index merge here, so this leads to a scan + two index checks.<br> '''SOLUTION:'''<br> Use UNIONs instead of ORs. I.e. this same query is now translated to<br>
<pre>  (SELECT bug_id FROM cc WHERE who=X)
  UNION
  (SELECT bug_id FROM longdescs WHERE who=X)</pre>
:It's important to note that the UNION of &quot;non-seeding&quot; queries is worse than OR, because UNION must be always executed fully and can't be just checked while executing other part of query.<br> '''SOLUTION:''' (Partially experimental by now, but works)<br> Expand brackets in expressions. I.e. transform AND(a,OR(b,c)) to OR(AND(a,b),AND(a,c)). This also allows to handle OR query parts with correlated search terms.<br>
; '''Usage of multiple JOINS with many rows in one query, leading to insane amounts of rows when multiplied.''' : '''SOLUTION:'''<br> Wrap them into (SELECT DISTINCT) subqueries if there is more than one such term in the query. This removes duplicate rows from the result and makes DBMS's life easier. To do so in Bugzilla, specify { many_rows =&gt; 1 } for such terms.<br>

== SEARCH FUNCTIONS ==

Search functions get and return arguments through $self. Input arguments:  
; $self-&gt;{sequence} : Sequence number of current condition, starting with 0. Used mostly to prevent table name collisions.<br>
; $self-&gt;{field} : Field name.<br>
; $self-&gt;{fieldsql} : Field SQL representation.<br>
; $self-&gt;{type} : Search operator type (equals, greaterthan, etc)<br>
; $self-&gt;{negated} : Most &quot;negative&quot; search operators (notequals, etc) are automatically derived from negated terms for corresponding &quot;posivite&quot; operators. So $self-&gt;{type} cannot be &quot;negative&quot;, and $self-&gt;{negated} contains true if it's really negative.<br>
; $self-&gt;{value} : Search value.<br>
; $self-&gt;{quoted} : SQL representation of value. Search functions must set this field before calling default search operator implementation ($self-&gt;call_op) if they want to use different SQL code for the value.<br>

=== OUTPUT ARGUMENT, $self-&gt;{term} ===

Resulting SQL condition or an expression consisting of several conditions.

All conditions are logically divided into two classes: ''scalar'' conditions and ''list'' conditions. ''Scalar'' condition is a check of a single value linked to single bug (for example, &quot;bug assignee = somebody&quot;). ''List'' condition is a check in the form of &quot;ANY of linked values match condition&quot; or &quot;NONE of linked values match condition&quot;. A lot search conditions in Bugzilla are ''List'' conditions. A simple example is &quot;CC&quot; field: &quot;CC = somebody&quot; is really &quot;ANY CC is somebody&quot;, and &quot;CC != somebody&quot; is really &quot;NONE of CC is somebody&quot;.

''Scalar'' conditions can be returned either as a simple string with SQL condition, or as a hashref with following keys:
<blockquote>  
; supp =&gt; [ &quot; LEFT JOIN t1 ON ...&quot;, ... ] : Arrayref with table JOINs (plaintext with ON) required to match.<br>
; term =&gt; &quot;string&quot; : SQL condition string.<br>
; description =&gt; [ 'field', 'operator', 'value' ] : Search term description for the UI. It is not required for search functions to provide this description unless they return an expression consisting of several terms. The default 'field', 'operator' and 'value' are taken from the request.<br></blockquote>

''List'' conditions must be always returned as a hashref with the following keys:
<blockquote>  
; table =&gt; &quot;string&quot; : Table specification with alias(es), just like &lt;table&gt; inside SQL query:<br>
<pre> ... JOIN <table> ON <condition> ...</pre> For example, it may itself contain JOINs inside brace expressions:<br>
<pre> ... JOIN (table1 JOIN table2 ON ...) ON <condition> ...</pre> $self-&gt;{sequence} should be appended to alias(es) to easily combat table name collisions.<br>
; where =&gt; &quot;string&quot; : Join &lt;condition&gt;. fields of 'bugs' table could be used for joining.<br>
; bugid_field =&gt; &quot;string&quot; : When the &lt;table&gt; is joined to 'bugs' simply on 'bug_id', and none of other 'bugs' fields are used, function should omit &quot;bugs.bug_id=table_alias.bug_id&quot; from 'where', and specify &quot;tablealias.bug_id&quot; as 'bugid_field'.<br>
; notnull_field =&gt; &quot;string&quot; : When the &lt;table&gt; is joined to 'bugs' on some field other than 'bug_id', 'bugid_field' key MUST be omitted and any field of table that cannot be NULL must be specified in 'notnull_field'. This is used in negative lookups (&quot;NONE of linked values match&quot;) for NULL checks.<br>
; neg =&gt; boolean value (1 or 0) : If a positive term corresponds to negative lookup, search function can specify a true value for this key. Bugzilla search engine will then negate the term itself.<br>
; many_rows =&gt; boolean value (1 or 0) : If many_rows is true, this term will be wrapped into a (SELECT DISTINCT bug_id ...) subquery when INNER JOINed to different terms with many_rows. Use it when 'table' could have relatively many rows for a single bug.<br>
; description =&gt; [ 'field', 'operator', 'value' ]</blockquote>

=== CORRELATED SEARCH TERMS ===

There is also a subclass of &quot;list&quot; conditions - conditions that can be correlated. I.e., conditions that can possibly match different fields of one entity linked to a bug (for example, comment date and comment author). If the user specifies &quot;comment date is ...&quot; AND &quot;comment author is ...&quot;, then he probably means to find ONE comment with date and author matching these terms.

To specify such conditions, search term must have different keys:
<blockquote>  
; base_table =&gt; &quot;string&quot; : The name of base table in which the entities linked to a bug are stored without any aliases, subqueries or JOINs.<br>
; base_joins =&gt; [ [ 'LEFT'|'INNER', &lt;table name&gt;, &lt;alias&gt;, &lt;on&gt; ], ... ] : The names of tables which must be additionally joined to base table to run the term. LEFT|INNER is join type. &lt;alias&gt; MUST be non-unique (MUST not contain $self-&gt;{sequence}). When two such terms are united, base_joins duplicates are filtered based on &lt;alias&gt;. Unique aliases are then generated automatically by SQL core.<br>
; where =&gt; string or [ string1, string2, ... ]
; bugid_field =&gt; &quot;string&quot;
; notnull_field =&gt; &quot;string&quot;
; neg =&gt; 1|0
; many_rows =&gt; 1|0
; description =&gt; [ 'field', 'operator', 'value' ] : All these fields have meaning identical to their meaning in simple &quot;list&quot; conditions.<br></blockquote>

==== EXPLANATION ====

As it was mentioned above, original boolean charts in Bugzilla were &quot;constraints on a single piece of data&quot;.

This magic enables certain amount of power in queries, but is useful only when the binary relation between the field and value is not enough to spell the real condition.

Such conditions are possible only for ANDed terms, and only for &quot;list&quot; bug fields, i.e. when there can be more than one entity linked to bug: ((e1 in A) &amp; (e2 in B)) is not necessarily equal to (e in (A intersect B)), which the user probably meant.

When there is no more than 1 entity linked to a bug, the situation ((e1 in A) &amp; (e2 in B)) is impossible, as e1 and e2 may not be different. So the condition is always (e in (A intersect B)). Also, they are impossible for OR: swap e1 and e2 in ((e1 in A) | (e2 in B)), and you'll get ((e2 in A) | (e1 in B)), OR it with unswapped term, and you'll get (e in (A union B)).

In Bugzilla, this is possible for bug comments, bug changes, flags and attachments. I.e. if somebody writes:
<pre>   commented after 2010-01-01
   AND commented before 2010-02-01
   AND commented by foo@bar.org</pre>

Then he probably means to search for bugs with a comment from foo@bar.org left between 2010-01-01 and 2010-02-01, not for bugs with 3 different comments corresponding to single terms.

=== EXPRESSIONS ===

Although this should be needed very rarely, search functions can also return expressions consisting of several single conditions.

'''ATTENTION!''' Functions that return expressions MUST specify $term-&gt;{description} for each single term inside this expression to correctly generate search descriptions in the UI.

An expression is an arrayref with the operation name as the first element and operation arguments as the following ones. Possible operation names are 'OR', 'AND', 'OR_MANY', 'AND_MANY', 'OR_NE', 'AND_NE'. Each argument may either be an expression itself, or a search term in a format returned by search functions.

OR is replaced with SQL UNION.

There are (partially experimental) variations of OR:

OR_NE is not expanded when its outer expression is expanded, i.e. AND(OR(a,b),OR_NE(c,d)) becomes OR(AND(a,OR_NE(c,d)),AND(b,OR_NE(c,d))).

OR_MANY is attached as LEFT JOINs instead of an INNER JOIN to UNION, if there are any terms ANDed with it.

Example: ((assignee=X) and (changed since 2008 or commented since 2008))

The second, predictably, produces an insane amount of rows itself, so it's faster to scan rows selected by first and check the second on them. But, it's faster to select simply (changed since 2008 or commented since 2008) using indexes and UNION.