3 ms·

> One of the primary functions of NULL is to say that a tuple satisfies a predicate except we don’t know what this one position is—it’s certainly is something o

> One of the primary functions of NULL is to say that a tuple satisfies a predicate except we don’t know what this one position is—it’s certainly is something out in the real world, we just don’t know what. I'm not sure I understand this. Can you explain it with an example? I'm thinking something like: let (x, y) = NULL; But then I'm stuck.

kstrauser 2y ago
In the broader DB theory context and not strictly Pandas, think of a `users` table with a `middle_name` column. There's a difference between `middle_name is null` (we don't know whether they have a middle name or what it is if they do) and `middle_name = ''` (we know that they don't have a middle name). In that case, `select * from users where middle_name is null` gives us a set of users to prod for missing information next time we talk to them. `...where middle_name = ''` gives us a list of people without a middle name, should we care. Edit: A related aside is that Django's ORM's handling of NULL gives me freaking hives. From https://docs.djangoproject.com/en/5.1/ref/models/fields/#null https://docs.djangoproject.com/en/5.1/ref/models/fields/#nul...: > If a string-based field has null=True, that means it has two possible values for “no data”: NULL, and the empty string. In most cases, it’s redundant to have two possible values for “no data;” the Django convention is to use the empty string, not NULL. I've been around numerous Django devs when they found out that other DBs and ORMs do not remotely consider NULL and empty string to be the same thing.
- emmelaich 2y ago
  In Oracle, "" is converted to NULL so maybe that's where they get that from.
  - kstrauser 2y ago
    Whoa, you're right.[0] I wouldn't have expected that at all. [0] https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Nulls.html#GUID-B0BA4751-9D88-426A-84AD-BCDBD5584071 https://docs.oracle.com/en/database/oracle/oracle-database/1...
  - rbanffy 2y ago
    You can almost count on Oracle doing the wrong thing… It never ceases to amaze me they got themselves into a position where they had to introduce a VARCHAR2 type.
mananaysiempre 2y ago
What I mean is, the ivory-tower relational model is that each table (“relation”) represents a predicate (Boolean-valued function) accepting as many arguments as there are columns (“attributes”), and the rows (“tuples”) of the table are an exhaustive listing of those combinations of arguments for which the predicate yields true (“holds”). E.g. the relation Lived may contain a tuple (Edgar Codd, 1923-08-19, 2003-04-18) to represent the fact that Franklin was born on 19 August 1923 and died on 18 April 2003. One well-established approach[1] to NULLs is that they represent the above “closed-world assumption” of exhaustiveness to encompass values we don’t know. For example, the same relation could also contain the tuple (Leslie Lamport, 1941-02-07, NULL) to represent that Lamport was born on 7 February 1941 and lives to the present day. We could then try to have some sort of three-valued logic to propagate this notion of uncertainty: define NULL = x to be (neither true nor false but) NULL (“don’t know”), and then any Boolean operation involving NULL to also yield NULL; that’s more or less what SQL does. As far as I know, it’s possible to make this consistent, but it’ll always be weaker than necessary: a complete solution would instead assign a variable to each unknown value of this sort and recognize that those values are, at the very least, equal to themselves, but dealing with this is NP-complete, as it essentially amounts to implementing Prolog. [1] http://www.esp.org/foundations/database-theory/holdings/codd-1979.pdf http://www.esp.org/foundations/database-theory/holdings/codd...
- mananaysiempre 2y ago
  s/Franklin/Codd/ s/represent the above/relax the above/ (Sorry!)

kstrauser 2y ago

In the broader DB theory context and not strictly Pandas, think of a `users` table with a `middle_name` column. There's a difference between `middle_name is null` (we don't know whether they have a middle name or what it is if they do) and `middle_name = ''` (we know that they don't have a middle name). In that case, `select * from users where middle_name is null` gives us a set of users to prod for missing information next time we talk to them. `...where middle_name = ''` gives us a list of people without a middle name, should we care. Edit: A related aside is that Django's ORM's handling of NULL gives me freaking hives. From https://docs.djangoproject.com/en/5.1/ref/models/fields/#null https://docs.djangoproject.com/en/5.1/ref/models/fields/#nul...: > If a string-based field has null=True, that means it has two possible values for “no data”: NULL, and the empty string. In most cases, it’s redundant to have two possible values for “no data;” the Django convention is to use the empty string, not NULL. I've been around numerous Django devs when they found out that other DBs and ORMs do not remotely consider NULL and empty string to be the same thing.

emmelaich 2y ago
In Oracle, "" is converted to NULL so maybe that's where they get that from.
- kstrauser 2y ago
  Whoa, you're right.[0] I wouldn't have expected that at all. [0] https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Nulls.html#GUID-B0BA4751-9D88-426A-84AD-BCDBD5584071 https://docs.oracle.com/en/database/oracle/oracle-database/1...
- rbanffy 2y ago
  You can almost count on Oracle doing the wrong thing… It never ceases to amaze me they got themselves into a position where they had to introduce a VARCHAR2 type.

mananaysiempre 2y ago

What I mean is, the ivory-tower relational model is that each table (“relation”) represents a predicate (Boolean-valued function) accepting as many arguments as there are columns (“attributes”), and the rows (“tuples”) of the table are an exhaustive listing of those combinations of arguments for which the predicate yields true (“holds”). E.g. the relation Lived may contain a tuple (Edgar Codd, 1923-08-19, 2003-04-18) to represent the fact that Franklin was born on 19 August 1923 and died on 18 April 2003. One well-established approach[1] to NULLs is that they represent the above “closed-world assumption” of exhaustiveness to encompass values we don’t know. For example, the same relation could also contain the tuple (Leslie Lamport, 1941-02-07, NULL) to represent that Lamport was born on 7 February 1941 and lives to the present day. We could then try to have some sort of three-valued logic to propagate this notion of uncertainty: define NULL = x to be (neither true nor false but) NULL (“don’t know”), and then any Boolean operation involving NULL to also yield NULL; that’s more or less what SQL does. As far as I know, it’s possible to make this consistent, but it’ll always be weaker than necessary: a complete solution would instead assign a variable to each unknown value of this sort and recognize that those values are, at the very least, equal to themselves, but dealing with this is NP-complete, as it essentially amounts to implementing Prolog. [1] http://www.esp.org/foundations/database-theory/holdings/codd-1979.pdf http://www.esp.org/foundations/database-theory/holdings/codd...

mananaysiempre 2y ago
s/Franklin/Codd/ s/represent the above/relax the above/ (Sorry!)