异常SQL行为：无关计算列导致变量无法正确赋值

Let’s break down why your two SQL queries might be returning drastically different results, using the table details you shared as our starting point:

Key Table Context #

mips Table #

• Indexed on the time field
• Contains paired metric fields: time, rtt_good, rtt_bad, rexb_good, rexb_bad, nae_good, nae_bad, etc. (each measured metric has a "_good" and "_bad" variant)

metrics Table #

• Indexed on time, asn, cty, source
• Has an unrelated computed column that causes abnormal SQL behavior where variables fail to assign correctly

Likely Causes for Result Discrepancies #

1. Index Usage Mismatches #

Since both tables use time as an indexed field, small differences in how your queries filter on time can lead to huge result gaps:

• A query like SELECT * FROM mips WHERE time BETWEEN '2024-01-01' AND '2024-01-02' will leverage the time index efficiently, pulling only relevant rows.
• A query like SELECT * FROM mips WHERE DATE(time) = '2024-01-01' might bypass the index entirely (due to the function applied to time) and do a full table scan—this could miss rows if there’s timezone drift, index fragmentation, or unindexed time values.

2. Inconsistent Handling of _good/_bad Pairs #

If your queries treat these paired metric fields differently, that’s a direct source of skewed results:

• Double-check if you’re aggregating consistently: one query might sum rtt_good + rtt_bad for total latency, while the other only uses rtt_good.
• Watch for NULL values: if one query uses COALESCE(rtt_bad, 0) to handle missing data and the other doesn’t, your totals will be off.

3. The metrics Table’s Computed Column Anomaly #

This is a critical red flag—broken variable assignment can derail your entire query logic:

• If your queries rely on variables (e.g., @start_time = '2024-01-01') for filtering or joins, the faulty computed column might be corrupting variable scope or evaluation.
• Computed columns can also warp execution plans unexpectedly. Try running EXPLAIN ANALYZE on both queries to see if one uses the composite time/asn/cty/source index while the other falls back to a full scan.

4. Join Logic Inconsistencies (If Queries Use Both Tables) #

If your queries join mips and metrics, mismatched join rules will change row counts drastically:

• Are you joining on time alone, or including asn/cty/source from metrics? Missing a key join field could create a Cartesian product (way too many rows) or exclude valid matches.
• Swapping INNER JOIN and LEFT JOIN will also shift results: an inner join only keeps rows with matches in both tables, while a left join retains all rows from the first table regardless of matches.

Next Debugging Steps #

• Run EXPLAIN ANALYZE on both queries to compare execution plans—look for differences in index usage, rows scanned, and join types.
• Simplify queries step by step: remove aggregations, filters, or joins one at a time to isolate which part is causing the gap.
• Test variable assignment directly in the metrics table: run something like SET @test_var = 'test'; SELECT @test_var, * FROM metrics LIMIT 1; to confirm if variables are working as expected.
• Validate NULL handling: check if _good/_bad fields have missing values and ensure both queries handle them the same way.

内容的提问来源于stack exchange，提问作者stevendesu