1

I have a Joomla (3.9.13) database that contains a table called fields_values, it is a denormalized key value store (so i've been told). It contains the following data;

+----------+---------+--------+
| field_id | item_id | value  |
+----------+---------+--------+
| 6        | 119     | bloggs |
| 17       | 119     | 1      |
| 19       | 119     | 45     |
| 21       | 119     | 55     |
| 17       | 119     | 14     |
| 17       | 119     | 100    |
+----------+---------+--------+

When I run this query;

SELECT `field_id`
     , `item_id`
     , `value` 
  FROM `fields_values` 
 WHERE `field_id` IN (6,17) AND `item_id` = '119'

I get these results;

+----------+---------+--------+
| field_id | item_id | value  |
+----------+---------+--------+
| 6        | 119     | bloggs |
| 17       | 119     | 1      |
| 17       | 119     | 2      |
| 17       | 119     | 13     |
| 17       | 119     | 14     |
| 17       | 119     | 100    |
+----------+---------+--------+

That all seems fine, but when I run this query (omit the 6 from IN clause);

SELECT `field_id`
     , `item_id`
     , `value` 
  FROM `fields_values` 
 WHERE `field_id` IN (17) AND `item_id` = '119'

I get zero results.

I would have expected the following results;

+----------+---------+--------+
| field_id | item_id | value  |
+----------+---------+--------+
| 17       | 119     | 1      |
| 17       | 119     | 2      |
| 17       | 119     | 13     |
| 17       | 119     | 14     |
| 17       | 119     | 100    |
+----------+---------+--------+

I've also tried this simple version of the query;

SELECT 
    `field_id`, 
    `item_id`, 
    `value` 
FROM 
    `o3m0z_fields_values` 
WHERE
    `field_id` = 17 
AND 
    `item_id` = '119'

I get zero results.

Here's the table information taken from HeidiSQL;

enter image description here

I've tried to replicate this on a fresh Joomla install and I can't. Why is this happening?

Note: I can't change the query as it's generated by the core Joomla system.

jonboy
  • 2,729
  • 6
  • 37
  • 77
  • 1
    Can't replicate https://www.db-fiddle.com/f/krkchC29r8eBxDimraey3x/0 – Nick Dec 19 '19 at 12:01
  • Likewise, can't replicate. – Strawberry Dec 19 '19 at 12:06
  • What happens if you comment out ``` AND `item_id` = '119' ``` part? – Salman A Dec 19 '19 at 12:47
  • Cross-posted here: https://joomla.stackexchange.com/q/26559/12352 – mickmackusa Dec 20 '19 at 03:36
  • @Nick that is not exactly the schema we are dealing with. Notice that jonboy's `item_id` is `VARCHAR`. I am suspicious of potentially overlooked spaces / invisible characters lurking _somewhere_. – mickmackusa Dec 20 '19 at 03:57
  • @mickmackusa schema makes no difference https://www.db-fiddle.com/f/krkchC29r8eBxDimraey3x/1. Without seeing the joomla code I guess it's possible spaces/hidden characters are being inserted into the query, but why would a fresh install solve the problem? – Nick Dec 20 '19 at 04:44
  • I don't recommend a fresh install. I also tried changing the schema. I also tried it on postgre. To no avail. I am running out of possible causes. If jonboy proves that there are no invisible characters, then I may have to tap RickJames to come and stick his nose in here. @Nick – mickmackusa Dec 20 '19 at 04:50
  • In case volunteers don't want to chase link to link for more info, this is the environment: `Joomla 3.9.13`, `PHP version: 7.3.9`, `Mysql Ver 15.1 Distrib 10.4.6-MariaDB` – mickmackusa Dec 21 '19 at 12:02

0 Answers0