Databases - TopAnswers

i-one replying to Josh Darnell

Yeah, not being verbose is something I should definitely train. OK, will keep in mind for the future. I appreciate your feedback, thanks.

Josh Darnell replying to i-one

It *is* very long.  It would have been easier for me to consume in two parts, I think. But I don't speak for everyone :)  That being said, I think your explanations are clear and the whole thing flows well.

Josh Darnell replying to i-one

Hahaha this really made me laugh :D

i-one replying to Josh Darnell

Thanks, Josh! Is it hard reading? Once I've written it, I realized that probably it is quite long (it was even longer with logical trees included, which were thrown out at some revision). I was thinking to split it in two parts, but now it's as it is.

i-one

```xml
<WaitStats>
  <Wait WaitType="JOSH_TO_READ_MY_POST_IN_DEPTH" WaitTimeMs="Infinite" WaitCount="1" />
</WaitStats>
```  
:D

Josh Darnell replying to i-one

Just got around to reading it in depth - fantastic stuff!  So neat to see how those predicates are removed at different stages (i.e., simplification vs earlier compilation / constant folding activity).

i-one

Phew... I did it

i-one

::: quote 1 115032 70ac1a052b69788b840dc7af631544ba35aa71997240f3a4b5b0b7f57bc166a4 219,219,219 131,133,133
Josh Darnell replying to i-one *— 5 days ago* *in [The Tavern](/meta?room=1#c115032)*  
>Awesome :D I am looking forward to it!
:::

Truilus replying to Jack Douglas

Thanks!

Jack Douglas replying to Truilus

Sorry about that, pretty much all the Linux machines were affected when I added MariaDB 10.6 and ran out of space in my ZFS pool (which I need to keep an eye on going forward)

Andriy M replying to Truilus

MySQL, MariaDB, Oracle seem to be failing as well.  SQL Server 2019 seemed to work for me (the version test at least) but not SQL Server 2019 for Linux.

Truilus

dbfiddle doesn't seem to work with Postgres any more. If I call e.g. https://dbfiddle.uk/?rdbms=postgres_13 and then click the "Run" button, it results in "Run failed"

Paul White replying to Andriy M

very nice!

Andriy M

| | | | | | | |
-|-|-|-|-|-|-
**M**|**o**|**r**|**n**|**i**|**n**|**g**

Andriy M

Time to open the blinds

FoggyFinder

`morning`

Paul White

|M|o|r|n|i|n|g|  
|-|-|-|-|-|-|-|

Josh Darnell

M  
o  
r  
n  
i  
n  
g

Andriy M

Morning

John K N

Morning

Josh Darnell

LOL

PeterVandivier

for the transcript though: if you've arrived here from GlobalSign internal documentation, you are morally obligated to @-reply/ping me from this message    

![blob](/image?hash=9281c078b714ebed1d91068e852139218bb4738546b82428b935e482717d8008)

PeterVandivier

:wave:

Josh Darnell

👋 hey Postgres nerds!

Josh Darnell

Well.  Let's wave to be sure we're being polite.

PeterVandivier replying to Josh Darnell

i wonder if anyone will ever follow it :-P

Josh Darnell replying to PeterVandivier

Haha that's a lovely circular reference.

PeterVandivier

in a pleasing (possibly ironic) symmetry, the link in that screencap is actually to the chat message in this room where i started rubber-ducking :sweat_smile:

PeterVandivier

...but it's the explanation I put in the documentation I did for it ¯\\\_(ツ)_/¯  
  
![blob](/image?hash=2e179e8867756fa56cf6b16502b25eee3f44c24fc6e1275469b84786ddf8d762)

PeterVandivier

my working theory is this: because there's a single element in the `row()` returned by `jsonb_array_elements()`, then `->>` gets lazily assigned. `with ordinality` adds a second element to the `row()` returned and forces the error. sadly this isn't supported by my attempt at a repro...  
  
<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=02994ab816288a1f0aba1287ab299d21

Andriy M

I wonder, if there was a `->>`operator for `record`, how would it know which `jae` to pick to apply the operator? Because I'm guessing in the above query the `jae` in `jae->>'a'` resolves as `jae.jae`.  In other words, it's the `jae` column alias, not the `jae` table alias.  The operator applies to the column because there's no such operator for `record` (the table alias), but what if there was?

PeterVandivier

:-/   ¯\\\_(ツ)_/¯  
  
> "_just add the column alias, too_"
  
<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3e095ae88f1559181450e8d219224e69&hide=255

PeterVandivier

wondering if that warrants a bug report :thinking:

PeterVandivier

wtaf

PeterVandivier

it's the _alias_ that changes the resultset

PeterVandivier

<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=9c5e0d9d1772851cfc7373e5620ce3b1&hide=127

PeterVandivier

omg it gets grosser

PeterVandivier

`with ordinality` isn't a drop-in, it changes the darn structure of the resultset

PeterVandivier

aw eff this noise  
  
<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=85b5f12cab1053c8af83d0a68569bb66&hide=31

Josh Darnell

You're welcome!

kus replying to Josh Darnell

thank you, Josh

Josh Darnell

https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=8ae9d459bfc003158a4258647045094e  
  
It looks like, in DB2, that could be selecting from the vendor-specific "dual" table:  
  
```  
SELECT 'potato', 'bananas' 
FROM SYSIBM.DUAL
```  
  
or using the `VALUES` clause to define and build a table expression:
  
```  
SELECT t.c1, t.c2 
FROM (VALUES ('potato', 'bananas')) t (c1, c2);
```

Josh Darnell replying to kus

https://stackoverflow.com/questions/3732422/select-from-nothing

kus

this is valid query in sql server 2019 Linux  https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=e504210afbcffe28e949a8efdd745a3a but not in db2 https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=169ee4b22bab1d7bf3fc76b363a9192f

kus

Good morning guys, how do I write `select 'apple', 'banana'` in db2?

Andriy M

Morning

John K N

Morning

Andriy M

No worries, happy to help

FoggyFinder

thank you. Seems like this is exactly what I was looking for

Andriy M

Same suggestions elsewhere: https://sqlskull.com/2020/12/18/get-row-counts-for-all-tables-in-sql-server/