271–280 of 293 posts
Earlier quoted context omitted.
You put a record in the Measurement table, and none in the Value table. > But honestly that level of normalization is much more work than it's worth Yes. I question whether it's worth anything to begin with.
Select M.*, V.* from Measurement as M left outer join Value_table as V… You end up with nulls again.
But there is no stored null representing 'no value'.
The NULLs in unique constraints quirk actually works differently in ORACLE databases, which is infuriating to say the least. Apparently this comes from some ambiguity in some sql standard, anyone know more about this?
Earlier quoted context omitted.
If only it had a name that was more indicative of that, like UNKNOWN, or UNDEFINED or INDERTIMINATE or something.
Honestly, at this point I just wish SQL servers supported proper discriminated union types and nullable columns were implemented as some kind of MaybeKnown and I could just have a normal Maybe with normal equality semantics if I wanted. SQL needs to join 21st century type systems... or needs to get replaced altogether. SQL is the FORTRAN of relational programming languages, but hangs around because every time somebod…
and what is an alternative to sql ... quel?
Earlier quoted context omitted.
If you can avoid learning SQL or the underlying DBMS then great, I have no problem with people ignoring things that are ignore-able. Problem is you can't. You will need to learn the DBMS, and the ORM will end up just getting in the way of what you really want to do.
I've used ORMs extensively in my career, and I've seen it trotted out as a Boogeyman here a million times. Why do I still prefer writing queries with a good ORM over awkwardly composing SQL by conditionally concatenating strings? Is buggy, error prone string concatenation with a bunch of if statements the true way? What am I missing?
Earlier quoted context omitted.
There are two values, TRUE and FALSE. Null is not a value, it the the lack of a value. You have a list of people and you ask if they own a car. You didn't get around to asking George, so that, somehow means he owns a car because you are using boolean logic? Or does it mean he doesn't own a car, because you are using boolean logic? No, it means you haven't gathered this data point and don't know.
If there are exactly two possible values, TRUE and FALSE, you're working with Boolean logic. If there are three possible values, TRUE, FALSE and NULL (unknown), then you're probably working with something like Kleene logic. You can't truly be working with Boolean logic, though, any more than you can be doing integer arithmetic when 15.37 is a member of your domain. To put it another way, if we're talking about the ma…
That's why it's name-squatting. Rather than introduce a 'kleene' datatype & operations, and let the user opt-in, they decided that in our 'bool' world, the following is not a truism:
a = a or a aEarlier quoted context omitted.
> No, it means you haven't gathered this data point and don't know. This is how it should be. > Somehow means he owns a car because you are using boolean logic? This is how it unfortunately is. There are 3 people, and there are 3 people who don't have a NULL car. Therefore George has a car. CREATE TABLE people(name text, carId uuid); INSERT INTO people values('Bill', '40c8a2d7-1eb9-40a9-b064-da358d6cee2b'); INSERT IN…
> There are 3 people, and there are 3 people who don't have a NULL car. This is not what you are asking with your query: as someone else stated, NULL is meant to be "UNKNOWN", or "it could be any valid value". So nothing is ever equal to something that can be anything, because even another NULL (i.e. unknown) value is in general different. So in the line SELECT name FROM people WHERE carId = NULL the condition will a…
It's worse than that. It's something is neither equal nor not-equal to something else.
Whether you can implement something differently as a workaround is immaterial. It's weird, per the title.
SQL NULLs aren't weird , they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! If you want you can read NULL as UNKNOWN and suddenly a whole bunch of operations involving them become a lot more intuitive: 1. TRUE OR UNKNOWN = TRUE, because you know you have at least one TRUE already. 2. TRUE AND UNKNOWN = UNKNOWN, because you don't know whether you have two TRUEs or not. It's just out there. 3. UNKNOWN XOR UNK…
If only it had a name that was more indicative of that, like UNKNOWN, or UNDEFINED or INDERTIMINATE or something.
ANSI SQL:1991 provides an optional feature that introduces a special value UNKNOWN to boolean expressions [1]. But few databases support it. MSSQL [2] is one of the few that do. As I understand it, it's not a data type that be used in tables, but is only a potential result of boolean operations.
[1] https://modern-sql.com/concept/three-valued-logic
[2] https://learn.microsoft.com/en-us/sql/t-sql/language-element...
Earlier quoted context omitted.
The granularity of virtual address mapping is usually a page. On many systems, that's 4 kilobytes of address space. In order to trigger a fault when the address 0x0000000000000000 is dereferenced, it's necessary to map the entire address range from 0x0000000000000000 to 0x0000000000000fff to the same faulting behaviour. That's a waste of a page.
Yeah but there is no "page" there. Assuming a process starts with an empty page table (meaning every single address will segfault), you don't have to do anything else to get the crashing behaviour for null. Unless you're talking about some kind of device which supports virtual memory, but also by default sets up mappings, including at 0 which seems weird to me.
Memory mapping on all hardware with which I am familiar (which is not a small integer) is done by pages. If you have to fault on one address in a page (eg. zero page, stack canaries, heap guards) you have to fault on every single address in that page. But "memory is cheap". Until it's not.
Earlier quoted context omitted.
Except that NULL is not the same as UNKNOWN! NULL is a data value (like integers) that can appear in data expressions (like NULL + 1) and comparisons (like NULL = 1) whereas UNKNOWN is a truth value that can appear in boolean/logical expressions constructed from logical connectives like AND, OR, NOT. A data expression always evaluates to a data value, and usually whenever any part of the expression is NULL, the entir…
Indeed, they're not identical - that's why I just said "based on", and that's likely why the word UNKNOWN itself isn't used in SQL. Nevertheless I find it a useful intuition pump. I wager that most people reading `UNKNOWN = UNKNOWN` or `UNKNOWN UNKNOWN` and thinking about the examples above would stop and say, "Wait, I actually don't know the value of that statement for sure either, since the LHS and the RHS could be…
[1] https://modern-sql.com/concept/three-valued-logic#compatibil...
Earlier quoted context omitted.
Sentinel values suck especially when the language already has a perfectly good one built in. Is 0 for a temp measurement unknown sentinel or an actual measurement, how about 2,147,483,647 great probably not a measurement now its always included in greater than queries same with max negative with less than. Null separates the value into its own distinct group and prevents it from being including in range queries due t…
> Null separates the value NULL is not a value. NULL is a statement that a value is not available or unspecified reasons. If you want a particular value where a query would return NULL, it's your job to replace the NULLs with the contextually-appropriate value, e.g., using COALESCE(), to provide it. It's a convenience shortcut to allow more complicated data models to be rpersented in simpler table structures than a f…
I do not agree it is most certainly a value that is stored in the database.
>NULL is a statement that a value is not available or unspecified reasons.
Again I disagree, it is a value that denotes its value is of a different type than specified in the columns data type. The meaning of that value is for the user to decide. The system is literally storing a value that can be compared against using special equality syntax (IS NULL, IS DISTINCT FROM, etc).
The actual column definition is a sum type defining possible values in the column it is a constraint on the values:
columnA int null columnB int not null
If someone asks you what the value of a column in a result is when null do you say "I don't know" or do you say "null"?
For all these statements about what null means philosophically and the history about why it is treated the way it is in SQL there is little compelling argument to what value having the the equals operator always returns false when comparing nulls and instead one must use a separate syntax to properly compare null values for equality other than its for historical reasons and it changing it would be difficult.
This adds no value over typical programming languages where the normal equality operator can be used, its is weird and the source of confusion and even more bugs than your typical null handling creates.
I would prefer a database with fully fleshed out sum types rather than marking a column nullable, then a column could be marked as say a number + string + special sentinel type or whatever combination of types makes sense for my application, and if it that db did exist I am sure its equality operator would properly compare type and value to give a consistent binary result rather than the nonsense that is SQL null equality.
Your statements about using coalesce don't seem compelling to me, maybe I am a misunderstanding, to efficiently search for a value with a index you must use that value, I should be searching for with column IS NULL not Coalesce(column,[special value]) = [special value] which would be extremely inefficient.
Many languages have null coalescing operators and still use the standard equality operators for null. Coalesce to a special numerical value for a numerical column to represent a sentinel value is again a waste of time that again leads to strange greater than less than issues. Given a type system that allows nulls I would rather use IS NULL than coalesce that would be a further step backward but even better would be = NULL and get rid of the IS operator and its variants.