No error thrown for a wrong query

[bedarkarpriyanka]

Does not throw any errors for

echo "select from b;" | pgsanity

[markdrago]

So, I just tried this out, and I was just as surprised as you, but "select from b;" is valid SQL. I did the following test with postgresql-9.6.1:

test=# create table b (id bigint, str text);
CREATE TABLE
test=# insert into b values (1, 'a');
INSERT 0 1
test=# insert into b values (2, 'b');
INSERT 0 1
test=# select * from b;
 id | str 
----+-----
  1 | a
  2 | b
(2 rows)

test=# select from b;
--
(2 rows)

It looks like it runs the select but then doesn't return any columns of data. I have no idea why this would be useful except perhaps as a worse version of 'select count(*) from b;'

[markdrago]

I spoke with a colleague (@djljr) about this and he pointed me towards the postgresql docs where this is mentioned. They say, "The list of output expressions after SELECT can be empty, producing a zero-column result table. This is not valid syntax according to the SQL standard. PostgreSQL allows it to be consistent with allowing zero-column tables. However, an empty list is not allowed when DISTINCT is used."

Search this page for "Empty SELECT Lists": https://www.postgresql.org/docs/9.6/static/sql-select.html

[bedarkarpriyanka]

Thanks a lot for the reply. I have some more doubts. It does not throw an error for the following as well -

1) echo "select a,b from foo group by 1,2,3;" | pgsanity 2) echo "select a,count(b) from foo ;" | pgsanity 3) echo "select a,count(b) from foo group by 1,2,3;" | pgsanity

[markdrago]

Interesting. I think what's happening here is that these are valid from a syntax perspective, but are still guaranteed to fail at runtime. I understand that this may not be satisfying.

For example, the following statement is valid SQL: select id, count(*) from b group by 1;

While the following, AFAIK, is guaranteed to fail: select id, count(*) from b group by 5;

Pgsanity is not in a great position, due to its reliance on ecpg, to detect the differences between these two statements. I would say that if you need that level of checking you'd be better off actually connecting to your database and running your SQL in a transaction that you rollback.

[bedarkarpriyanka]

Okay. Thank you!