Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Public Beta Tests » View Thread |
Messages 1 to 6 of 6 total |
Views? |
Sat, Dec 16 2006 11:00 AM | Permanent Link |
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 | |
Sat, Dec 16 2006 5:24 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sun, Dec 17 2006 4:59 AM | Permanent Link |
Michael Baytalsky | 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 |
Sun, Dec 17 2006 4:53 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sun, Dec 17 2006 7:02 PM | Permanent Link |
Michael Baytalsky | > << 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 |
Sun, Dec 17 2006 10:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |