Hi Makoto, try create table t2 (like t1); I am not sure why it needs the parenthesis:) lefteris On Sun, Jan 3, 2010 at 9:51 PM, Makoto YUI wrote:

Martin,

Thank you for the quick reply.

Martin Kersten wrote:

...

...

create table t2 as (select * from t1 limit 1) with data; This is improper SQL syntax.

Limit can only be placed on the result set.

Okey. Here is another issue.

create table t1 (f1 int); create table t2 like t1;

syntax error, unexpected LIKE, expecting AS in: "create table t2 like"

Is "create table like" statement as in the document supported in the current release? http://monetdb.cwi.nl/SQL/Documentation/Table-Management.html

Thanks, Makoto

------------------------------------------------------------------------------ This SF.Net email is sponsored by the Verizon Developer Community Take advantage of Verizon's best-in-class app development support A streamlined, 14 day to market process makes app distribution fast and easy Join now and get one step closer to millions of Verizon customers http://p.sf.net/sfu/verizon-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Hi folks, I continue searching for a workaround for "create table .. as select .. order by .. limit .." in MonetDB. I would like to create a table using a result-set of a query with "order by & limit" though I'm aware that this is a geeky requirement. -- pareparing data create table t1 (f1 int, f2 char); insert into t1 values(3, 'a'); insert into t1 values(1, 'b'); insert into t1 values(4, 'c'); insert into t1 values(5, 'd'); insert into t1 values(2, 'e'); [Not allowed in SQL spec but what I somehow want] create table t2 as select * from t1 where f2 <> 'b' order by f1 limit 3; Here below is my unwise workaround representing the above DDL&DML :-( Are there any better suggestions? ---------------------------- [workaround #1] create table t2 as ( select f1,f2 from (select *, row_number() over (order by f1) as rownum from t1 where f2 <> 'b') as tmp where tmp.rownum <= 3 ) with data; Pros: Everything is done in SQL. Cons: However, more than 3 or every records are scanned. Automatic query rewriting is slightly hard. ---------------------------- [workaround #2] create table t2 as select * from t1 where f2 <> 'b' with no data; Then, is t1 fully scanned in the above case? Or, only 1 record is scanned with the "no data" option. select * from t1 where f2 <> 'b' order by f1 limit 3; foreach(r: results) { // batch inserts using prepared statements insert into t2 values(r.f1,..,r.fN); insert into t2 values(r.f1,..,r.fN); insert into t2 values(r.f1,..,r.fN); insert into t2 values(r.f1,..,r.fN); } Pros: Redundant scan might be avoided. Cons: Data copy between client and DB would happen. Moreover, iterating the resultset and inserting records would be in-efficient. --------------------------------------- BTW, a table 't_union' is not created though the following query as in [1] are successfully executed. create table t1 (f1 int); -- insert into t1 values(1); /* at least one result is requred */ create table t2 (f1 int); create table t_union as (select f1 from t1 union all select f1 from t2) with data; The table 't_union' is not created even when using "with no data" option. This would be a minor documentation issue. [1] http://old.nabble.com/'create-table-as-select...'-from-union-queries-doesn't-work-td26517185.html#a26517185 Thanks, Makoto

Martin, Martin Kersten wrote:

...

create table t2 as select * from t1 where f2 <> 'b' order by f1 limit 3;

If your intention is to stop after seeing the first 3 tuples where f2 <> 'b', then i have to disappoint you. MonetDB will calculate all answers first and deliver only 3.

My intention was this behavior as a result of a sort of volcano-style processing.

...

create table t2 as ( select f1,f2 from (select *, row_number() over (order by f1) as rownum from t1 where f2 <> 'b') as tmp where tmp.rownum <= 3 ) with data;

Pros: Everything is done in SQL. Cons: However, more than 3 or every records are scanned. yes it will always do that for you, there is no escape as far as i can see. Other then a user defined function that implements something like select(f2,'<>','b',3)

I see.

...

create table t2 as select * from t1 where f2 <> 'b' with no data;

Then, is t1 fully scanned in the above case? look at the TRACE <sqlquery> and you will see both the timing and the result sizes of all intermediates.

"set trace = 'show,ticks,stmt,rbytes,wbytes'; trace .." was very useful. However, trace has no effect on "create table" (DDL). So, I could not find how "with no data"/"with data" effects. I assume that "with (no) data" does not effect query execution itself. Thanks, Makoto