Tim,

There are some problems with views...

Consider the following example:
CREATE TABLE t1 (f1 int);
CREATE TABLE t3 (f1 int);
Each table has 3 or 4 rows.
SELECT * FROM t1, t3
returns 12 rows.

CREATE VIEW v1 AS SELECT * from t1, t3
Now attempt to execute the view
SELECT * FROM v1
Returns empty result set

Looking into information.views table. Definition field contains the
following text:
SELECT ALL
"RowID" AS "RowID",
"f1" AS "f1",
"RowID" AS "RowID1",
"f1" AS "f11"
FROM "t1",
"t3"

The transalation above is:
1. Obviously wrong - it doesn't detect the ambiquity nor it matches the
columns correctly
2. Uncalled for!!! - it wrecks my view definition, which I would like to be
able to extract
and edit later (at least as supported by most databases).

Another example is this:

CREATE VIEW v3 AS
SELECT t1.RowID as R1, t3.RowID as R3
FROM t1, t3
WHERE t1.RowID = t3.RowID

is converted into:
SELECT ALL
"t1"."RowID" AS "R1",
"t3"."RowID" AS "R3"
FROM "t1" INNER JOIN "t3" ON "t1"."RowID" = "t3"."RowID"
"t3"

This SQL works, however:
1. It's not what I wrote
2. I don't understand the meaning of t3 at the end of it


Cheers,
Michael

Michael,

<< Consider the following example:
CREATE TABLE t1 (f1 int);
CREATE TABLE t3 (f1 int);
Each table has 3 or 4 rows.
SELECT * FROM t1, t3
returns 12 rows.

CREATE VIEW v1 AS SELECT * from t1, t3
Now attempt to execute the view
SELECT * FROM v1
Returns empty result set >>

Fixed.  It was an issue with the max rows setting and views.

<< Looking into information.views table. Definition field contains the
following text:
SELECT ALL
"RowID" AS "RowID",
"f1" AS "f1",
"RowID" AS "RowID1",
"f1" AS "f11"
FROM "t1",
"t3"

The transalation above is:
1. Obviously wrong - it doesn't detect the ambiquity nor it matches the
columns correctly >>

Fixed.

<< 2. Uncalled for!!! - it wrecks my view definition, which I would like to
be able to extract and edit later (at least as supported by most databases).
>>

Once correct, it's the equivalent of what you entered.  And there's nothing
stopping you from editing it later.  I'll see what I can do about this, but
this is a pretty integral part of the way EDB handles storing SQL
internally.  It always "prettys" it up and makes it as explicit as possible
in terms of resolving column names, etc.  The SQL for views, for example,
has to match the internal columns stored for the view.  Otherwise you run
into a problem where the view definition doesn't match what EDB is reporting
for the view columns.  Such as the case with this:

SELECT * FROM T1

If we didn't convert that into explicit column references and we added a
column called "Test", then EDB would report back that the view returns 1
column when in actuality it returns 2 columsn - "F1" and "Test".

<< This SQL works, however:
1. It's not what I wrote
2. I don't understand the meaning of t3 at the end of it >>

The T3 is a bug and has been fixed.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Pretty quick fixes, I should say, thanks

> Once correct, it's the equivalent of what you entered.  And there's nothing
> stopping you from editing it later.  I'll see what I can do about this, but
> this is a pretty integral part of the way EDB handles storing SQL
> internally.  It always "prettys" it up and makes it as explicit as possible
> in terms of resolving column names, etc.  
Why not just create a separate column for prettied up definition, used
internally, why still preserving users definition? You don't even
have to show that to user.

> If we didn't convert that into explicit column references and we added a
> column called "Test", then EDB would report back that the view returns 1
> column when in actuality it returns 2 columsn - "F1" and "Test".
I understand that. Still, no reason to ruin the original sql, just make a
copy and optimize it all you want


Michael

Michael,

<< Why not just create a separate column for prettied up definition, used
internally, why still preserving users definition? You don't even
have to show that to user. >>

It's not the metadata storage that's the problem.  The problem is that the
parsing/tokenizing is completely separate from the evaluation and actual
execution.  EDB makes up for this by recreating the actual SQL from the
tokens.

--
Tim Young
Elevate Software
www.elevatesoft.com

> << Why not just create a separate column for prettied up definition, used
> internally, why still preserving users definition? You don't even
>  have to show that to user. >>
>
> It's not the metadata storage that's the problem.  The problem is that the
> parsing/tokenizing is completely separate from the evaluation and actual
> execution.  EDB makes up for this by recreating the actual SQL from the
> tokens.
That's totally fine, why not just *also* store the original SQL.
Also, I'm not sure what the standard says about view defined as
select * from table

what should happen if you add/remove fields to the table?
I believe in most engines the view will still work fine, returning
all the rows... In your case, it's a problem obviously.


Michael

Michael,

<< That's totally fine, why not just *also* store the original SQL. Also,
I'm not sure what the standard says about view defined as select * from
table >>

AFAIK, iIt doesn't say anything about storage of the view specification
specifically.  It is assumed that what is stored is semantically the same as
what was defined.

<< what should happen if you add/remove fields to the table? I believe in
most engines the view will still work fine, returning all the rows... In
your case, it's a problem obviously. >>

I'm not sure if that's the case or not with a SELECT *.  I know for a fact
that most will issue an error and require the view to be dropped and
redefined if the columns in the underlying table(s) change after the view
was defined.

--
Tim Young
Elevate Software
www.elevatesoft.com