Risingwave

For installation and running instructions, see [Get started](https://www.risingwave.dev/docs/latest/get-started/).

Main changes

SQL features

- SQL commands:

- `DROP` commands now support the `CASCADE` option, which drops the specified item and all its dependencies. [11250](https://github.com/risingwavelabs/risingwave/pull/11250)

- `CREATE TABLE` now supports the `APPEND ONLY` clause, allowing the definition of watermark columns on the table. [11233](https://github.com/risingwavelabs/risingwave/pull/11233)

- Supports new commands `START TRANSACTION`, `BEGIN`, and `COMMIT` for read-only transactions. [10735](https://github.com/risingwavelabs/risingwave/pull/10735)

- Supports `SHOW CLUSTER` to show the details of your RisingWave cluster, including the address of the cluster, its state, the parallel units it is using, and whether it's streaming data, serving data or unschedulable. [10656](https://github.com/risingwavelabs/risingwave/pull/10656), [#10932](https://github.com/risingwavelabs/risingwave/pull/10932)

- SQL functions:

- Supports new window functions: `lead()` and `lag()`. [10915](https://github.com/risingwavelabs/risingwave/pull/10915)

- Supports new aggregate functions: `first_value()` and `last_value()`, which retrieve the first and last values within a specific ordering from a set of rows. [10740](https://github.com/risingwavelabs/risingwave/pull/10740)

- Supports the `grouping()` function to determine if a column or expression in the `GROUP BY` clause is part of the current grouping set or not. [11006](https://github.com/risingwavelabs/risingwave/pull/11006)

- Supports the `set_config()` system administration function. [11147](https://github.com/risingwavelabs/risingwave/pull/11147)

- Supports the `sign()` mathematical function. [10819](https://github.com/risingwavelabs/risingwave/pull/10819)

- Supports `string_agg()` with `DISTINCT` and `ORDER BY`, enabling advanced string concatenation with distinct values and custom sorting. [10864](https://github.com/risingwavelabs/risingwave/pull/10864)

- Supports the co-existence of `string_agg()` and other aggregations with `DISTINCT`. [10864](https://github.com/risingwavelabs/risingwave/pull/10864)

- Supports the `zone_string` parameter in the `date_trunc()`, `extract()`, and `date_part()` functions, ensuring compatibility with PostgreSQL. [10480](https://github.com/risingwavelabs/risingwave/pull/10480)

- **Breaking change**: Previously, when the input for `date_trunc` was actually a date, the function would cast it to a timestamp and record the choice in the query plan. However, after this release, new query plans will cast the input to `timestamptz` instead. As a result, some old SQL queries, especially those saved as views, may fail to bind correctly and require type adjustments. It's important to note that old query plans will still continue working because the casting choice is recorded with a cast to timestamp.

Before this release:

sql
SELECT date_trunc('month', date '2023-03-04');

date_trunc
---------------------------
2023-03-01 00:00:00
(1 row)


After this release:

sql
SELECT date_trunc('month', date '2023-03-04');

date_trunc
---------------------------
2023-03-01 00:00:00+00:00
(1 row)


Now, the result of `date_trunc` includes the timezone offset (`+00:00`) in the output, making it consistent with the behavior in PostgreSQL.

- `round()` now accepts a negative value and rounds it to the left of the decimal point. [10961](https://github.com/risingwavelabs/risingwave/pull/10961)

- `to_timestamp()` now returns `timestamptz`. [11018](https://github.com/risingwavelabs/risingwave/pull/11018)

- Query clauses

- `SELECT` now supports the `EXCEPT` clause which excludes specific columns from the result set. [10438](https://github.com/risingwavelabs/risingwave/pull/10438), [#10723](https://github.com/risingwavelabs/risingwave/pull/10723)

- `SELECT` now supports the `GROUPING SETS` clause which allows users to perform aggregations on multiple levels of grouping within a single query. [10807](https://github.com/risingwavelabs/risingwave/pull/10807)

- Supports index selection for temporal joins. [11019](https://github.com/risingwavelabs/risingwave/pull/11019)

- Supports `CUBE` in group-by clauses to generate multiple grouping sets. [11262](https://github.com/risingwavelabs/risingwave/pull/11262)

- Patterns
- Supports multiple rank function calls in TopN by group. [11149](https://github.com/risingwavelabs/risingwave/pull/11149)

- System catalog

- Supports querying `created_at` and `initialized_at` from RisingWave relations such as sources, sinks, and tables in RisingWave catalogs. [11199](https://github.com/risingwavelabs/risingwave/pull/11199)

Connectors

- Supports specifying Kafka parameters when creating a source or sink. [11203](https://github.com/risingwavelabs/risingwave/pull/11203)

- JDBC sinks used for upserts must specify the downstream primary key via the `primary_key` option. [11042](https://github.com/risingwavelabs/risingwave/pull/11042)

- `access_key` and its corresponding `secret_key` are now mandatory for all AWS authentication components. [11120](https://github.com/risingwavelabs/risingwave/pull/11120)

**Full Changelog**: https://github.com/risingwavelabs/risingwave/compare/v1.0.0...v1.1.0

For installation and running instructions, see [Get started](https://www.risingwave.dev/docs/latest/get-started/).

Main changes

SQL features

- SQL command:

- Supports the `SHOW CLUSTERS` command. [10656](https://github.com/risingwavelabs/risingwave/pull/10656)

- Supports the `GROUPING SETS` clause. [10807](https://github.com/risingwavelabs/risingwave/pull/10807)

- SQL function:

- Adds the `current_setting()` function to get the current value of a configuration parameter. [10051](https://github.com/risingwavelabs/risingwave/issues/10051)

- Adds new array functions: `array_position()`, `array_replace()`, `array_ndims()`, `array_lower()`, `array_upper()`, `array_length()`, and `array_dims()`. [10166](https://github.com/risingwavelabs/risingwave/pull/10166), [#10197](https://github.com/risingwavelabs/risingwave/pull/10197)

- Adds new aggregate functions: `percentile_cont()`, `percentile_disc()`, and `mode()`. [10252](https://github.com/risingwavelabs/risingwave/pull/10252)

- Adds new system functions: `user()`, `current_user()`, and `current_role()`. [10366](https://github.com/risingwavelabs/risingwave/pull/10366)

- Adds new string functions: `left()` and `right()`. [10765](https://github.com/risingwavelabs/risingwave/pull/10765)

- Adds new bytea functions: `octet_length()` and `bit_length()`. [10462](https://github.com/risingwavelabs/risingwave/pull/10462)

- `array_length()` and `cardinality()` return integer instead of bigint. [10267](https://github.com/risingwavelabs/risingwave/pull/10267)

- Supports the `row_number` window function that doesn't match the TopN pattern. [10869](https://github.com/risingwavelabs/risingwave/pull/10869)

- User-defined function:

- Adds support for defining UDFs in Java. [10095](https://github.com/risingwavelabs/risingwave/pull/10095)

- Adds support for more Java UDF and Python UDF data types. [10399](https://github.com/risingwavelabs/risingwave/pull/10399)

- The language parameter is no longer required in `CREATE FUNCTION`. [10608](https://github.com/risingwavelabs/risingwave/pull/10608)

- System catalog:

- Adds more columns to `information_schema.columns`: `column_default`, `character_maximum_length`, and `udt_name`. [10269](https://github.com/risingwavelabs/risingwave/pull/10269)

- Adds a new system catalog `pg_proc`. [10216](https://github.com/risingwavelabs/risingwave/pull/10216)

- Adds new RisingWave catalogs:

- `rw_table_fragments`, `rw_fragments`, `rw_actors` [10712](https://github.com/risingwavelabs/risingwave/pull/10712)
- `rw_worker_nodes`, `rw_parallel_units` [10656](https://github.com/risingwavelabs/risingwave/pull/10656)
- `rw_connections`, `rw_databases`, `rw_functions`, `rw_indexes`, `rw_materialized_views`, `rw_schemas`, `rw_sinks`, `rw_sources`, `rw_tables`, `rw_users`, `rw_views` [10593](https://github.com/risingwavelabs/risingwave/pull/10593)

- Supports `GROUP BY` output alias or index. [10305](https://github.com/risingwavelabs/risingwave/pull/10305)

- Supports using scalar functions in the `FROM` clause. [10317](https://github.com/risingwavelabs/risingwave/pull/10317)

- Supports tagging the created VPC endpoints when creating a PrivateLink connection. [10582](https://github.com/risingwavelabs/risingwave/pull/10582)

Connectors

- ***Breaking change***: When creating a source or table with a connector whose schema is auto-resolved from an external format file, the syntax for defining primary keys within column definitions is replaced with the table constraint syntax. [10195](https://github.com/risingwavelabs/risingwave/pull/10195)

sql title="Old"
CREATE TABLE debezium_non_compact (order_id int PRIMARY KEY) WITH (
connector = 'kafka',
kafka.topic = 'debezium_non_compact_avro_json',
kafka.brokers = 'message_queue:29092',
kafka.scan.startup.mode = 'earliest'
) ROW FORMAT DEBEZIUM_AVRO ROW SCHEMA LOCATION CONFLUENT SCHEMA REGISTRY 'http://message_queue:8081';


sql title="New"
CREATE TABLE debezium_non_compact (PRIMARY KEY(order_id)) WITH ( ...


- ***Breaking change***: Modifies the syntax for specifying data and encoding formats for a source in `CREATE SOURCE` and `CREATE TABLE` commands. For v1.0.0, the old syntax is still accepted but will be deprecated in the next release. [10768](https://github.com/risingwavelabs/risingwave/pull/10768)

Old syntax - part 1:

sql
ROW FORMAT data_format
[ MESSAGE 'message' ]
[ ROW SCHEMA LOCATION ['location' | CONFLUENT SCHEMA REGISTRY 'schema_registry_url' ] ];


New syntax - part 1:

sql
FORMAT data_format ENCODE data_encode (
message = 'message',
schema_location = 'location' | confluent_schema_registry = 'schema_registry_url'
);


Old syntax - part 2:

sql
ROW FORMAT csv WITHOUT HEADER DELIMITED BY ',';


New syntax - part 2:

sql
FORMAT PLAIN ENCODE CSV (
without_header = 'true',
delimiter = ','
);


- Supports sinking data to Delta Lake. [10374](https://github.com/risingwavelabs/risingwave/pull/10374), [#10580](https://github.com/risingwavelabs/risingwave/pull/10580)

- Supports sinking data to AWS Kinesis. [10437](https://github.com/risingwavelabs/risingwave/pull/10437)

- Supports `BYTES` as a row format. [10592](https://github.com/risingwavelabs/risingwave/pull/10592)

- Supports specifying schema for the PostgreSQL sink. [10576](https://github.com/risingwavelabs/risingwave/pull/10576)

- Supports using the user-provided publication to create a PostgreSQL CDC table. [10804](https://github.com/risingwavelabs/risingwave/pull/10804)

**Full Changelog**: https://github.com/risingwavelabs/risingwave/compare/v0.19.0...v1.0.0