Repository: risingwavelabs/risingwave (language: python)

Issue:
Sqlsmith: Sql feature generation
As we add more features, these should be tested via sqlsmith as well.

SQL Features:
- [ ] Generate other table functions: `generate_series`, `unnest`. See: https://github.com/singularity-data/risingwave/issues/4540
- [ ] Generate array_agg, jsonb_agg, jsonb_object_agg
- [ ] Generate select from System Tables
- [ ] Generate `NATURAL JOIN`
- [ ] Generate `JOIN` with `USING`
- [ ] Generate query w/o `GROUP BY`
- [ ] Randomly toggle session variables
- [ ] Generate `pow`/`exp`.
- [ ] Generate TopN by group: https://www.risingwave.dev/docs/current/sql-pattern-topn/
- [ ] Generate jsonb exprs, see: https://github.com/risingwavelabs/risingwave/issues/7714
- [ ] #7132
- [x] Generate order-by-clauses with `NULLS { FIRST | LAST }` (will be done in https://github.com/risingwavelabs/risingwave/pull/10864/commits/000cbc11cbc04f20abfd127b1bf4b3fd24e75c44)
- [ ] Generate `max`,`min`,`count` for `timestamptz`
- [ ] Generate `bit_and`, `bit_or` and `big_xor` in batch queries, and `bit_xor` in streaming queries.
- [ ] Generate array range access.
- [ ] https://github.com/risingwavelabs/risingwave/issues/10241
- [ ] Generate window functions other than `row_number`, `rank`.
- [ ] Generate `ALTER` DDL statements.\
- [ ] https://github.com/risingwavelabs/risingwave/issues/10516
- [ ] Generate temporal filter in sqlsmith


Discussion / hints:

Fix patch:
diff --git a/src/frontend/src/binder/expr/function/mod.rs b/src/frontend/src/binder/expr/function/mod.rs
index 64e95e518c72c..b716f7c1f81ae 100644
--- a/src/frontend/src/binder/expr/function/mod.rs
+++ b/src/frontend/src/binder/expr/function/mod.rs
@@ -209,36 +209,6 @@ impl Binder {
             ) {
             // record the dependency upon the UDF
             referred_udfs.insert(func.id);
-
-            if func.language == "sql" {
-                let name = format!("SQL user-defined function `{}`", func.name);
-                reject_syntax!(
-                    scalar_as_agg,
-                    format!("`AGGREGATE:` prefix is not allowed for {}", name)
-                );
-                reject_syntax!(
-                    !arg_list.is_args_only(),
-                    format!(
-                        "keywords like `DISTINCT`, `ORDER BY` are not allowed in {} argument list",
-                        name
-                    )
-                );
-                reject_syntax!(
-                    within_group.is_some(),
-                    format!("`WITHIN GROUP` is not allowed in {} call", name)
-                );
-                reject_syntax!(
-                    filter.is_some(),
-                    format!("`FILTER` is not allowed in {} call", name)
-                );
-                reject_syntax!(
-                    over.is_some(),
-                    format!("`OVER` is not allowed in {} call", name)
-                );
-                return self.bind_sql_udf(func.clone(), args);
-            }
-
-            // now `func` is a non-SQL user-defined scalar/aggregate/table function
             Some(func.clone())
         } else {
             None
@@ -289,7 +259,7 @@ impl Binder {
             return self.bind_window_function(kind, args, arg_list.ignore_nulls, filter, over);
         }
 
-        // now it's a aggregate/scalar/table function call
+        // now it's an aggregate/scalar/table function call
         reject_syntax!(
             arg_list.ignore_nulls,
             "`IGNORE NULLS` is not allowed in aggregate/scalar/table function call"
@@ -371,6 +341,9 @@ impl Binder {
                     "`VARIADIC` is not allowed in table function call"
                 );
                 self.ensure_table_function_allowed()?;
+                if udf.language == "sql" {
+                    return self.bind_sql_udf(udf.clone(), args);
+                }
                 return Ok(TableFunction::new_user_defined(udf.clone(), args).into());
             }
             // builtin table function
@@ -391,6 +364,9 @@ impl Binder {
                 arg_list.variadic,
                 "`VARIADIC` is not allowed in user-defined function call"
             );
+            if udf.language == "sql" {
+                return self.bind_sql_udf(udf.clone(), args);
+            }
             return Ok(UserDefinedFunction::new(udf.clone(), args).into());
         }
 


Test patch:
diff --git a/e2e_test/udf/drop_function.slt b/e2e_test/udf/drop_function.slt
index b7d36bd97627a..ffe4e0eea481f 100644
--- a/e2e_test/udf/drop_function.slt
+++ b/e2e_test/udf/drop_function.slt
@@ -10,17 +10,25 @@ def add(a, b):
 $$;
 
 statement ok
-create materialized view mv as select add(a, b) as c from t;
+create function add_v2(INT, INT) returns int language sql as $$select $1 + $2$$;
+
+statement ok
+create materialized view mv as select add(a, b) + add_v2(a, b) as c from t;
 
 statement error function used by 1 other objects
 drop function add;
 
+statement error function used by 1 other objects
+drop function add_v2;
+
 statement ok
 drop materialized view mv;
 
 statement ok
 drop function add;
 
+statement ok
+drop function add_v2;
 
 statement ok
 create function add(a int, b int) returns int language python as $$