ged
Execute raw sql using WITH CLAUSE failed when two with clauses are present
Original report by Jerko Čulina (Bitbucket: JerkoCulina, ).
Not sure if this is right place for this error, sorry if I am wrong.
When executing raw query with one WITH clause everything works fine. Please see example:
ActiveRecord::Base.connection.execute(sql)
(7.1ms)
WITH occupancy_types AS (
SELECT *
FROM (
VALUES
(1, 'free'),
(2, 'crowded'),
(3, 'full'),
(4, 'closed')
) AS occupancy_types(id, symbol)
)
SELECT *
FROM parking_place_occupancies
JOIN occupancy_types ON occupancy_types.id = parking_place_occupancies.occupancy_type_id
WHERE occupancy_types.id = 1
=> #<PG::Result:0x007fe0b02a9020 status=PGRES_TUPLES_OK ntuples=4525 nfields=10 cmd_tuples=4525>
When executing raw query with two WITH clauses I got async_exec error. Please see example:
ActiveRecord::Base.connection.execute(sql)
(0.8ms)
WITH occupancy_types AS (
SELECT *
FROM (
VALUES
(1, 'free'),
(2, 'crowded'),
(3, 'full'),
(4, 'closed')
) AS occupancy_types(id, symbol)
),
icon_groups AS (
SELECT *
FROM (
VALUES
(1, 'enabled'),
(2, 'disabled'),
(3, 'occupancy'),
(4, 'badge'),
(5, 'reservable')
) AS icon_groups(id, symbol)
)
SELECT *
FROM parking_place_occupancies
JOIN occupancy_types ON occupancy_types.id = parking_place_occupancies.occupancy_type_id
where occupancy_types.id = 1
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near " SELECT"
LINE 4: SELECT *
^
:
WITH occupancy_types AS (
SELECT *
FROM (
VALUES
(1, 'free'),
(2, 'crowded'),
(3, 'full'),
(4, 'closed')
) AS occupancy_types(id, symbol)
),
icon_groups AS (
SELECT *
FROM (
VALUES
(1, 'enabled'),
(2, 'disabled'),
(3, 'occupancy'),
(4, 'badge'),
(5, 'reservable')
) AS icon_groups(id, symbol)
)
SELECT *
FROM parking_place_occupancies
JOIN occupancy_types ON occupancy_types.id = parking_place_occupancies.occupancy_type_id
where occupancy_types.id = 1
from /Users/pc/.rbenv/versions/2.3.0/lib/ruby/gems/2.3.0/gems/activerecord-4.2.11.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec'
Ruby 2.3.0
Rails 4.2.11
pg (0.21)
Original comment by Chris Bandy (Bitbucket: cbandy, GitHub: cbandy).
That error message is coming from the PostgreSQL server. I’ve not seen a syntax error inside whitespace like that before. Is it possible there’s some hidden character(s) there?
Original comment by Jerko Čulina (Bitbucket: JerkoCulina, ).
This sql is crated in Postico where it works fine. Then I copied it in rails and tried it in rails console and got this error. Then I took sql from error message in rails console and paste it in Postico again, and it works fine.
I tried to find if there’s some hidden character but can’t find any.
Did you find an solution to that. I am having similar issue.
PG::SyntaxError: ERROR: syntax error at or near "MATERIALIZED" (PG::SyntaxError)
LINE 1: ...N ('draft', 'suspended')), "documents_filtered" AS MATERIALIZ...
^
The problem I do not understand why this error is reported. I have no issue to run this query manually via psql.
The error is correct. The query was ran on a PG 11.
I think this issue can be closed.