`UNION` with `ORDER BY` and `LIMIT` produced wrong result in multi-node version but not in single-node version (1)

[r33s3n6]

Describe the problem UNION with ORDER BY and LIMIT produced wrong result in multi-node version but not in single-node version

To Reproduce First execute init.sql to create the table. Then executing error.sql yields unexpected results. Note that reproducing these results might not be entirely stable. Typically, it can be completed within three attempts. You can try executing multiple times or execute it again after rebuilding the table. init.sql.txt error.sql.txt

Expected behavior The multi-node version of CockroachDB produced incorrect results. The correct result should have only been one line, but it outputted 48 lines instead. output_re_main.log output_re_single.log

Environment:

• CockroachDB version: 23.1.9
• Server OS: Linux a002 6.5.0-18-generic #18~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Wed Feb 7 11:40:03 UTC 2 x86_64 x86_64 x86_64 GNU/Linux
• Client app: postgresql odbc/psql

docker compose file:

version: '3.5'

services:
  crdb1:
    image: cockroachdb/cockroach:v23.1.19
    command: "start --advertise-addr=crdb1:26357 --http-addr=crdb1:8080 --listen-addr=crdb1:26357 --sql-addr=crdb1:26257 --insecure --join=crdb1:26357,crdb2:26357,crdb3:26357"
    volumes:
      - "crdb1_data:/cockroach/cockroach-data"
    networks:
      crnet:
        ipv4_address: 10.0.5.11

  crdb2:
    image: cockroachdb/cockroach:v23.1.19
    command: "start --advertise-addr=crdb2:26357 --http-addr=crdb2:8080 --listen-addr=crdb2:26357 --sql-addr=crdb2:26257 --insecure --join=crdb1:26357,crdb2:26357,crdb3:26357"
    volumes:
      - "crdb2_data:/cockroach/cockroach-data"
    networks:
      crnet:
        ipv4_address: 10.0.5.12

  crdb3:
    image: cockroachdb/cockroach:v23.1.19
    command: "start --advertise-addr=crdb3:26357 --http-addr=crdb3:8080 --listen-addr=crdb3:26357 --sql-addr=crdb3:26257 --insecure --join=crdb1:26357,crdb2:26357,crdb3:26357"
    volumes:
      - "crdb3_data:/cockroach/cockroach-data"
    networks:
      crnet:
        ipv4_address: 10.0.5.13

  crdb_single:
    image: "cockroachdb/cockroach:v23.1.19"

    command: start-single-node --insecure
    volumes:
      - "crdb_single_data:/cockroach/cockroach-data"
    networks:
      crnet:
        ipv4_address: 10.0.5.21

volumes:
  crdb1_data:
  crdb2_data:
  crdb3_data:
  crdb_single_data:

networks:
  crnet:
    driver: bridge
    ipam:
      config:
        - subnet: 10.0.5.0/24
          gateway: 10.0.5.254

about us

We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned possible vulnerabilities that may lead to database logic error.

Jira issue: CRDB-38140

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[r33s3n6]

I apologize for accidentally mixing up the attachments for the SQL output results of these two issues(#112960 and #112961 ). I have now edited and corrected them. I'm very sorry for the confusion.

[michae2]

Thank you for filing the error report @r33s3n6! Using your corrected files, I have successfully reproduced on a 3-node roachprod cluster running the tip of master (379d332c9a4ce49cda4a3565a852e7ddb850ffb5).

[mgartner]

Now that we have a repro, let's figure out how to prioritize fixing it.