SQL Dialects and Built-in Functions Questions

Situation: On a cross-DB pipeline migration I noticed a filter that returned rows in MySQL but zero rows in PostgreSQL, causing downstream joins to miss data.

Concrete data (table users):

-- contents
id | username   | active
1  | 'Alice'    | '1'
2  | 'bob'      | '0'
3  | 'CHARLIE'  | 'true'

Query developers wrote:

SELECT * FROM users WHERE active = true AND username = 'charlie';

Behavior:

• MySQL: returns row id=3. Reason: MySQL performs loose/implicit type coercion ('true' -> boolean-ish) and default collation is case-insensitive (so 'CHARLIE' = 'charlie').
• PostgreSQL: returns no rows. Reason: Postgres is strict about types (text 'true' ≠ boolean true) and string equality is case-sensitive by default.

Why this matters as a Data Engineer:

• Relying on implicit coercion or default collation leads to non-portable pipelines and hidden data-quality bugs when switching engines or running in different locales.

How to write portable filters:

• Use explicit casts for types:

WHERE (active::boolean IS TRUE)   -- Postgres
-- or for portability, normalize to a canonical value in ETL:
WHERE lower(active) IN ('1','t','true')

• Normalize case explicitly:

WHERE lower(username) = 'charlie'

• Avoid engine-specific implicit behavior; document required collation or use explicit COLLATE where supported:

-- Postgres example
WHERE username COLLATE "C" = 'charlie'

• Add tests (unit/integration) validating filters on representative DBs and include schema-level constraints (typed columns, enums) so downstream code can rely on consistent types.

Result: After converting active to a boolean column or normalizing values in ETL and changing filters to use explicit casts/lower(), queries behaved identically across MySQL and Postgres and pipeline failures stopped.

Situation: When an orders.amount column is INT, summing many rows can overflow the integer range and produce incorrect (wrapped) results. As a data engineer you must detect, prevent, and choose the correct target type for aggregation.

How to detect type/coercion problems

• Check metadata: inspect column type and DB-specific SUM behavior (some DBs widen automatically, others don't).
• Run sanity queries: compare MAX(amount) * COUNT(*) or approximate expected sum to SUM(amount) to spot anomalies.
• Use checks for overflow risk:

SELECT COUNT(*) AS rows,
       MAX(amount) AS max_amt,
       COUNT(*) * MAX(amount) AS theoretical_max
FROM orders;

• Log negative/implausible sums, run post-aggregation validations (e.g., compare daily sums to running totals).

How to fix

• Cast to a wider integer if values are integral and exactness matters:

SELECT SUM(CAST(amount AS BIGINT)) FROM orders;

• Cast to DECIMAL/NUMERIC when you need fractional precision (money) or exact fixed-point arithmetic:

SELECT SUM(CAST(amount AS DECIMAL(18,2))) FROM orders;

• In big-data engines (Spark, Presto), use safe aggregation helpers (e.g., Spark’s sum with LongType or DecimalType) or define schema with larger types upstream.

Which to prefer: BIGINT vs DECIMAL

• Use BIGINT when amounts are whole units and you only need integer sums — it's space- and compute-efficient and preserves integer semantics.
• Use DECIMAL/NUMERIC when amounts represent currency or require fractional precision; DECIMAL avoids floating-point errors and preserves exactness but uses more storage and can be slower.
• Choose scale/precision to cover maximum expected sum: DECIMAL(precision, scale) where precision > digits(max_sum).

Best practices

• Define schema with safe types at ingestion (prevent downstream fixes).
• Add automated checks (data quality tests) that flag sums near type bounds.
• Document assumptions (units, scale) and include type-casting near aggregation to make intent explicit.

Approach: expand the JSON array into one row per product per session (explode), filter NULL/empty, then aggregate distinct session counts per product. I'll show both Postgres (jsonb) and BigQuery; both handle NULL/empty explicitly.

Postgres (jsonb):

-- Count sessions where a product appears at least once
SELECT p.product_id, COUNT(DISTINCT s.session_id) AS sessions_count
FROM sessions s
-- ignore NULL products
CROSS JOIN LATERAL (
  SELECT jsonb_array_elements_text(s.products) AS product_id
) p
WHERE s.products IS NOT NULL
  AND jsonb_typeof(s.products) = 'array'
  AND (s.products <> '[]'::jsonb)
GROUP BY p.product_id
ORDER BY sessions_count DESC;

BigQuery (standard SQL):

SELECT product_id, COUNT(DISTINCT session_id) AS sessions_count
FROM sessions,
UNNEST(
  CASE
    WHEN products IS NULL THEN [] -- treat NULL as empty
    WHEN JSON_TYPE(products) != 'ARRAY' THEN [] 
    ELSE JSON_EXTRACT_ARRAY(products)
  END
) AS prod
CROSS JOIN UNNEST([JSON_VALUE(prod)]) AS product_id
GROUP BY product_id
ORDER BY sessions_count DESC;

Key points / edge cases:

• Explicitly handle NULL and non-array JSON to avoid errors.
• Empty arrays produce zero rows; NULL produces zero rows via guard.
• Deduplicate per session if same product appears multiple times using COUNT(DISTINCT session_id).

Optimization suggestions:

• Normalize: store purchases in a junction table (session_products(session_id, product_id)) during ingestion; best for query performance and indexing.
• If keeping JSON: use Postgres jsonb with GIN index on products to accelerate containment queries (e.g., products @> '["123"]'), but GIN doesn't help well for exploding/aggregations.
• In BigQuery: store product_ids as native ARRAY<STRING> instead of JSON string to enable efficient UNNEST and columnar compression; cluster/partition the table (partition by date) and cluster by product_id to speed aggregations.
• Pre-aggregate daily/session-level materialized views if queries are frequent.

Trade-offs:

• Normalization increases write complexity but gives best query performance and smaller storage for analytics.
• JSON/ARRAY keeps schema-flexibility but costs more CPU to parse at query time; prefer native arrays over serialized JSON when possible.

Postgres (JSONB)
To select rows where payload JSON has user_id = '42' (string or number), and handle missing keys:

SELECT *
FROM events
WHERE payload->>'user_id' = '42';

Safer variants:
-- ensure key exists (faster with indexes)
WHERE payload ? 'user_id' AND payload->>'user_id' = '42';

-- numeric comparison
WHERE (payload->>'user_id')::int = 42;

Notes (Postgres):

• payload->>'user_id' extracts text (or NULL if key missing); comparing to '42' is a text compare.
• Casting with ::int will raise if the value is non-numeric; use try/catch logic or WHERE (payload->>'user_id') ~ '^[0-9]+$' before casting.
• You can index with a GIN functional index: CREATE INDEX ON events USING gin ((payload ->> 'user_id'));

BigQuery (JSON_EXTRACT_SCALAR)
SELECT *
FROM project.dataset.events
WHERE JSON_EXTRACT_SCALAR(payload, '$.user_id') = '42';

Safer variants:
-- numeric comparison with safe cast
WHERE SAFE_CAST(JSON_EXTRACT_SCALAR(payload, '$.user_id') AS INT64) = 42

-- check key exists (returns NULL if missing)
AND JSON_EXTRACT_SCALAR(payload, '$.user_id') IS NOT NULL

Notes (BigQuery):

• JSON_EXTRACT_SCALAR returns a STRING or NULL if the path doesn't exist or value is JSON null.
• If user_id is a JSON number, JSON_EXTRACT_SCALAR returns its string lexeme (e.g., '42'); numeric comparisons require CAST/SAFE_CAST.
• SAFE_CAST prevents query failure on non-numeric strings (returns NULL instead).

Subtle differences & recommendations

• Both return NULL when the key is absent; both extract textual representations. Postgres ->> always yields text; BigQuery returns a string via JSON_EXTRACT_SCALAR.
• Casting behavior differs: Postgres ::int will error on bad input; BigQuery SAFE_CAST returns NULL. Use EXISTS checks or regex guards before casting in Postgres.
• Indexing: Postgres can index JSONB expressions (GIN or functional b-tree on (payload->>'user_id')). BigQuery requires partitioning/clustering or creating a derived column for performance.
• For reliability, prefer explicit existence checks and SAFE_CAST (BigQuery) or validation-before-cast (Postgres) when you need numeric comparison.

Approach: use PostgreSQL's POSIX regex match operators. Use . to match a literal dot and $ to anchor the end of the string. Use ~ for case-sensitive matching and ~* for case-insensitive matching.

Example SQL (case-sensitive):

-- returns emails that literally end with ".edu" or ".org" (case-sensitive)
SELECT email
FROM users
WHERE email ~ '\.(edu|org)$';

Example SQL (case-insensitive):

-- returns emails ending with .edu or .org regardless of case (e.g. .EDU, .Org)
SELECT email
FROM users
WHERE email ~* '\.(edu|org)$';

Key points / reasoning:

• . escapes the dot (otherwise . matches any character).
• (edu|org) is an alternation group for the two TLDs.
• $ ensures the TLD is at the end of the string (so "user@school.edu1" won't match).

Case-sensitive vs case-insensitive:

• ~ performs a case-sensitive POSIX regex match (so "user@X.edu" only matches if the case in pattern matches).
• ~* performs a case-insensitive match (ignores letter case).

Performance note:

• Regex filters generally can't use a plain b-tree index. For large tables, consider:
  • creating a trigram index (pg_trgm) for regex acceleration, or
  • storing/ indexing a lowercased email and querying with an expression index: CREATE INDEX ON users (lower(email)); then use WHERE lower(email) ~ '.(edu|org)$' for more efficient case-insensitive searches.