<p dir="auto"><strong>Apache Airflow version</strong>: 2.0.0</p>
<p dir="auto"><strong>What happened</strong>:</p>
<p dir="auto">While investigating issues relating to task getting stuck, I saw this sql error in postgres logs. I am not entirely sure of what it impacts but I thought of letting you know.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="ERROR:  column &quot;connection.password&quot; must appear in the GROUP BY clause or be used in an aggregate function at character 8
STATEMENT:  SELECT connection.password AS connection_password, connection.extra AS connection_extra, connection.id AS connection_id, connection.conn_id AS connection_conn_id, connection.conn_type AS connection_conn_type, connection.description AS connection_description, connection.host AS connection_host, connection.schema AS connection_schema, connection.login AS connection_login, connection.port AS connection_port, connection.is_encrypted AS connection_is_encrypted, connection.is_extra_encrypted AS connection_is_extra_encrypted, count(connection.conn_id) AS count_1 
        FROM connection GROUP BY connection.conn_id 
        HAVING count(connection.conn_id) &gt; 1
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT connection.password AS connection_password, connection.extra AS connection_extra, connection.id AS connection_id, connection.conn_id AS connection_conn_id, connection.conn_type AS connection_conn_type, connection.description AS connection_description, connection.host AS connection_host, connection.schema AS connection_schema, connection.login AS connection_login, connection.port AS connection_port, connection.is_encrypted AS connection_is_encrypted, connection.is_extra_encrypted AS connection_is_extra_encrypted 
        FROM connection 
        WHERE connection.conn_type IS NULL"><pre class="notranslate"><code class="notranslate">ERROR:  column "connection.password" must appear in the GROUP BY clause or be used in an aggregate function at character 8
STATEMENT:  SELECT connection.password AS connection_password, connection.extra AS connection_extra, connection.id AS connection_id, connection.conn_id AS connection_conn_id, connection.conn_type AS connection_conn_type, connection.description AS connection_description, connection.host AS connection_host, connection.schema AS connection_schema, connection.login AS connection_login, connection.port AS connection_port, connection.is_encrypted AS connection_is_encrypted, connection.is_extra_encrypted AS connection_is_extra_encrypted, count(connection.conn_id) AS count_1 
        FROM connection GROUP BY connection.conn_id 
        HAVING count(connection.conn_id) &gt; 1
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT connection.password AS connection_password, connection.extra AS connection_extra, connection.id AS connection_id, connection.conn_id AS connection_conn_id, connection.conn_type AS connection_conn_type, connection.description AS connection_description, connection.host AS connection_host, connection.schema AS connection_schema, connection.login AS connection_login, connection.port AS connection_port, connection.is_encrypted AS connection_is_encrypted, connection.is_extra_encrypted AS connection_is_extra_encrypted 
        FROM connection 
        WHERE connection.conn_type IS NULL
</code></pre></div>
<p dir="auto"><strong>How to reproduce it</strong>:</p>
<ol dir="auto">
<li>Run <code class="notranslate">docker-compose run initdb</code></li>
<li>Run <code class="notranslate">docker-compose run upgradedb</code></li>
</ol>
<details> <summary> Here's my docker-compose </summary>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="version: &quot;3.2&quot;
networks:
    airflow:

services:
    postgres:
        container_name: af_postgres
        image: postgres:9.6
        environment:
            - POSTGRES_USER=airflow
            - POSTGRES_DB=airflow
            - POSTGRES_PASSWORD=airflow
        volumes:
            - ./postgresql/data:/var/lib/postgresql/data
        command: &gt;
            postgres
            -c listen_addresses=*
            -c logging_collector=on
            -c log_destination=stderr
        networks:
            - airflow
    initdb:
        container_name: af_initdb
        image: docker.io/apache/airflow:2.0.0-python3.7
        environment:
            - AIRFLOW__CORE__SQL_ALCHEMY_CONN=postgresql+psycopg2://airflow:airflow@postgres:5432/airflow
        depends_on:
            - postgres
        entrypoint: /bin/bash
        command: -c &quot;airflow db init&quot;
        networks:
            - airflow
    upgradedb:
        container_name: af_upgradedb
        image: docker.io/apache/airflow:2.0.0-python3.7
        environment:
            - AIRFLOW__CORE__SQL_ALCHEMY_CONN=postgresql+psycopg2://airflow:airflow@postgres:5432/airflow
        depends_on:
            - postgres
        entrypoint: /bin/bash
        command: -c &quot;airflow db upgrade&quot;
        networks:
            - airflow"><pre class="notranslate"><code class="notranslate">version: "3.2"
networks:
    airflow:

services:
    postgres:
        container_name: af_postgres
        image: postgres:9.6
        environment:
            - POSTGRES_USER=airflow
            - POSTGRES_DB=airflow
            - POSTGRES_PASSWORD=airflow
        volumes:
            - ./postgresql/data:/var/lib/postgresql/data
        command: &gt;
            postgres
            -c listen_addresses=*
            -c logging_collector=on
            -c log_destination=stderr
        networks:
            - airflow
    initdb:
        container_name: af_initdb
        image: docker.io/apache/airflow:2.0.0-python3.7
        environment:
            - AIRFLOW__CORE__SQL_ALCHEMY_CONN=postgresql+psycopg2://airflow:airflow@postgres:5432/airflow
        depends_on:
            - postgres
        entrypoint: /bin/bash
        command: -c "airflow db init"
        networks:
            - airflow
    upgradedb:
        container_name: af_upgradedb
        image: docker.io/apache/airflow:2.0.0-python3.7
        environment:
            - AIRFLOW__CORE__SQL_ALCHEMY_CONN=postgresql+psycopg2://airflow:airflow@postgres:5432/airflow
        depends_on:
            - postgres
        entrypoint: /bin/bash
        command: -c "airflow db upgrade"
        networks:
            - airflow
</code></pre></div>
</details>
<p dir="auto"><strong>Anything else we need to know</strong>:</p>
<p dir="auto">Upon looking the code, I believe having <code class="notranslate">Connection.conn_id</code> <a href="https://github.com/apache/airflow/blob/ab5f770bfcd8c690cbe4d0825896325aca0beeca/airflow/utils/db.py#L613">here</a> will resolve the sql syntax error.</p>
<h3 dir="auto">Apache Airflow Provider(s)</h3>
<p dir="auto">google</p>
<h3 dir="auto">Versions of Apache Airflow Providers</h3>
<p dir="auto">7.0.0</p>
<h3 dir="auto">Apache Airflow version</h3>
<p dir="auto">2.2.5</p>
<h3 dir="auto">Operating System</h3>
<p dir="auto">google composer</p>
<h3 dir="auto">Deployment</h3>
<p dir="auto">Composer</p>
<h3 dir="auto">Deployment details</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">What happened</h3>
<p dir="auto">BigQueryInsertJobOperator can not be used to run SQL query without specifying destination table explicitly in the operator</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&quot;query&quot;: {
                &quot;query&quot;: f&quot;&quot;&quot;
                          INSERT INTO `{{{{params.project}}}}-{os.getenv('COMPOSER_ENVIRONMENT_STAGE')}.{{{{params.dataset}}}}.{{{{params.table_order_objects}}}}`
                          SELECT
                              *
                          FROM
                              `{{{{params.project}}}}-{os.getenv('COMPOSER_ENVIRONMENT_STAGE')}.{{{{params.dataset}}}}.{{{{params.table_order_objects}}}}_SZ`;
                          TRUNCATE TABLE `{{{{params.project}}}}-{os.getenv('COMPOSER_ENVIRONMENT_STAGE')}.{{{{params.dataset}}}}.{{{{params.table_order_objects}}}}_SZ`;
                          &quot;&quot;&quot;,
                &quot;useLegacySql&quot;: False,
            }

Traceback (most recent call last):
  File &quot;/opt/python3.8/lib/python3.8/site-packages/airflow/providers/google/cloud/operators/bigquery.py&quot;, line 2269, in execute
    table = job.to_api_repr()[&quot;configuration&quot;][&quot;query&quot;][&quot;destinationTable&quot;]"><pre class="notranslate"><code class="notranslate">"query": {
                "query": f"""
                          INSERT INTO `{{{{params.project}}}}-{os.getenv('COMPOSER_ENVIRONMENT_STAGE')}.{{{{params.dataset}}}}.{{{{params.table_order_objects}}}}`
                          SELECT
                              *
                          FROM
                              `{{{{params.project}}}}-{os.getenv('COMPOSER_ENVIRONMENT_STAGE')}.{{{{params.dataset}}}}.{{{{params.table_order_objects}}}}_SZ`;
                          TRUNCATE TABLE `{{{{params.project}}}}-{os.getenv('COMPOSER_ENVIRONMENT_STAGE')}.{{{{params.dataset}}}}.{{{{params.table_order_objects}}}}_SZ`;
                          """,
                "useLegacySql": False,
            }

Traceback (most recent call last):
  File "/opt/python3.8/lib/python3.8/site-packages/airflow/providers/google/cloud/operators/bigquery.py", line 2269, in execute
    table = job.to_api_repr()["configuration"]["query"]["destinationTable"]
</code></pre></div>
<h3 dir="auto">What you think should happen instead</h3>
<p dir="auto">To run a SQL a destination is not necessary to be specified</p>
<h3 dir="auto">How to reproduce</h3>
<p dir="auto">Airflow 2.2.5 and airflow provider 7.0.0, BigQueryInsertJobOperator  without specifying destinationTable</p>
<h3 dir="auto">Anything else</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">Are you willing to submit PR?</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Yes I am willing to submit a PR!</li>
</ul>
<h3 dir="auto">Code of Conduct</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I agree to follow this project's <a href="https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md">Code of Conduct</a></li>
</ul>