Bugs item #2134428, was opened at 2008-09-28 19:59
Message generated for change (Comment added) made by skinkie
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2134428&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: MonetDB5 CVS Head
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Stefan de Konink (skinkie)
Assigned to: Niels Nes (nielsnes)
Summary: some issue with 'as' clause
Initial Comment:
1) It is not the latest monetdb CVS version
2) If you cannot reproduce it I'll upgrade later
select w1.wayid from way_tags as w1, way_tags as w2 where w1.wayid=w2.wayid and
w1.k='bridge' and w2.k<>'layers';
or
select * from way_tags as w1, way_tags as w2 where w1.k = 'bridge' and w1.wayid
= w2.wayid;
Result in:
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x43a56950 (LWP 6885)]
0x00007fc6f55fc969 in CMD_EQ_oid (ret=0x2b73688, l=0x2b73668, r=0x2b73678)
at ../../../../src/modules/kernel/batcalc.mx:1112
1112 *o = (*p @2 *t);
(gdb) bt
#0 0x00007fc6f55fc969 in CMD_EQ_oid (ret=0x2b73688, l=0x2b73668, r=0x2b73678)
at ../../../../src/modules/kernel/batcalc.mx:1112
#1 0x00007fc7018ebfd5 in DFLOWstep (t=0x2baaa78, fs=0x41618298)
at ../../../src/mal/mal_interpreter.mx:1523
#2 0x00007fc7018f0610 in runDFLOWworker (t=0x2baaa78)
at ../../../src/mal/mal_interpreter.mx:810
#3 0x00007fc6ffdc9067 in start_thread () from /lib/libpthread.so.0
#4 0x00007fc6fef03edd in clone () from /lib/libc.so.6
#5 0x0000000000000000 in ?? ()
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2008-10-21 00:40
Message:
I wish there was a day where i would *just* accept your answer and behave
like someone that didn't care.
Do you agree from a functional point of view that:
1) The query can be rewritten into a Query, With Subquery
2) The amount of memory should never exceed the amount of memory it would
take for only one of those subqueries to succeed.
Ok I tricked you. Or MonetDB tricked me to hide something:
1) SELECT * FROM way_tags WHERE way IN (SELECT way WHERE k = 'bridge');
I hope you agree with me on that.
2)
[New Thread 0xb2331b90 (LWP 1705)]
[New Thread 0xb1f30b90 (LWP 1706)]
mserver5: ../../../src/server/sql_select.mx:475: find_pivot: Assertion
`subset->type == st_ptable' failed.
Program received signal SIGABRT, Aborted.
[Switching to Thread 0xb1f30b90 (LWP 1706)]
0xb7fee424 in __kernel_vsyscall ()
(gdb) bt
#0 0xb7fee424 in __kernel_vsyscall ()
#1 0xb7075670 in raise () from /lib/libc.so.6
#2 0xb7076eb8 in abort () from /lib/libc.so.6
#3 0xb706e64e in __assert_fail () from /lib/libc.so.6
#4 0xb24af4db in find_pivot (subset=0xa568e40, t=0xa5601c8)
at ../../../src/server/sql_select.mx:475
#5 0xb24b2e8d in sql_column_value (sql=0xa512080, scp=0xa568950,
se=0xa514340, grp=0x0, subset=0xa568e40, f=3)
at ../../../src/server/sql_select.mx:1571
#6 0xb24b3436 in sql_value_exp (sql=0xa512080, scp=0xa568950,
se=0xa514340,
grp=0x0, subset=0xa568e40, f=3, ek=
{type = 0 '\0', card = 2 '\002', reduce = 0 '\0'})
at ../../../src/server/sql_select.mx:1665
#7 0xb24b6905 in _column_exp (sql=0xa512080, scp=0xa568950,
column_e=0xa5143b0, grp=0x0, subset=0xa568e40, f=3)
at ../../../src/server/sql_select.mx:2701
#8 0xb24b6b1b in sql_column_exp (sql=0xa512080, scp=0xa568950,
column_e=0xa5143b0, grp=0x0, subset=0xa568e40, f=3)
at ../../../src/server/sql_select.mx:2735
#9 0xb24ba64f in sql_simple_select (sql=0xa512080, scp=0xa568950,
sn=0xa514610) at ../../../src/server/sql_select.mx:3791
#10 0xb24babe8 in sql_select (sql=0xa512080, scp=0xa568950, sn=0xa514610,
ek=
{type = 0 '\0', card = 3 '\003', reduce = 0 '\0'})
---Type <return> to continue, or q <return> to quit---
at ../../../src/server/sql_select.mx:3907
#11 0xb24ae830 in sql_subquery (sql=0xa512080, scp=0xa568950,
sq=0xa514610, ek=
{type = 0 '\0', card = 3 '\003', reduce = 0 '\0'})
at ../../../src/server/sql_select.mx:236
#12 0xb24ae9fd in scope_subquery (sql=0xa512080, scp=0xa568950,
sq=0xa514610,
ek={type = 0 '\0', card = 3 '\003', reduce = 0 '\0'})
at ../../../src/server/sql_select.mx:279
#13 0xb24b348d in sql_value_exp (sql=0xa512080, scp=0xa55b1e0,
se=0xa514610,
grp=0x0, subset=0x0, f=1, ek=
{type = 0 '\0', card = 3 '\003', reduce = 0 '\0'})
at ../../../src/server/sql_select.mx:1669
#14 0xb24b8eed in sql_logical_exp_ (sql=0xa512080, scp=0xa55b1e0,
sc=0xa5146e0, grp=0x0, subset=0x0, f=1, ek=
{type = 0 '\0', card = 4 '\004', reduce = 1 '\001'})
at ../../../src/server/sql_select.mx:3370
#15 0xb24b9de6 in sql_logical_exp (sql=0xa512080, scp=0xa55b1e0,
sc=0xa5146e0,
grp=0x0, subset=0x0, f=1, ek=
{type = 0 '\0', card = 4 '\004', reduce = 1 '\001'})
at ../../../src/server/sql_select.mx:3628
#16 0xb24bac4c in sql_select (sql=0xa512080, scp=0xa55b1e0, sn=0xa514790,
ek=
{type = 0 '\0', card = 4 '\004', reduce = 1 '\001'})
at ../../../src/server/sql_select.mx:3910
#17 0xb24ae830 in sql_subquery (sql=0xa512080, scp=0xa55b1e0,
sq=0xa514790, ek=
---Type <return> to continue, or q <return> to quit---
{type = 0 '\0', card = 4 '\004', reduce = 1 '\001'})
at ../../../src/server/sql_select.mx:236
#18 0xb24ae9fd in scope_subquery (sql=0xa512080, scp=0xa55b1e0,
sq=0xa514790,
ek={type = 0 '\0', card = 4 '\004', reduce = 1 '\001'})
at ../../../src/server/sql_select.mx:279
#19 0xb24bc327 in selects (sql=0xa512080, scp=0x0, s=0xa514790)
at ../../../src/server/sql_select.mx:4292
#20 0xb24bed01 in semantic (sql=0xa512080, scp=0x0, s=0xa514790)
at ../../../src/server/sql_semantic.mx:1160
#21 0xb24bef49 in output_semantic (sql=0xa512080, scp=0x0, s=0xa514790)
at ../../../src/server/sql_semantic.mx:1231
#22 0xb244bfe9 in sql_symbol2stmt (c=0xa512080, sym=0xa514790)
at ../../../../src/backends/monet5/sql.mx:995
#23 0xb2482844 in SQLparser (c=0x804d3f4)
at ../../../../src/backends/monet5/sql_scenario.mx:1023
#24 0xb7f7c74d in runPhase (c=0x804d3f4, phase=1)
at ../../../src/mal/mal_scenario.mx:591
#25 0xb7f7c845 in runScenarioBody (c=0x804d3f4)
at ../../../src/mal/mal_scenario.mx:622
#26 0xb7f7ca43 in runScenario (c=0x804d3f4)
at ../../../src/mal/mal_scenario.mx:656
#27 0xb7f456b7 in MSserveClient (dummy=0x804d3f4)
at ../../../src/mal/mal_session.mx:462
---Type <return> to continue, or q <return> to quit---
#28 0xb73ae170 in start_thread () from /lib/libpthread.so.0
#29 0xb7116dfe in clone () from /lib/libc.so.6
It is not the very very latest version, but it is from last week. If it is
already fixed you can make me feel bad again.
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2008-10-21 00:11
Message:
It is possible, unless (e.g.) some intermediate result or the end result
exceeds the available address space, or available disk space.
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2008-10-20 23:43
Message:
sql>select * from way_tags as w1, way_tags as w2 where w1.k = 'bridge' and
more>w1.wayid = w2.wayid;
0 tuples
!MALException:algebra.join:GDKerror
!ERROR: GDKload: cannot mmap(): name=27/2705, ext=tail
!OS: Cannot allocate memory
!ERROR: GDKload failed: name=27/2705, ext=tail
0 tuples
This looks like a much better error handling :) :)
But still; why isn't it possible to select two tables at the same time;
First select from table one a feature; And try to match the foreign key of
this table, on the identical table, to match other features of (in my case)
a way.
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2008-10-09 04:45
Message:
I'll give detailed instructions now...
http://repo.or.cz/w/handlerosm.git?a=blob_plain;f=osmparser.c;hb=HEAD
Download this program. Compile it, it is easy C.
http://hypercube.telascience.org/planet/planet-nl-latest.osm.gz
Now sadly there is a problem in this file. Find way id=7062297, and remove
all duplicate tags, or remove the way entirely.
using: osmparser /path/to/your/planet-nl-latest > insert.sql
will result in some *.csv files and an insert.sql
mclient -lsql <insert.sql should automatically work (if you fixed the
above artifact).
Execute the following statement:
select * from way_tags as w1, way_tags as w2 where w1.k = 'bridge' and
w1.wayid = w2.wayid;
Your gdb session in mserver5 should look like this:
0x00007fd3f83402b6 in CMD_EQ_oid (ret=0x259f3c8, l=0x259f3a8, r=0x259f3b8)
at ../../../../src/modules/kernel/batcalc.mx:1128
1128 *o = (*p @2 *t);
(gdb) bt
#0 0x00007fd3f83402b6 in CMD_EQ_oid (ret=0x259f3c8, l=0x259f3a8,
r=0x259f3b8) at ../../../../src/modules/kernel/batcalc.mx:1128
#1 0x00007fd404653455 in DFLOWstep (t=0x253aa08, fs=0x4292cdf0) at
../../../src/mal/mal_interpreter.mx:1523
#2 0x00007fd404657a81 in runDFLOWworker (t=0x253aa08) at
../../../src/mal/mal_interpreter.mx:810
#3 0x00007fd403e95373 in wrapper_routine (data=0x4292d270) at
../../../src/gdk/gdk_posix.mx:935
#4 0x00007fd402b3a027 in start_thread () from /lib/libpthread.so.0
#5 0x00007fd401c6afad in clone () from /lib/libc.so.6
#6 0x0000000000000000 in ?? ()
(I have a system ready you already are able to login to, with the latest
CVS version)
----------------------------------------------------------------------
Comment By: Niels Nes (nielsnes)
Date: 2008-10-07 20:01
Message:
I need data to run the query. Probably a small number or records will do.
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2008-10-07 17:12
Message:
printf("CREATE TABLE nodes_legacy (id serial, x double, y double,
username varchar(255), timestamp timestamptz);\n");
printf("CREATE TABLE node_tags (node integer, k varchar(255), v
varchar(1024), primary key (node, k), foreign key(node) references
nodes_legacy);\n");
printf("CREATE TABLE ways (id serial,username varchar(255), timestamp
timestamptz);\n");
printf("CREATE TABLE way_tags (way integer, k varchar(255), v
varchar(1024), primary key (way, k), foreign key(way) references
ways);\n");
printf("CREATE TABLE way_nds (way integer, idx integer, to_node
integer, foreign key(way) references ways, foreign key(to_node) references
nodes_legacy, primary key(way, idx));\n");
printf("CREATE TABLE relations(id serial, username varchar(255),
timestamp timestamptz);\n");
printf("CREATE TABLE relation_members_node (relation integer, idx
integer, to_node integer, role varchar(255), foreign key(relation)
references relations, foreign key(to_node) references nodes_legacy, primary
key(relation, idx));\n");
printf("CREATE TABLE relation_members_relation (relation integer, idx
integer, to_relation integer, role varchar(255), foreign key(relation)
references relations, foreign key(to_relation) references relations,
primary key(relation, idx));\n");
printf("CREATE TABLE relation_members_way (relation integer, idx
integer, to_way integer, role varchar(255), foreign key(relation)
references relations, foreign key(to_way) references ways, primary
key(relation, idx));\n");
printf("CREATE TABLE relation_tags (relation integer, k varchar(255),
v varchar(1024), foreign key(relation) references relations, primary
key(relation, k));\n");
This is the program code that was used to generate the initial table. I
hope you mean this, and not the other 74GB.
----------------------------------------------------------------------
Comment By: Niels Nes (nielsnes)
Date: 2008-10-07 15:31
Message:
Sofar I cannot reproduce it. I think also some (or all) data is needed for
this query to fail.
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2008-10-07 14:16
Message:
What do you want me to add here? The full openstreetmap dataset, the table
description?
sql>\d ways
CREATE TABLE "sys"."ways" (
"id" int NOT NULL DEFAULT next value for "sys"."seq_3903",
"username" varchar(255),
"timestamp" timestamptz(7),
CONSTRAINT "ways_id_pkey" PRIMARY KEY ("id")
);
sql>\d way_tags
CREATE TABLE "sys"."way_tags" (
"way" int NOT NULL,
"k" varchar(255) NOT NULL,
"v" varchar(1024),
CONSTRAINT "way_tags_way_k_pkey" PRIMARY KEY ("way", "k"),
CONSTRAINT "way_tags_way_fkey" FOREIGN KEY ("way") REFERENCES
"ways" ("id")
);
----------------------------------------------------------------------
Comment By: Niels Nes (nielsnes)
Date: 2008-10-07 13:38
Message:
please add the full sql (create table statements).
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2134428&group_id=56967
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
