Bugs item #2630278, was opened at 2009-02-23 15:25
Message generated for change (Comment added) made by stmane
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2630278&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: SQL CVS Head
Status: Closed
Resolution: Fixed
Priority: 5
Private: Yes
Submitted By: Jens Oehlschlägel (jens_oehl)
Assigned to: Niels Nes (nielsnes)
Summary: WHERE in WITH crashes the server
Initial Comment:
observed under Win32 with MonetDB5-SQL-Installer-i386-20090220.msi with 150k or
1.5M rows
Adding a WHERE clause to an on-the-fly view definition using WITH crashes the
server. In addition WITH can slow-down execution by factor 7.
Replication code attached
----------------------------------------------------------------------
>Comment By: Stefan Manegold (stmane)
Date: 2009-02-24 18:24
Message:
Niels also fixed the performance of the WITH variant:
sql>SELECT dt_curr, postingyear, COUNT(*) AS cc
more>, SUM(dt_d00) AS dt_d00
more>, SUM(dt_d01) AS dt_d01
more>, SUM(dt_d02) AS dt_d02
more>, SUM(dt_d03) AS dt_d03
more>, SUM(dt_d04) AS dt_d04
more>, SUM(dt_d05) AS dt_d05
more>FROM voc.q_local_Source_currency
more>WHERE dt_curr = 'USD'
more> AND dt_uom = 'kg'
more>GROUP BY dt_curr, postingyear
more>ORDER BY dt_curr, postingyear
more>;
+---------+-------------+-------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
| dt_curr | postingyear | cc | dt_d00 | dt_d01
| dt_d02 | dt_d03 | dt_d04
| dt_d05 |
+=========+=============+=======+========================+========================+========================+========================+========================+========================+
| USD | 2005 | 77071 | 893317923.54067528 |
890043738.04487252 | 766314385.69348729 | 341006606.34187269 |
236727569.96897939 | 118503157.67650178 |
| USD | 2006 | 72929 | 936231548.33830655 |
934097209.92310703 | 796742903.4459691 | 331508542.05018795 |
236810711.61478955 | 112686518.11059061 |
+---------+-------------+-------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
2 tuples
Timer 81.964 msec 2 rows
^^^^^^
sql>WITH my_currency AS (SELECT * FROM voc.q_local_Source_currency)
more>SELECT dt_curr, postingyear, COUNT(*) AS cc
more>, SUM(dt_d00) AS dt_d00
more>, SUM(dt_d01) AS dt_d01
more>, SUM(dt_d02) AS dt_d02
more>, SUM(dt_d03) AS dt_d03
more>, SUM(dt_d04) AS dt_d04
more>, SUM(dt_d05) AS dt_d05
more>FROM my_currency
more>WHERE dt_curr = 'USD'
more> AND dt_uom = 'kg'
more>GROUP BY dt_curr, postingyear
more>ORDER BY dt_curr, postingyear
more>;
+---------+-------------+-------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
| dt_curr | postingyear | cc | dt_d00 | dt_d01
| dt_d02 | dt_d03 | dt_d04
| dt_d05 |
+=========+=============+=======+========================+========================+========================+========================+========================+========================+
| USD | 2005 | 77071 | 893317923.54067528 |
890043738.04487252 | 766314385.69348729 | 341006606.34187269 |
236727569.96897939 | 118503157.67650178 |
| USD | 2006 | 72929 | 936231548.33830655 |
934097209.92310703 | 796742903.4459691 | 331508542.05018795 |
236810711.61478955 | 112686518.11059061 |
+---------+-------------+-------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
2 tuples
Timer 81.909 msec 2 rows
^^^^^^
sql>WITH my_currency AS (
more> SELECT *
more> FROM voc.q_local_Source_currency
more> WHERE dt_curr = 'USD'
more> AND dt_uom = 'kg'
more> )
more>SELECT dt_curr, postingyear, COUNT(*) AS cc
more>, SUM(dt_d00) AS dt_d00
more>, SUM(dt_d01) AS dt_d01
more>, SUM(dt_d02) AS dt_d02
more>, SUM(dt_d03) AS dt_d03
more>, SUM(dt_d04) AS dt_d04
more>, SUM(dt_d05) AS dt_d05
more>FROM my_currency
more>GROUP BY dt_curr, postingyear
more>ORDER BY dt_curr, postingyear
more>;
+---------+-------------+-------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
| dt_curr | postingyear | cc | dt_d00 | dt_d01
| dt_d02 | dt_d03 | dt_d04
| dt_d05 |
+=========+=============+=======+========================+========================+========================+========================+========================+========================+
| USD | 2005 | 77071 | 893317923.54067528 |
890043738.04487252 | 766314385.69348729 | 341006606.34187269 |
236727569.96897939 | 118503157.67650178 |
| USD | 2006 | 72929 | 936231548.33830655 |
934097209.92310703 | 796742903.4459691 | 331508542.05018795 |
236810711.61478955 | 112686518.11059061 |
+---------+-------------+-------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
2 tuples
Timer 78.615 msec 2 rows
^^^^^^
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-02-24 17:02
Message:
Fixed in Feb2009 branch by Niels.
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-02-23 19:04
Message:
the query triggers an assertion:
mserver5:
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1057:
rel2bin: Assertion `list_length(l)' failed.
Program received signal SIGABRT, Aborted.
[Switching to Thread 0x7fffe1492950 (LWP 16164)]
0x0000003675232f05 in raise () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install
bzip2-libs-1.0.5-3.fc10.x86_64 e2fsprogs-libs-1.41.3-2.fc10.x86_64
glibc-2.9-3.x86_64 keyutils-libs-1.2-3.fc9.x86_64
krb5-libs-1.6.3-16.fc10.x86_64 libselinux-2.0.73-1.fc10.x86_64
ncurses-libs-5.6-20.20080927.fc10.x86_64 openssl-0.9.8g-12.fc10.x86_64
pcre-7.8-1.fc10.x86_64 readline-5.2-13.fc9.x86_64 zlib-1.2.3-18.fc9.x86_64
(gdb) up
#1 0x0000003675234a73 in abort () from /lib64/libc.so.6
(gdb)
#2 0x000000367522bef9 in __assert_fail () from /lib64/libc.so.6
(gdb)
#3 0x00007fffe1f1d2c0 in rel2bin (c=0x13cc858, s=0x175d8b8) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1057
1057 assert(list_length(l));
(gdb) li
1052 node *n;
1053
1054 list *ol, *l = s->op1.lval;
1055
1056 ol = l;
1057 assert(list_length(l));
1058 if (list_length(l) == 1) {
1059 res = rel2bin(c, l->h->data);
1060 } else {
1061 stmt *sel;
(gdb) bt
#0 0x0000003675232f05 in raise () from /lib64/libc.so.6
#1 0x0000003675234a73 in abort () from /lib64/libc.so.6
#2 0x000000367522bef9 in __assert_fail () from /lib64/libc.so.6
#3 0x00007fffe1f1d2c0 in rel2bin (c=0x13cc858, s=0x175d8b8) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1057
#4 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x175d7b8) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#5 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x17dfd98) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#6 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x17dfba8) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#7 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x17dfc98) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#8 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x17f4818) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#9 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x18f74f8) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#10 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x19050b8) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#11 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x190b358) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#12 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x190b558) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#13 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x190b408) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#14 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x190b948) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#15 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x190ba78) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#16 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x1907078) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#17 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x1910b98) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#18 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x1910cf8) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#19 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x1910da8) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#20 0x00007fffe1f1d4bb in rel2bin (c=0x13cc858, s=0x190b608) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/server/sql_rel2bin.mx:1161
#21 0x00007fffe1eb828d in sql_symbol2stmt (c=0x13cc858, sym=0x172af78) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/backends/monet5/sql.mx:1107
#22 0x00007fffe1ef2f42 in SQLparser (c=0x604c40) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/sql/src/backends/monet5/sql_scenario.mx:1013
#23 0x00007ffff7d336cb in runPhase (c=0x604c40, phase=1) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/MonetDB5/src/mal/mal_scenario.mx:591
#24 0x00007ffff7d337c2 in runScenarioBody (c=0x604c40) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/MonetDB5/src/mal/mal_scenario.mx:627
#25 0x00007ffff7d339ef in runScenario (c=0x604c40) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/MonetDB5/src/mal/mal_scenario.mx:661
#26 0x00007ffff7cf15aa in MSserveClient (dummy=0x604c40) at
/ufs/manegold/_/scratch0/Monet/Testing/Stable/source/MonetDB5/src/mal/mal_session.mx:460
#27 0x0000003675e073da in start_thread () from /lib64/libpthread.so.0
#28 0x00000036752e62bd in clone () from /lib64/libc.so.6
(gdb) print l
$1 = (list *) 0x17b0eb8
(gdb) print *l
$2 = {destroy = 0x7fffe1f09105 <stmt_destroy>, h = 0x0, t = 0x0, cnt = 0}
(gdb)
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-02-23 19:01
Message:
test added in WITH_Crash.SF-2630278.*
server crashes with segfault
detailed analysis pending
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2630278&group_id=56967
------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
