Convert Column Type Failed

[coloraven]

What happens?

The phone column in the test table contains the following data: ["123949", "sdkkd", "ppd23f", "56565652"]. I'm trying to delete the records that are not purely numeric and then convert the column to the BIGINT type. Here are the steps I followed:

1. First, I used DELETE FROM test WHERE NOT regexp_matches(phone, '^\d{1,}$'); to delete the non-numeric records.
2. Then I used SELECT * FROM test; to check the results after the deletion, and I can only see only the numbers remaining.
3. Finally, I used ALTER TABLE test ALTER phone TYPE BIGINT; try to convert the column type to BIGINT. However, I received an error message: Conversion Error: Could not convert string 'sdkkd' to INT64.

At first, I thought the DELETE operation didn't fully take effect, but even after disconnecting and reconnecting the data, the same error still occurred.

I'm not sure what the problem is.

To Reproduce

CREATE TABLE test (
  phone VARCHAR(10)
);

INSERT INTO test (phone) VALUES
  ('123949'),
  ('sdkkd'),
  ('ppd23f'),
  ('56565652');

-- To delete the non-numeric records
DELETE FROM test WHERE NOT regexp_matches(phone, '^\d{1,}$');

-- To check the results
SELECT * FROM test;

-- Try to convert the column type to `BIGINT`
ALTER TABLE test ALTER phone TYPE BIGINT;
-- SQL error: Conversion Error: Could not convert string 'sdkkd' to INT64

OS:

WIN_x64

DuckDB Version:

v0.10.2

DuckDB Client:

DBEAVER

Full Name:

john

Affiliation:

ignore

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[cmdlineluser]

I am not sure what the problem is - but I did notice that this avoids the error:

ALTER TABLE test ALTER phone TYPE BIGINT USING try_cast(phone as BIGINT)

[Tishj]

I suspect running a CHECKPOINT after the delete, before the alter, fixes the issue (as a temporary workaround)