9/19/06, P Kishor <punkish (AT) eidesis (DOT) orgwrote:

CREATE VIEW v_attr AS
SELECT pt.id AS pt_id, Count(pt.id) AS pt_id_count, py.attr AS py_attr
FRM pt JIN py N
(
(pt.x BETWEEN py.xmin AND py.xmax) AND
(pt.y BETWEEN py.ymin AND py.ymax)
)
GRUP BY pt_id_count
HAVING pt_id_count = 1
)

Since you have a count() and a group by it's going to have to execute
the entire query, then summarize the results, before it can produce even
a single row of result.

Views are generally used (at least by me) to gain efficiency in retrieval.
I don't do updates to views. They're generally difficult to use that way
and I avoid it. If you can, always update the base tables.

To unsubscribe, send email to sqlite-users-unsubscribe (AT) sqlite (DOT) org

P Kishor wrote:
Gentlefolks,

I tried my own steps as stated below, and failed rather miserably. I
created everything as below, and ran an UPDATE yesterday mid-aft. Came
back this morning, and by mid-morning it hadn't even budged. Killed
the process, and tried a simple about 10 mins ago

SELECT * FRM v_attr WHERE pt_id = 1;

and even that hasn't returned anything as of yet.

By the way, I also added non-composite indexes to all the columns, and
ran ANALYZE before doing any of this.

Any suggestions, rays of hope, etc. welcome.

9/19/06, P Kishor <punkish (AT) eidesis (DOT) orgwrote:
>I have the following two tables and related indexes --
>>
>CREATE TABLE IF NT EXISTS pt (
>id INTEGER PRIMARY KEY,
>x REAL,
>y REAL,
>attr TEXT
>)
>>
>CREATE INDEX ix_pt_x N pt (x)
>CREATE INDEX ix_pt_y N pt (y)
>>
>CREATE TABLE IF NT EXISTS py (
>id INTEGER PRIMARY KEY,
>xmin REAL,
>ymin REAL,
>xmax REAL,
>ymax REAL,
>attr TEXT
>)
>>
>CREATE INDEX ix_py N py (xmin, ymin, xmax, ymax)
>>
>I want to UPDATE pt SETting pt.attr = py.attr WHEREver the Count of
>(pt.x BETWEEN py.xmin AND py.xmax AND pt.y BETWEEN py.ymin AND
>py.ymax) is 1.
>>
>So, I have come up with the following way -- I have created a view to
>do my SELECTs
>>
>CREATE VIEW v_attr AS
>SELECT pt.id AS pt_id, Count(pt.id) AS pt_id_count, py.attr AS
>py_attr
>FRM pt JIN py N
>(
>(pt.x BETWEEN py.xmin AND py.xmax) AND
>(pt.y BETWEEN py.ymin AND py.ymax)
>)
>GRUP BY pt_id_count
>HAVING pt_id_count = 1
>)
>>
>Well, any SELECTs from the view v_attr take forever. Anyway to speed
>it up?
>>
>If all of the above works, then I can do the following UPDATE --
>>
>UPDATE pt AS a_pt
>SET attr = (
>SELECT py_attr
>FRM v_attr
>WHERE a_pt.id = v_attr.pt_id
>)
>>
>And so I ask you, does this make sense? Is my JIN okay? local wag
>was saying that JINs can only be done using '=', not BETWEEN.
>Nevertheless, perhaps a SQL guru on this list can guide me on this
>task.
>>
>
>
You have an error in the select statement inside your create view
statement.

sqliteSELECT pt.id AS pt_id, Count(pt.id) AS pt_id_count, py.attr AS
py_attr
FRM pt JIN py N
(
(pt.x BETWEEN py.xmin AND py.xmax) AND
(pt.y BETWEEN py.ymin AND py.ymax)
)
GRUP BY pt_id_count
HAVING pt_id_count = 1;
SQL error: misuse of aggregate:

Sqlite does not complain when you create the view because it does not
compile the query at that point. You should get the error message if you
ever try to use the view.

The group by clause is wrong. You should change pt_count_id to pt_id.
Furthermore, standard SQL (I'm not sure about sqlite here) doesn't allow
you to use an alias name defined in the select clause in the having
clause, because those output fields may not exist yet. Since the having
clause will only return groups where the count is 1, there is no sense
returning that constant value from the view. Try this instead.

SELECT pt.id AS pt_id, py.attr AS py_attr
FRM pt JIN py N
(
(pt.x BETWEEN py.xmin AND py.xmax) AND
(pt.y BETWEEN py.ymin AND py.ymax)
)
GRUP BY pt.id
HAVING count(pt.id) = 1;

Also, our update statement will not work at all. Firstly, there is no AS
clause allowed in an update statement. Secondly, there is no condition
on the update so that all rows in the pt table will be changed to the
same new value of attr if it executes. The value would be the first
value returned by the subquery that is getting data from the view.

Given these errors, I don't think you tested the statements you posted.

HTH
Dennis Cote

To unsubscribe, send email to sqlite-users-unsubscribe (AT) sqlite (DOT) org