Short Summary: Running in a Drupal environment, I have a query, which returns an empty result set, unless I run some kind of command on the result of an inner select. This can be Trim, replace, upper.
All the unneeded fields have been removed from the select, so I only have the relevant part of the query left.
mysql> SELECT HEX(taxonomy_term_field_data_node_field_data.name)
FROM node_field_data
LEFT JOIN (SELECT td.*, tn.nid AS nid
FROM taxonomy_term_field_data td
LEFT JOIN taxonomy_index tn ON tn.tid = td.tid
WHERE (td.vid IN ('category'))
) taxonomy_term_field_data_node_field_data
ON node_field_data.nid = taxonomy_term_field_data_node_field_data.nid
WHERE (taxonomy_term_field_data_node_field_data.name = "Politik");
Empty set (0.01 sec)
It is possible to select from the taxonomy_term_field_data table when it is not in the join (where name = "Politik")?
mysql> SELECT name FROM taxonomy_term_field_data WHERE name = "Politik";
+---------+
| name |
+---------+
| Politik |
+---------+
1 row in set (0.00 sec)
And If I replace nothing with nothing in the query, I get results back: This also work with UPPER on both strings or TRIM.
mysql> SELECT HEX(taxonomy_term_field_data_node_field_data.name)
FROM node_field_data
LEFT JOIN (SELECT td.*, tn.nid AS nid
FROM taxonomy_term_field_data td
LEFT JOIN taxonomy_index tn ON tn.tid = td.tid
WHERE (td.vid IN ('category'))
) taxonomy_term_field_data_node_field_data
ON node_field_data.nid = taxonomy_term_field_data_node_field_data.nid
WHERE (REPLACE(taxonomy_term_field_data_node_field_data.name, "", "") = "Politik");
+----------------------------------------------------+
| HEX(taxonomy_term_field_data_node_field_data.name) |
+----------------------------------------------------+
| 506F6C6974696B |
| 506F6C6974696B |
| 506F6C6974696B |
+----------------------------------------------------+
3 rows in set (0.01 sec)
I have also tried and do an update name = trim(name) in the taxonomy_term_field_data table, but that does not work.
One of the queries are using the where and the other a conditional index, but any input on how to fix this is very welcomed.
mysql> explain SELECT taxonomy_term_field_data_node_field_data.name FROM node_field_data LEFT JOIN (SELECT td.*, tn.nid AS nid FROM taxonomy_term_field_data td LEFT JOIN taxonomy_index tn ON tn.tid = td.tid WHERE (td.vid IN ('category')) ) taxonomy_term_field_data_node_field_data ON node_field_data.nid = taxonomy_term_field_data_node_field_data.nid WHERE (trim(taxonomy_term_field_data_node_field_data.name) = "Politik");
+----+-------------+-----------------+-------+----------------------------------------------+-------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+----------------------------------------------+-------------+---------+--------------------------+------+-------------+
| 1 | PRIMARY | node_field_data | index | PRIMARY,node__id__default_langcode__langcode | node__vid | 4 | NULL | 20 | Using index |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | v15x.node_field_data.nid | 14 | Using where |
| 2 | DERIVED | td | ALL | taxonomy_term__tree,taxonomy_term__vid_name | NULL | NULL | NULL | 1404 | Using where |
| 2 | DERIVED | tn | ref | term_node | term_node | 4 | v15x.td.tid | 1 | Using index |
+----+-------------+-----------------+-------+----------------------------------------------+-------------+---------+--------------------------+------+-------------+
4 rows in set (0.00 sec)
mysql> explain SELECT taxonomy_term_field_data_node_field_data.name FROM node_field_data LEFT JOIN (SELECT td.*, tn.nid AS nid FROM taxonomy_term_field_data td LEFT JOIN taxonomy_index tn ON tn.tid = td.tid WHERE (td.vid IN ('category')) ) taxonomy_term_field_data_node_field_data ON node_field_data.nid = taxonomy_term_field_data_node_field_data.nid WHERE (taxonomy_term_field_data_node_field_data.name = "Politik");
+----+-------------+-----------------+-------+----------------------------------------------+-------------+---------+--------------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+----------------------------------------------+-------------+---------+--------------------------+------+-----------------------+
| 1 | PRIMARY | node_field_data | index | PRIMARY,node__id__default_langcode__langcode | node__vid | 4 | NULL | 20 | Using index |
| 1 | PRIMARY | <derived2> | ref | <auto_key1> | <auto_key1> | 5 | v15x.node_field_data.nid | 14 | Using index condition |
| 2 | DERIVED | td | ALL | taxonomy_term__tree,taxonomy_term__vid_name | NULL | NULL | NULL | 1404 | Using where |
| 2 | DERIVED | tn | ref | term_node | term_node | 4 | v15x.td.tid | 1 | Using index |
+----+-------------+-----------------+-------+----------------------------------------------+-------------+---------+--------------------------+------+-----------------------+
4 rows in set (0.00 sec)
