[Used gemini for formatting. It seems to have edited the text somewhere, and the table on bottom is atrocious. I ought to come back to this later. It's too late to continue with it now.]

When I was working with Oracle, I thought I had heard that EXISTS(SELECT * ...) was a bad thing because the query rewriter would do column expansion, causing an unnecessary lookup to the data dictionary. Tom Kyte corrected me (and others!) by stating it's possible that this happened on some old version on one OS (z/OS?) but that is not the case now.

It makes sense to use SELECT *. * says something. In COUNT(), * means count every record, even if every column is null. Indeed, the only way to directly count a record where every column is null is to use count(*). * means check existence. So, in EXISTS, where the only thing we care about is the existence, * makes perfect sense. (Of course, it would be better to just remove the entire SELECT clause and the FROM keyword, because it is redundant.)

Some people use SELECT 1, others SELECT NULL. Does it really matter? Even if it did do a dictionary lookup, how long would it take anyway?

Well, I found my answer. Recently, I was running a query using HAVING without a GROUP BY. Now, some people might object and say you can't do that, or it makes no sense. Those people do not understand GROUP BY. Whenever you use an aggregate, the entire table is the group. You only have to use GROUP BY if you want to segment the table further. Now, in most cases, using HAVING with no GROUP BY will make no sense. But, say you wanted a correlated subquery to check if a parent has multiple children, you would want a HAVING on the subtable with no need for a GROUP BY:

select * from parent where exists(select * from child where child.parent = parent.id having count(*) > 1);

Okay, but does this work? Let's use OneCompiler to test.

mysql:

with a as (select 1) select 1 from a having count(*) = 1;

works

with a as (select 1) select * from a having count(*) = 1;

ERROR 1140 (42000) at line 1: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'a.1'; this is incompatible with sql_mode=only_full_group_by

oracle:

with a as (select 1 from dual) select 1 from a having count(*) = 1;

works

with a as (select 1 from dual) select * from a having count(*) = 1;

ERROR at line 1: ORA-00937: not a single-group group function

postgresql:

with a as (select 1) select 1 from a having count(*) = 1;

works

with a as (select 1) select * from a having count(*) = 1;

psql:commands.sql:1: ERROR: column "a.?column?" must appear in the GROUP BY clause or be used in an aggregate function

sqlite:

with a as (select 1) select 1 from a having count(*) = 1;

Parse error near line 1: HAVING clause on a non-aggregate query

with a as (select 1) select * from a having count(*) = 1;

Parse error near line 1: HAVING clause on a non-aggregate query

mariadb:

with a as (select 1) select 1 from a having count(*) = 1;

works

with a as (select 1) select * from a having count(*) = 1;

works

sql server:

with a as (select 1 a) select 1 from a having count(*) = 1;

works

with a as (select 1 a) select * from a having count(*) = 1;

Msg 8120, Level 16, State 1, Server b2de965b1bf1, Line 2: Column 'a.a' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

duckdb:

with a as (select 1) select 1 from a having count(*) = 1;

works

with a as (select 1) select * from a having count(*) = 1;

Binder Error: column "1" must appear in the GROUP BY clause or must be part of an aggregate function. Either add it to the GROUP BY list, or use "ANY_VALUE(1)" if the exact value of "1" is not important.

So, results are mixed. Most of the tested databases allow HAVING without a GROUP BY as long as you do not specify any columns, and instead use a literal. This makes sense, because the * in the select clause needs to be expanded so the db knows what to return. That in turn will check the HAVING clause. The only exceptions are sqlite that does not even allow the literal, and mariadb that allows the *.

But what about in an exists clause where the select clause is of no consequence?

mysql:

with a as (select 1) select 1 where exists(select 1 from a having count(*) = 1)

works

with a as (select 1) select 1 where exists(select * from a having count(*) = 1)

ERROR 1140 (42000) at line 1: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'a.1'; this is incompatible with sql_mode=only_full_group_by

oracle:

with a as(select 1 from dual) select 1 where exists(select * from a having count(*) = 1);

works

with a as(select 1 from dual) select 1 where exists(select 1 from a having count(*) = 1);

works

postgresql:

with a as (select 1) select 1 where exists(select 1 from a having count(*) = 1)

works

with a as (select 1) select 1 where exists(select * from a having count(*) = 1)

psql:commands.sql:1: ERROR: column "a.?column?" must appear in the GROUP BY clause or be used in an aggregate function

sqlite:

with a as (select 1) select 1 where exists(select 1 from a having count(*) = 1)

Parse error near line 1: HAVING clause on a non-aggregate query

with a as (select 1) select 1 where exists(select * from a having count(*) = 1)

Parse error near line 1: HAVING clause on a non-aggregate query

mariadb:

with a as (select 1) select 1 where exists(select 1 from a having count(*) = 1)

works

with a as (select 1) select 1 where exists(select * from a having count(*) = 1)

works

sql server:

with a as (select 1 a) select 1 where exists(select 1 from a having count(*) = 1)

works

with a as (select 1 a) select 1 where exists(select * from a having count(*) = 1)

works

duckdb:

with a as (select 1) select 1 where exists(select 1 from a having count(*) = 1)

works

with a as (select 1) select 1 where exists(select * from a having count(*) = 1)

Binder Error: column "1" must appear in the GROUP BY clause or must be part of an aggregate function.

So, the same as before, except, oracle and sql server allow it (as well as mariadb). This is understandable, because there is no reason to expand the * in an exists.

So, maybe those databases actually run the query? Let's check. We'll put a function in the select clause that causes an error if run.

mysql:

CREATE FUNCTION f()RETURNS INT RETURN(SELECT 1,2); select f()

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax...

CREATE FUNCTION f()RETURNS INT RETURN(SELECT 1,2); SELECT 1 WHERE exists(select f());

works

oracle:

CREATE FUNCTION f RETURN INT IS BEGIN RETURN 1/0;END; / SELECT f;

ORA-01476: divisor is equal to zero

CREATE FUNCTION f RETURN INT IS BEGIN RETURN 1/0;END; / SELECT 1 where exists(select f);

works

postgresql:

CREATE FUNCTION f()RETURNS INT LANGUAGE SQL RETURN 1/0; SELECT f();

ERROR: division by zero

CREATE FUNCTION f()RETURNS INT LANGUAGE SQL RETURN 1/0; SELECT 1 WHERE EXISTS(SELECT f());

works

sqlite:

SELECT json('x');

Runtime error near line 1: malformed JSON

SELECT 1 WHERE EXISTS(SELECT json('x'));

works

mariadb:

CREATE FUNCTION f()RETURNS INT RETURN(SELECT 1,2); SELECT f();

ERROR 4078 (HY000) at line 2: Cannot cast 'row' as 'int' in assignment of `f()`

CREATE FUNCTION f()RETURNS INT RETURN(SELECT 1,2); SELECT 1 WHERE EXISTS(SELECT f());

works

sql server:

select 1/0;

Divide by zero error encountered.

select 1 where exists(select 1/0);

works

duckdb:

CREATE MACRO f()AS CAST('x'AS INT); SELECT f();

Conversion Error: Could not convert string 'x' to INT32

CREATE MACRO f()AS CAST('x'AS INT); SELECT 1 WHERE EXISTS(SELECT f());

works

So, every single one of these did not evaluate the exists projection list. There's more ways to test this. I learned a few while going along, until I realized I could have just used 1/0 for all of them! That's what I get for trying too hard.

Here is the final behavior matrix comparing these database systems (where "y" indicates successful execution and "n" indicates a raised error or parse failure):

+------------+----------+----------+----------+----------+--------------+--------------+
| Database | 1/having | */having | exists 1 | exists * | select error | exists error |
+------------+----------+----------+----------+----------+--------------+--------------+
| mysql | y | n | y | n | n | y |
| oracle | y | n | y | y | n | y |
| postgresql | y | n | y | n | n | y |
| sqlite | n | n | n | n | n | y |
| mariadb | y | y | y | y | n | y |
| sql server | y | n | y | y | n | y |
| duckdb | y | n | y | n | n | y |
+------------+----------+----------+----------+----------+--------------+--------------+

So, my son is around 20 pounds now. At my age, that's heavy. My left shoulder became sore from holding all the time he wanted to be held. So sore, i slept on my right side the last few nights just so it wouldn't hurt. But not only that, my daughter just had her one-month checkup and is at 7lb 9oz. She's also getting heavy. Sometimes, i want to hold her all day, but after a few minutes, i have to give up. She lying on my right shoulder as i type this right now. :)

Old'aVista

Babies are delicious, especially when they are yours. I find myself kissing him all the time thinking of how delicious he is. So, i started telling people: I'm not kissing my son, i'm holding back from eating him.

Oh me, oh my. I'm old and just getting older. But this year i got the best birthday present ever. My son was born just a couple days ago. I can't wait to take him home from the NICU.

I keep looking for these two links: https://slashdot.org/journal.pl?op=list and https://slashdot.org/journal.pl?op=edit.

I just read a comment: "Correlation does not connote causation." A search found the more common adage uses "imply" instead of "connote". Though, they are somewhat synonymous.

Anyway, that seems wrong. I mean, the whole point is that it does indeed imply causation. That's why we need to remind people that it does not equal causation.

Working on a report for someone in her late 70s over the phone, i lamented on being 50 when i took off my glasses and got close to the laptop to read the small letters on the screen. Her response: Ah, to be 50 again.

It made me smile. It's refreshing to hear things put in perspective.

Just like last year, my birthday again shifted me up one year. And like Marxist Hacker 42 said, 50 is really depressing. I could go on, but i won't.

Just the other day someone told me that he was a database programmer. So, i asked him which database. He said, "Access". I snickered (and remebered Brent Ozar's tweet on the subject).

As i turn 49, i felt the urge to post, because i always post on my birthday. Except i don't. Going through the list (link takes you through your own posts, as adding a username does not work), i found:

News: I'm thirty and related ramblings
Verbiage: So, 32 is here
Verbiage: Ding! I just hit 33.
Verbiage: 42 is here

Really? That's it? I'm such a lazy bum. But, i don't have to do it anyway. It's just something i value.

I always seem to remember the 30 post because my b-in-law commented that he liked the line, "I went from being a twenty-something to a thirty-nothing". In a sense, any time someone says a "line" is a good one, i'll think myself special for coming up with it. Heh. Half the time i don't even need anyone else to tell me, i guess i just forget about those "inspirations" more quickly.

As for "I, myself, seem to be about fourteen or fifteen, but wiser for the wear. If not for my experiences, I doubt I'd be much different." I cannnot say that anymore. Midlife changes you, and i am definitely different. But that feeling does persist to some extent anyway. It must be partially true.

In the 32 post, i didn't care so much. Same. Birthdays are just dates on the calendar, and perhaps a time to reflect. Not much more.

33: asks "Ah, to be forty, to be over the hill. Will it be then that i can finally enjoy life?" Heh.

42 just laments, and i feel the same now.

---

Regarding age, my thoughts are now about 50. I didn't really feel 40 until i was 43 or so. But i've felt 50 for a few years already. I'm not a young chicken, even if at time i wish i were like one. Age is just a number, but it's also an indicator of the journey.

I've been on slashdot for about 25 years now. Wow. I even got that 25 years on ebay email with a $25 coupon.

So, i'm writing a program/dll to make API calls to a data provider. For the API itself, disregarding authentication, i have only implemented GETs. Well, i just did a POST and after verifying it worked, i thought to myself, "that was my first post". At which point, i felt a strong need to post that here.

There is a Michigan Left directly opposite the exit of a busy parking lot. It's difficult to explain a mindset, but there are three major reasons you exit: to go straight to the turnaround, to turn right onto the street, to turn right and get on the freeway. All three options are common.

The turnaround has a sign that says, "turn right only". I argued with a friend if that meant, "do not go straight" or if it meant "if you turn, it must be to the right" because it is one way traffic. Turn signs here are confusingly placed; even when you get on the freeway there's a no left turn sign. What's up with that?

Anyway. i went straight out of the parking lot today (just like the guy before me did a minute or two earlier) and a cop stopped me, telling me i couldn't do that. I told him, everyone does it and there are two lanes in the turn (no line marker, but you could easily fit two cars in there with room to spare) and there is only one lane leading up to it. (It is obviously made to do this, because of the placement of the turnaround (which came second) and the two lanes.) He told me the width was there for long cars and i almost did not give him enough room to make the turn. (He was in an unmarked car, though from the looks of it, i figured he was probably a cop anyway. I did the turn right in front of him, because i thought it was legal.) He also explain the solid white line barred the maneuver. He was nice the entire time, and i was not argumentative. Anyway, i got a ticket for "improper lane usage". The remarks read (in all caps):

Drove across NB Greenfield RD from private drive to get to turn around lane 4 lanes over over solid white line

That sounds pretty bad. Heh.

Anyway. the street is shared by two cities, and northbound isn't in his, so, i wonder how he could give me the ticket anyway. That is, the violation itself was in a different city that he is supposed to patrol.

The court date is in 14 days. I have to speak to a lawyer and see what's going on. At the very least, i can finally get clarity on two things: can a cop of one city give tickets in another, and is that straight across drive allowed.

The Cambridge volume has been released. A bit expensive, so i will either have to wait for the price to come down or to borrow it from a library that may have it.

I started this search on 9/10/13, and now it ends about 5.5 years later. Time passes and things happen, i guess.

Project Gutenberg finally has De Temperamentis available for reading. It took nearly 5 years but its done. Wow.

The Cambrige volume has a release date of February 2019, but is still not out. I assume it will be done very soon.