"No such column" error for table alias defined inside brackets

[Iosipos Mpekenmpaouer]

Hello,

I have the same problem that is described here
http://stackoverflow.com/questions/8112936/sqlexception-no-such-column

When I am running the query directly in Sqlite, everything works fine.

The exception appears in conn.prepareStatement line.

I am using sqlite-jdbc-3.7.15-SNAPSHOT-2.jar in Ubuntu 12.04 with Java 6 Oracle.

[Grace B]

Are you referring to the query below?

select * 
from (  person as p 
        inner join company as c on p.p_id=c.p_id
     ) 
     inner join contact as ct on p.p_id=ct.p_id 
 where p.p_id=?;

[Iosipos Mpekenmpaouer]

On Tuesday, May 14, 2013 1:56:31 AM UTC+3, Grace B wrote:

Are you referring to the query below?

select * 
from (  person as p 
        inner join company as c on p.p_id=c.p_id
     ) 
     inner join contact as ct on p.p_id=ct.p_id 
 where p.p_id=?;

My query is similar. If I replace the question mark with a value it runs normally in Sqlite, but within Java it throws exception even if I execute it as normal statement (with the value instead of the question mark of course). The error is exactly that :

no such column: p.p_id

[Grace B]

Not sure what is going on your end. The following works on my computer. Can you try it on your computer

        Class.forName("org.sqlite.JDBC");

        Connection conn = DriverManager.getConnection("jdbc:sqlite:");

        Statement stat = conn.createStatement();

        stat.execute("create table person (p_id primary key, p_name)");

        stat.execute("insert into person values (1, 'employee')");

        stat.execute("create table contact (p_id primary key, p_name)");

        stat.execute("insert into contact values (1, 'contact')");

        stat.execute("create table company (p_id primary key, p_name)");

        stat.execute("insert into company values (1, 'company')");

        PreparedStatement stmt = conn.prepareStatement(

            "select * from ( person as p " + 

                "inner join company as c on p.p_id=c.p_id " +

                ") " +

                "inner join contact as ct on p.p_id=ct.p_id " +

            "where p.p_id=?;");

        stmt.setInt(1, 1);

        ResultSet rs = stmt.executeQuery();

        rs.next();

        System.out.println(

                rs.getString(1) + "|" + rs.getString(2) + "|" +

                rs.getString(3) + "|" + rs.getString(4) + "|" +

                rs.getString(5) + "|" + rs.getString(6));

[Iosipos Mpekenmpaouer]

Yes, you are right, that works, but this doesn't:
select * from contact as ct inner join ( person as p inner join company as c on p.p_id=c.p_id )  on p.p_id=ct.p_id where p.p_id=1;

[Grace B]

What are you trying to accomplish with these queries?

[Iosipos Mpekenmpaouer]

I have some auto-generated queries from Sesame RDF framework. Do you think this is a driver issue?

[Grace B]

Nope, it is not a driver issue. 

For SQLite, this the framework is generated a syntactically wrong SQL statement according to http://www.sqlite.org/syntaxdiagrams.html#join-source.

And I actually doubt another that particular query works on any other database.

Anyways, the fix for it is to add a nested select statement as shown below

select * from 

contact as ct 

inner join 

(select * from person as p inner join company as c on p.p_id=c.p_id) as p

on p.p_id=ct.p_id where p.p_id=1

Also note, that this query and the one mentioned at the beginning of this thread will return the same set of data.

[Iosipos Mpekenmpaouer]

Well, that's strange, because the query runs normally in Sqlite terminal. Furthermore, according to the syntax diagrams I can't see why this query is wrong. Anyway, I 'll try to figure something out. Thanks for your responses.

[Peter Borissow]

Interesting. If the JDBC driver is passing the raw SQL to SQLite and SQLite is throwing an error, I wonder if the SQLite terminal is updating the SQL in some way? Someone would need to throw it into a debugger to see.

---

From: Iosipos Mpekenmpaouer <iosipos.mp...@gmail.com>
To: xer...@googlegroups.com
Sent: Wednesday, May 15, 2013 8:35 AM
Subject: [xerial 720] Re: "No such column" error for table alias defined inside brackets

[Iosipos Mpekenmpaouer]

It seems that I have found the problem, and indeed it's not a driver issue. The query runs normally on terminal with Sqlite 3.7.16.2, while with Sqlite 3.7.9 it returns the aforementioned error. Thanks both for your interest and answers.

[Grace Batumbya]

On Wed, May 15, 2013 at 10:18 AM, Iosipos Mpekenmpaouer <iosipos.mp...@gmail.com> wrote:

It seems that I have found the problem, and indeed it's not a driver issue. The query runs normally on terminal with Sqlite 3.7.16.2, while with Sqlite 3.7.9 it returns the aforementioned error. Thanks both for your interest and answers.

In that case I will update the driver to use the latest SQLite library.