Обсуждение: Syntax problem for a newbie

Dear all,

I have a syntax problem but I don't find the clue.
Actually I adapt an mySQL query to a postgreSQL but I got a message
error that I can't interpret.

SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as
subpath,l.id-1 as level
FROM  graph_path g
    INNER JOIN term AS t1
    INNER JOIN term AS t2  ON (t2.id = g.term2_id)
    INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id
 AND g.distance+1 >= l.id)
WHERE t2.name = 'blood_coagulation' and g.term1_id=1
ORDER BY g.id, subpath;

ERROR:  syntax error at or near "WHERE" at character 284
LINE 7: WHERE t2.name = 'blood_coagulation' and g.term1_id=1

Let me know if you need details concerning the tables but I don't think
it is necesseray.
Hopefully I did a newbie mistake.

Thanks in advance,

Fred

"Fred" <frederic.fleche@gmail.com> writes:
> I have a syntax problem but I don't find the clue.
> Actually I adapt an mySQL query to a postgreSQL but I got a message
> error that I can't interpret.

> SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as
> subpath,l.id-1 as level
> FROM  graph_path g
>     INNER JOIN term AS t1
>     INNER JOIN term AS t2  ON (t2.id = g.term2_id)
>     INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id
>  AND g.distance+1 >= l.id)
> WHERE t2.name = 'blood_coagulation' and g.term1_id=1
> ORDER BY g.id, subpath;

You're short an ON condition: there has to be an ON for every JOIN.
Or turn the first INNER JOIN into a CROSS JOIN, so it doesn't need an ON.

Does MySQL really accept that as-is?  (Standards compliance was never
their strong point :-()

            regards, tom lane

Fred wrote:
> Dear all,
>
> I have a syntax problem but I don't find the clue.
> Actually I adapt an mySQL query to a postgreSQL but I got a message
> error that I can't interpret.
>
> SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as
>
The thing that stands out to me is the syntax for the substring
function, see:
http://www.postgresql.org/docs/8.1/static/functions-string.html

Hope this helps,

--

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

On Fri, 2006-05-05 at 12:49 -0400, Tom Lane wrote:
> "Fred" <frederic.fleche@gmail.com> writes:
> > I have a syntax problem but I don't find the clue.
> > Actually I adapt an mySQL query to a postgreSQL but I got a message
> > error that I can't interpret.
>
> > SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as
> > subpath,l.id-1 as level
> > FROM  graph_path g
> >     INNER JOIN term AS t1
> >     INNER JOIN term AS t2  ON (t2.id = g.term2_id)
> >     INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id
> >  AND g.distance+1 >= l.id)
> > WHERE t2.name = 'blood_coagulation' and g.term1_id=1
> > ORDER BY g.id, subpath;
>
> You're short an ON condition: there has to be an ON for every JOIN.
> Or turn the first INNER JOIN into a CROSS JOIN, so it doesn't need an ON.
>
> Does MySQL really accept that as-is?  (Standards compliance was never
> their strong point :-()
>

Yes, MySQL (4.1.14) quite happily accepts that as-is...

--

Russ

Russ Brown <pickscrape@gmail.com> writes:
> On Fri, 2006-05-05 at 12:49 -0400, Tom Lane wrote:
>> "Fred" <frederic.fleche@gmail.com> writes:
>>> SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as
>>> subpath,l.id-1 as level
>>> FROM  graph_path g
>>> INNER JOIN term AS t1
>>> INNER JOIN term AS t2  ON (t2.id = g.term2_id)
>>> INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id
>>> AND g.distance+1 >= l.id)
>>> WHERE t2.name = 'blood_coagulation' and g.term1_id=1
>>> ORDER BY g.id, subpath;
>>
>> Does MySQL really accept that as-is?  (Standards compliance was never
>> their strong point :-()

> Yes, MySQL (4.1.14) quite happily accepts that as-is...

[ tries it... ] Hm, 5.0.x is no better.  This is really bad, because it
shows that they completely misimplemented the JOIN syntax.  The above
query is ambiguous because it's not clear which JOIN each ON is supposed
to go with.  Per spec, you can write something like

    FROM a JOIN b JOIN c ON b_c_cond ON a_bc_cond

which is supposed to be parenthesized as

    FROM a JOIN (b JOIN c ON b_c_cond) ON a_bc_cond

so that the conditions are associated with the joins I named them after.
If you parse things so that ON is optional then it's completely unclear
which JOINs the ONs that are there are supposed to go with.  And this
matters, particularly for outer joins.

mysql> select * from a join b left join c on (b1=c1) on (a1=b1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
versionfor the right syntax to use near 'on (a1=b1)' at line 1 

Wonderful :-(

            regards, tom lane