One argument I have against using columns in the constraint is that we currently are not able to actually handle the CHECK spec in SQL. In SQL the CHECK expression is a valid search expression (eg. price > 42 AND tag = ‘foo’), and is limited to the current row.
Where we differ in Apache Cassandra 1) the mutation might not include the columns referenced in the search expression: price numeric CHECK (name IS NOT NULL) (requires each mutation to define all columns referenced in any constraint) 2) our current checks are not normal search expressions (that limit functions to deterministic ones), its a allowed listed set of expressions (fixable) If we actually want to be the SQL version of constraints then we need to put more restrictions on what people can do. For example price numeric CHECK (length(name) > 42) This constraint can only be handled if both price and name are present in the query, so we would need to force all mutations to have price and name, as we wouldn’t know the value of name in the following query INSERT INTO tbl (pk, ck, price) VALUES (0, 0, 0); So, do we put such a limitation on users and force them to define every column present in a constraint, or do we break the SQL spec? If we choose to break the spec, then why allow column names in the search expressions? Only “this” column is safe > On Apr 14, 2025, at 1:24 PM, Benedict <bened...@apache.org> wrote: > > If we have a goal of eventually providing ANSI SQL support one day, we should > at least stick to the ANSI SQL standard where applicable for features in the > meantime. AFAICT the reason everyone else does this the same is it is part of > the standard. So, I am more than happy to stick to the CHECK qualifier for > all of our unique extensions, but for NOT NULL we should absolutely follow > the SQL standard, and for the same reason we should use the field name in the > CHECK expression. > >> On 14 Apr 2025, at 21:10, Josh McKenzie <jmcken...@apache.org> wrote: >> >> >> Consistency within our own ecosystem supersedes consistency with other >> familiar ecosystems IMO. >> >> I'd prefer we consistently apply the CHECK keyword and don't have special >> cases that omit it, or perhaps have those as optional syntactic sugar but at >> its base the syntax is uniform and consistent. >> >> On Mon, Apr 14, 2025, at 3:31 PM, Štefan Miklošovič wrote: >>> To be honest, I agree with Bernardo here. >>> >>> The requirement to have "val int not null" makes sense at first, but it is >>> not so straightforward. >>> >>> I think that what we are trying to do here is to "copy like they have it" >>> because ... well, because they have it like that. And we do not want to >>> differ too much, because we like adoption and if it is easier for newcomers >>> who are used to some style already, better to support it as well in this >>> case because, why not, right? >>> >>> Yeah, right ... but we are in a different position than SQL implementation. >>> I already said it, but I think that what happened first was that they >>> started to support "val int not null" and then they had a need to support >>> checks as well, so they slapped "val int not null check ..." on that. They >>> could not start from scratch, but we can. So not supporting "val int not >>> null" in favor of "val int check not null" makes more sense to me. So what >>> if it is different? It is at least consistent. >>> >>> This is a broader discussion to have though. AFAIK, we do not have any >>> official policy which would command us to follow SQL as closely as >>> possible. We have never agreed on following it like SQL has it officially. >>> So "doing it like they have it" as it most a "nice to have" but not a >>> strict requirement. >>> >>> On the other hand, if we ever codified that "we are following SQL" then >>> everything would need to follow it to the t. Not only constraints, every >>> other new feature which would change CQL would need to behave like that. >>> >>> We do not have this policy anywhere afaik so when it comes to that I do not >>> think that supporting "val int not null" is necessary. I think that their >>> mixture of two styles is actually wrong and misleading. Having one way to >>> specify it with "val int check ..." is just a superior solution here imho. >>> >>> On Mon, Apr 14, 2025 at 9:14 PM Bernardo Botella >>> <conta...@bernardobotella.com <mailto:conta...@bernardobotella.com>> wrote: >>> Now this is becoming a really interesting discussion. Thanks everyone for >>> pitching in! >>> >>> Here is my take on some of the proposed changes: >>> >>> We are talking about treating some constraints (NOT_NULL, JSON) as special >>> cases by omitting the CHECK keyword (not reserved as per current >>> implementation). Now, while this may seem like a nice approach to feature >>> gaps on our CQL, it really worries me that by doing so we open the door to >>> not needed complexity both at implementation and conceptually with the >>> constraints framework. >>> >>> In my mind, what’s the constraints framework? It is a simple and really >>> easy to extend integration point for validators for a row. (LENGHT, SCALAR, >>> REGEX, are really good examples of it). >>> >>> What’s NOT the responsibility of the constraints framework? I don’t think >>> this should be used to deliver partial solutions to feature gaps on CQL >>> data modeling. Let’s take JSON constraint as an example. In the constraints >>> case, it is as simple as checking that the provided string is valid json. >>> Easy. Simple. But, how would JSON look like if it was a first class citizen >>> in CQL? Setting the grammar aside, it would be handled differently >>> probably. Things like: Can we store it better? Do we allow making queries >>> for fields inside the json blob? Are there any optimizations that can be >>> done when serializing/deserializing it? All of those definitely fall out of >>> the scope of the constraints framework. So, I guess the question then >>> becomes, is the JSON constraint a valid constraint to have? Just a temporal >>> patch until (if) JSON type is in? Should we just remove it and keep >>> ignoring JSON? Those are valid questions and discussions to have. But, I >>> really think that we shouldn’t see this simple validator as a full fledged, >>> first class citizen, type in CQL. Similar arguments could be have for the >>> NOT_NULL constraint that has spawned so many interesting conversations. >>> >>> Now, having made that distinction, I don’t think we should have constraints >>> that can be defined differently on the CQL statement. They should all have >>> a CHECK keyword, specifying that they are a constraint that will be checked >>> (aka, row value will be validated against whatever function). That’s easy >>> to identify, and it’s conceptually easy to understand the limitations it >>> comes with (as opposed to the JSON example mentioned above). >>> >>> Bernardo >>> >>> >>> >>>> On Apr 14, 2025, at 10:53 AM, Štefan Miklošovič <smikloso...@apache.org >>>> <mailto:smikloso...@apache.org>> wrote: >>>> >>>> As Yifan already said, "check" is not a reserved word now and its usage >>>> does not collide with anything. >>>> >>>> If people have columns, tables, keyspaces with name "check" that seems to >>>> work already so they don't need to do anything: >>>> >>>> CREATE TABLE ks.tb (id int check id > 0, val int check val > 0, primary >>>> key (id)); >>>> >>>> ALTER TABLE ks.tb ADD check int check check > 0; >>>> >>>> DESCRIBE ks.tb; >>>> >>>> CREATE TABLE ks.tb ( >>>> id int CHECK id > 0 PRIMARY KEY, >>>> check int CHECK check > 0, >>>> val int CHECK val > 0 >>>> ) .... >>>> >>>> CREATE TABLE ks.check (id int check id > 0, check int check check > 0, >>>> primary key (id)); >>>> CREATE KEYSPACE check WITH replication = {'class': 'SimpleStrategy', >>>> 'replication_factor': 1}; >>>> CREATE TABLE check.check (check int check check > 0, val int check val > >>>> 0, primary key (check)); >>>> INSERT INTO check.check (check , val ) VALUES ( 1, 1); >>>> >>>> PostgreSQL has this: >>>> >>>> CREATE TABLE products ( >>>> product_no integer, >>>> name text, >>>> price numeric CHECK (price > 0) >>>> ); >>>> >>>> we follow this approach (minus parenthesis). We can also chain constraints >>>> whatever we like >>>> >>>> val int CHECK val > 0 and age < 100 >>>> >>>> We can make a stab in trying to model >>>> >>>> val int not null check val > 0 >>>> >>>> this is how PostgreSQL has it (1). >>>> >>>> but that would be more complicated on the implementation side because we >>>> would need to also accommodate "CQL describe" to dump it like that, plus I >>>> am not sure how complicated it would be to tweak the parser as well. >>>> >>>> I will try to make some progress and will report back. >>>> >>>> Regards >>>> >>>> (1) >>>> https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-NOT-NULL >>>> >>>> On Sun, Apr 13, 2025 at 6:49 PM Dinesh Joshi <djo...@apache.org >>>> <mailto:djo...@apache.org>> wrote: >>>> On Sun, Apr 13, 2025 at 9:24 AM Patrick McFadin <pmcfa...@gmail.com >>>> <mailto:pmcfa...@gmail.com>> wrote: >>>> I'm loving all the syntax discussion lately. It's a good debate and >>>> essential for the project's future with a good developer experience. >>>> >>>> +1 >>>> >>>> On NULL. I've been asked this a million times by end users. Why is there >>>> no "NOT NULL" in the schema? >>>> >>>> I would've expected this to be in billions by now ;) >>>> >>>> I'm in favor of the standard SQL syntax here because it's what users have >>>> been using forever: >>>> name text NOT NULL >>>> >>>> I hold a weak opinion on this. We don't have to necessarily align on to >>>> the standard SQL syntax. In my experience, users subconsciously feel >>>> Cassandra is a SQL database and try to design their schema to fit the >>>> traditional SQL / RDBMS design and then later are disappointed to find out >>>> it doesn't have joins or foreign key constraints. But that's just my >>>> personal experience working with users. However, I think we should strive >>>> for consistency and if it aligns with SQL I have no issues with the >>>> syntax. Just sharing my experience. >>>> >>>> On CHECK. Also in favor of making this a reserved word but in context. >>>> Namely, how Postgres SQL works. CHECK ( boolean_expression_on_column) >>>> >>>> Making CHECK a reserved keyword may result in issues that Scott described. >>>> It will present a substantial barrier for users to upgrade as applications >>>> will have to be updated. >>>> >>>> Thanks, >>>> >>>> Dinesh >>
