0

So, I get this error message:

EDT ERROR:  syntax error at or near "union" at character 436

The query in question is a large query that consists of 12 smaller queries all connected together with UNION ALL, and each small query has two inner join statements. So, something like:

SELECT table.someid as id
    ,table.lastname as name
    ,table2.groupname as groupname
    , 'Leads     ' as Type
from table 
inner join table3 on table3.specificid = table.someid
INNER JOIN table2 on table3.specificid=table2.groupid
where table3.deleted=0 
 and table.someid > 0
 and table2.groupid in ('2','3','4')
LIMIT 5 
UNION all 
query2....

Note that table2 and table3 are the same tables in each query, and the fields from table2 and table3 are also the same, I think.

Quick question (I am still kinda new to all this):
What does 'Leads ' as Type mean? Unlike the other statements preceding an AS, this one isn't written like table.something.
Quick edit question: What does table2.groupid in ('2','3','4') mean?

I checked each small query one by one, each one works and returns a result, though the results are always empty for some reason(this may or may not be dependent on the user logged in though, as some PHP code generated this query).

As for the results themselves, most of them look something like this (they are arranged horizontally though):

id(integer)
name (character varying(80))
groupname (character varying(100))
type (unknown)

The difference in the results are twofold: 1)Most of the results contain the same field names but quite a few of them have different field lengths. Like some will say character varying (80), while others will say character varying (100), please correct me if this is actually not field length. 2)2 of the queries contain different fields, but only the id field is different, and it's probably because they don't have the "as id" part.

I am not quite sure of what the requirements of UNION ALL are, but if I think, it is meant to only work if all the fields are the same, but if that funky number changes (the one in the brackets), then are the fields considered to be different even if they have the same name?

Also, what's strange is that some of the queries returned the exact same fields, with the same field length, so I tried to UNION ALL only those queries, but no luck, still got a syntax error at UNION.

Another important thing I should mention is that the DB used to be MySQL, but we changed to PostGreSQL, so this bug might be a result of the change (i.e. code that might work in MySQL but not in PostGres).

Thanks for your time.

wildplasser
  • 43,142
  • 8
  • 66
  • 109
zermy
  • 611
  • 1
  • 11
  • 25
  • 1
    something you should consider is reviewing your tables. You state 'Note that table2 and table3 are the same tables in each query, and the fields from table2 and table3 are also the same, I think.' The I think, is a red flag. – Taryn Sep 21 '11 at 17:03
  • In the `select` clause, the `as` keyword names a column. In your case you'll get 'Leads ' in the Type column on every row. – Mike Partridge Sep 21 '11 at 17:14
  • @bluefeet Sorry, I wasn't very clear, when I said I think fields are the same, I meant that the queries use the same tables and the same fields for those tables (e.g. each query used table3.specificid). – zermy Sep 21 '11 at 17:56

3 Answers3

2

You can have only one "LIMIT xxx" clause. At the end of the query, and not before the UNION.

wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • Thanks a lot, this was probably the problem (the query works if I remove all the Limits except the last one, now I just need to fix the PHP code. Hmm. – zermy Sep 21 '11 at 18:02
2

The error you get is due to missing parentheses here:

...
LIMIT 5
UNION all 
...

The manual:

(ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

Later example:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you! Is the LIMIT restriction only in Postgresql? – zermy Sep 21 '11 at 18:01
  • 1
    Generally yes, but in mysql you can do something like this (example from the [docs](http://dev.mysql.com/doc/refman/5.6/en/union.html)): `(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);` – Erwin Brandstetter Sep 21 '11 at 18:06
  • Edit: you can do *exactly* the same in PostgreSQL: `(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);` That gives you 20 rows. You could add another LIMIT at the end .. – Erwin Brandstetter Sep 21 '11 at 18:22
  • Oh, thanks for that, I was just going to go and blindly remove the limits, which would have been ok most likely, but I like your order by solution. Important note: The brackets are important! – zermy Sep 21 '11 at 18:55
1

The only real way I have found to debug big queries is to break it into understandable parts and debug each subexpression independently:

  • Does each show the expected rows?
  • Are the resulting fields and types as expected?
  • For union, do the result fields and types exactly match corresponding other subexpressions?
wallyk
  • 56,922
  • 16
  • 83
  • 148