Bug report

[x] I confirm this is a bug with Supabase, not with my own application.
[x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

This is the database function:


DECLARE
    v_unmapped_emails TEXT[] := '{}'; -- Initialize the array
    emails TEXT[];
    email_obj_id INTEGER;
    available_emails TEXT[];
    new_msg_id TEXT;
    existing_email_obj_id INTEGER;
    new_thread_id TEXT;
    existing_thread_id INTEGER;
    combined_emails TEXT[];
    v_connection_id TEXT;
    existing_connection_id TEXT;
BEGIN
    new_msg_id := email_obj->>'msg_id';
    new_thread_id := email_obj->>'thread_id';
    v_connection_id := email_obj->>'connection_id';

    SELECT connection_id INTO existing_connection_id FROM integration_connection WHERE connection_id = v_connection_id;

    IF FOUND THEN
      raise log 'rpc save_email log: found connection_id %', existing_connection_id;
      SELECT id INTO existing_email_obj_id FROM email_obj WHERE msg_id = new_msg_id AND thread_id = new_thread_id;

      IF NOT FOUND THEN
          raise log 'rpc save_email log: no existing email_obj entry with this msg_id and thread_id';
          -- Extract the 'from', 'to', 'cc_recipients', and 'bcc_recipients' from the email object and combine them into the emails array
          emails := ARRAY[(email_obj->'from'->'emailAddress'->>'address')] 
                    || ARRAY(SELECT (jsonb_array_elements(email_obj->'to')->'emailAddress'->>'address')) 
                    || ARRAY(SELECT (jsonb_array_elements(email_obj->'cc_recipients')->'emailAddress'->>'address')) 
                    || ARRAY(SELECT (jsonb_array_elements(email_obj->'bcc_recipients')->'emailAddress'->>'address'));

          available_emails := public.get_phonebook_emails(emails);

          IF array_length(available_emails, 1) > 0 THEN
              raise log 'rpc save_email log: have phonebook emails';
              -- Insert the thread_id into the thread table
              SELECT id INTO existing_thread_id
              FROM thread
              WHERE thread_id = new_thread_id;

              IF NOT FOUND THEN
                  INSERT INTO thread (thread_id, involved_emails, connection_id)
                  VALUES (new_thread_id, array_to_json(emails)::jsonb, v_connection_id);

                  v_unmapped_emails := public.process_and_save_emails(emails, new_thread_id, v_connection_id);
              ELSE
                  combined_emails := public.get_filtered_emails(emails);
                  v_unmapped_emails := public.process_and_save_emails(emails, new_thread_id, v_connection_id);

                  UPDATE thread
                  SET involved_emails = array_to_json(combined_emails)::jsonb
                  WHERE id = existing_thread_id;
              END IF;

              -- Insert the email object into the email_obj table
              INSERT INTO email_obj (created_at, "from", "to", cc_recipients, bcc_recipients, thread_id, subject, body, received_date_time, web_link, sent_by_us, msg_id, has_attachments, is_read)
              VALUES ((email_obj->>'created_at')::timestamptz, email_obj->'from', email_obj->'to', email_obj->'cc_recipients', email_obj->'bcc_recipients', email_obj->>'thread_id', email_obj->>'subject', email_obj->'body', (email_obj->>'received_date_time')::timestamptz, email_obj->>'web_link', (email_obj->>'sent_by_us')::boolean, email_obj->>'msg_id', (email_obj->>'has_attachments')::boolean, (email_obj->>'is_read')::boolean)
              RETURNING id INTO email_obj_id;

              -- If the v_unmapped_emails array is not empty, update the unmapped_senders in the email_obj table
              IF array_length(v_unmapped_emails, 1) > 0 THEN
                  UPDATE email_obj
                  SET unmapped_senders = array_to_json(v_unmapped_emails)::jsonb
                  WHERE id = email_obj_id;
              END IF;
          END IF;
      END IF;
    END IF;

    RETURN email_obj;
END;

If I have two entries for the integration_connection with the same email then there are two related connection_id values for them. Under this condition, the issue could be reproduced.

As given in the query it uses v_connection_id (it is just read from jsonb input) to do check if it is already available.

SELECT connection_id INTO existing_connection_id FROM integration_connection WHERE connection_id = v_connection_id;

when the execution reaches the insertion below which is inside nested conditions as given:

 INSERT INTO thread (thread_id, involved_emails, connection_id)
                  VALUES (new_thread_id, array_to_json(emails)::jsonb, v_connection_id);

what happens is v_connection_id has changed to the second connection_id (from the second entry I mentioned above). It should never happen as we have never changed it when reaching to insertion.

This is the table:

Workaround fix

I could use email_obj->>'connection_id' everywhere it is wanted for now.

Expected behavior

This shouldn't happen as it was never changed once after the initial read.

Screenshots

If applicable, add screenshots to help explain your problem.

System information

OS: [e.g. macOS, Windows]
Browser (if applies) [e.g. chrome, safari]
Version of supabase-js: [e.g. 6.0.2]
Version of Node.js: [e.g. 10.10.0]

Additional context

Add any other context about the problem here.

supabase / supabase

When reading a declared vaiable (once initialized from a jsonb input) in a database function (in plpgsql) gives another value when read from nested blocks #29130