The Heap™ – Consultancy ©®

Erik Darling

12:47 AM

@PaulWhite glad you approve

Josh Darnell

2:25 AM

@ErikDarling 😳😳😳

John K. N.

8:37 AM

@Criggie It's not just about dairy though. Another reason that the sheep population dropped, was due to a new regulation in the EU that forbid the import of lamb from just one country. As a result the need for N.Z. lamb dropped on the international market and the N.Z. farmers had to reduce the sheep population.

As a result there was no excess lamb on the N.Z. market and the prices for lamb in N.Z. sky rocketed, which had the side effect that N.Z. families were no longer able to afford a lamb roast on Sunday.

or so I was told

Paul White

9:28 AM

One person was told to stop growing sheep and the others just sort of mindlessly followed along

Zikato

9:49 AM

Does one grow sheep?

Paul White

10:07 AM

Of course

Well, not any more

Zikato

I thought it was called "raising sheep". But I'm not a native so I've asked for clarification

Paul White

That parses too

As does, "farming sheep"

I find, "growing sheep" preferable

Zikato

fair enough, thank you

Paul White

an invoice will not be sent this time

Zikato

10:24 AM

I appreciate it

Paul White

That's paying invoices talk

Zikato

🤐

Paul White

10:41 AM

Before anyone asks, your nice xml chat became ephemeral because you both know better

Charlieface

10:53 AM

@ErikDarling In some cases yes, but not every byte can be converted in every collation. I suppose it might work if you use a binary collation dbfiddle.uk/AtRbfkzB it's just messy. SUBSTRING works on varbinary there is no reason why STRING_AGG couldn't

Sean Gallardy

11:33 AM

@Criggie Oh, I thought it was NZ slang for something. I guess it was face value then :D

Peter Vandivier

12:56 PM

@Charlieface but... just don't try to render the bytes then. that's what hex and base64 are for dbfiddle.uk/VExiwtQL?hide=8

@SeanGallardy when can we get base64_encode() in on-prem versions pleaseandthanku

Erik Darling

1:20 PM

@PeterVandivier xml reigns supreme once again

Hannah Vernon

Wordle 1,012 3/6*

⬛🟨🟨⬛⬛
🟨🟨⬛⬛⬛
🟩🟩🟩🟩🟩

Paul White

Wordle 1,012 6/6*

⬜⬜⬜⬜🟨
🟨⬜⬜⬜⬜
🟩🟩🟩⬜⬜
🟩🟩🟩⬜⬜
🟩🟩🟩⬜⬜
🟩🟩🟩🟩🟩

Wife did it in two today

Sean Gallardy

1:35 PM

@PeterVandivier It might happen!

Erik Darling

0

Q: How does a NULL value get created in the various DBMS?

I'm wondering if the creation of a NULL value is standardized across the various DBMS and, if not, how it differs across them.

null

i can't find anything academic/agnostic on null bitmaps

if anyone knows of anything like that, it would be a good general answer

Peter Vandivier

@ErikDarling there's an old Aaron Bertrand SO answer that seems to imply convert() can do varbin->base64 conversion but dbfiddle was bombing out on it and i didn't care enough to start my localhost sql server and try it out ¯\_(ツ)_/¯

Erik Darling

at least for row stores

@PeterVandivier link?

Peter Vandivier

2

A: Convert a hexadecimal varbinary to its string representation?

DECLARE @x VARBINARY(64); SET @x = 0x4b78374c6a3733514f723444444d35793665362f6c513d3d; SELECT CONVERT(VARCHAR(64), @x), SUBSTRING(CONVERT(VARCHAR(64), @x, 1), 3, 64); ----- style number is important ---^ Results: Kx7Lj73QOr4DDM5y6e6/lQ== 4B78374C6A3733514F723444444D357936653...

@PaulWhite ✋

@HannahVernon roadkill frog

Erik Darling

works on my machine

dbfiddle.uk/LJYASmlG?hide=2

Peter Vandivier

1:42 PM

yea, the standalone example works. but i couldn't get it working over a table

and like i said... didn't care enough to try more than a couple syntax variations

huh

what a little creep

1:55 PM

@PeterVandivier db<>fiddle just isn't interested in displaying a character with code 0x00

dbfiddle.uk/Y6EIYx3x

Peter Vandivier

from Aaron's answer though, i thought that was meant to display the base64 encoding - not the rendered ASCII :\

misreading on my part

quel surprise

Paul White

quelle

anyway

Peter Vandivier

my surprise is masculine kthx

Paul White

strings were just this huge mistake

@PeterVandivier oic

Peter Vandivier

oh lollllll the literal ASCII of that varbinary string in Aaron's answer is itself a base64 string

cheeky

Paul White

2:00 PM

traps for the unwary

or the unfamiliar with beaker's sense of humour

Peter Vandivier

😂😂

yea i was wondering wtf was up when i read that

like... no way have i never realized it's that simple

oh weird... it's actually the premise of the OP

~~turtles~~ strings all the way down

John K. N.

Wordle 1,012 6/6*

⬛⬛⬛⬛⬛
🟩⬛⬛🟨⬛
🟩🟩🟩⬛⬛
🟩🟩🟩🟩⬛
🟩🟩🟩🟩⬛
🟩🟩🟩🟩🟩

Paul White

@PeterVandivier every day's a little voyage of discovery and wonder

tales to tell the grandchildren

Charlieface

@PeterVandivier yeah I would just convert it back to varbinary anyway (not sure why you would touch string representations such as base64).

Peter Vandivier

weren't you asking how to string_agg() them?

Charlieface

2:15 PM

But I don't know for sure if STRING_AGG works on weird characters. It seems to, with a binary collation, but I don't know.

I was asking if you could aggregate binary not strings.

Peter Vandivier

you must first string before you can agg

it's right there in the name

3

Charlieface

SUBSTRING has the same naming argument and that's not true for it. There's no reason why you shouldn't be able to aggregate up binary data as varbinary rather than strings.

You take say 6 rows of varbinary(16) and make varbinary(96) out of it.

Paul White

expecting consistency from SQL Server is the road to disappointment

Peter Vandivier

is the road to disappointment on the map for the voyage of discovery & wonder?

Paul White

quite

I feel kinda sorry for them when they start to fill some of the obvious gaps like binary and bits. It only highlights the rest of the missing things

I mean, of course you should be able to aggregate binary

But you can't

Hence strings

Charlieface

2:20 PM

@ErikDarling SQLite doesn't seem to use null bitmaps sqlite.org/fileformat2.html#schema_layer

Paul White

It's inefficient but so long as the character encoding you're using has an entry for all the code points, it should hang together ok

It would be wrong to say these things get heaps of testing though

Peter Vandivier

clusters of testing perhaps?

Paul White

tested in production as all the best things are

Peter Vandivier

💪

Charlieface

@PaulWhite So COLLATE Latin1_General_100_BIN2 is enough?

Erik Darling

2:24 PM

@Charlieface eh

Paul White

@Charlieface I really wouldn't want to speculate. It's all so horrible all the way down.

One is never quite sure exactly when the conversions occur

Database collation is probably important but ick ick ick code pages and whatnot

It's almost impossible to use precise language about all this

I know it drives Solomon nuts

Let's not even think about what happens in SQL Server on Linux

Not that anyone uses that

Hannah Vernon

I wonder how well AT TIME ZONE works on Linux

Paul White

I'm surprised you don't run Linux

Hannah Vernon

I could try it, but I'm on vacay

Paul White

Could only assist your efforts to break SQL

Hannah Vernon

2:30 PM

I do have a SQL Server on Linux environment on my home network, and it mostly works great.

Paul White

> mostly

also, ofc u do

Peter Vandivier

6

Sean Gallardy

^^^^^

Hannah Vernon

I wonder if Microsoft runs Azure SQL on Linux. It wouldn't surprise me.

Paul White

To save on licences?

Hannah Vernon

2:32 PM

Hahahaha

Yes

Lol

Paul White

I do wonder about you sometimes

Hannah Vernon

Oh I know, and you're not the only one

Paul White

Might just be a little out of phase with reality

Temporally maladjusted or something

Hannah Vernon

Did you know birds aren't real

Paul White

For sure

Everyone knows that

Sean Gallardy

2:35 PM

@HannahVernon Nope

Paul White

Exceptionally weird transcript so far today

I need chocolate

chocolate frog

Even has Paul's hat

I wonder why it has fairy wings though

The prompt:

> an illustration of a chocolate frog

Erik Darling

2:45 PM

maybe the frog is just getting back rub from a fairy

Paul White

AI idea of a chocolate fish

We're doomed

Hannah Vernon

Mmmm tasty

Paul White

Chocolate fish

A chocolate fish or choccy fish is a traditional confectionery item in New Zealand, and in New Zealand culture is a common reward for a job done well ("Give that kid a chocolate fish").Chocolate fish have a conventional fish-shape and a length of 5 to 8 centimetres (2.0 to 3.1 in). They are made of pink or white marshmallow covered in a thin layer of milk chocolate with the ripples or "scales" on the fish created simply by the fish moving under a blower; this slides the unset chocolate back, creating the illusion of scales on the fish. Several manufacturers make the fish; the most well-recognised...

Context arrives for the needy

Hannah Vernon

Zikato

@PaulWhite Yummy

Hannah Vernon

2:50 PM

@PaulWhite yeah sounds delish

Paul White

Why does my house have no choc fish in

This is hopeless

Zikato

Biggest oversight

Paul White

Stupid country can't even buy them at 4 am

Zikato

what are you doing awake at 4 am?

Paul White

talking about the lakh of choccy fish

please consult the transcript

Hannah Vernon

2:53 PM

Hth

Paul White

yes I might try a 24/7 supermarket if we fucking had one

that's so very H

Hannah Vernon

Bing being helpful

Paul White

y do u encourage it

always so damned chipper about everything

this is a crisis

Hannah Vernon

I'm trying to trap it

Paul White

ask it to run on Linux

Hannah Vernon

2:56 PM

Just in case

maybe you need a book on choc fish

Also, see moorewilsons.co.nz/cadbury-chocolate-fish for what appears to be a reasonable wayto ensure you don't run out

I just went to Amazon.nz but it redirected me to amazon.com.au how absurdely rude of it.

Apparently NZ doesn't exist

@SeanGallardy presumably because Linux is a nightmare.

Sean Gallardy

@HannahVernon Just has so many issues compared to SQL on Windows

Among other reasons

Erik Darling

@PaulWhite have you tried moving to nyc

Paul White

3:23 PM

@ErikDarling do they sell choccy fish

Erik Darling

ofc

choccy everything really

fferinsofbrooklyn.com

Paul White

3:43 PM

either of those would be acceptable

I could find neither at that link

looks like fake news

ooo giant fish thegardenparty.co.nz/products/giant-chocolate-fish

now I want a chocolate aquarium

Erik Darling

4:06 PM

oh you want a specific brand

i see

paul is OPping me

Sean Gallardy

8:21 PM

P-cores run on choco fish, TIL

Sean Gallardy

9:20 PM

E-core obviously run on ethyl

Erik Darling

10:12 PM

C10H14N2

C8H10N4O2

CH3CH2OH

That should be nicotine caffeine and alcohol

If it’s not you can whatever and so forth

Peter Vandivier

What’s the chemical code for crippling anxiety?

Zikato

YUNOOK2

Charlieface

Can anyone get rid of the table spool in this dbfiddle.uk/zaXaafQy

MERGE didn't help, neither did various combinations of filters and check constraints.

Relevance:

0

A: Storing an arithmetic parse tree in SQL

As I mentioned in my other answer, it is also possible to do this using a temp table. This is again likely more efficient and easier than using a recursive CTE. Note that the following solution is set-based where possible. The number of loops is only up to the max depth of the tree, not for each ...

Zikato

Probably a Halloween problem protection, right?

sqlperformance.com/2013/02/t-sql-queries/…

> The query optimizer recognizes that this pipelined update plan is vulnerable to the Halloween Problem, and introduces an Eager Table Spool to prevent it from occurring. There is no hint or trace flag to prevent inclusion of the spool in this execution plan because it is required for correctness.

There - fixed this for you
https://dbfiddle.uk/fby8T1Pa

Charlieface

10:50 PM

@Zikato Yes appears so, well obvs if you introduce a blocking operator it won't need the spool. Point is to avoid it altogether: the child rows cannot be the same as the parent rows, but can't persuade the compiler of this fact.

Zikato

11:09 PM

This might be relevant?

itprotoday.com/sql-server/…

Transcript for

The Heap™ – Consultancy ©®