Dia dhaoibh a chairde - morning all!

@MaxVernon "I lolled: MangoDB is worth a look." - Yes, indeed, I did have wry smile - but then I thought about it a bit. That person mightn't be totally familiar with the dba.se Zeitgeist (wouldn't be the first) and a remark like that might put that user off dba.se for life. It's all too easy for those with a modicum of technical knowledge to sneer at the great unwashed...

I would also remind you that you edited a post of mine (which IMHO was clearly tongue in cheek) where I suggested that those who remove ONLY_FULL_GROUP_BY from MySQL's sql_mode should be taken out and shot... IMHO, the MangoDB comment was (potentially) way more hurtful!

@Vérace but it differs if it's on a chat channel - who very few check - vs a public answer - which hundreds may see - even more since it has been upvoted.

I have had encounters with servers on - the default then - ONLY_FULL_GROUP_BY. Surpisingly there were a few who knew what they were doing and the setting was actually helping improve performance of some queries without compromising accuracy.

oh, the mango comment was on a question. I thought it was in here.

Morning

Morning

We had snow yesterday here

@ypercubeᵀᴹ in the Heap?

haha no. In South England

@ypercubeᵀᴹ "the mango comment was on a question." - precisely my point. I know that certain users have a drier, more acerbic, wit than others - but in the context of a "New Contributor", I felt it was a bit OTT. Agree entirely with your remarks about public Q&A forum and a private chat room - plenty of room for "slagging" (for non speakers of Hiberno-English - "teasing" or "joshing") here...

Re. your remarks on ONLY_FULL_GROUP_BY - yes, sure, if you know what you're doing, then it may be worthwhile switching it off. I definitely got the impression that particular OP had a loaded gun that they were pointing southwards at the appendage at the end of the leg - a bit like PostgreSQL's DISTINCT ON maybe? Or do PostgreSQL devs have a better chance of knowing what they're doing? :-)

DISTINCT ON does not give random results. They are predictable.

Ooops - just checked here - should have known better... When ONLY_FULL_GROUP_BYis off/blank, does it simulate DISTINCT ON sometimes? I'm trying to think of an example?

@ypercubeᵀᴹ But only if you have an unambiguous ORDER BY

Morning

mods - can we get a magic link for "please post text instead of screencaps". slash does this need to be a meta post so it can be actioned by an SO developer?

should i just include the given canonical text on our FAQ? seems rather unwieldy to be a "magic" link 🤔

@AndriyM right, I should have stated that more accurately

DISTINCT ON will always return data that are in the table(s). If the ORDER BY is ambiguous, the returned results will be unpredictable.

MySQL's result with ONLY_FULL_GROUP_BY setting off and used wrongly are not only unpredictable but it can return rows that are not in the table(s), i.e. column values from different rows.

But that would probably qualify as a bug, no?

No, it's in the docs.

> The server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic.

Ah, I see what you mean. Basically it's like aggregating, which can also produce rows that aren't in the underlying dataset (for instance, if you pull minimum values from two columns that are actually found in different rows), but in this case it's completely unpredictable.

I'm certainly not a fan of how non-full GROUP BY is implemented in MySQL (compared to Postgres, for instance), but my main beef regarding it is that they had that as the default for a long time (until one of 5.7 releases, I think).

yes, 5.7 is when they improved it

and set ONLY_FULL_GROUP_BY to ON by default

Nope.

Was reviewed by me, myself an I. And I even had a go at answering it.

@Vérace I only remarked that the comment was funny, and closed the question. The result there is no one but 2k+ users can see the comment (and the question asker). The comment was neither rude, nor angry in its tone, and does in fact suggest something the OP might be interested in knowing. If the question would have stayed open, I'd have deleted the comment.

With your comment, I simply edited out the opinion that folks should be "taken out and shot", which to a non-English speaker could be seen as minimally edgy and perhaps inciting violence if you read it to the letter.

@PeterVandivier possible false positive, I assume.

FYI, @Vérace - I absolutely loved this tidbit from your answer on storing the IPv6 address as a key: "These are not for the faint-hearted", with which I completely concur. It looks like Regex was designed to confound. Not so terrible to write but almost impossible to decipher without being extremely careful and uber-pedantic.

@MaxVernon I'm in the middle of completely revising the answer to that question - I edited the question with a view to proposing that it be reopened as a technical question and not a shopping-list - I'll post here when done! Yes, indeed, a 1080 character regex is not fun to read...

@Vérace I re-opened it a few minutes ago after your edit.

@MaxVernon - great, my new (all-singing, all-dancing) answer will be along presently!

:-)

I'm looking forward to it :-)

Good morning everyone

Busy weekend here

Daughter's first time sledding - thought she might be apprehensive, but she's a speed demon and loved it

@PeterVandivier add it to meta, so we can figure out how we want it to look.

That look on her face! oh that brings back memories of sledging on the hill outside my house in Wales. Good times.

sledging -> old skool for sledding lol

@MaxVernon That's too many vowels for Wales. sldggng.

lol

On the other hand, that's not enough consonants, particularly double Ls

@MaxVernon - as discussed previously, here is my [revised answer] to the the question about using substrings of a large HEXADECIMAL PK as FOREIGN KEYs. I had to repudiate PostgreSQL as a solution because it doesn't allow columns to be FOREIGN KEY's unless they're UNIQUE - obviously, this criterion can hardly apply in the case of substrings...

one of the few times that I've found MySQL to have a better solution than PostgreSQL - but hey, nothing's perfect. I'd be interested in any feedback here or under the question - I will try and put anything from here back into the question if it's helpful!

Why can fields not be FOREIGN KEYs unless they're UNIQUE? Surely all that matters is that the field with the value exists in the parent table?

Re. answer - don't want to go down triggers rabbit-hole! Putting that in a comment to my answer!

But would be willing to listen to suggestions about same!

@Vérace Keys are unique. So having something called a foreign key that is not unique is antithetical to the relational model and one of the reasons why MySQL is bad

If a header of a string has meaning/its own limited set of values, that should form its own domain which can then be used as part of a composite key

@bbaird Amen to that

@bbaird Your knowledge of the theoretical foundations of relational algebra are undoubtedly superior to my own - but I don't see why it's not possible to have the MySQL "arrangement" - it doesn't even have to be called an FK - call it a "FRIENDLY KEY" or a "NEIGHBOURLY KEY" or a "CONTIGUOUS KEÝ"...

I don't mind what it's called, but if a user wants to have substrings as FKs (and for them to be guaranteed to work) - as long as one of them exists in the parent table, why not allow it? Triggers and suchlike allow devs/dbas to subvert the relational model all the time - I suppose that I'm arguing for it to be allowed in DDL - give the user enough powder to shoot themselves in the foot if that's what they want!

@Vérace If you need things to come from a certain domain, that domain has meaning and should be quantified. In the context of that question (IPv6), if there is need to search on the prefix (ISP/network), and there are other entities with relationships to just the prefix, then the prefix forms its own entity which then is used as part of the primary key for the full address.

It probably points to that other table needing to be split in the model, then the key would become unique in one of the new tables.

Like if you have Country/State in the same table and you want the Country to just exist for some other table X - maybe Country/State should be split into two tables, with State now having a FK to Country, just like your other table X can now have an FK to Country.

It basically comes down to: you only need to "break" things if you haven't gone through the process of correctly normalizing for your use case.

that's basically the same as keeping a list of comma-separated things in a column, then complaining how hard it its to search or enforce integrity

only in this cases things are separated by colons

@mustaccio Exactly

I also hate that I didn't think of that analogy first

I'm used to explaining things to five-year-olds that surround me in my workplace

and I don't work in a kindergarten

@bbaird You can one-up by posting this as an answer to the actual question

@mustaccio I'm considering it, but I'd feel to actually do it justice I'd need to know exactly what they're doing - Q only says "like an IPv6", not "an IPv6".

And I suppose I'd use IPv6 as an example, but I'm not a networking guy so I'd probably fumble around the terminology too much.

@Vérace ahhhh, MySQL. "The CHECK clause is parsed but ignored by all storage engines."

lol

Maybe the developers of MySQL should be shot and not MySQL devs?

@MaxVernon

possibly

They'll have to get in line because I'm stringing up the team that created Hadoop first

I'm open to negotiating a discount on a job lot - throw in the MongoDB team and I'll (humanely - which is more than they've done for us) shoot them all for free!

OMFG - this guy is planning on teaching databases!

I said "humanely" shoot - make that "happily"!