weird performance data

Classic Servoy
1

Hi,

Not sure if this is by design or a bug but I have a batchprocessor that runs a custom query inside a controller.loadRecords().
Normally you see that query in full in the Performance data page in the Servoy admin pages.
But now I only see part of it.
Essentially my query is this (simplyfied, it had a bunch of joins and a HAVING as well):

SELECT DISTINCT id, max(columnZ) AS maxval FROM (
    SELECT id
    FROM tableName
    WHERE (columnX = ? OR columnX is NULL)
    GROUP BY id

    UNION

    SELECT id, max(columnZ) AS maxval
    FROM tableName
    WHERE columnY = ?
    GROUP BY id
) t
WHERE maxval<value1

But in the performance data page it only shows the following:

SELECT id FROM tableName WHERE id in (
    SELECT DISTINCT id FROM (
        SELECT id
        FROM tableName
        WHERE (columnX OR columnX is NULL)

It seems like the query is cut off and notice that the placeholder is gone.

Any ideas?

Servoy 5.2.9
Java HotSpot™ 64-Bit Server VM 1.6.0_26

2

I’m relatively certain that when you load a foundset using a query you are only supposed to SELECT the pk columns with your statement, but you are also selecting a MAX…I would try at least taking that out first.

3

jgarfield:
I’m relatively certain that when you load a foundset using a query you are only supposed to SELECT the pk columns with your statement, but you are also selecting a MAX…I would try at least taking that out first.

It should be fine as long as the first selected column(s) is the PKey.

4

Yeah, it’s not that the query doesn’t work. It works fine.
But the performance page seems to chop it off. Which is odd because I have even larger queries showing in full.

5

Robert,

Is this the exact query you are sending to controller.loadRecords()?
There are some checks that look at the query to be compatible with the pre-3.5 custom sql handling which may apply here, but it did not with the sql you posted.

Rob

6

Hi Rob,

The exact query looks more like this:

SELECT DISTINCT msg_id FROM ( 

    SELECT msg_id, max(msl_record_creation) AS lastdate 
    FROM pfx_message 
    JOIN pfx_profile ON (msg_fk_profile=prf_id) 
    LEFT JOIN pfx_messagelog ON (msg_id=msl_fk_message) 
    WHERE msg_is_activated=1 
    AND msg_count < prf_amount 
    AND (msl_record_creation IS NULL OR date(msl_record_creation)<?) 
    GROUP BY msg_id, msl_account 
    
    UNION 
    
    SELECT msg_id, null AS lastdate 
    FROM pfx_message 
    JOIN pfx_profile ON (msg_fk_profile=prf_id) 
    LEFT JOIN pfx_messagelog ON (msg_id=msl_fk_message) 
    WHERE msg_is_activated=1 
    AND msg_count < prf_amount 
    AND date(msl_record_creation)=? 
    GROUP BY msg_id, msl_account 
    HAVING count(date(msl_record_creation)) < 5 
    
) t 
WHERE (lastdate IS NULL OR lastdate <= ?)
7

Robert,

Some of the sql was interfering with the html in the admin page.
That is fixed in next release.

Rob

8

Glad to hear it was a minor thing.
Thanks!