The Heap™ – Consultancy ©® - 2021-05-11

« first day (3780 days earlier) ← previous day next day → last day (1071 days later) »

5:17 AM

A chairde - Morning all...

Peter Vandivier

6:12 AM

8

Q: Is it w̲r̲o̲n̲g̲ to Underline words on SE (specifically dba.se)?

Sometimes, I feel that text can be emphasised a bit more if u̲n̲d̲e̲r̲l̲i̲n̲e̲ or d̳o̳u̳b̳l̳e̳ underlines were allowed? Given that SE's markdown doesn't support it, would it actually be against the site rules to work around that and create the presentation anyway? Not sure if I should include ̲...

is @Srutzky or another unicode wizard about? I wanted to update the link to db<>fiddle since SQL Fiddle seems to be choking on MSSQL but I can't figure out which character to use for the d̳o̳u̳b̳l̳e̳ underline dbfiddle.uk/…

admittedly it's not in the original link but I look to improve where I can 🤷‍♂️

fwiw it seems like there should be a few different options but i couldn't find one that worked in the function

user image

Peter Vandivier

6:58 AM

user image

0_0 i guess i should stop feeling so guilty about still running postgresql 10

7:19 AM

@EvanCarroll How so: ", the great advantage to CREATE DOMAIN is that you only have to pay the cost of coercion or validation once."? I really like DOMAINs for readability and the fact that it reduces cognitive overhead... I didn't realise there were performance benefits? Obviously, I'm thinking of PostgreSQL, but also Firebird (and Oracle has it, and MS SQL Server has CREATE TYPE)...

@PeterVandivier Sometimes I'm surprised that we don't receive questions about the schema design for the Lascaux cave paintings - that's one information store that's lasted a long time!... :-)

@Vérace The documentation says PG assumes domain types only ever contain immutable (deterministic) data. There's some weirdness around being able to store null in a non-null domain.

1 hour later…

8:27 AM

I just tested this here - PG (at least for this simple test) rejects all attempts to insert inappropriate NULLs - can you point to where there could be a problem?

@PaulWhite - oops, that was for your consumption - what's the problem with PG and DOMAINs and NULLs?

8:47 AM

@Vérace dbfiddle.uk/…

@Vérace I did mention it was in the documentation postgresql.org/docs/current/sql-createdomain.html

I wouldn't say it was a "problem". I used "weirdness".

I found this paragraph hard to read:

> An example of a common way to break this assumption is to reference a user-defined function in a CHECK expression, and then change the behavior of that function. PostgreSQL does not disallow that, but it will not notice if there are stored values of the domain type that now violate the CHECK constraint. That would cause a subsequent database dump and reload to fail.

> The recommended way to handle such a change is to drop the constraint (using ALTER DOMAIN), adjust the function definition, and re-add the constraint, thereby rechecking it against stored data.

A lot of negatives in a row!

> "PostgreSQL does not disallow that, but it will not notice..."

9:08 AM

Arrrrgh! @PaulWhite - IMNSHO, it's a total bug... it's completely contrary to the principle of least astonishment...

And the documentation as this point is about as clear as bottled sh**e...

Careful, Evan will smite you

I wonder how many people over the years have thought they were doing something really clever with functions and constraints.

The null thing is more forgivable, but my preference would be for a not-null domain to be checked at the point of change as well.

But the semantics are probably just unavoidably weird due to SQL.

9:23 AM

@PaulWhite what is SQL Server's behaviour on similar issue?

I mean using a UDF in a check constraint, inserting rows in the table - that obviously pass the check - and then modifying the function?

@ypercubeᵀᴹ From memory, that's not allowed.

But I've never used a function in a check because it's crazy.

@PaulWhite - much as I hate to admit it (😊), SQL Server's behaviour is impeccable (I'm not talking about UDFs under the hood) in this regard - unlike PostgreSQL which fails on both counts that SQL Server passes on in my fiddles!

I'm quite frankly (still) astonished, flabbergasted, outraged and disappointed... the NOT NULL constraint is possibly the most fundamental of all - it should be inviolate and inviolable... the only small niggle that I would have with SQL Server's implementation is that CHECK constraints (not again...) aren't apparently allowed with DOMAINs (or CREATE TYPE to use SQL Server's lingo).

I did post on the Firebird dev list about SQL in CHECK constraints not being enforced subsquent to insertions (updates...) and how the TRIGGER's that are created are before INSERT... I suggested that the DBA/dev be allowed choose BEFORE, ON or AFTER... was told that was not supported and it was a recipe for shooting myself in the foot! If the syntax is permitted, then it should do what it says on the tin!

9:44 AM

@ypercubeᵀᴹ Just checked. SQL Server will not allow you to modify the function while it is being used in a check constraint.

> Msg 3729, Level 16, State 3, Procedure Length5to40, Line 1 [Batch Start Line 29]
Cannot ALTER 'dbo.Length5to40' because it is being referenced by object 'CK__test__addr1__5D96B091'.

@Vérace The situation with domains is much worse in SQL Server. Yes we have alias types but they're deprecated without a workable replacement.

sommarskog.se/wishlist.html#Domains

Sadly I can't show it on db<>fiddle because it won't allow CREATE RULE due to permissions.

But still dbfiddle.uk/…

So you can do it, but it's deprecated and doesn't always do what one would wish. And the syntax is hardly standard.

Peter Vandivier

@PaulWhite i thought i remembered it not allowing you to use the function in the constraint at all without marking it with schemabinding as well but clearly i'm thinking of something else dbfiddle.uk/…

@PeterVandivier It's a good idea to use schemabinding, but not required, no. The constraint is always bound to the function, but the function does not need to bind to things it references.

Peter Vandivier

i suppose that makes sense, yea

10:01 AM

One remark - if you're knowledgeable enough to be writing functions to subvert constraints like that, then shooting yourself in the foot is a risk and you should be knowledgeable enough to point the gun in the right direction - I'm talking about grunt programmer X who is happily programming away, sure in the conviction that his column Y can never be NULL...

However, this whole DOMAIN is really just syntactical sugar - why can't the table definition internally not just substitute TEXT NOT NULL CHECK (LENGTH(value) > 5 AND LENGTH(value) <= 40)... The code can do this behind the scenes shouldn't be rocket science - the table definition would then be clear and enforced at all times!

I don't think one needs to be knowledgeable to be subversive

See Evan for details

More seriously, yes it is unexpected and IMO should not be allowed

and binding things tightly is great until you need to change something

one of the reasons CLR types aren't widely used

The one nice thing about check constraints in SQL Server is the conditions they enforce are guaranteed and visible to the optimizer, allowing simplifications and transformations on that basis.

e.g. the NOT NULL constraint supplied by an alias type can be overridden in the table definition to allow NULLs, which is a little weird.

But this is all weird, edge-case, legacy behaviour anyway so

are check constraints using UDF visible to the optimizer as well?

@ypercubeᵀᴹ no. potentially they could, but inlining is explicitly disallowed for scalar functions used in check constraints.

@Vérace right but you get to repeat those checks in every email column in your database. Using a domain allows to write that check once.

10:16 AM

sounds like a normalization problem 😋

Imagine you have certain columns that are FLOAT but have to be restricted to say between 0 and 1. There is some beauty in defining a ("probability") domain and use that instead

yes I agree. domains are a nice idea.

also no one knows how to validate an email address

2

But, the fact that you can define it as NOT NULL and then override that (albeit it in a tricky way...) effectively renders them NULL (pardon the pun) and VOID -- your DB server (from whomever) is your last bastion of defence against rubbish in your tables - the PostgreSQL DOMAIN functionality fails that the usefullness test. Yes, PROBABILITY as a type would be lovely IF it couldn't be NULL'ed!

Though I will admit that PROBABILTY NOT NULL is better than FLOAT NOT NULL CHECK (p >= 0 AND p <= 1)... still, it's pretty egregious...

There are some lovely regexps out there for email validation...

all of them wrong

regexes are json are in the same circle of hell. (debatably) even more deeply than nulls ;)

3

Nulls are probably in a UNKNOWN level of the circles.

10:22 AM

or a missing level, or a not-applicable level, or...

probably the only way to validate an email is to send to the address and get a reply. maybe even that's not a guarantee.

it's like trying to validate a postal address

I had a postal address that didn't exist for a few years

Peter Vandivier

10:39 AM

@ypercubeᵀᴹ i think that's just purgatory

10:49 AM

@PeterVandivier only? purgatory doesn't even exist in some christian subdivisions.

@Vérace To be clear, in SQL Server, overriding NOT NULL in the alias type is possible, but it is not possible to override a not null check in an associated rule. I wonder if that really is clear. Anyway, it is what it is.

If you write code that allows and disallow nulls, you get to expect weird

Like OPTION (KEEPFIXED PLAN, RECOMPILE)

No - I kinda "grokked" what you were saying - I suppose it's good that you can have a non-overrideable scenario - but I just think that if you declare a DOMAIN (or TYPE) as NOT NULL, then that's what it should be, no IFs, no BUTs no COCONUTs! You shouldn't have to remember - Oh Gawd, I have to create a rule to ensure that my NOT NULL remains NOT NULL - the "rule" should be in the definition, or rather, the definition should be the rule!

Single Source of Truth AND Principle of Least Astonishment!!

I tend to agree. Legacy behaviour.

CREATE TYPE ADDR FROM character varying (40) NOT NULL;
GO
CREATE RULE ADDR_DOMAIN AS LEN(@ADDR) >= 5 AND @ADDR IS NOT NULL;
GO
EXECUTE sys.sp_bindrule
    @rulename = N'ADDR_DOMAIN',
    @objname = N'ADDR',
    @futureonly = 'futureonly';
GO
CREATE TABLE test
(
    addr ADDR NULL
);
GO
-- Fails
INSERT test (addr) SELECT (SELECT 1 WHERE 1 = 0);

For anyone that reads code more easily than English.

@Vérace hard if not impossible to have that with SQL's nulls.

I wonder if it would be possible to outlaw outer joins.

10:57 AM

But, NOT NULL in the table definition can't be overridden!

FROM a LEFT OUTLAW JOIN b ... ;)

subversive!

@Vérace dbfiddle.uk/…

I suppose it would be nice if the world made sense

But instead we have people buying Tesla with $DOGE

@PaulWhite yesterday. Today they use SHIBA ;)

github.com/agentm/project-m36/blob/master/docs/on_null.markdown

5

Q: The key of the relation resulting from a full outer join

What is the key of the relation resulting from a full outer join? If there is no key, how is it said that the relational algebra is closed under its operators and the result of each operator is also a relation?

relational-theory join

11:23 AM

But this makes perfect sense - it's not the FK you're looking at, it's the result of a LEFT JOIN...

@Vérace makes sense only after 20 years of using outer joins.

Otherwise the result value of t2.fk being null is certainly not of less surprise

This would be a better way to look at it...

the above linked answer sums it up nicely.

> Two relations went in, but a relation did not come out.

@Vérace That doesn't answer any questions though, it just kicks the issue down the road by renaming. What is the meaning of "joined field"? What type is it? What is its domain of values?

Outlaw join indeed.

I quite like this: scholarscompass.vcu.edu/cgi/…

Not least because of:

> MISSING DATA IN THE RELATIONAL MODEL
A dissertation submitted in partial fulfillment of the requirements for
the degree of **Doctor of Philosophy** at Virginia Commonwealth
University.

It's probably easier to accept that SQL isn't relational, and makes no sense if you really think about it.

11:51 AM

Another related article: HAVING A Blunderful Time or Wish You Were WHERE by Hugh Darwen.

12:16 PM

An entertaining rant with some good points, as always

12:42 PM

morning

Good morning

@PaulWhite Then call me nuts because I use them fairly regularly to enforce things like: subtypes, column validation that cannot occur through keys, valid state transitions. As long as you know what you're getting into, it's not so bad.

"you're nuts"

3

You sound like my wife when I talk about getting a boat or a sportscar to get a leg up on my midlife crisis

perhaps I *am* your wife
think about that

Mind blown. Wait, aren't you supposed to be keeping an eye on unruly middle schoolers?

12:54 PM

there's more than one of me, as previously noted

So is there a primary Paul that would take down the network or is there a failover in place?

1:09 PM

17

Q: We’re looking for a new Vice President of Community at Stack Overflow

You might have noticed that we posted a VP of Community job description on our “Careers at Stack Overflow” page. It’s a big role, and important here at the company. As we move forward in the process of becoming a product-led company, we’re putting a lot of thought into our original product - our...

discussion announcements company community-managers

@bbaird The former arrangement would be a terrible design

I'm sure I don't need to point one of those out to you 😄

From said Q&A:

> That doesn't really help, it's like saying a cashier is two levels below an assistant floor manager... okay? How does that make the VP role here different to what Sara's role was, other than by name?

Does that mean Sara (Chipps?) has been gone?

> Sara Chipps has decided to leave the role of Director of Community and Stack Overflow, and May 11th is her last day with us.

oopsie

Thanks.

Makes me wonder.

@PaulWhite I Knew How To Validate An Email Address Until I Read The RFC (www.haacked.com)

A list of valid e-mail addresses as per the RFC for SMTP (RFC 2821):

Abc\@def@example.com
Fred\ Bloggs@example.com
Joe.\\Blow@example.com
"Abc@def"@example.com
"Fred Bloggs"@example.com
customer/department=shipping@example.com
$A12345@example.com
!def!xyz%abc@example.com
_somename@example.com

1:24 PM

@JohnK.N. I liked the update at the end

Anyway that's another rabbit hole

Seeing as somebody is leaving will Monica be back any time soon?

:-|

To put this in more concrete terms: the CMs had a VP until the start of 2019. Y'all remember 2019... — Shog9 10 mins ago

LOLZ

@JohnK.N. Not if she has any sense

Yes, I read that comment

Miss this Meta though:

951

Q: Firing Community Managers: Stack Exchange is not interested in cooperating with the community, is it?

Less than a month ago, there was some settling of the incident with firing Monica. It's obvious that there was not a lot of approval from the community on the course of action chosen by SE, or the settlement. Today, we are seeing a new wave of SE actions targeted on work with the community: firi...

discussion support community company community-managers

How did I miss that?

user image

user image

nice, secure filter

1:39 PM

That's exactly what a robot would say

the regex says robot

Dec 24 '19 at 12:20, by Tom V - Team Monica

Step 1 should be just firing Sarah but that's not going to happen

1:51 PM

What does "process of becoming a product-led company" mean? Are we the product? Of course we are...

The new "system font" is special

@Vérace Skynet

@Vérace Not sure. Maybe it means selling more Teams?

I find American English extremely hard to parse sometimes

The will use Deep Learning AI to create a sentient collection of IF/ELSE IF statements that will take over the world

2:23 PM

@PaulWhite I don't think it's American English; sounds more like Corporatese

I'm saying if you do `CREATE TABLE foo(a mydomain); CREATE TABLE bar(a mydomain);` then if you do `INSERT INTO foo TABLE bar;` you do **not** have recheck `foo.a` for the constraint of mydomain. It's already passed the test.

This is not true if you do `CREATE TABLE foo(a int CHECK (a BETWEEN 7 and 42)); CREATE TABLE bar(a int CHECK (a BETWEEN 7 and 42));` Now `a` must be rechecked for validity under the terms of `bar`

@mustaccio Yes for some reason I associate that with the USA

Oct 11 '19 at 13:41, by Paul White

chat markdown strikes again

The real problem in SQL Server there is that Microsoft is more interested in selling their programming stack then in providing functionality the user actually needs. And they're damn good at doing that. So rather than creating a very simply language internally that can parse the DML into CLR and produce the type for you, the require you to learn C#.

Which is fine for me, because I actually like C# and think it's a decent language.

But it really sucks for their user base that identifies as being Database Administrators and refuses to touch code.

They might have been interested in that at some stage, but now they're a hosting company

Just use VB.NET like the good lord intended

2:29 PM

Good grief owl man

:D

Every time I think B in KFC has hit a low point

He comes out with something like that

I was rapidly looking up whether F# would work as I figured that'd be more palatable but then went in the opposite direction

Anyway CLR types would need to be reimplemented completely. Complete serialization/deserialization on access is not a recipe for performance.

I'm sure F# and Scala would be tools I would learn if I had to work with those systems more.

(assuming rust never compiles to CLR/JVM, which seems highly unlikely)

2:31 PM

Reminds me of a meme I saw today. Can't spell python without y tho

Been a python fanboi since 2003

I worked at a marketing company and the number of implementation of "CSV" we would receive from our clients was astronomical. The native CSV parser chewed them all up like a champ

Now I just use Rust for everything.

I am one of them.

But my day job is in Perl, and generally (though not for csv parsing), I would prefer Python.

I'm "meh" on Python. Like, I can use it, I just have found better ways to do things not using it.

Generally, I wouldn't advocate people learn perl/python/ruby/php etc. Go is as easy of a language to learn, and it's clearly much faster and growing to consume all of them.

But if you want to learn a language better at everything but remarkably difficult to learn and you're going to be doing it a lot: pick up Rust.

2:58 PM

have you used rails at all

hahhahhah

needs a moar active record

id thought you'd say that

3:32 PM

> But my day job is in Perl,

-- you have my deepest sympathies!

Pays well.

I'm about to move departments though.

Devops Engineer, never had that title before.. WhY noT?!?

You see Rust's SQLx.

It's doing something new anyway.

compile time type checking against the database.

And because you're using rust and it can export C libs, you can link it into the database easily with FFI. So you can be sure your native types in the application match your (custom) native types in the database.

@Andriy you should

> Not insisting on this being the only correct reading, ...

there should be 2 left joins to table1

I thought we outlawed those

@AndriyM ^

3:55 PM

> So you can be sure your native types in the application match your (custom) native types in the database.

Hmmm... what about passing in a NULL to a DOMAIN which is specifically created as NOT NULL? See the debate above about PG's (outrageous - am I'm a huge fan...) behaviour in this regard!

I'm confused at what that conversation is about

if you declare a domain as not null, it does not accept null. The problem isn't there with the type. If you declare a container and a type inside the container is declared as not-null, it can still accept null, because the container itself is the only type that is checked.

(for nullness)

Are you referring to container problems or the caveat in createtable about sql itself generating null?

@Vérace re this,

Domain constraints, particularly NOT NULL, are checked when converting a value to the domain type. It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on the nullable side of the outer join. A more subtle example is

INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
?

The only way that problem gets resolved is moving SQL from 3vl to 4vl which is what the spec calls for.

type NonNullable<T> = T;
type Nullable<T> = Null | NonNullablle<T>;
type Option<T> = Unknown | Nullable<T>;

good luck teaching that to people. ;)

4:11 PM

The problem would not exist if we had stayed with 2VL in the first place.

heh, trollolol

SELECT middle_name FROM users; who has one? who did you forget to ask?

It's really not that confusing - I contend and maintain (and will till they pry the keyboard from my cold dead hands...) that if I declare something as NOT NULL, it should remain NOT NULL till the END OF TIME and not be able to be subverted by OUTER JOINs of any sort -

the conversation showed that the result of a LEFT JOIN might be defined as some NOT NULL field - I'm saying that when you JOIN like that - you are not getting a value for the field, but the result of a LEFT JOIN where there is no corresponding record...

see this and this... The LEFT JOIN should be allowed - inserting its result into the table where there's a NOT NULL constraint should NOT!

Morning

@ypercubeᵀᴹ Maybe I should have. I think I secretly wanted to.

If the left join is allowed, what is the type returned in the result set?

What is the type returned by the subquery in the above insert?

((SELECT domcol FROM tab WHERE false))

4:26 PM

CREATE TABLE foo(bar) AS SELECT 42::mydomain;
SELECT f1.bar AS f1, f2.bar AS f2
FROM foo AS f1
LEFT JOIN foo AS f2
ON f1.bar <> f2.bar;

What is the type of `f2.bar` that you want in that result set.

Please answer the question. What is the type that Postgres returns?

is the type of f2.bar mydomain or not?

crickets

someone got Rusty

there'll be a perl of wisdom along any moment

the answer might be null

4:32 PM

CREATE DOMAIN f AS INT;
CREATE TABLE tab(domcol) AS VALUES (1::f);

ecarroll=# CREATE TABLE tab2 AS TABLE tab;
SELECT 1
ecarroll=# \d tab2
Table "public.tab2"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
domcol | f | | |

It's returning a type of f.

dbfiddle.uk/… - it's an ADDR despite being NULL (really)!

@ypercubeᵀᴹ - see fiddle above

Yeah, pg_typeof is an easier and better way to show this.

So that's still raises the issue what do you want to happen in my example @Vérace

CREATE DOMAIN mydomain AS int;
CREATE TABLE foo(bar) AS SELECT 42::mydomain;
SELECT f1.bar AS f1, f2.bar AS f2, pg_typeof(f1.bar), pg_typeof(f2.bar)
FROM foo AS f1
LEFT JOIN foo AS f2
ON false;

What should that return?

(it does return),

f1 | f2 | pg_typeof | pg_typeof
----+----+-----------+-----------
42 | | mydomain | mydomain
(1 row)

But I would contend in 3vl that's the correct answer. This is why you need 4vl.

so Postgres is broken, ok

SQL is broken. heh

5 hours ago, by Paul White

It's probably easier to accept that SQL isn't relational, and makes no sense if you really think about it.

4:38 PM

The result of a query is strongly typed, and the problem is regardless of what the data specifies in the case of an outer join each column of the result set must be able to represent NULL. Because SQL itself needs a method to say "that side of the join does not exist", the problem that it may become ambiguous with "this column doesn't exist" seems obvious.

Try the sum of an empty set of a not-null domain type as well

I've seen all this before, I'm just curious to know what's @Vérace's better way to solve this, do you think PostgreSQL is WRONG?!?!?!?!???????

That's never happened, and if you ever actually think that chances are you haven't thought about the problem long enough. =)

you just convinced us it is, are you taking it back? ;)

hahhahah

SQL has so many different "meanings" for null it's not even funny

4:44 PM

PostgreSQL is WRONG! Again, as far as I'm concerned, the SELECT isn't important - it's a bit like Russell's paradox - a set doesn't exist until it's properly defined - a resultset isn't properly defined until the time comes to "materialise" it - i.e. INSERT it - up to that point, it's just a UX/UI entity and it can have type my_knob for all I care - but when it comes to INSERT it into a table, I DO NOT WANT NULLS WHERE I HAVE DECLARED NOT NULL!

thoughts.davisjeff.com/2009/08/02/what-is-the-deal-with-nulls for the pg people

ooo bold caps

Apologies - I'm getting hot under the collar - it's very simple - if I say NOT NULL, my RDBMS should damn well respect that - it may not agree with my choice of type (or non-type) but it should defend to the death my right to choose it!

It's not up to the server to second guess me - it's my data tool, it should do what I tell it to do...

it sounds like you're advocating for a system that checks values against constraints at the time the change is made, rather than assuming whatever is in the domain-typed holder is acceptable.

But with the SELECT, nothing has changed - it's only on the INSERT that the server should say to itself, "Oh, this is meant to be of type TEXT NOT NULL CHECK (LENGTH(value) >= 5 AND LENGTH(value) <= 40) - if it passes this test, then the insert works, if not, it's rejected. What else are ON UPDATE triggers about?

dbfiddle.uk/…

19 hours ago, by Evan Carroll

Of course, the great advantage to CREATE DOMAIN is that you only have to pay the cost of coercion or validation once.

Just as a reminder where this all started

5:01 PM

> Just as a reminder where this all started

Nice one!

@Vérace I agree but this is not very likely to change soon in Postgres, unless you convince the hackers list that it's the right thing to and/or you start writing a patch.

Until then, I suggest you treat the IS NOT NULL as forbidden in domain declarations.

The select is the problem though

Is it possible to write a non-deterministic domain check?

Everything else is fine but null/not null should be in the column definition.

@Vérace you can't pretend like the insertion is the problem. That's absurd. You have an argument in claiming the select is the problem. The fact that PG is returning a type of mydomain with a value of NULL as a resultset of a SELECT is going to be a problem for any downstream consumer regardless of whether or not that consumer is an INSERT or a user's application.

But that's SQL for you.

5:05 PM

> Until then, I suggest you treat the IS NOT NULL as forbidden in domain declarations.

I know, but how many people will know this? How many contract <blah> programmers will remember this? How many project managers casting a tired eye over the code will realise that under stange circumstances the NOT NULL can be circumvented?

@PaulWhite you mean like this?

CREATE DOMAIN ffs TIMESTAMPTZ
CHECK (VALUE <= NOW());

@ypercubeᵀᴹ Yes something along those lines

would be dully accepted

Or checking a timestamptz is less than 5 seconds in the past

That would quickly become not true

I made it <= ;)

5:07 PM

better

at first I thought that was a complex emoji

0

Q: Why does PostgreSQL allow NULLs in domains that prohibit NULL?

PostgreSQL allows NULLs in domains marked as NOT NULL. Why is this, the docs says this about it, It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on...

postgresql null domain

for ya'll

I should delete that now before it hits HNQ

😁

@PaulWhite actually >= makes more sense

date math is hard

@ypercubeᵀᴹ want me to change it back?

@PaulWhite HNQ is pretty much my blog.

5:11 PM

dbfiddle.uk/…

@PaulWhite we could change it to equal ;)

only allowing the value of NOW()

ha! that's true

and null of course

so not so true!

stop it now

@ypercubeᵀᴹ that's just offensive. wait til @Vérace sees it

makes for a legit use case. store a timestamp of when the row was inserted. No dev can overrride ;)

CREATE TABLE test (a datetime2 NOT NULL CHECK (a = SYSUTCDATETIME()));

yikes

5:17 PM

The db architect should be punished with restoring the backup.

Indeed. But why on this green earth is that even allowed

@EvanCarroll Re: your Q & A: One could argue that an error should result, or the type coerced to e.g. mydomain? or some other suitable system-generated type?

It's just not logical for a domain to contain a 'value' outside that domain

Sure, so long as you believe. SELECT foo FROM bar WHERE false should be capable of producing an error. I wouldn't find that a compelling argument, but yeah that'd work too.

SELECT statements could be error producing.

SELECT 1/0

SELECT CAST('fish' AS int)

Those are all good counter points. I don't have anything further on that. ;) That's a position you could take.

@EvanCarroll I think my fundamental objection is to SQL coercing empty set to null

An empty set of values in domain D is not null

It's not like databases don't go around changing types all the time

e.g. SUM(x::mydomain) produces bigint

I wonder if a moderator on the network has ever locked comments on a Q & A before the first comment has been posted

How dare you misquote my fish as foo

5:35 PM

@PaulWhite I Added your suggestion in the answer.

@EvanCarroll I know! Hence the misquote!

@PaulWhite foo fish.

Also I wonder how many times Vérace will use bold in his answer.

2

moar bold, moar respek

@EvanCarroll As it happens, the fish was named foo so I'll allow it

5:49 PM

twitter.com/SQL_Kiwi/status/1392172277358219264?s=19

Do you have an explanation as well @PaulWhite

@TomV Yes: databases were a mistake.

Makes sense

Schemaless nosql solves this right?

That constraint can only be true for a very brief period of time.
If you ever check it again, it will not hold (unless someone reset the clock).

Ah yes sure

@TomV Eventually

2

@EvanCarroll I'm not sure your answer captures the crux of Vérace's objection e.g. dbfiddle.uk/…

I don't know if Postgres has a table-integrity-checking built-in, but if it does, would that fail?

6:42 PM

> the crux of Vérace's objection e.g.

@PaulWhite @EvanCarroll - this is even more plain here - two scenarios, virtually identical and the behaviour is different. If PostgreSQL doesn't want to enforce constraints in DOMAIN declarations, then they shouldn't be allowed - Principle of Least Suprise. As far as I"m concerned, it's a bug... This fiddle might be a good example in your question...

I"ve permanently disabled the caps lock key! :-)

HOORAY!

It does seem necessary to repeat the NOT NULL constraint in the table def.

My point about Russell's paradox stands - the query result is just that, a result of a LEFT JOIN - it has no real "existence" until it is INSERTed into a table - it should only be allowed to be "materialised" i.e. come into physical existence in a table if and only if the table constraints allow that. The second example is the behavior that should also occur with NOT NULL domains!

Repeating the damn thing is the crux of my problem - if you don't enforce it, don't allow programmers/DBAs to declare it - POLS...

I wonder how long you are expecting those comments to last. That should be an answer.

@Vérace I agree. Just stating it for the record.

OK - I'll do it tomorrow - fiancee is grumbling about me spending my life on the laptop!

Peter Vandivier

7:39 PM

user image

4

@PaulWhite i shared this in our "Python Scripting" chat room at work. i've yet to receive praise i believe i am due

right

8:01 PM

But look you have stars next to your name so the riches are bound to come rolling in

StackCoin is the newest hottest blockchain NFT idealized for all your cryptoexchanges

Maybe it should be st@kcoin or something cooler

8:45 PM

@Vérace This isn't true.

re: "it has no real "existence" until it is INSERTed into a table"

@billinkc ST4CKOIN

@Vérace When you run a query to a database you get back a "result" is has existence in memory at that point in time. Because SQL is strongly typed, every database internally will tell you the type of that result, or give you some kind of meta information on it. (Without which you wouldn't know whether or not you have an int, or a reference to a string). So for example in libpq an executed query returns a PGresult where each column can be resolved to an oid (representing the type).

Such as PQftype

So you can say something like "it doesn't matter until you INSERT" that is not true at all, because it matters just as much when you do anything with it.

If you want to solve this the only type-safe way (without bailing out with a panic as @PaulWhite proposes), you'd have to know that the PGresult that you have can never return a concrete type, because any method to get a concrete type could itself return a value that the concrete type doesn't support (that SQL needs a way to communicate this column isn't null but can't represent it because the query that produced the resultset didn't contain a value).

9:03 PM

@bbaird Congratulations, you are now head of sales. Or condolences, however that goes compared to your personality

Peter Vandivier

@EvanCarroll i'd share the flower-mind-blown-cat gif here if i didn't think Paul would ban me for it

I don't know the cat memes. I consent to you sharing it. And, I consent to Paul banning you for it. ;)

Peter Vandivier

flower-mind-blown-cat gif: i.stack.imgur.com/pAgSw.gif

Evan said it's okay

i'm safe

OH WAIT

🤔

9:25 PM

RIP

You know what's fun: SQL Server is predictably more broke than PostgreSQL in its null handling. ;)

All null handling is broken so

9:57 PM

0

A: What is the SQL Server equivalent of pg_typeof(), how can I get the type of a column returned in a result set?

One method is to use SQL_VARIANT_PROPERTY( expression , property ), where property is "BaseType". -- returns int SELECT SQL_VARIANT_PROPERTY(cast(42 as int), 'BaseType'); It's also totally broken on NULL, -- returns null SELECT SQL_VARIANT_PROPERTY(cast(null AS int), 'BaseType'); And it does no...

10:08 PM

0

Q: How can I cast to a user-defined type in SQL Server?

SQL Server provides an ability to create a user defined type with CREATE TYPE CREATE TYPE foo FROM int NOT NULL ; However, there is no mention how I construct a value with that type. Is this of use anywhere outside of CREATE TABLE? I'm not having luck with CAST or CONVERT?

sql-server datatypes

10:21 PM

0

Q: Does SQL Server's is_nullable mean anything at all?

SQL Server has a type flag called is_nullable you can see it on sys.types. Currently, (SQL Server 2019) it's only set to FALSE two types, sys.timestamp and sys.sysname. On sys.timestamp this type seems to accept null. On sys.sysname it does not. Is there any explaination of this behavior? You can...

sql-server timestamp sql-server-2019

« first day (3780 days earlier) ← previous day next day → last day (1071 days later) »

Transcript for

May '2111

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile