Type:
Bug
Priority:
Major
Resolution:
Unresolved
Affects Version/s:
10.0(EOL) , 10.1(EOL) , 10.2(EOL) , 10.3(EOL) , 10.4(EOL)
Environment:
Ubuntu Server 14.04 64bit
After creating a full text targeting a column on a table, a "select distinct" query to retrieve the range of values returns an empty set.
If you use the count operator, it returns the real number of distinct occurences.
If you apply a function, like "UCASE", it returns the correct record set.
How to repeat:
Install the sample employee database:
https://dev.mysql.com/doc/employee/en/employees-installation.html
Query the titles table with the following query:
mysql> select distinct title from titles;
+--------------------+
| title |
+--------------------+
| Senior Engineer |
| Staff |
| Engineer |
| Senior Staff |
| Assistant Engineer |
| Technique Leader |
| Manager |
+--------------------+
7 rows in set (0.38 sec)
Then create a fulltext index on the titles table, over the title column:
mysql> alter table titles add fulltext index `title` (`title`);
Query OK, 0 rows affected (14.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
Issue the same query again:
mysql> select distinct title from titles;
Empty set (0.00 sec)
Repeat the query, but with COUNT:
mysql> select count(distinct title) from titles;
+-----------------------+
| count(distinct title) |
+-----------------------+
| 7 |
+-----------------------+
1 row in set (0.24 sec)
Repeat the query with UCASE:
mysql> select distinct(ucase(title)) from titles;
+--------------------+
| (ucase(title)) |
+--------------------+
| SENIOR ENGINEER |
| STAFF |
| ENGINEER |
| SENIOR STAFF |
| ASSISTANT ENGINEER |
| TECHNIQUE LEADER |
| MANAGER |
+--------------------+
7 rows in set (0.44 sec)
So, how come the first query returns an empty set, when there are 7 records to display.
If you drop the FT index, the query returns the correct result set.
{"report":{"fcp":873.2000000476837,"ttfb":285.90000009536743,"pageVisibility":"visible","entityId":54436,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"33b481b2-0ad5-4679-aca2-df4ac20edcfd","navigationType":0,"readyForUser":955.9000000953674,"redirectCount":0,"resourceLoadedEnd":1632.1000000238419,"resourceLoadedStart":294.60000002384186,"resourceTiming":[{"duration":111.80000007152557,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":294.60000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":294.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":406.40000009536743,"responseStart":0,"secureConnectionStart":0},{"duration":111.60000002384186,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":295,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":295,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":406.60000002384186,"responseStart":0,"secureConnectionStart":0},{"duration":143.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":295.10000002384186,"connectEnd":295.10000002384186,"connectStart":295.10000002384186,"domainLookupEnd":295.10000002384186,"domainLookupStart":295.10000002384186,"fetchStart":295.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":295.10000002384186,"responseEnd":438.60000002384186,"responseStart":438.60000002384186,"secureConnectionStart":295.10000002384186},{"duration":296,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":295.8000000715256,"connectEnd":441.2000000476837,"connectStart":441.2000000476837,"domainLookupEnd":441.2000000476837,"domainLookupStart":441.2000000476837,"fetchStart":295.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":441.60000002384186,"responseEnd":591.8000000715256,"responseStart":453.90000009536743,"secureConnectionStart":441.2000000476837},{"duration":144.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":296,"connectEnd":296,"connectStart":296,"domainLookupEnd":296,"domainLookupStart":296,"fetchStart":296,"redirectEnd":0,"redirectStart":0,"requestStart":296,"responseEnd":440.5,"responseStart":440.5,"secureConnectionStart":296},{"duration":145,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":296.2000000476837,"connectEnd":296.2000000476837,"connectStart":296.2000000476837,"domainLookupEnd":296.2000000476837,"domainLookupStart":296.2000000476837,"fetchStart":296.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":296.2000000476837,"responseEnd":441.2000000476837,"responseStart":441.2000000476837,"secureConnectionStart":296.2000000476837},{"duration":146,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":296.40000009536743,"connectEnd":296.40000009536743,"connectStart":296.40000009536743,"domainLookupEnd":296.40000009536743,"domainLookupStart":296.40000009536743,"fetchStart":296.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":296.40000009536743,"responseEnd":442.40000009536743,"responseStart":442.40000009536743,"secureConnectionStart":296.40000009536743},{"duration":146.70000004768372,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":296.60000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":296.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":443.3000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":146.19999992847443,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":296.8000000715256,"connectEnd":296.8000000715256,"connectStart":296.8000000715256,"domainLookupEnd":296.8000000715256,"domainLookupStart":296.8000000715256,"fetchStart":296.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":296.8000000715256,"responseEnd":443,"responseStart":443,"secureConnectionStart":296.8000000715256},{"duration":146.89999997615814,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":296.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":296.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":443.8000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":146.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":297,"connectEnd":297,"connectStart":297,"domainLookupEnd":297,"domainLookupStart":297,"fetchStart":297,"redirectEnd":0,"redirectStart":0,"requestStart":297,"responseEnd":443.5,"responseStart":443.5,"secureConnectionStart":297},{"duration":1331.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":299.5,"connectEnd":299.5,"connectStart":299.5,"domainLookupEnd":299.5,"domainLookupStart":299.5,"fetchStart":299.5,"redirectEnd":0,"redirectStart":0,"requestStart":299.5,"responseEnd":1631.2000000476837,"responseStart":1631.2000000476837,"secureConnectionStart":299.5},{"duration":1332.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":299.60000002384186,"connectEnd":299.60000002384186,"connectStart":299.60000002384186,"domainLookupEnd":299.60000002384186,"domainLookupStart":299.60000002384186,"fetchStart":299.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":299.60000002384186,"responseEnd":1632.1000000238419,"responseStart":1632.1000000238419,"secureConnectionStart":299.60000002384186},{"duration":358.7000000476837,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":598.1000000238419,"connectEnd":598.1000000238419,"connectStart":598.1000000238419,"domainLookupEnd":598.1000000238419,"domainLookupStart":598.1000000238419,"fetchStart":598.1000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":598.1000000238419,"responseEnd":956.8000000715256,"responseStart":956.8000000715256,"secureConnectionStart":598.1000000238419}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":96,"responseStart":286,"responseEnd":298,"domLoading":289,"domInteractive":1656,"domContentLoadedEventStart":1656,"domContentLoadedEventEnd":1711,"domComplete":2747,"loadEventStart":2747,"loadEventEnd":2747,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1636.1000000238419},{"name":"bigPipe.sidebar-id.end","time":1636.9000000953674},{"name":"bigPipe.activity-panel-pipe-id.start","time":1637.1000000238419},{"name":"bigPipe.activity-panel-pipe-id.end","time":1638.3000000715256},{"name":"activityTabFullyLoaded","time":1727.5}],"measures":[],"correlationId":"480d07103351f7","effectiveType":"4g","downlink":9.7,"rtt":0,"serverDuration":114,"dbReadsTimeInMs":19,"dbConnsTimeInMs":27,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Thanks for the report.
It appears to be an upstream issue, reproducible on current 5.6 and 5.7. In such cases we normally also report bugs at bugs.mysql.com to inform MySQL. Are you willing to do it, or should we do it on your behalf?
It's possible that the bug has already been filed there, but things like that are not easy to find. I tried and couldn't.