Invalid type error in a Spider2 Lite example.

[JimXiongGM]

The SQL query in the file Spider2/spider2-lite/baselines/utils/2-shot-Snowflake.txt for the question What is the total number of severe storm events that occurred in the most affected month over the past 15 years according to NOAA records, considering only the top 100 storm events with the highest property damage? is currently failing with the following error:

invalid type [CAST("values"."event_begin_time" AS DATE)] for parameter 'TO_DATE'

I think the issue arises because the event_begin_time field is being cast incorrectly. The current query uses:

"event_begin_time"::Date

This needs to be updated to:

TO_TIMESTAMP("event_begin_time" / 1000000)

to properly handle the timestamp conversion.

So the correct one should be:

WITH base_info AS (
  SELECT
    "episode_id", 
    CONCAT(CAST(EXTRACT(MONTH FROM MIN(TO_TIMESTAMP("event_begin_time" / 1000000))) AS STRING), '-', CAST(EXTRACT(YEAR FROM MIN(TO_TIMESTAMP("event_begin_time" / 1000000))) AS STRING)) AS "episode_month",
    EXTRACT(MONTH FROM MIN(TO_TIMESTAMP("event_begin_time" / 1000000))) AS "month",
    SUM("damage_property") / 1000000000 AS "damage_property_in_billions"
  FROM (
    SELECT * FROM noaa_historic_severe_storms.storms_2010
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2011
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2012
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2013
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2014
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2015
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2016
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2017
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2018
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2019
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2020
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2021
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2022
    UNION ALL
    SELECT * FROM noaa_historic_severe_storms.storms_2023
  )
  WHERE
    EXTRACT(YEAR FROM TO_TIMESTAMP("event_begin_time" / 1000000)) BETWEEN (EXTRACT(YEAR FROM CURRENT_DATE()) - 15) AND EXTRACT(YEAR FROM CURRENT_DATE())
  GROUP BY
    "episode_id"
  ORDER BY
    "damage_property_in_billions" DESC
  LIMIT 100
)

SELECT COUNT(*) AS "month_count"
FROM base_info
GROUP BY "month"
ORDER BY "month_count" DESC
LIMIT 1

[lfy79001]

Thank you for your suggestion! We will promptly correct the mistakes. Regarding baselines like DailSQL, we think they are somewhat outdated. We recommend using the Spider-Agent baseline instead.