[SQL] Ignore case when casting string to Boolean

mihaibudiu · mihaibudiu · commit 5c8576f56732 · 2025-12-31T18:55:15.000Z
Signed-off-by: Mihai Budiu &lt;mbudiu@feldera.com&gt;
diff --git a/crates/sqllib/src/casts.rs b/crates/sqllib/src/casts.rs
@@ -314,7 +314,7 @@ cast_to_b!(u, usize);
 #[doc(hidden)]
 #[inline]
 pub fn cast_to_b_s(value: SqlString) -> SqlResult<bool> {
-    Ok(value.str().trim().parse().unwrap_or(false))
+    Ok(value.str().to_lowercase().trim().parse().unwrap_or(false))
 }
 
 #[doc(hidden)]
diff --git a/docs.feldera.com/docs/sql/boolean.md b/docs.feldera.com/docs/sql/boolean.md
@@ -161,6 +161,9 @@ The following table defines the `IS NOT TRUE` operator:
   </tr>
 </table>
 
+Casting a string to a Boolean value produces the result *true* for any
+string which spells 'true' when converted to lowercase.  Any other
+non-null string produces the result *false*.
 
 :::info
 
diff --git a/sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/Regression1Tests.java b/sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/Regression1Tests.java
@@ -1589,7 +1589,6 @@ public void limitDuplicate() {
 
     @Test
     public void issue5345() {
-        this.showPlan();
         var ccs = this.getCCS("""
                 CREATE MATERIALIZED VIEW t1 AS
                     SELECT DISTINCT
@@ -1608,4 +1607,83 @@ public void issue5345() {
                  a| 1 | { by} | true  | 1
                  b| 1 | {}    | false | 1""");
     }
+
+    @Test
+    public void issue5352() {
+        var ccs = this.getCCS("""
+                CREATE TABLE tbl(str VARCHAR);
+                
+                CREATE MATERIALIZED VIEW v AS SELECT
+                str::BOOLEAN IS FALSE AS arr,
+                str::BOOLEAN IS TRUE AS arr1
+                FROM tbl;""");
+        ccs.step("INSERT INTO TBL values('TRUE')", """
+                 arr | arr1 | weight
+                ---------------------
+                 false | true | 1""");
+    }
+
+    @Test
+    public void issue5352_a() {
+        this.qs("""
+             SELECT 'TRUE'::BOOLEAN;
+              r
+             ---
+              true
+             (1 row)
+             
+             SELECT 'true'::BOOLEAN;
+              r
+             ---
+              true
+             (1 row)
+             
+             SELECT 'TrUe'::BOOLEAN;
+              r
+             ---
+              true
+             (1 row)
+             
+             SELECT 't'::BOOLEAN;
+              r
+             ---
+              false
+             (1 row)
+             
+             SELECT '1'::BOOLEAN;
+              r
+             ---
+              false
+             (1 row)
+             
+             SELECT '0'::BOOLEAN;
+              r
+             ---
+              false
+             (1 row)
+             
+             SELECT 'yes'::BOOLEAN;
+              r
+             ---
+              false
+             (1 row)
+             
+             SELECT ''::BOOLEAN;
+              r
+             ---
+              false
+             (1 row)
+             
+             SELECT 'NULL'::BOOLEAN;
+              r
+             ---
+              false
+             (1 row)
+             
+             SELECT NULL::BOOLEAN;
+              r
+             ---
+             NULL
+             (1 row)""");
+    }
 }

Original file line number	Diff line number	Diff line change
`@@ -314,7 +314,7 @@ cast_to_b!(u, usize);`
`314`	`314`	`#[doc(hidden)]`
`315`	`315`	`#[inline]`
`316`	`316`	`pub fn cast_to_b_s(value: SqlString) -> SqlResult<bool> {`
`317`		`- Ok(value.str().trim().parse().unwrap_or(false))`
	`317`	`+ Ok(value.str().to_lowercase().trim().parse().unwrap_or(false))`
`318`	`318`	`}`
`319`	`319`
`320`	`320`	`#[doc(hidden)]`