Query caching issue

Contributor

Hi, I think I have encountered an issue with query caching.
Query in question is as follows:

SELECT DISTINCT ( 
    "PARTNER"."PARTNERNUMMER" ,
    "PARTNER"."PARTNERNUMMER" ,
    "PARTNER"."ANREDE" ,
    "PARTNER"."AKADEMISCHER_TITEL" ,
    "PARTNER"."AKADEMISCHER_TITEL_2" ,
    "PARTNER"."VORNAME" ,
    "PARTNER"."NAME" ,
    "PARTNER"."GEBURTSDATUM" ,
    "PARTNER"."STRASSE" ,
    "PARTNER"."HN1" ,
    "PARTNER"."HN2" ,
    "PARTNER"."PLZ4" ,
    "PARTNER"."ORT" ,
    "PARTNER"."PARTNERNUMMER" 
 ) FROM
    "WISTAE"."PARTNER" 
WHERE
    ( ( ( LOWER("PARTNER"."PARTNERNUMMER") LIKE '%%' AND "PARTNER"."PARTNERNUMMER" IS NOT NULL ) AND "PARTNER"."KUNDENWERT" IN (
        'A'
    ) ) AND (   NOT ( ( ( ( ( ( ( ( ( ( ( "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_BEHINDERT" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_BEHINDERT"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_BEHINDERT"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_BEHINDERT"."PARTNERNUMMER" IS NOT NULL ) 
    ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_CRS" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_CRS"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_CRS"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_CRS"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_FAT" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_FAT"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_FAT"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_FAT"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_GELDWAESCHE_RISIKO3" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_GELDWAESCHE_RISIKO3"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_GELDWAESCHE_RISIKO3"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_GELDWAESCHE_RISIKO3"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_INSOLVENZ" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_INSOLVENZ"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_INSOLVENZ"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_INSOLVENZ"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_PEP" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_PEP"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_PEP"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_PEP"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_NEGATIVKENNZEICHEN" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_NEGATIVKENNZEICHEN"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_NEGATIVKENNZEICHEN"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_NEGATIVKENNZEICHEN"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_PEP" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_PEP"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_PEP"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_PEP"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_PEP" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_PEP"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_PEP"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_PEP"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_PEP" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_PEP"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_PEP"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_PEP"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_PEP" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_PEP"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_PEP"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_PEP"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) OR "PARTNER"."PARTNERNUMMER" IN (
        SELECT
            "PARTNER"."PARTNERNUMMER" 
        FROM
            "WISTAE"."PARTNER" LEFT OUTER JOIN "WISTAE"."AUSSCHLUSS_PEP" ON 
            "PARTNER"."PARTNERNUMMER" = "AUSSCHLUSS_PEP"."PARTNERNUMMER"  
        WHERE
            ( LOWER("AUSSCHLUSS_PEP"."PARTNERNUMMER") LIKE '%%' AND "AUSSCHLUSS_PEP"."PARTNERNUMMER" IS NOT NULL ) 
    ) ) ) ) 
ORDER BY
    "PARTNER"."PARTNERNUMMER" 
LIMIT 10;

 This query contains repetition as it is generated by an ORM, but it somehow interacts with a LIMIT clause very weirdly. What occurs is that it returns only 5 results, while there's much more results than 5, roughly 1k actually, if the limit clause is removed. This only occurs when query caching is enabled, once we have disabled it, this issue no longer occurs and the query behaves as expected.

1 ACCEPTED SOLUTION

Community Manager
Community Manager

Quick update, we have created an internal ticket and will follow up

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

View solution in original post

3 REPLIES 3

Community Manager
Community Manager

Hi there,

let me ping some people about this

exa-Chris

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

Community Manager
Community Manager

Quick update, we have created an internal ticket and will follow up

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

View solution in original post

Contributor

Thank you.