Issue 1:
Apache Airflow version: 2.0.0

What happened:

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.

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

How to reproduce it:

Run docker-compose run initdb

Run docker-compose run upgradedb

Here's my docker-compose

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

Anything else we need to know:

Upon looking the code, I believe having Connection.conn_id here will resolve the sql syntax error.

Issue 2:
Apache Airflow Provider(s)

google

Versions of Apache Airflow Providers

7.0.0

Apache Airflow version

2.2.5

Operating System

google composer

Deployment

Composer

Deployment details

No response

What happened

BigQueryInsertJobOperator can not be used to run SQL query without specifying destination table explicitly in the operator

"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"]

What you think should happen instead

To run a SQL a destination is not necessary to be specified

How to reproduce

Airflow 2.2.5 and airflow provider 7.0.0, BigQueryInsertJobOperator without specifying destinationTable

Anything else

No response

Are you willing to submit PR?

Yes I am willing to submit a PR!

Code of Conduct

I agree to follow this project's Code of Conduct