.github/workflows/main.yml

name: ci

on:
  push:
    branches:
      - main
    paths:
      - 'src/**'
      - '.github/workflows/*.yml'
  pull_request:
    branches:
      - main
    paths:
      - 'src/**'
      - '.github/workflows/*.yml'

jobs:
  misc:
    name: General tests
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Verify
        run: mvn -B verify -DskipTests=true
      - name: Misc Tests
        run: mvn -B '-Dtest=!sqlancer.dbms.**,!sqlancer.qpg.**,!sqlancer.reducer.**' test
      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.10'
      - name: Naming Convention Tests
        run: python src/check_names.py

  citus:
    name: DBMS Tests (Citus)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Set up Citus
        run: |
          echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee  /etc/apt/sources.list.d/pgdg.list
          curl https://install.citusdata.com/community/deb.sh | sudo bash
          sudo apt-get -y install  postgresql-15-citus-11.1
          sudo chown -R $USER:$USER /var/run/postgresql
          export PATH=/usr/lib/postgresql/15/bin:$PATH
          cd ~
          mkdir -p citus/coordinator citus/worker1 citus/worker2
          initdb -D citus/coordinator
          initdb -D citus/worker1
          initdb -D citus/worker2
          echo "shared_preload_libraries = 'citus'" >> citus/coordinator/postgresql.conf
          echo "shared_preload_libraries = 'citus'" >> citus/worker1/postgresql.conf
          echo "shared_preload_libraries = 'citus'" >> citus/worker2/postgresql.conf
          pg_ctl -D citus/coordinator -o "-p 9700" -l coordinator_logfile start || cat coordinator_logfile || cat citus/coordinator/coordinator_logfile
          pg_ctl -D citus/worker1 -o "-p 9701" -l worker1_logfile start
          ls citus/worker1
          pg_ctl -D citus/worker2 -o "-p 9702" -l worker2_logfile start
          psql -c "CREATE ROLE sqlancer SUPERUSER LOGIN CREATEDB PASSWORD 'sqlancer';" -p 9700 -d postgres -U $USER
          createdb test -p 9700 -U $USER
          psql -c "CREATE ROLE sqlancer SUPERUSER LOGIN CREATEDB PASSWORD 'sqlancer';" -p 9701 -d postgres -U $USER
          createdb test -p 9701 -U $USER
          psql -c "CREATE ROLE sqlancer SUPERUSER LOGIN CREATEDB PASSWORD 'sqlancer';" -p 9702 -d postgres -U $USER
          createdb test -p 9702 -U $USER
          psql -c "CREATE EXTENSION citus;" -p 9700 -U $USER -d test
          psql -c "CREATE EXTENSION citus;" -p 9701 -U $USER -d test
          psql -c "CREATE EXTENSION citus;" -p 9702 -U $USER -d test
          psql -c "SELECT * from citus_add_node('localhost', 9701);" -p 9700 -U $USER -d test
          psql -c "SELECT * from citus_add_node('localhost', 9702);" -p 9700 -U $USER -d test
      - name: Run Tests
        run: CITUS_AVAILABLE=true mvn -Dtest=TestCitus test

  clickhouse:
    name: DBMS Tests (ClickHouse)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Set up ClickHouse
        run: |
          docker pull clickhouse/clickhouse-server:head
          docker run --ulimit nofile=262144:262144 --name clickhouse-server -p8123:8123 -d clickhouse/clickhouse-server:head
          sleep 5
      - name: Run Tests
        run: CLICKHOUSE_AVAILABLE=true mvn -Dtest=ClickHouseBinaryComparisonOperationTest,TestClickHouse,ClickHouseOperatorsVisitorTest,ClickHouseToStringVisitorTest test
      - name: Show fatal errors
        run: docker exec clickhouse-server grep Fatal /var/log/clickhouse-server/clickhouse-server.log || echo No Fatal Errors found
      - name: Teardown ClickHouse server
        run: |
          docker stop clickhouse-server
          docker rm clickhouse-server

  cockroachdb:
    name: DBMS Tests (CockroachDB)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Set up CockroachDB
        run: |
          wget -qO- https://binaries.cockroachdb.com/cockroach-v23.1.0-beta.2.linux-amd64.tgz | tar  xvz
          cd  cockroach-v23.1.0-beta.2.linux-amd64/ && ./cockroach start-single-node --insecure &
          sleep 10
      - name: Create SQLancer user
        run: cd cockroach-v23.1.0-beta.2.linux-amd64/ && ./cockroach sql --insecure -e "CREATE USER sqlancer; GRANT admin to sqlancer" && cd ..
      - name: Run Tests
        run: COCKROACHDB_AVAILABLE=true mvn -Dtest=TestCockroachDB test

  cockroachdb-qpg:
    name: QPG Tests (CockroachDB)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Set up CockroachDB
        run: |
          wget -qO- https://binaries.cockroachdb.com/cockroach-v23.1.0-beta.2.linux-amd64.tgz | tar  xvz
          cd  cockroach-v23.1.0-beta.2.linux-amd64/ && ./cockroach start-single-node --insecure &
          sleep 10
      - name: Create SQLancer user
        run: cd cockroach-v23.1.0-beta.2.linux-amd64/ && ./cockroach sql --insecure -e "CREATE USER sqlancer; GRANT admin to sqlancer" && cd ..
      - name: Run Tests
        run: COCKROACHDB_AVAILABLE=true mvn -Dtest=TestCockroachDBQPG test

  databend:
    name: DBMS Tests (Databend)
    runs-on: ubuntu-latest
    services:
      databend:
        image: datafuselabs/databend
        env:
          QUERY_DEFAULT_USER: sqlancer
          QUERY_DEFAULT_PASSWORD: sqlancer
        ports:
          - 8000:8000
          - 3307:3307
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Run Tests
        run: |
          DATABEND_AVAILABLE=true mvn -Dtest=TestDatabend test

  duckdb:
    name: DBMS Tests (DuckDB)
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build
        run: mvn -B package -DskipTests=true
      - name: DuckDB Tests
        run: mvn -Dtest=TestDuckDB test

  h2:
    name: DBMS Tests (H2)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Run Tests
        run: H2_AVAILABLE=true mvn -Dtest=TestH2 test

  mariadb:
    name: DBMS Tests (MariaDB)
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Install MariaDB
        run: |
          sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
          sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.3/ubuntu bionic main'
          sudo apt update
          sudo apt install mariadb-server
          sudo systemctl start mariadb
      - name: Create SQLancer User
        run: sudo mysql -uroot -proot -e "CREATE USER 'sqlancer'@'localhost' IDENTIFIED BY 'sqlancer'; GRANT ALL PRIVILEGES ON * . * TO 'sqlancer'@'localhost';"
      - name: Run Tests
        run: MARIADB_AVAILABLE=true mvn -Dtest=TestMariaDB test

  materialize:
    name: DBMS Tests (Materialize)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up Materialize
        run: |
          docker pull materialize/materialized:latest
          docker run -d -p6875:6875 -p6877:6877 -p 26257:26257 materialize/materialized:latest
          sleep 5
          # Workaround for https://github.com/cockroachdb/cockroach/issues/93892
          psql postgres://root@localhost:26257 -c "SET CLUSTER SETTING sql.stats.forecasts.enabled = false"
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Run Tests
        run: |
          MATERIALIZE_AVAILABLE=true mvn test -Dtest=TestMaterialize
          MATERIALIZE_AVAILABLE=true mvn test -Dtest=TestMaterializePQS

  materialize-qpg:
    name: QPG Tests (Materialize)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up Materialize
        run: |
          docker pull materialize/materialized:latest
          docker run -d -p6875:6875 -p6877:6877 -p 26257:26257 materialize/materialized:latest
          sleep 5
          # Workaround for https://github.com/cockroachdb/cockroach/issues/93892
          psql postgres://root@localhost:26257 -c "SET CLUSTER SETTING sql.stats.forecasts.enabled = false"
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Run Tests
        run: |
          MATERIALIZE_AVAILABLE=true mvn test -Dtest=TestMaterializeQPG
          MATERIALIZE_AVAILABLE=true mvn test -Dtest=TestMaterializeQueryPlan

  mysql:
    name: DBMS Tests (MySQL)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Set up MySQL
        run: |
          sudo systemctl start mysql.service # MySQL 8.0.32-0ubuntu0.22.04.2
      - name: Create SQLancer user
        run: mysql -uroot -proot -e "CREATE USER 'sqlancer'@'localhost' IDENTIFIED BY 'sqlancer'; GRANT ALL PRIVILEGES ON * . * TO 'sqlancer'@'localhost';"
      - name: Run Tests
        run: |
          MYSQL_AVAILABLE=true mvn test -Dtest=TestMySQLPQS
          MYSQL_AVAILABLE=true mvn test -Dtest=TestMySQLTLP

  postgres:
    name: DBMS Tests (PostgreSQL)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up PostgreSQL
        uses: harmon758/postgresql-action@v1.0.0
        with:
          postgresql version: '12'
          postgresql user: 'sqlancer'
          postgresql password: 'sqlancer'
          postgresql db: 'test'
      - name: Set up JDK 11
        uses: actions/setup-java@v1
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Run Tests
        run: POSTGRES_AVAILABLE=true mvn -Dtest=TestPostgres test

  sqlite:
    name: DBMS Tests (SQLite)
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build
        run: mvn -B package -DskipTests=true
      - name: SQLite Tests
        run: |
          mvn -Dtest=TestSQLitePQS test
          mvn -Dtest=TestSQLite3 test

  sqlite-qpg:
    name: QPG Tests (SQLite)
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build
        run: mvn -B package -DskipTests=true
      - name: SQLite Tests for QPG
        run: |
          mvn -Dtest=TestSQLiteQPG test

  stonedb:
    name: DBMS Tests (StoneDB)
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up StoneDB
        run: |
          docker run -p 3306:3306 -itd  -e MYSQL_ROOT_PASSWORD='123456'  stoneatom/stonedb
          ContainerID=$(docker ps --filter ancestor=stoneatom/stonedb --format "{{.ID}}") 
          docker exec $ContainerID bash
          sleep 60s
          docker exec $ContainerID sed -i "s/tianmu_insert_delayed=1/tianmu_insert_delayed=0/" /opt/stonedb57/install/my.cnf
          sleep 3s
          docker exec $ContainerID /opt/stonedb57/install/bin/mysql -uroot -p123456 -e "CREATE USER 'sqlancer'@'%' IDENTIFIED WITH mysql_native_password BY 'sqlancer'; GRANT ALL PRIVILEGES ON *.* TO 'sqlancer'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;"
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build
        run: mvn -B package -DskipTests=true
      - name: StoneDB Tests
        run: |
          STONEDB_AVAILABLE=true mvn test -Dtest=TestStoneDBNoRec
          STONEDB_AVAILABLE=true mvn test -Dtest=TestStoneDBTLP

  tidb:
    name: DBMS Tests (TiDB)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Set up TiDB
        run: |
          docker pull pingcap/tidb:latest
          docker run --name tidb-server -d -p 4000:4000 pingcap/tidb:latest
          sleep 10
      - name: Create SQLancer user
        run: sudo mysql -h 127.0.0.1 -P 4000 -u root -D test -e "CREATE USER 'sqlancer'@'%' IDENTIFIED WITH mysql_native_password BY 'sqlancer'; GRANT ALL PRIVILEGES ON *.* TO 'sqlancer'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;"
      - name: Run Tests
        run: TIDB_AVAILABLE=true mvn -Dtest=TestTiDB test

  tidb-qpg:
    name: QPG Tests (TiDB)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Set up TiDB
        run: |
          docker pull pingcap/tidb:latest
          docker run --name tidb-server -d -p 4000:4000 pingcap/tidb:latest
          sleep 10
      - name: Create SQLancer user
        run: sudo mysql -h 127.0.0.1 -P 4000 -u root -D test -e "CREATE USER 'sqlancer'@'%' IDENTIFIED WITH mysql_native_password BY 'sqlancer'; GRANT ALL PRIVILEGES ON *.* TO 'sqlancer'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;"
      - name: Run Tests
        run: TIDB_AVAILABLE=true mvn -Dtest=TestTiDBQPG test

  yugabyte:
    name: DBMS Tests (YugabyteDB)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Set up Yugabyte
        run: |
          docker pull yugabytedb/yugabyte:latest
          docker run -d --name yugabyte -p7000:7000 -p9000:9000 -p5433:5433 -p9042:9042 yugabytedb/yugabyte:latest bin/yugabyted start --daemon=false
          sleep 5
      - name: Run Tests
        run: YUGABYTE_AVAILABLE=true mvn -Dtest=TestYugabyte test


  doris:
    name: DBMS Tests (Apache Doris)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1
        with:
          java-version: 11
      - name: install mysql client
        run: |
          sudo apt update
          sudo apt install mysql-client --assume-yes
      - name: Set up Apache Doris
        run: |
          sudo sysctl -w vm.max_map_count=2000000
          LASTEST_TAG=$(curl -s GET https://api.github.com/repos/apache/doris/releases | jq -r '.[].tag_name' | sed -n 1p)
          LASTEST_TAG_BIG_VERSION=$(echo ${LASTEST_TAG} | awk '{split($1, arr, "."); print arr[1]"."arr[2]}')
          curl -LJO "https://www.apache.org/dyn/mirrors/mirrors.cgi?action=download&filename=doris/${LASTEST_TAG_BIG_VERSION}/${LASTEST_TAG}/apache-doris-fe-${LASTEST_TAG}-bin-x86_64.tar.xz"
          curl -LJO "https://www.apache.org/dyn/mirrors/mirrors.cgi?action=download&filename=doris/${LASTEST_TAG_BIG_VERSION}/${LASTEST_TAG}/apache-doris-be-${LASTEST_TAG}-bin-x86_64.tar.xz"
          curl -LJO "https://www.apache.org/dyn/mirrors/mirrors.cgi?action=download&filename=doris/${LASTEST_TAG_BIG_VERSION}/${LASTEST_TAG}/apache-doris-dependencies-${LASTEST_TAG}-bin-x86_64.tar.xz"
          mkdir ./doris
          tar xf apache-doris-fe-${LASTEST_TAG}-bin-x86_64.tar.xz -C ./doris && mv doris/apache-doris-fe-${LASTEST_TAG}-bin-x86_64 doris/fe
          tar xf apache-doris-be-${LASTEST_TAG}-bin-x86_64.tar.xz -C ./doris && mv doris/apache-doris-be-${LASTEST_TAG}-bin-x86_64 doris/be
          tar xf apache-doris-dependencies-${LASTEST_TAG}-bin-x86_64.tar.xz -C ./doris && mv doris/apache-doris-dependencies-${LASTEST_TAG}-bin-x86_64 doris/dependencies
          cp doris/dependencies/*.jar doris/be/lib/
          doris/fe/bin/start_fe.sh --daemon
          doris/be/bin/start_be.sh --daemon
          sudo apt install libnet-ifconfig-wrapper-perl --assume-yes
          IP=$(ifconfig eth0 | grep inet | grep -v inet6 | awk '{print $2}')
          mysql -u root -h 127.0.0.1 --port 9030 -e "ALTER SYSTEM ADD BACKEND '${IP}:9050';"
          mysql -u root -h 127.0.0.1 --port 9030 -e "CREATE USER 'sqlancer' IDENTIFIED BY 'sqlancer'; GRANT ALL ON *.* TO sqlancer;"
      - name: Build SQLancer
        run: mvn -B package -DskipTests=true
      - name: Run Tests
        run: DORIS_AVAILABLE=true mvn -Dtest=TestDoris test


  java13:
    name: Java 13 Compatibility (DuckDB)
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 13
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 13
      - name: Build
        run: mvn -B package -DskipTests=true
      - name: Shortly run DuckDB
        run: cd target && java -jar $(ls | grep -P 'sqlancer-[0-9.]*.jar') --num-threads 4 --timeout-seconds 30 --num-queries 0 duckdb

  java14:
    name: Java 14 Compatibility (DuckDB)
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 14
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 14
      - name: Build
        run: mvn -B package -DskipTests=true
      - name: Shortly run DuckDB
        run: cd target && java -jar $(ls | grep -P 'sqlancer-[0-9.]*.jar') --num-threads 4 --timeout-seconds 30 --num-queries 0 duckdb

  java15:
    name: Java 15 EA Compatibility (DuckDB)
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 15
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 15-ea
      - name: Build
        run: mvn -B package -DskipTests=true
      - name: Shortly run DuckDB
        run: cd target && java -jar $(ls | grep -P 'sqlancer-[0-9.]*.jar') --num-threads 4 --timeout-seconds 30 --num-queries 0 duckdb

  reducer:
    name: Reducer Tests
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Set up JDK 11
        uses: actions/setup-java@v1.4.4
        with:
          java-version: 11
      - name: Build
        run: mvn -B package -DskipTests=true
      - name: Run Tests
        run: |
          mvn -Dtest=TestStatementReducer test