Errors while using filters

[vlad-ignatov]

Not sure what the exact error is since I only see the generic error messages. Example:

https://api.smartcumulus.org/data-packages/data_metrics__count_c_system_use_observation_code__001/chart?column=year&filter=year%3AafterYear%3A2000-01-01

It looks like you are expecting just the year number here. The question is, for compatibility reasons, can you accept YYYY-MM-DD and extract the info from it? (applies to all date filters)

Other filters that do not seem to work correctly:

• RegExp filters
• < and <= (eg.: cnt < 10000)

The filter implementation can be very tricky. Keep in mind that at the database level, all columns other than cnt, regardless of their data type are stored as text (to allow cumulus__none and other custom values). Then string operators and filters are also applicable to non-string columns. As a reference, here is the relevant part of the dashboard's current filter implementation:

const FilterConfig: Record<string, (col: string) => string> = {
    
    // Text -------------------------------------------------------------------
    strEq             : col => `"${col}"::TEXT LIKE ?`,
    strContains       : col => `"${col}"::TEXT LIKE concat('%', ?, '%')`,
    strStartsWith     : col => `"${col}"::TEXT LIKE concat(?, '%')`,
    strEndsWith       : col => `"${col}"::TEXT LIKE concat('%', ?)`,
    matches           : col => `"${col}"::TEXT ~ ?`, 
    strEqCI           : col => `"${col}"::TEXT ILIKE ?`,
    strContainsCI     : col => `"${col}"::TEXT ILIKE concat('%', ?, '%')`,
    strStartsWithCI   : col => `"${col}"::TEXT ILIKE concat(?, '%')`,
    strEndsWithCI     : col => `"${col}"::TEXT ILIKE concat('%', ?)`,
    matchesCI         : col => `"${col}"::TEXT ~* ?`,
    strNotEq          : col => `"${col}"::TEXT NOT LIKE ?`,
    strNotContains    : col => `"${col}"::TEXT NOT LIKE concat('%', ?, '%')`,
    strNotStartsWith  : col => `"${col}"::TEXT NOT LIKE concat(?, '%')`,
    strNotEndsWith    : col => `"${col}"::TEXT NOT LIKE concat('%', ?)`,
    notMatches        : col => `"${col}"::TEXT !~ ?`, 
    strNotEqCI        : col => `"${col}"::TEXT NOT ILIKE ?`,
    strNotContainsCI  : col => `"${col}"::TEXT NOT ILIKE concat('%', ?, '%')`,
    strNotStartsWithCI: col => `"${col}"::TEXT NOT ILIKE concat(?, '%')`,
    strNotEndsWithCI  : col => `"${col}"::TEXT NOT ILIKE concat('%', ?)`,
    notMatchesCI      : col => `"${col}"::TEXT !~* ?`,
    
    // Dates ------------------------------------------------------------------
    sameDay           : col => `date_trunc('day'  , "${col}"::TIMESTAMP) =  date_trunc('day'  , TIMESTAMP ?)`,
    sameWeek          : col => `date_trunc('week' , "${col}"::TIMESTAMP) =  date_trunc('week' , TIMESTAMP ?)`,
    sameMonth         : col => `date_trunc('month', "${col}"::TIMESTAMP) =  date_trunc('month', TIMESTAMP ?)`,
    sameYear          : col => `date_trunc('year' , "${col}"::TIMESTAMP) =  date_trunc('year' , TIMESTAMP ?)`,
    sameDayOrBefore   : col => `date_trunc('day'  , "${col}"::TIMESTAMP) <= date_trunc('day'  , TIMESTAMP ?)`,
    sameWeekOrBefore  : col => `date_trunc('week' , "${col}"::TIMESTAMP) <= date_trunc('week' , TIMESTAMP ?)`,
    sameMonthOrBefore : col => `date_trunc('month', "${col}"::TIMESTAMP) <= date_trunc('month', TIMESTAMP ?)`,
    sameYearOrBefore  : col => `date_trunc('year' , "${col}"::TIMESTAMP) <= date_trunc('year' , TIMESTAMP ?)`,
    sameDayOrAfter    : col => `date_trunc('day'  , "${col}"::TIMESTAMP) >= date_trunc('day'  , TIMESTAMP ?)`,
    sameWeekOrAfter   : col => `date_trunc('week' , "${col}"::TIMESTAMP) >= date_trunc('week' , TIMESTAMP ?)`,
    sameMonthOrAfter  : col => `date_trunc('month', "${col}"::TIMESTAMP) >= date_trunc('month', TIMESTAMP ?)`,
    sameYearOrAfter   : col => `date_trunc('year' , "${col}"::TIMESTAMP) >= date_trunc('year' , TIMESTAMP ?)`,
    beforeDay         : col => `date_trunc('day'  , "${col}"::TIMESTAMP) <  date_trunc('day'  , TIMESTAMP ?)`,
    beforeWeek        : col => `date_trunc('week' , "${col}"::TIMESTAMP) <  date_trunc('week' , TIMESTAMP ?)`,
    beforeMonth       : col => `date_trunc('month', "${col}"::TIMESTAMP) <  date_trunc('month', TIMESTAMP ?)`,
    beforeYear        : col => `date_trunc('year' , "${col}"::TIMESTAMP) <  date_trunc('year' , TIMESTAMP ?)`,
    afterDay          : col => `date_trunc('day'  , "${col}"::TIMESTAMP) >  date_trunc('day'  , TIMESTAMP ?)`,
    afterWeek         : col => `date_trunc('week' , "${col}"::TIMESTAMP) >  date_trunc('week' , TIMESTAMP ?)`,
    afterMonth        : col => `date_trunc('month', "${col}"::TIMESTAMP) >  date_trunc('month', TIMESTAMP ?)`,
    afterYear         : col => `date_trunc('year' , "${col}"::TIMESTAMP) >  date_trunc('year' , TIMESTAMP ?)`,

    // Booleans ---------------------------------------------------------------
    isTrue            : col => `"${col}"::BOOLEAN IS TRUE`,
    isNotTrue         : col => `"${col}"::BOOLEAN IS NOT TRUE`,
    isFalse           : col => `"${col}"::BOOLEAN IS FALSE`,
    isNotFalse        : col => `"${col}"::BOOLEAN IS NOT FALSE`,

    // Numbers ----------------------------------------------------------------
    eq                : col => `"${col}"::NUMERIC  = ?` ,
    ne                : col => `"${col}"::NUMERIC != ?`,
    gt                : col => `"${col}"::NUMERIC  > ?` ,
    gte               : col => `"${col}"::NUMERIC >= ?`,
    lt                : col => `"${col}"::NUMERIC  < ?` ,
    lte               : col => `"${col}"::NUMERIC <= ?`,

    // Any --------------------------------------------------------------------
    isNull            : col => `"${col}" IS NULL`,
    isNotNull         : col => `"${col}" IS NOT NULL`,

    // isNull            : col => `"${col}"  = 'cumulus__null'`,
    // isNotNull         : col => `"${col}" != 'cumulus__null'`,
};

[dogversioning]

I think a lot of this is coming down to differences in the database implementation - I had to do the date cast in a slightly different way than it works in postgres, but I have something that is working locally.

Can you provide specific URLs for the other broken filters?

[dogversioning]

@vlad-ignatov pinging on this from the meeting - dates are fixed, just get me urls for other broken filters.

[vlad-ignatov]

I don't see the dates being fixed. There are too many errors and this is hard to test. Let me lis a few errors, and I'll test again after you fix them.

Using https://api.smartcumulus.org/data-packages/data_metrics__count_c_system_use_observation_code__001/chart as base url, these are some queries that fail for me:

• AafterYear 2000: ?column=year&filter=year%3AafterYear%3A2000-01-01
• cnt < 15: ?column=year&filter=cnt%3Alt%3A15 - getting bigger numbers than 15
• cnt <= 15: ?column=year&filter=cnt%3Alte%3A25 - getting bigger numbers than 15
• cnt > 100M: ?column=year&filter=cnt%3Agt%3A100000000 - returns no data but without the filter I can see counts above 100M
• contains: ?column=year&filter=cnt%3AstrContains%3A3 - looks like contains cannot be used on cnt
• regexp: ?column=year&stratifier=site&filter=site%3Amatches%3Auc_davis
• strNotContainsCI: ?column=year&stratifier=site&filter=site%3AstrNotContainsCI%3Auc_davis

[dogversioning]

@vlad-ignatov ok - looks like this is cumulus__none related, which is odd since i copied the last_valid version of this file to the dev aggregator, but didn't end up with that value in dev.

I know you've got them split out in some way at the dashboard layer - do you want cumulus_none value in the query block itself, or returned as a separate part of the response?

[dogversioning]

addressed via #139