InfluxDB 3.x SQL vs InfluxQL compatibility for migrated queries

Direct Answer

InfluxQL in 3.x is a native reimplementation on the Arrow/Parquet engine — DBRP mappings are ignored, continuous queries and retention policies are removed, and several InfluxQL functions return different results due to columnar storage ordering and null-handling changes. Flux is entirely unsupported; no automated Flux-to-SQL translation exists. The influxdb3-migration CLI (alpha) checks schema and line-protocol compatibility only; it does not validate query semantics.

Confirmed Behavioral Differences (2.x → 3.x)

• DBRP layer gone: Queries must reference databases and measurements directly; DBRP mappings from 2.x are not consulted.
• Continuous queries & retention policies: Not supported. Downsampling must be replaced with external tooling (Telegraf tasks) or 3.x SQL scheduled queries (UTC-only timezone handling in early releases — verify for your version).
• Time-series functions: DERIVATIVE, INTEGRAL, SPREAD, NON_NEGATIVE_DERIVATIVE exist but may produce different numeric results because the columnar engine orders and fills gaps differently.
• Windowing: GROUP BY time() aligns with SQL TIME_BUCKET but boundary inclusion/exclusion differs; test at DST transitions.
• Schema-on-write: Field types are enforced at write. Type mismatches that returned empty results in 2.x now raise errors. Tag/field distinction is strict.
• INTO clause: Not implemented in 3.x InfluxQL; use SQL INSERT INTO ... SELECT or client-side writes.
• Regex: SQL uses RE2 syntax; InfluxQL retains 2.x regex behavior but may be slower on columnar storage.
• Special characters in field keys: Require double-quoting in SQL; may break InfluxQL parsing.
• Clustered vs single-node: JOIN across partitions only works in clustered deployments.

Systematic Identification & Remediation Steps

1. Spin up a 3.x test instance (Docker: docker run -p 8181:8181 influxdb:3-core or Cloud free tier).
2. Export representative 2.x line protocol and run influxdb3-migration check-schema to surface field-type conflicts before migration.
3. Run a query suite against both 2.x and 3.x instances. Compare row counts, timestamps, and numeric precision for each query. Focus on windowed aggregations, derivatives, and timezone-sensitive buckets.
4. Enable 3.x query logging (--log-level=debug) to see parsed InfluxQL/SQL plans and identify rewrites or failures.
5. Manually translate Flux queries to SQL using EXPLAIN on the Flight SQL endpoint to verify partition pruning and pushdown behavior.
6. Test at DST boundaries for any GROUP BY time(1d) or TIME_BUCKET queries; 3.x stores timestamps as UTC nanoseconds and buckets in UTC unless a session TIMEZONE is set in SQL.
7. Update client drivers: Python influxdb3-python>=0.7.0, Go github.com/influxdata/influxdb3-go/v2>=v2.0.0 for Flight SQL parameter binding.

Migration Tooling & Validation

No official query-result comparator exists. The influxdb3-migration tool (alpha) validates schema and line-protocol compatibility only. Teams must perform manual result comparison on representative time ranges. For Flux workloads, plan a rewrite to SQL (preferred for joins, pivots, unions) or InfluxQL (simpler aggregations) — there is no automated path.

One Diagnostic Detail Needed

Are you targeting InfluxDB 3.x Cloud or a self-hosted single-node/clustered deployment? Clustered deployments enable cross-partition JOIN and may have different scheduler behavior for SQL scheduled queries; this changes the remediation path for downsampling and join-heavy Flux queries.