Cast variant to boolean in external table

Question

I have an external table on top of a csv file:

create or replace external table PAGES
(
near VARIANT as (nullif(value:c1,null)::VARIANT) 
)
with location = @test_stage
file_format = test_file_format
pattern = '.*[.]csv';


select * from PAGES;

It gives me result as

"None"
"True"
"False"
"None"

I want my table to be like:

create or replace external table PAGES
(
near BOOLEAN as (nullif(value:c1,null)::BOOLEAN) 
)
with location = @test_stage
file_format = test_file_format
pattern = '.*[.]csv';


select * from PAGES;

but it gives me error: Failed to cast variant value "None" to BOOLEAN

How can I make it work with boolean.

My stage and file format looks like:

create or replace file format test_file_format type = 'csv' field_delimiter = ','
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"' ESCAPE = '\\' 
empty_field_as_null=TRUE;


  
create or replace stage oncrawl_stage url='s3://unload-dev/'
  file_format = test_file_format
  storage_integration=snowflake_s3_integration;

Answer 1

One of the rows should return "None" (JS Null) to get this error. Otherwise the DDL should work:

select VALUE from pages;

+---------------------+
|        VALUE        |
+---------------------+
| {   "c1": "True" }  |
| {   "c1": "False" } |
| {   "c1": "None" }  |
+---------------------+

select * from pages;

Failed to cast variant value "None" to BOOLEAN

Make sure you set USE_CACHED_RESULT to false to prevent unindented caching on your tests!

PS: Why do you use nullif(value:c1,null)? It means "return NULL if value:c1 is NULL"!

Can you try this one?

create or replace external table PAGES
(
near BOOLEAN as (decode(value:c1,'None',False,value:c1)::BOOLEAN)
)
with location = @test_stage
file_format = test_file_format
;