Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.0, 10.1, 10.4.10, 10.2, 10.3, 10.4
-
None
Description
we want to return all unique values from a column (using distinct), the column has a fulltext index defined.
In this case a simple select does not return any values, removing the DISTINCT or the fulltext index solves the problem.
MariaDB [regressiondb]> SELECT DISTINCT tagResult AS name FROM regressRun_tagResult WHERE tagResult LIKE 'esm_202-start_18677%';
|
Empty set (0.00 sec)
|
|
|
MariaDB [regressiondb]> SELECT tagResult AS name FROM regressRun_tagResult WHERE tagResult LIKE 'esm_202-start_18677%' ORDER BY tagResult;
|
+----------------------------------------------+
|
| name |
|
+----------------------------------------------+
|
| esm_202-start_18677#date=2019/12/18_16:48:24 |
|
+----------------------------------------------+
|
1 row in set (0.15 sec)
|
|
|
MariaDB [regressiondb]> show create table regressRun_tagResult;
|
+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| regressRun_tagResult | CREATE TABLE `regressRun_tagResult` (
|
`regressRun_id` int(11) unsigned NOT NULL,
|
`tagResult` varchar(255) COLLATE latin1_bin NOT NULL,
|
PRIMARY KEY (`regressRun_id`,`tagResult`),
|
FULLTEXT KEY `tagResult` (`tagResult`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
|
+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [regressiondb]> alter table regressRun_tagResult drop index tagResult;
|
Query OK, 0 rows affected (3.98 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [regressiondb]> SELECT DISTINCT tagResult AS name FROM regressRun_tagResult WHERE tagResult LIKE 'esm_202-start_18677%';
|
+----------------------------------------------+
|
| name |
|
+----------------------------------------------+
|
| esm_202-start_18677#date=2019/12/18_16:48:24 |
|
+----------------------------------------------+
|
1 row in set (0.11 sec)
|
|
|
MariaDB [regressiondb]> SELECT tagResult AS name FROM regressRun_tagResult WHERE tagResult LIKE 'esm_202-start_18677%' ORDER BY tagResult;
|
+----------------------------------------------+
|
| name |
|
+----------------------------------------------+
|
| esm_202-start_18677#date=2019/12/18_16:48:24 |
|
+----------------------------------------------+
|
1 row in set (0.16 sec)
|
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-