[MonetDB-users] SQL server crash

newer
[MonetDB-users] copy into table

older
[MonetDB-users] Changing locale to...

dariuszs

14 Jan 2009 14 Jan '09

9:46 p.m.

Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

Show replies by date

Martin Kersten

15 Jan 15 Jan

10:17 a.m.

dariuszs wrote:

...

Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

It would be helpful if you provide a 5 line example that demonstrates the problem.

...

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

dariuszs

2:13 p.m.

Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1) They key (key27) on all tables is varchar(27) SQL select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000) At this point server crashed. I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz. Martin Kersten wrote:

...

dariuszs wrote:

...
Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

It would be helpful if you provide a 5 line example that demonstrates the problem.

...
------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Stefan Manegold

2:44 p.m.

Hi, - How does the server crash, i.e., do you get any error messages either on the mserver5 console or on the mclient side? - Which version of MonetDB are you using? - Which OS are you running MonetDB on? - What kind of hardware (CPU, amount of RAM, amount of swap) do you use? - Is the HW 32 or 64 bit? If HW is 64 bit, is the OS 32 or 64 bit? If OS is 64 bit, is your MonetDB 32 or 64 bit? If MonetDB is 64 bit, do you use 32-bit OIDs or 64-bit OIDs? Providing us with the output of mserver5 --version should answer most of the above questions about MonetDB. Stefan On Thu, Jan 15, 2009 at 09:13:25AM -0500, dariuszs wrote:

...

Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

They key (key27) on all tables is varchar(27) SQL

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

At this point server crashed.

I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz.

Martin Kersten wrote:

...
dariuszs wrote:

...
Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

It would be helpful if you provide a 5 line example that demonstrates the problem.

...
------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

dariuszs

2:56 p.m.

Hi, specs: windows server 2003 x64 enterprise, 24GB memory, 1TB storage (sas drives raid 0). MonetDB-SQL-Installer.msi-x86_64-20081111.msi I've just installed this database so I guess that's the lates version. Dariusz. Stefan Manegold wrote:

...

Hi,

- How does the server crash, i.e., do you get any error messages either on the mserver5 console or on the mclient side?

- Which version of MonetDB are you using?

- Which OS are you running MonetDB on?

- What kind of hardware (CPU, amount of RAM, amount of swap) do you use?

- Is the HW 32 or 64 bit? If HW is 64 bit, is the OS 32 or 64 bit? If OS is 64 bit, is your MonetDB 32 or 64 bit? If MonetDB is 64 bit, do you use 32-bit OIDs or 64-bit OIDs?

Providing us with the output of

mserver5 --version

should answer most of the above questions about MonetDB.

Stefan

On Thu, Jan 15, 2009 at 09:13:25AM -0500, dariuszs wrote:

...
Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

They key (key27) on all tables is varchar(27) SQL

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

At this point server crashed.

I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz.

Martin Kersten wrote:

...
dariuszs wrote:

...
Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

It would be helpful if you provide a 5 line example that demonstrates the problem.

...
------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Sjoerd Mullender

3:08 p.m.

dariuszs wrote:

...

Hi, specs: windows server 2003 x64 enterprise, 24GB memory, 1TB storage (sas drives raid 0). MonetDB-SQL-Installer.msi-x86_64-20081111.msi

I've just installed this database so I guess that's the lates version.

Actually, it isn't. The latest version is MonetDB5-SQL-Installer-x86_64-20081216.msi. See http://monetdb.cwi.nl/downloads/Windows/Nov2008-SP1/.

...

Dariusz.

Stefan Manegold wrote:

...
Hi,

- How does the server crash, i.e., do you get any error messages either on the mserver5 console or on the mclient side?

- Which version of MonetDB are you using?

- Which OS are you running MonetDB on?

- What kind of hardware (CPU, amount of RAM, amount of swap) do you use?

- Is the HW 32 or 64 bit? If HW is 64 bit, is the OS 32 or 64 bit? If OS is 64 bit, is your MonetDB 32 or 64 bit? If MonetDB is 64 bit, do you use 32-bit OIDs or 64-bit OIDs?

Providing us with the output of

mserver5 --version

should answer most of the above questions about MonetDB.

Stefan

On Thu, Jan 15, 2009 at 09:13:25AM -0500, dariuszs wrote:

...
Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

They key (key27) on all tables is varchar(27) SQL

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

At this point server crashed.

I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz.

Martin Kersten wrote:

...
dariuszs wrote:

...
Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

It would be helpful if you provide a 5 line example that demonstrates the problem.

...
------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Sjoerd Mullender

dariuszs

3:17 p.m.

Hi, I'm sorry my fault, I'll install the latest version and give it a try. Thanks. Dariusz. Sjoerd Mullender wrote:

...

dariuszs wrote:

...
Hi, specs: windows server 2003 x64 enterprise, 24GB memory, 1TB storage (sas drives raid 0). MonetDB-SQL-Installer.msi-x86_64-20081111.msi

I've just installed this database so I guess that's the lates version.

Actually, it isn't. The latest version is MonetDB5-SQL-Installer-x86_64-20081216.msi. See http://monetdb.cwi.nl/downloads/Windows/Nov2008-SP1/.

...
Dariusz.

Stefan Manegold wrote:

...
Hi,

- How does the server crash, i.e., do you get any error messages either on the mserver5 console or on the mclient side?

- Which version of MonetDB are you using?

- Which OS are you running MonetDB on?

- What kind of hardware (CPU, amount of RAM, amount of swap) do you use?

- Is the HW 32 or 64 bit? If HW is 64 bit, is the OS 32 or 64 bit? If OS is 64 bit, is your MonetDB 32 or 64 bit? If MonetDB is 64 bit, do you use 32-bit OIDs or 64-bit OIDs?

Providing us with the output of

mserver5 --version

should answer most of the above questions about MonetDB.

Stefan

On Thu, Jan 15, 2009 at 09:13:25AM -0500, dariuszs wrote:

...
Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

They key (key27) on all tables is varchar(27) SQL

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

At this point server crashed.

I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz.

Martin Kersten wrote:

...
dariuszs wrote:

...
Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

It would be helpful if you provide a 5 line example that demonstrates the problem.

...
------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword ------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

dariuszs

16 Jan 16 Jan

9:06 p.m.

New subject: [MonetDB-users] SQL server crash - R6031

Hi, I've got this after running a query against 20,000,000 records 2 times in a row runtime error R6031 Attempt to initialize CRT more than once

dariuszs

17 Jan 17 Jan

5:09 p.m.

New subject: [MonetDB-users] copy into - weird output

Hi, Please verify: col1varchar(6) col2 varchar(6) copy select col1,col2 from table1 into 'c:\testfile.csv' using delimiters ','; current version "abc ","def " previous version "abc","def" Why did you change that? Thanks. Dariusz. Sjoerd Mullender wrote:

...

dariuszs wrote:

...
Hi, specs: windows server 2003 x64 enterprise, 24GB memory, 1TB storage (sas drives raid 0). MonetDB-SQL-Installer.msi-x86_64-20081111.msi

I've just installed this database so I guess that's the lates version.

Actually, it isn't. The latest version is MonetDB5-SQL-Installer-x86_64-20081216.msi. See http://monetdb.cwi.nl/downloads/Windows/Nov2008-SP1/.

...
Dariusz.

Stefan Manegold wrote:

...
Hi,

- How does the server crash, i.e., do you get any error messages either on the mserver5 console or on the mclient side?

- Which version of MonetDB are you using?

- Which OS are you running MonetDB on?

- What kind of hardware (CPU, amount of RAM, amount of swap) do you use?

- Is the HW 32 or 64 bit? If HW is 64 bit, is the OS 32 or 64 bit? If OS is 64 bit, is your MonetDB 32 or 64 bit? If MonetDB is 64 bit, do you use 32-bit OIDs or 64-bit OIDs?

Providing us with the output of

mserver5 --version

should answer most of the above questions about MonetDB.

Stefan

On Thu, Jan 15, 2009 at 09:13:25AM -0500, dariuszs wrote:

...
Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

They key (key27) on all tables is varchar(27) SQL

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

At this point server crashed.

I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz.

Martin Kersten wrote:

...
dariuszs wrote:

...
Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

It would be helpful if you provide a 5 line example that demonstrates the problem.

...
------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword ------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

dariuszs

15 Jan 15 Jan

3 p.m.

Hi, I'll get the output of that command in few minutes. Dariusz. Stefan Manegold wrote:

...

Hi,

- How does the server crash, i.e., do you get any error messages either on the mserver5 console or on the mclient side?

- Which version of MonetDB are you using?

- Which OS are you running MonetDB on?

- What kind of hardware (CPU, amount of RAM, amount of swap) do you use?

- Is the HW 32 or 64 bit? If HW is 64 bit, is the OS 32 or 64 bit? If OS is 64 bit, is your MonetDB 32 or 64 bit? If MonetDB is 64 bit, do you use 32-bit OIDs or 64-bit OIDs?

Providing us with the output of

mserver5 --version

should answer most of the above questions about MonetDB.

Stefan

On Thu, Jan 15, 2009 at 09:13:25AM -0500, dariuszs wrote:

...
Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

They key (key27) on all tables is varchar(27) SQL

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

At this point server crashed.

I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz.

Martin Kersten wrote:

...
dariuszs wrote:

...
Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

It would be helpful if you provide a 5 line example that demonstrates the problem.

...
------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

dariuszs

3:38 p.m.

Hi, This is the output of: mserver5 --version MonetDB server v5.8.2 (64-bit), based on kernel v1.26.2 (32-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008- MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Configured for prefix: C:\Documents and Settings\sjoerd\My Documents\src\stable\vs64\NT64 Libraries: libpcre: 7.4 2007-09-21 (@pcreversion@) Compiled by: @builtby@ Compilation: @compilercall@ Linking : @linkercall@ Stefan Manegold wrote: I really appreciate your help people. Thank you. Dariusz.

...

Hi,

- How does the server crash, i.e., do you get any error messages either on the mserver5 console or on the mclient side?

- Which version of MonetDB are you using?

- Which OS are you running MonetDB on?

- What kind of hardware (CPU, amount of RAM, amount of swap) do you use?

- Is the HW 32 or 64 bit? If HW is 64 bit, is the OS 32 or 64 bit? If OS is 64 bit, is your MonetDB 32 or 64 bit? If MonetDB is 64 bit, do you use 32-bit OIDs or 64-bit OIDs?

Providing us with the output of

mserver5 --version

should answer most of the above questions about MonetDB.

Stefan

On Thu, Jan 15, 2009 at 09:13:25AM -0500, dariuszs wrote:

...
Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

They key (key27) on all tables is varchar(27) SQL

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

At this point server crashed.

I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz.

Martin Kersten wrote:

...
dariuszs wrote:

...
Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

It would be helpful if you provide a 5 line example that demonstrates the problem.

...
------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Chris Curvey

3:02 p.m.

On Thu, Jan 15, 2009 at 9:13 AM, dariuszs wrote:

...

Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

They key (key27) on all tables is varchar(27) SQL

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

At this point server crashed.

I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz.

Just curious, does it work if you don't have a cartesian product in the subquery? (there is no condition to match table2 rows with table3 rows)

dariuszs

3:21 p.m.

Hi Chris, SQL like this: select count(*) from table1 where key27 in (select key27 from table2) or key27 in (select key27 from table3); Does NOT crash the server. What I'm looking for is a good example how to match tables in MonetDB, I mean the software is amazing. Thanks. Dariusz. Chris Curvey wrote:

...

On Thu, Jan 15, 2009 at 9:13 AM, dariuszs mailto:dariuszs@svnp.com> wrote:

Hi, table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

They key (key27) on all tables is varchar(27) SQL

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

At this point server crashed.

I'm trying to join 3 tables to get a count on table1 only when there is a match in table2 or table3. Could you provide me with a sample how to properly match 3 or more tables based on the match key? Thank you. Dariusz.

Just curious, does it work if you don't have a cartesian product in the subquery? (there is no condition to match table2 rows with table3 rows) ------------------------------------------------------------------------

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword ------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Sam Mason

16 Jan 16 Jan

2:43 a.m.

On Thu, Jan 15, 2009 at 10:21:16AM -0500, dariuszs wrote:

...

What I'm looking for is a good example how to match tables in MonetDB, I mean the software is amazing.

SQL is an old language and there are many tutorials available on the web and books in the real world. Each implementation has its own quirks, but there's a core that's pretty good. Postgres has a good focus on the standards so SQL tutorials designed for it will probably work well on Monetdb, MySQL tutorials, while popular, are less good as it seems to deliberately do the wrong thing with respect to the standard. If you're after a more definite recommendation, sqlzoo[1] is a web site that I've pointed people at before. It's good for showing the differences between the different implementations as well. -- Sam http://samason.me.uk/ [1] http://sqlzoo.net/

dariuszs

2:13 p.m.

Hi Sam, I really appreciate that, thanks so much for help. Dariusz. Sam Mason wrote:

...

On Thu, Jan 15, 2009 at 10:21:16AM -0500, dariuszs wrote:

...
What I'm looking for is a good example how to match tables in MonetDB, I mean the software is amazing.

SQL is an old language and there are many tutorials available on the web and books in the real world. Each implementation has its own quirks, but there's a core that's pretty good. Postgres has a good focus on the standards so SQL tutorials designed for it will probably work well on Monetdb, MySQL tutorials, while popular, are less good as it seems to deliberately do the wrong thing with respect to the standard.

If you're after a more definite recommendation, sqlzoo[1] is a web site that I've pointed people at before. It's good for showing the differences between the different implementations as well.

Sam Mason

2:28 a.m.

On Thu, Jan 15, 2009 at 09:13:25AM -0500, dariuszs wrote:

...

table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)

select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)

If I'm reading this right, it'll be trying to create the Cartesian product of table2 and table3 (i.e. 300,000 * 400,000 rows).

...

At this point server crashed.

This is a bug that needs to be fixed, but I think your query may want to be something more like: SELECT COUNT(*) FROM table1 WHERE key27 IN ( SELECT key27 FROM table2 UNION SELECT key27 FROM table3); or spelled differently: SELECT COUNT(*) FROM table1 a, ( SELECT key27 FROM table2 UNION SELECT key27 FROM table3) b WHERE a.key27 = b.key27; Hope that helps! -- Sam http://samason.me.uk/

dariuszs

20 Jan 20 Jan

8:03 p.m.

New subject: [MonetDB-users] copy into - different output

Hi, Could someone please help me with this one? I've got a table table1 where col1 is varchar(10) After I run this query copy col1 from table1 into 'c:\test.csv' using delimiters ',' limit 1; I get "abc " in the file test.csv (it used to be trimmed eg. "abc") Is this the way it's going to be or this is a bug? Thanks. Dariusz.

5892

Age (days ago)

5898

Last active (days ago)

List overview

Download

16 comments

6 participants

participants (6)

Chris Curvey
dariuszs
Martin Kersten
Sam Mason
Sjoerd Mullender
Stefan Manegold