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

Hi Laurenz, thanks for your reply, but I think it is wrong -

On Wed, Feb 16, 2022 at 1:24 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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.
>

if you go to an online PostgreSQL editor like
https://extendsclass.com/postgresql-online.html

and just enter my simple test code below,
you will see that wrongly "__CORRECT__" is printed:

DROP TABLE words_nouns;
DROP TABLE words_verbs;

CREATE TABLE words_nouns (
word text PRIMARY KEY, -- CHECK is added below
hashed text NOT NULL,
expl text
);

CREATE TABLE words_verbs (
word text PRIMARY KEY, -- CHECK is added below
hashed text NOT NULL
);

ALTER TABLE words_nouns
ADD CONSTRAINT words_nouns_word_check
CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я'
);

ALTER TABLE words_verbs
ADD CONSTRAINT words_verbs_word_check
CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я' AND
word !~ 'Ц[ЮЯ]' AND
(word ~ '[ТЧ]ЬСЯ$' OR
word ~ '[ТЧ]Ь$' OR
word ~ 'ТИ$')
);

CREATE OR REPLACE FUNCTION words_guess_puzzle(
in_mid bigint,
in_guess text,
OUT out_text text
) RETURNS text AS
$func$
BEGIN
in_guess := UPPER(in_guess);

-- 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;

out_text := '___CORRECT___'
RETURN;
END
$func$ LANGUAGE plpgsql;

SELECT words_guess_puzzle(123, 'ABCDE');