Explanation

A wiki search misses pages whose text differs from the term only in case, or fails outright with:

Argument data type text is invalid for argument 1 of upper function.

Only the database-backed search page, Main.DatabaseSearch, does this. The search XWiki ships enabled is the Solr Search Application, which has its own index and never asks the database to match text, so an instance that kept the default never meets this.

Cause

Two things have to be true at once.

The first is the query. The search page that runs through the database matches by applying upper() to a document's content:

upper(doc.content) like upper(:text) escape '!'

The second is the type of the column it is applied to, which the Hibernate dialect decides. XWiki maps a document's content, and nineteen other long columns, as CLOB or long text, and each dialect turns those into a different SQL Server type:

SQLServerDialect is the SQL Server 2000 dialect. An instance gets it by naming it in hibernate.cfg.xml, which is what the instructions of the time said to do, because in 2006 it was the only SQL Server dialect Hibernate had. Left unset, Hibernate reads the server version from the connection and picks the matching dialect.

Removing the upper() calls from the query, which the oldest advice on this suggested, does not fix the matching: it leaves the comparison to the column's own collation, case-insensitive on SQL Server's usual default and case-sensitive on a _CS_ one.

Solution

1. Open WEB-INF/hibernate.cfg.xml and look in the active block for a dialect property.

       <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>

2. Delete it, so that Hibernate resolves the dialect from the connection.
3. Restart the application server. New columns are now created as varchar(MAX), but the ones already in the database keep the type they were created with: Hibernate's schema update adds tables and columns, it does not retype them.
4. List the columns still stored as text or ntext.

   SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
     FROM INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE IN ('text', 'ntext')
    ORDER BY TABLE_NAME, COLUMN_NAME;
   GO

5. Convert each of them, with XWiki stopped and a backup taken first. text becomes varchar(MAX) and ntext becomes nvarchar(MAX), which is what the detected dialect expects.

   ALTER TABLE dbo.xwikidoc ALTER COLUMN XWD_CONTENT varchar(MAX) NOT NULL;
   GO

6. Start XWiki and search for a term in a case the pages do not use. The results now include them, and the query above returns no rows.

Related

• Configure Microsoft SQL Server