Pages with and without trailing whitespaces are not returned properly when unique pages are requested on MySQL

Do the following test, on a wiki on mysql:

• create a page with name "Sandbox.Test"
• create another page with name "Sandbox.Test " (note the trailing whitespace)
  • note that at this point nothing stops you from doing this and both pages are properly accessible if the proper URL is used
  • in addition, both pages are visible and accessible from the Main.AllDocs and in the documents tree
• use the following query in a page, for example to list all pages from the wiki:

  $services.query.hql('where 1=1').addFilter("unique").execute()
  

  This query is supposed to get all documents from the wiki. However, only one of the two "Test" pages created above will be returned.

Note that this issue is related to the mysql management of trailing whitespaces when doing comparisons (and the fact that unique filter uses the distinct keyword): https://dev.mysql.com/doc/refman/5.7/en/char.html#idm140674151070208 , so, unless other database systems have the same type of limitations, it won't happen on a different database.

Also note that this might impact other functions that are supposed to return "all documents of the wiki" and which use the distinct keyword, such as all the searchDocuments() functions from com.xpn.xwiki.api.XWiki, deprecated or not.