Syntax error in joined subquery

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Syntax error in joined subquery

Perry Trolard-2
Dear All,

I'd appreciate anyone's input about a query that, in a particular form, produces a syntax error but in a slightly different but semantically equivalent form does not. I've either made a subtle mistake or found a bug, I believe.

Given a simple database created by 

  -- DDL
  create table person (id integer, name varchar(255));
  create table book (id integer, title varchar(255), author varchar(255));
  insert into person values (1, 'bill'), (2, 'joe');
  insert into person values (100, 'book1', 'joe'), (101, 'book2', 'bill), (102, 'book3', 'joe'), (103, 'book4', 'unknown');

the following query returns results as expected:

  -- Q1
  SELECT book.author 
  FROM   person 
         INNER JOIN ( book 
                      INNER JOIN (SELECT person.id   AS ID, 
                                         person.name AS NAME 
                                  FROM   person) class_map_context 
                              ON ( class_map_context.name = book.author )) 
                 ON person.id = class_map_context.id 

However, if in the nested inner join the order of the tables "book" & "class_map_context" is reversed, I receive a syntax error. The offending query is

  -- Q2
  SELECT book.author 
  FROM   person 
         INNER JOIN ( (SELECT person.id   AS ID, 
                            person.name AS NAME 
                     FROM   person) class_map_context 
                      INNER JOIN book 
                              ON ( class_map_context.name = book.author )) 
                 ON person.id = class_map_context.id 

Again, the only difference between Q2 & Q1 is that in Q2 class_map_context INNER JOINs book, whereas in Q1 book INNER JOINs class_map_context. The stacktrace produced is

  java.sql.SQLSyntaxErrorException: unexpected token: CLASS_MAP_CONTEXT required: ) : line: 6
  [...]
  Caused by: unexpected token: CLASS_MAP_CONTEXT required: ) : line: 6
              (Unknown Source) org.hsqldb.error.Error.parseError
              (Unknown Source) org.hsqldb.ParserBase.unexpectedTokenRequire
              (Unknown Source) org.hsqldb.ParserBase.readThis
              (Unknown Source) org.hsqldb.ParserDQL.XreadTableSubqueryOrNull
              (Unknown Source) org.hsqldb.ParserDQL.readTableOrSubquery
              (Unknown Source) org.hsqldb.ParserDQL.XreadTableReference
              (Unknown Source) org.hsqldb.ParserDQL.XreadFromClause
              (Unknown Source) org.hsqldb.ParserDQL.XreadTableExpression
              (Unknown Source) org.hsqldb.ParserDQL.XreadQuerySpecification
              (Unknown Source) org.hsqldb.ParserDQL.XreadSimpleTable
              (Unknown Source) org.hsqldb.ParserDQL.XreadQueryPrimary
              (Unknown Source) org.hsqldb.ParserDQL.XreadQueryTerm
              (Unknown Source) org.hsqldb.ParserDQL.XreadQueryExpressionBody
              (Unknown Source) org.hsqldb.ParserDQL.XreadQueryExpression
              (Unknown Source) org.hsqldb.ParserDQL.compileCursorSpecification
              (Unknown Source) org.hsqldb.ParserCommand.compilePart
              (Unknown Source) org.hsqldb.ParserCommand.compileStatement
              (Unknown Source) org.hsqldb.Session.compileStatement
              (Unknown Source) org.hsqldb.StatementManager.compile
              (Unknown Source) org.hsqldb.Session.execute

Sincere thanks for any help,
Perry


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Syntax error in joined subquery

Fred Toussi-2
The second form is not yet supported. Will be supported in a future version.
 
If you add WHERE clauses to , Nested joins such as these do not perform as well as flat joins
 
Fred
 
On Fri, Jun 1, 2012, at 14:53, Perry Trolard wrote:
Dear All,
 
I'd appreciate anyone's input about a query that, in a particular form, produces a syntax error but in a slightly different but semantically equivalent form does not. I've either made a subtle mistake or found a bug, I believe.
 
Given a simple database created by 
 
  -- DDL
  create table person (id integer, name varchar(255));
  create table book (id integer, title varchar(255), author varchar(255));
  insert into person values (1, 'bill'), (2, 'joe');
  insert into person values (100, 'book1', 'joe'), (101, 'book2', 'bill), (102, 'book3', 'joe'), (103, 'book4', 'unknown');
 
the following query returns results as expected:
 
  -- Q1
  SELECT book.author 
  FROM   person 
         INNER JOIN ( book 
                      INNER JOIN (SELECT person.id   AS ID, 
                                         person.name AS NAME 
                                  FROM   person) class_map_context 
                              ON ( class_map_context.name = book.author )) 
                 ON person.id = class_map_context.id
 
However, if in the nested inner join the order of the tables "book" & "class_map_context" is reversed, I receive a syntax error. The offending query is
 
  -- Q2
  SELECT book.author 
  FROM   person 
         INNER JOIN ( (SELECT person.id   AS ID, 
                            person.name AS NAME 
                     FROM   person) class_map_context 
                      INNER JOIN book 
                              ON ( class_map_context.name = book.author )) 
                 ON person.id = class_map_context.id
 
Again, the only difference between Q2 & Q1 is that in Q2 class_map_context INNER JOINs book, whereas in Q1 book INNER JOINs class_map_context. The stacktrace produced is
 
  java.sql.SQLSyntaxErrorException: unexpected token: CLASS_MAP_CONTEXT required: ) : line: 6
  [...]
  Caused by: unexpected token: CLASS_MAP_CONTEXT required: ) : line: 6
              (Unknown Source) org.hsqldb.error.Error.parseError
              (Unknown Source) org.hsqldb.ParserBase.unexpectedTokenRequire
              (Unknown Source) org.hsqldb.ParserBase.readThis
              (Unknown Source) org.hsqldb.ParserDQL.XreadTableSubqueryOrNull
              (Unknown Source) org.hsqldb.ParserDQL.readTableOrSubquery
              (Unknown Source) org.hsqldb.ParserDQL.XreadTableReference
              (Unknown Source) org.hsqldb.ParserDQL.XreadFromClause
              (Unknown Source) org.hsqldb.ParserDQL.XreadTableExpression
              (Unknown Source) org.hsqldb.ParserDQL.XreadQuerySpecification
              (Unknown Source) org.hsqldb.ParserDQL.XreadSimpleTable
              (Unknown Source) org.hsqldb.ParserDQL.XreadQueryPrimary
              (Unknown Source) org.hsqldb.ParserDQL.XreadQueryTerm
              (Unknown Source) org.hsqldb.ParserDQL.XreadQueryExpressionBody
              (Unknown Source) org.hsqldb.ParserDQL.XreadQueryExpression
              (Unknown Source) org.hsqldb.ParserDQL.compileCursorSpecification
              (Unknown Source) org.hsqldb.ParserCommand.compilePart
              (Unknown Source) org.hsqldb.ParserCommand.compileStatement
              (Unknown Source) org.hsqldb.Session.compileStatement
              (Unknown Source) org.hsqldb.StatementManager.compile
              (Unknown Source) org.hsqldb.Session.execute
 
Sincere thanks for any help,
Perry
 
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user