Re: if not exists (SELECT 1... UNION SELECT 1...)

On Wed, 2022-02-16 at 11:51 +0100, Alexander Farber wrote:
> when I search for a non existent word in the two tables hosted in PostgreSQL 14.1 then I get zero records as expected:
>
> words_en=> SELECT 1 FROM words_nouns WHERE word = 'ABCDE' UNION SELECT 1 FROM words_verbs WHERE word = 'ABCDE';
>  ?column?
> ----------
> (0 rows)
>
> But when I try to use the same command in my stored function, then it goes through,
> as if the word would exist (and a new record is inserted into the words_puzzle table):
>
> CREATE OR REPLACE FUNCTION words_guess_puzzle(
>     in_mid       bigint,
>     in_social    integer,
>     in_sid       text,
>     in_auth      text,
>     in_guess     text,
>     OUT out_text text
> ) RETURNS text AS
> $func$
> [...]
>     -- check if the in_guess is a valid word - WHY DOES THIS NEVER TRIGGER?
>   IF NOT EXISTS(SELECT 1 FROM words_nouns WHERE word = in_guess UNION
>                   SELECT 1 FROM words_verbs WHERE word = in_guess) THEN
>         out_text := '___WRONG___'
>         RETURN;
>     END IF;
> [...]
>
> What could be the reason please?

One reason could be index corruption. If one query uses an index and the other doesn't,
that could lead to different results.

The other option is of course a trivial error, like you are using a different search
string or connect to a different database.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com