[sqlite] Can't order a view with joins

[Fitzharris, Jason]

Hi,
  I am getting an error when trying to do an order by on a view which has a
join. To replicate, create the following two tables and the view.

CREATE TABLE tabTest_1 (
  Field_1_1 CHAR(10),
  Field_1_2 CHAR(10),
  Field_1_3 CHAR(10),
  Field_1_4 CHAR(10)
)

CREATE TABLE tabTest_2 (
  Field_2_1 CHAR(10),
  Field_2_2 CHAR(10),
  Field_2_3 CHAR(10),
  Field_2_4 CHAR(10)
)

CREATE VIEW vTest
AS
SELECT *
FROM tabTest_1, tabTest_2
WHERE Field_1_1 = Field_2_1

There is no need to insert any data. Now do a simple select from the view
like so.

SELECT * FROM vTest

This works fine as it returns all eight columns. Now try and run this query.

SELECT * FROM vTest ORDER BY Field_1_1

This fails with the message "Select Error: no such column: Field_1_1"
However if I drop the view and recreate it by specifying all of the fields
like so.

CREATE VIEW vTest
AS
SELECT Field_1_1, Field_1_2, Field_1_3, Field_1_4, Field_2_1, Field_2_2,
Field_2_3, Field_2_4
FROM tabTest_1, tabTest_2
WHERE Field_1_1 = Field_2_1

Now if I run the second query with the order by it works fine.

Is this a bug with SQLite? And if so, is there any indication if/when it
will be fixed?

I have many views which join several tables which have many columns, and I
don't want to spend ages writing out all of the columns if a fix is pending.






---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]