Just spent half a day trying to track down some missing asset data in our logs. I was convinced our custom log source was broken, but it turns out the records were there—just with empty fields. I completely forgot that in AQL, you can't use `=` or `!=` to check for NULLs.
For anyone else hitting this, here's the syntax that finally worked. You need to use `IS NULL` or `IS NOT NULL`.
```sql
SELECT * FROM events
WHERE some_custom_field IS NULL
AND starttime > '2024-01-01 00:00'
LAST 1 DAYS
```
Or, if you're looking for populated fields:
```sql
SELECT * FROM events
WHERE some_custom_field IS NOT NULL
```
The classic `WHERE field = NULL` will return zero results every time. It's a simple thing, but when you're deep in a migration project and validating data flow from integrations, this little detail can save you a massive headache.
I ended up using this to verify that our Zapier webhooks were populating all the expected fields correctly. Super handy.
hth
Oh man, I've done the exact same thing! It feels like such a logic trap when you're stuck on it. Your example with Zapier fields is perfect, it's exactly the kind of integration where nulls sneak in and wreck your reports later.
A similar quirk got me recently: if you're trying to filter for empty strings in a query, you still can't use `= ''`. You have to use `IS NOT DISTINCT FROM ''`. Different syntax, same kind of time-sink frustration when you're under pressure.
Glad you got it sorted. That "aha" moment after hours of banging your head is weirdly satisfying.
This is a fundamental SQL behavior that extends far beyond AQL - it's rooted in three-valued logic where NULL represents an unknown value, so equality comparisons always return UNKNOWN rather than TRUE or FALSE. What's particularly tricky is that some database systems implement vendor-specific extensions that can catch people off guard.
For example, MySQL permits `SET ANSI_NULLS = OFF` to enable `= NULL` comparisons, while SQL Server has different behavior depending on the ANSI_NULLS setting. If you're working across multiple database systems or migrating queries, this inconsistency can create subtle bugs.
In the context of data validation for webhook integrations like your Zapier example, I'd recommend creating a validation script that explicitly checks for both NULL and empty string values separately. The semantic difference between "field not present" (NULL) and "field present but empty" (empty string) often matters for downstream processing, and treating them identically can mask integration issues.
That ANSI_NULLS point is crucial when you're moving queries between systems. I've seen production alerts fire off in SQL Server because someone ported a "field != NULL" check from a MySQL backup script and didn't catch the setting difference.
Your note about NULL vs empty string semantics is dead on. In our alert routing, we treat them completely differently. A NULL `error_code` means the event hasn't been evaluated yet, but an empty string means it was evaluated as "clean". If you conflate them, your incident response gets the wrong signal.
The three-valued logic explanation is correct, but in practice, I just drill it into my team as a hard rule: never use = or != with NULL. Saves the brain cycles.
Run it yourself.
That Zapier webhook check is such a great real-world use for this. I run into the same thing when we're mapping custom fields from Jira into Monday.com boards. The number of times a ticket's "Estimated Hours" comes through as null because a field wasn't set at transition, and it silently breaks a reporting column... it's a nightmare until you remember the IS NULL check.
It's also saved me when building Asana rules that trigger on a field change. The rule might fire when a due date is *set*, but you need a separate check with "IS NOT NULL" to catch when it's cleared out, because "field changed to blank" doesn't always register the same way.
The right tool saves a thousand meetings.
Oh, mapping Jira to Monday is exactly what I'm struggling with right now. When a custom field is blank, our project timeline summary just shows empty spaces and it looks broken. I haven't set up any checks for that yet.
> field changed to blank doesn't always register the same way.
That's a huge tip. I've only been setting up rules for when a field gets filled. So if a due date gets removed, my Asana rule wouldn't catch it? That explains why some of my automation seems spotty. Do you usually make two separate rules then, one for IS NULL and one for IS NOT NULL?
The Zapier validation case is exactly where these checks become critical. I've had to debug pipelines where a single missing field from a webhook payload cascaded into silent data loss because someone wrote `WHERE custom_field != NULL` in a downstream transformation.
One nuance: if you're also checking for empty strings, remember that `IS NOT NULL` will still return true for an empty string. In a validation script, you might want to chain conditions like `WHERE custom_field IS NOT NULL AND custom_field != ''` to ensure you're only capturing actually populated fields.
Show me the benchmarks
Ooh, the Zapier webhook check is such a perfect example. That "aha" moment when you realize it's just null data and not a broken integration is so real. It's exactly like hunting for a missing UI element only to find it's just rendered with zero opacity.
That syntax trip-up gets me in Figma sometimes too, like when you're filtering component instances for overrides. Searching for "undefined" versus "empty" gives you totally different results. Glad you posted this, it's one of those tiny things that wastes so much time!
Yep, that exact pattern hits in CI/CD too. I've spent way too long debugging a pipeline failure because a Terraform output variable was empty when a resource wasn't created.
The `=` vs `IS NULL` trap is a classic. It's one of those things you have to burn into muscle memory. Your Zapier check is spot on - validating webhook payloads before they hit your transforms is a solid move.
A related gotcha in automation: some systems (like GitHub Actions expressions) treat empty strings and nulls as the same in certain contexts. So even if you handle the SQL right, the next step in your flow might still misbehave.
Exactly. The "broken integration" feeling is so specific, and it's always a simple data issue. That's why setting up clear logging for your webhook payloads is critical. If you see the field is null in the raw log, you can skip the hours of checking API credentials and endpoint URLs.
The Figma comparison is apt because it's the same mental model shift: you're not looking for a thing, you're looking for the *absence* of a thing. Most search tools aren't built around that.
> you're not looking for a thing, you're looking for the *absence* of a thing.
This clicks for me with Terraform outputs. I'll spend ages thinking my module is broken, but the resource just didn't create and returned null. Logging the raw output json first has saved me so many times.
Does your team log the whole payload, or just specific fields you're checking?
We log the entire raw payload in a structured JSON field at the ingress point, but only for a limited retention window due to volume. The key is having a second, separate logging step after your initial validation, where you log only the fields that failed the null check. That way you can trace if a null was present in the original payload or if it was introduced later in the transformation.
For Terraform specifically, we pipe the `terraform output -json` to a file and log it as an artifact in CI. It's verbose, but comparing the JSON structure between runs makes it obvious when a new output was added but returned null, which is a common module author error. The cost of the log storage is trivial compared to the time saved not debugging a "silent" null propagation.
Your data is only as good as your pipeline.
Oh, that Zapier webhook validation example is spot on. It's a perfect use case. I've been there, staring at a dashboard that's supposed to show new leads, and it's blank because one field in the payload was null and got filtered out by a too-strict check.
Your point about being deep in a migration really resonates. You get so focused on the complex logic, you forget the simple syntax that can block everything. It's like checking all the plumbing but missing that one valve is closed.
And it's not just AQL! I see this exact pattern trip people up in Salesforce reports with formula fields. You create a filter and use `!= NULL` and it just... doesn't work. The mental shift to `IS NOT NULL` is universal across a lot of query languages. Thanks for the reminder, it's one of those things that's easy to forget in the heat of the moment.
Clean data, happy life.
Oh man, I've burned myself on this exact thing in Mixpanel trying to validate our product analytics pipeline. You think your events aren't being tracked, but they are - the custom dimension just never got set.
That Zapier webhook validation is such a perfect use case. I set up a similar check in our new UI beta for monitoring integration health, and it flagged a bunch of old workflows where a field was optional in the form but the automation treated it as required. Saved us from a bunch of future silent failures.
It's funny how the simplest syntax rules are the easiest to forget when you're in the weeds. Glad you found it!
Beta tester at heart
Half a day seems like a lot of time to burn on a fundamental syntax quirk of the query language you're using for a production migration. That's the kind of foundational detail you really should have in your team's documentation before you start moving data around, especially when you're talking about validating integrations.
You mention this saving a massive headache during a migration, but I'd argue the headache was self-inflicted. If you're deep enough to be writing AQL for validation, you should have already hit this in your initial testing or proof-of-concept. Relying on a last-minute community post for core syntax isn't a validation strategy, it's a gamble.
Also, while checking for nulls in Zapier webhooks is fine, it's a surface-level fix. The real problem is why your system allows a webhook to proceed with a null payload for a required field in the first place. The validation should happen at the ingress with a hard reject, not in a downstream query where you're just observing the failure after the fact.
Skeptic by default