fleetdm
Fleet recognizes window functions as syntax errors in queries
Fleet version: <!-- Copy this from the "My account" page in the Fleet UI, or run fleetctl --version -->
Web browser and operating system:
💥 Actual behavior
Fleet sees window functions within queries as syntax errors and displays warning. Query still ran with no errors.
Full example query
WITH extension_safety_hub_menu_notifications AS (
SELECT
parse_json.key,
parse_json.fullkey,
parse_json.path,
parse_json.value
FROM (
SELECT file.filename, file.path, file.btime FROM file WHERE path LIKE "/Users/%/Library/Application Support/Google/Chrome/%/Preferences" ORDER BY file.btime DESC limit 20
) as chrome_preferences
JOIN parse_json ON chrome_preferences.path = parse_json.path
WHERE parse_json.path LIKE "/Users/%/Library/Application Support/Google/Chrome/%/Preferences" AND (
fullkey IN ("profile/name", "profile/safety_hub_menu_notifications/extensions/isCurrentlyActive", "profile/safety_hub_menu_notifications/extensions/result/timestamp")
OR fullkey Like "profile/safety_hub_menu_notifications/extensions/result/triggeringExtensions%"
)
),
extension_details AS (
SELECT path,
CASE WHEN key = 'name' THEN value END AS profile_name,
CASE WHEN key = 'isCurrentlyActive' THEN value END AS notification_active,
CASE WHEN key GLOB '*[0-9]' THEN value END AS triggering_extension,
CASE WHEN key = 'timestamp' THEN value END AS timestamp
FROM extension_safety_hub_menu_notifications
GROUP BY path, profile_name, notification_active, triggering_extension, timestamp
),
problematic_extensions AS (
SELECT
path,
MAX(profile_name) AS profile_name,
MAX(notification_active) AS notification_active,
MAX(timestamp) AS timestamp,
triggering_extension
FROM extension_details
)
SELECT path,
profile_name,
notification_active,
timestamp,
triggering_extension
FROM problematic_extensions
WHERE triggering_extension IS NOT NULL;
With window functions (on lines 28-30), syntax error warning displays
With window functions removed, no syntax warning displays
🧑💻 Steps to reproduce
- Write a query with window functions
OVER(PARTITION BY <partition_key>)in Fleet UI. - Observe syntax error.
- Execute query and take note of successful execution with no errors. This particular query yields no results in a favorable setting.
🕯️ More info (optional)
N/A
Linked to Unthread ticket:
In estimation, discussed possibility of preventing Save being disabled when syntax error present
In estimation, discussed possibility of preventing Save being disabled when syntax error present
Here's the history of why we disabled save when the query fails validation:
- https://github.com/fleetdm/fleet/issues/12751
- https://github.com/fleetdm/fleet/issues/12520
After discussing at yesterday's design review, we don't want to undo the work in the issues referenced above, so we'll need to update the validation.
we'll need to update the validation
Sounds good, setting estimate to 3 then
The sqlite parser library we use hasn't been updated in 8 years and simply doesn't know about window functions. I'm looking at alternatives.
Queries dance like wind, No error in syntax seen, Fleet's gaze grows serene.
