Bug DISTINCT

[s6930064]

I do not know if it is a bug or by design but ?DISTINCT?, according to fabrikdebug ?list GetData?, is applied regardless of list merge option (lines 2479-2483 of com_fabrik\models\list.php).


Also the query has not limit, so if I display the first page of the list I have to query all records with ?DISTINCT? and no limit.

Can we use ?DISTINCT? only in case of lists merge and not in all other cases? And limit in all cases?

 

[rob]

The query is limited elsewhere in the code (when you apply the query to the database object) 99.99% sure we don't have unlimited queries running.

Eg for the list data

public static function finesseData($listId, $query, $start, $length, $outputFormat)
......
$fabrikDb->setQuery($query, $start, $length);

To my mind distinct should always be applied. Do you have a use case where the query is returning incorrect data? I'm failing to see the correlation between list merge options and the use of distinct

 

[s6930064]

Hi Rod,

In fabrikdebug GetData query I didn't see a limit. But in MySQL logs I see it, you are right.

DISTINCT, almost always creates a temporary table slowing performance. For example in my case a list with 300.000 records and some joins when I display it (10 records per page) takes 10 seconds for each page. If I remove DISTINCT it takes 0.1secs.

I thought that merge option ?Display mode = Each row separately? will not apply DISTINCT but it seems that the use of DISTINCT is global regardless of the Display mode.

I do not know the internals of Fabrik but if I could bypass the use of DISTINCT with ?Display mode = Each row separately? the specific list will be much faster.


Thanks for your time.

 

[rob]

Oh I didn't know about the temporary table creation with distinct, thanks for pointing that out to me.

At this point we can't just change the query based on the join merge data option as that might alter results on existing sites.

The only way I could think of getting round this is to add an option in the list's advanced section to toggle on/off the use of DISTINCT.
I've added that with this commit https://github.com/Fabrik/fabrik/commit/094a111ff984927b4918f591ab32b81cb10d8016

 

[s6930064]

thanks it works.