Line chart skips intermediate categories when x‑axis is string (but data exists)

### Bug description

Environment

• Superset version: v6.x


• Upgrade path: imported charts from v4 → v6


• Database: ClickHouse


• Chart type: Line chart

---

Description

When using a string dimension for the x‑axis (e.g. LEFT(toString(day_id), 6)), the line chart connects non‑adjacent categories even though intermediate categories exist in the dataset.

With a numeric dimension (toInt32(LEFT(...))), the chart renders correctly and includes all intermediate points.

---

Steps to Reproduce

1. Run the following query in SQL Lab:

-- INT version

SELECT

  toInt32(LEFT(toString(day_id), 6)) AS month_id,

  sum(value) AS total_value

FROM (

  SELECT

    toInt32(formatDateTime(d, '%Y%m%d')) AS day_id,

    toDayOfYear(d) AS value

  FROM (

    SELECT addDays(toDate('2024-01-01'), number) AS d

    FROM numbers(366) -- leap year

  )

  WHERE d < toDate('2024-05-15') OR d > toDate('2024-08-10')

)

GROUP BY month_id

ORDER BY month_id;

-- STRING version

SELECT

  LEFT(toString(day_id), 6) AS month_id,

  sum(value) AS total_value

FROM (

  SELECT

    toInt32(formatDateTime(d, '%Y%m%d')) AS day_id,

    toDayOfYear(d) AS value

  FROM (

    SELECT addDays(toDate('2024-01-01'), number) AS d

    FROM numbers(366)

  )

  WHERE d < toDate('2024-05-15') OR d > toDate('2024-08-10')

)

GROUP BY month_id

ORDER BY month_id;

1. Both queries return the following aggregated dataset:

month_id | SUM(value)
-- | --
202401 | 496
202402 | 1334
202403 | 2356
202404 | 3195
202405 | 1799
202408 | 4914
202409 | 7785
202410 | 8990
202411 | 9615
202412 | 10881

1. Create two line charts:
   
   
   
   • Chart A: x‑axis = month_id (INT)
   
   
   • Chart B: x‑axis = month_id (STRING)
   
   

---

Expected Behavior

Both charts should render a line through all categories present in the dataset, including 202408.

---

Actual Behavior

• INT chart: renders correctly → line goes 202405 → 202408 → 202409.


• STRING chart: skips 202408 and draws a line directly from 202405 → 202409, even though 202408 exists in the dataset.

---

Notes

• This behavior changed between Superset v4 and v6.


• In v4, both INT and STRING behaved the same.


• In v6, categorical (string) axes appear stricter, but this results in valid categories being skipped in line charts.

---

Query to reproduce Virtual Dataset:

```
SELECT
toInt32(formatDateTime(d, '%Y%m%d')) AS day_id,
toDayOfYear(d) AS value
FROM (
SELECT addDays(toDate('2024-01-01'), number) AS d
FROM numbers(366) -- 2024 is a leap year
)
WHERE d < toDate('2024-05-15')
OR d > toDate('2024-08-10')
ORDER BY day_id
```

### Screenshots/recordings

_No response_

### Superset version

master / latest-dev

### Python version

3.11

### Node version

18 or greater

### Browser

Chrome

### Additional context

_No response_

### Checklist

- [x] I have searched Superset docs and Slack and didn't find a solution to my problem.
- [x] I have searched the GitHub issue tracker and didn't find a similar bug report.
- [x] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.