Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Query caching issue

LordBertson
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

Accepted Solutions

exa-Chris
Exasol Alumni

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

View solution in original post

3 REPLIES 3

exa-Chris
Exasol Alumni

Hi there,

let me ping some people about this

exa-Chris

exa-Chris
Exasol Alumni

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

LordBertson
Contributor

Thank you.