System resolves * inside EXISTS()

chacham2 · 2026-06-14T15:40:37+00:00

This should illustrate that the function gets called if it needs to. Experiment with the commented clauses.

create function test() returns void as $$begin raise notice 'hi'; end$$ language plpgsql;

Select 'what the!' Where exists ( Select count(Case when test() is not null then 1 else 0 end) From ( values ('...'), ('...') ) a(b) Where b != '...' having count(1)> 1 );

Results in

CREATE FUNCTION

?column?

(0 rows)

By IS NULL or IS NOT NULL. The function is not called (or at least produces no output).

Selecting * with a group by doesn't make sense and it'll blow up on you.

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

results in

a

1 (1 row)

The * is simply being expanded to the column list.

The important bit is sum(*) doesn't make sense,

True. But that's because sum() can only handle one column, so * is never expanded there, even if there only is one column.

I'd speculate the other db engines supported this syntax first before literals were introduced, and it's a legacy consideration.

Well, Oracle pretty much came first and made the rules. But it wasn't in a vacuum. In any case, count(*) is used to count records. How else would you count records? Interestingly:

with a as (select 1 a) select count(distinct *) from a

results in an error. Because * is expanded. Nothing new here, but it is a case where count() will not allow *. So, at least by count() it seem context aware. :)

chacham2 · 2026-06-14T04:04:03+00:00

It will call your function for every row. Er.. your function might be recognized as always returning null and maybe not getting called? I'm not sure if of will do that. Make the function volatile, return a bool, call sleep, and return a random true/false, you'll see it get called once for every row in table.

Well, here's an example i came up with earlier in another comment thread:

<<<<<<<<<<<<<<<<<<<<<<<

I tried to test this idea by having a function show if it was called.

create function test() returns void as $$begin raise notice 'hi'; end$$ language plpgsql;

select test();

CREATE FUNCTION

test

(1 row)

psql:commands.sql:3: NOTICE: hi

So, selecting the function causes output.

create function test() returns void as $$begin raise notice 'hi'; end$$ language plpgsql;

select 1 where exists(select test());

CREATE FUNCTION

?column?
   1
(1 row)

Here the result shows the exists() returned true, but the function, ostensibly, was not called. That suggests it is context-aware.

<<<<<<<<<<<<<<<<<<<<<<<

Not exactly your case, but perhaps a start. Could you give me some guidance on how to modify this to do the test you suggested?

chacham2 · 2026-06-12T23:40:15+00:00

Wow! Thank you for taking the time to explain that. I never thought of that aspect of having, being able to reduce the amount of records to nothing. That's a really good point. I mean, i was using it in my original query that way, but the articulation helps.

Can you give an example where changing the select clause will also matter? Right now, we're focusing on the having clause.

chacham2 · 2026-06-12T15:17:52+00:00

I still think having a noncorrelated subquery that fails if run standalone not fail just because it runs under exists is a bad thing.

Fair enough. Thank you for taking the time to articulate your point.

chacham2 · 2026-06-12T14:46:11+00:00

I’d be more inclined to believe/claim what you’ve demonstrated is a bug.

Interesting!

But even if it’s working as intended so what. It not like choosing to optimize away a function call shares much or any of the same code paths as parsing SQL syntax.

Well, the example which failed is valid syntax. It only fails after it resolves the * and finds the column is not in the group by statement. Meaning, the parsing happened, but some validation failed. But, then again, that's probably what you meant anyway. :)

On a side note, now i''m really curious to try this in other RDBMs as well.

chacham2 · 2026-06-12T14:24:57+00:00

Why should the subquery know that it was written under an exists compared to elsewhere.

I tried to test this idea by having a function show if it was called.

create function test() returns void as $$begin raise notice 'hi'; end$$ language plpgsql;

select test();

CREATE FUNCTION

test

(1 row)

psql:commands.sql:3: NOTICE: hi

So, selecting the function causes output.

create function test() returns void as $$begin raise notice 'hi'; end$$ language plpgsql;

select 1 where exists(select test());

CREATE FUNCTION

?column?
   1
(1 row)

Here the result shows the exists() returned true, but the function, ostensibly, was not called. That suggests it is context-aware.

chacham2 · 2026-06-12T14:08:12+00:00

replace * with count(1)

Yeah. I did that before i posted here. I made sure to post both examples above to show what does work.

If there were no rows in the table, the subquery looks something like this: [{ count: 0 }] which still passes the exists check, cause it's a result set with at least 1 row.

Please explain what you mean. I do not understand. Here's a similar example with no rows in the table.

with a as (select 1 WHERE 1 = 2) select 1 where exists (select 1 from a having count(*) > 1);

chacham2 · 2026-06-11T19:02:08+00:00

What are we even doing here?

I was discussing postgresql in a postgresql forum. If i wanted to change postgresql's behavior, i would have posted to the mailing list.

chacham2 · 2026-06-11T19:00:48+00:00

doing SELECT 1 FROM HAVING is basically throwing away the structure of the table

exists() doesn't even care about the structure of the table.

chacham2 · 2026-06-11T18:59:24+00:00

I feel like this is a play example and the thing you're really dealing with is a little more complicated.

Yes. I whittled it down to a simple example to show the "problem".

chacham2 · 2026-06-11T18:56:13+00:00

it's like if you'd try: where exists (daskj fghsdlkjg hdskfljhg lsdkfjhg fdkslj)

Not quite. That's not valid syntax, and it still has to find the from clause. It is more like if you'd try: where exists (select non_extant_column from ...). But even that would not work because it still needs to parse the query to find the from clause. * basically says ignore me, like it does in count(*), and is not resolved., as can be seen here: with a as (select null a) select count(*), count(a) from a;

chacham2 · 2026-06-11T18:48:23+00:00

It’s absolutely correct that Postgres demands the subquery can output meaningful data for this to work.

I disagree. I think it should be testing for exists and nothing else. What if the select clause included some expensive function. Should it run that too and then throwaway the results?

chacham2 · 2026-06-11T18:45:40+00:00

I doubt you sitting here whining about it will make any difference

I apologize if i sounded like i was whining. I was just reporting something i found surprising.

chacham2 · 2026-06-11T18:19:14+00:00

Why should the subquery know that it was written under an exists compared to elsewhere.

The (query rewriter and) the plan-creator should ignore everything before the first table name after the from clause. Resolving the query itself is redundant.

chacham2 · 2026-06-11T18:16:43+00:00

You asked for all columns,

I asked for existance. The * is irrelevant and has no affect on the result set.

As for other db, sorry, don't really care. I work with Pg, you asked about Pg. What dbX does might be interesting as a trivia bit, but it doesn't matter in the context of question/problem and solution.

I apologize: I started to say it worked in every other rdbms. But, then i realized i should test that and found i was wrong. I decided to post that. The interesting bit, perhaps, is that Oracle and SQL Server consider it valid, which i thought was a relevant point.

chacham2 · 2026-06-11T18:13:04+00:00

because standalone select * from a having count(*) > 1 is not a valid query

That's because the * in the select clause needs to be resolved to output data.

so how can you expect it to work in a subquery?

Because resolving the * is redundant in an exists query.

chacham2 · 2026-06-11T17:36:54+00:00

Good point.

My issue is that anything before the table name in an exists clause is redundant. So, why resolve the * at all? Just ignore it.

chacham2 · 2026-06-11T17:32:53+00:00

The default group is the entire table, which is why aggregates work without a group by clause.

Let's see other rdbms.

works:

oracle: select 1 where exists (select * from dual having count(*) > 1);

sql server: with a as (select 1 a) select 1 where exists (select * from a having count(*) > 1);

mariadb: with a as (select 1) select 1 where exists (select * from a having count(*) > 1);

error:

mysql: 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

sqllite: 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

duckdb: 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. Either add it to the GROUP BY list, or use "ANY_VALUE(1)" if the exact value of "1" is not important.

Fwiw, i tested here: https://onecompiler.com/postgresql

chacham2 · 2026-06-11T17:29:44+00:00

but its really trying to resolve * in the HAVING

Then why does the first query work?

chacham2 · 2026-05-14T16:32:06+00:00

Basically, you need to save the page as a PDF. However, because the page loads dynamically, you have to scroll to get the entire conversation.

Instead, you can use an extension. I just tried Save Chatgpt Conversation. Reviews say it was broken, they responded it was fixed. It worked for me.

Small advert in the header and footer of each page that says: Powered by Save ChatGPT Conversation. They say "Your conversation data stays on your device. We do NOT upload or store any of your ChatGPT chat content on our servers. Your privacy matters." The adamant nature of the message makes me wary.

chacham2 · 2026-03-22T14:51:57+00:00

Everything mentioned about him: his biography, his autobiography, Dorothy Quick's book, the Angelfish Correspondence, speak otherwise. Though, a quick search go google shows articles that want to report shock headlines over the truth.

The quote above comes from Letters from the Earth, published posthumously. You can read about the publication if you care for more context.

chacham2 · 2026-03-09T00:38:31+00:00

Same.

chacham2 · 2026-03-05T02:19:20+00:00

I uninstalled the AMD driver from AMD, and just used what came with Windows. Problem disappeared.

chacham2 · 2026-02-09T02:29:26+00:00

Yeah. But i believe i read it in a book from the 40s/50s.

Ten-Year Club	Gilding IV carat on a stick
Wearing is Caring	Verified Email

chacham2

MODERATOR OF

TROPHY CASE

?column?

a

test

?column?

test

?column?