[MonetDB-users] SQL server crash
Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.
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
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
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
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
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
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
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
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
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
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
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
On Thu, Jan 15, 2009 at 9:13 AM, dariuszs <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)
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 <dariuszs@svnp.com <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
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/
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.
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/
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.
participants (6)
-
Chris Curvey -
dariuszs -
Martin Kersten -
Sam Mason -
Sjoerd Mullender -
Stefan Manegold