Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Usage of a NATURAL JOIN with a derived table crashes the server [CORE3834] #4176

Closed
firebird-automations opened this issue Apr 26, 2012 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Daniele Cocca (jmc)

I've found that the following query:

SELECT DISTINCT "Revision", COALESCE("Message", '') AS "LogMessage"
FROM (
SELECT "Revision", "StageID" FROM "tilemaps"
UNION SELECT "Revision", "StageID" FROM "entitymaps"
UNION SELECT "Revision", "StageID" FROM "log"
) AS "revisions"
NATURAL JOIN "log"
WHERE "StageID" = :stageId
ORDER BY "Revision" DESC

can systematically crash Firebird. I've tried running the server through a debugger on my x64 Linux system and it seems to me like a null pointer dereferencing. Here follows the backtrace:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x7ffff3b60700 (LWP 12346)]
MAKE_desc (statement=0x7ffff41661e8, desc=0x8, node=0x0, null_replacement=0x0)
at ../src/dsql/make.cpp:409
409 if (node->nod_desc.dsc_dtype && !null_replacement)
(gdb) bt
#⁠0 MAKE_desc (statement=0x7ffff41661e8, desc=0x8, node=0x0,
null_replacement=0x0) at ../src/dsql/make.cpp:409
#⁠1 0x000000000067cad2 in MAKE_desc_from_list (statement=0x7ffff41661e8,
desc=0x7ffff3b5c130, node=0x7ffff40cdf40, null_replacement=0x0,
expression_name=0x777fbb "COALESCE") at ../src/dsql/make.cpp:1494
#⁠2 0x000000000067d76d in MAKE_desc (statement=0x7ffff41661e8,
desc=0x7ffff40cd8a8, node=0x7ffff40cd800, null_replacement=0x0)
at ../src/dsql/make.cpp:614
#⁠3 0x000000000067cdfc in MAKE_desc (statement=0x7ffff41661e8,
desc=0x7ffff40cd8a8, node=0x7ffff40cd770, null_replacement=0x0)
at ../src/dsql/make.cpp:1178
#⁠4 0x000000000069cfd7 in set_parameter_type (statement=0x7ffff41661e8,
in_node=0x7ffff40cd8a0, node=0x7ffff40cd770, force_varchar=false)
at ../src/dsql/pass1.cpp:10470
#⁠5 0x00000000006a3be7 in PASS1_node (statement=0x7ffff41661e8,
input=<optimized out>) at ../src/dsql/pass1.cpp:1155
#⁠6 0x00000000006ac411 in pass1_node_psql (input=<optimized out>,
statement=0x7ffff41661e8, psql=<optimized out>) at ../src/dsql/pass1.cpp:7333
#⁠7 pass1_rse_impl (statement=0x7ffff41661e8, input=0x7ffff40f6010,
order=0x7ffff40f5e28, rows=<optimized out>, update_lock=0x0,
flags=<optimized out>) at ../src/dsql/pass1.cpp:8085
#⁠8 0x00000000006ad00e in pass1_rse (flags=0, update_lock=0x0,
rows=<optimized out>, order=<optimized out>, input=<optimized out>,
statement=0x7ffff41661e8) at ../src/dsql/pass1.cpp:7959
#⁠9 pass1_rse_impl (statement=0x7ffff41661e8, input=0x7ffff40f5eb0, order=0x0,
rows=0x0, update_lock=0x0, flags=<optimized out>)
at ../src/dsql/pass1.cpp:8011
#⁠10 0x00000000006afb5c in pass1_rse (flags=0, update_lock=<optimized out>,
rows=0x0, order=0x0, input=<optimized out>, statement=0x7ffff41661e8)
at ../src/dsql/pass1.cpp:7959
#⁠11 PASS1_rse (statement=0x7ffff41661e8, input=<optimized out>,
update_lock=<optimized out>) at ../src/dsql/pass1.cpp:1232
#⁠12 0x00000000006b6b0d in PASS1_statement (statement=0x7ffff41661e8,
input=0x7ffff40f5dd8) at ../src/dsql/pass1.cpp:1867
#⁠13 0x0000000000666909 in prepare (tdbb=0x7ffff3b5ec90,
database=<optimized out>, transaction=0x7ffff416cd18, string_length=24024,
string=<optimized out>, client_dialect=3, parser_version=2)
at ../src/dsql/dsql.cpp:2625
#⁠14 0x000000000066ae7c in DSQL_prepare (tdbb=0x7ffff3b5ec90,
transaction=0x7ffff416cd18, req_handle=0x7ffff7feb7a0, length=302,
string=0x7ffff7fc9188 "SELECT DISTINCT \"Revision\", COALESCE(\"Message\", '')---Type <return> to continue, or q <return> to quit---
AS \"LogMessage\"\nFROM (\n\tSELECT \"Revision\", \"StageID\" FROM \"tilemaps\"\n\tUNION SELECT \"Revision\", \"StageID\" FROM \"entitymaps\"\n\tUNION SELECT \"Revision\","..., dialect=3, item_length=26,
items=0x7ffff3b5f3d8 "~\025\004\a\t\v\f\r\016\020\021\022\023\b\005\a\t\v\f\r\016\020\021\022\023\b\265\363\377\177", buffer_length=32768,
buffer=0x7ffff0a5fc08 "~\004") at ../src/dsql/dsql.cpp:726
#⁠15 0x0000000000573d41 in jrd8_prepare (user_status=0x7ffff3b5f7f0,
tra_handle=0x7ffff3b5eee0, stmt_handle=0x7ffff7feb7a0, length=302,
string=0x7ffff7fc9188 "SELECT DISTINCT \"Revision\", COALESCE(\"Message\", '') AS \"LogMessage\"\nFROM (\n\tSELECT \"Revision\", \"StageID\" FROM \"tilemaps\"\n\tUNION SELECT \"Revision\", \"StageID\" FROM \"entitymaps\"\n\tUNION SELECT \"Revision\","..., dialect=32, item_length=26,
items=0x7ffff3b5f3d8 "~\025\004\a\t\v\f\r\016\020\021\022\023\b\005\a\t\v\f\r\016\020\021\022\023\b\265\363\377\177", buffer_length=32768,
buffer=0x7ffff0a5fc08 "~\004") at ../src/jrd/jrd.cpp:3892
#⁠16 0x000000000043b7a6 in isc_dsql_prepare_m (user_status=<optimized out>,
tra_handle=<optimized out>, stmt_handle=0x7ffff7feba00, length=302,
string=0x7ffff7fc9188 "SELECT DISTINCT \"Revision\", COALESCE(\"Message\", '') AS \"LogMessage\"\nFROM (\n\tSELECT \"Revision\", \"StageID\" FROM \"tilemaps\"\n\tUNION SELECT \"Revision\", \"StageID\" FROM \"entitymaps\"\n\tUNION SELECT \"Revision\","..., dialect=32, item_length=26,
items=0x7ffff3b5f3d8 "~\025\004\a\t\v\f\r\016\020\021\022\023\b\005\a\t\v\f\r\016\020\021\022\023\b\265\363\377\177", buffer_length=32768,
buffer=0x7ffff0a5fc08 "~\004") at ../src/jrd/why.cpp:3629
#⁠17 0x000000000041b702 in rem_port::prepare_statement (this=0x7ffff7fec578,
prepareL=0x7ffff7feaa58, sendL=0x7ffff7fea388)
at ../src/remote/server.cpp:3212
#⁠18 0x0000000000420e6e in process_packet (port=0x7ffff7fec578,
sendL=0x7ffff7fea388, receive=0x7ffff7fea798, result=0x7ffff3b5fe10)
at ../src/remote/server.cpp:3516
#⁠19 0x0000000000422a06 in loopThread () at ../src/remote/server.cpp:5212
#⁠20 0x0000000000436706 in run (this=<synthetic pointer>)
at ../src/jrd/ThreadStart.cpp:128
#⁠21 (anonymous namespace)::threadStart (arg=0x7ffff7feac70)
at ../src/jrd/ThreadStart.cpp:139
#⁠22 0x00007ffff66e5df0 in start_thread () from /lib/libpthread.so.0
#⁠23 0x00007ffff642b39d in clone () from /lib/libc.so.6
#⁠24 0x0000000000000000 in ?? ()

I can give the statements used to create the affected tables if needed.

Commits: cc6d721 3a3a5b8

====== Test Details ======

Field 'stageid' does exist in both tables ('tilemaps' and 'logs'), so it CAN be used here w/o alias prefix in WHERE clause.
Confirmed crashes for similar queries (differ only in argument in right part of WHERE expression:
1) select * from (select * from mon$attachments a) a natural join mon$statements s where mon$stat_id = 0;
and
2) select * from (select * from mon$attachments a) a natural join mon$statements s where mon$stat_id = ?;
WI-V2.5.0.26074: "1)" ==> SQLSTATE = HY004/Data type unknown; "2)" ==> SQLSTATE = 08006/Error reading data from the connection.
WI-V2.5.1.26351: the same as WI-V2.5.0.26074

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.1.1 [ 10223 ]

Version: 2.1.0 [ 10041 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Initial [ 10301 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: A query crashes the database => Usage of a NATURAL JOIN with a derived table crashes the server

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1.5 [ 10420 ]

Fix Version: 2.5.2 [ 10450 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

Test Details: Field 'stageid' does exist in both tables ('tilemaps' and 'logs'), so it CAN be used here w/o alias prefix in WHERE clause.
Confirmed crashes for similar queries (differ only in argument in right part of WHERE expression:
1) select * from (select * from mon$attachments a) a natural join mon$statements s where mon$stat_id = 0;
and
2) select * from (select * from mon$attachments a) a natural join mon$statements s where mon$stat_id = ?;
WI-V2.5.0.26074: "1)" ==> SQLSTATE = HY004/Data type unknown; "2)" ==> SQLSTATE = 08006/Error reading data from the connection.
WI-V2.5.1.26351: the same as WI-V2.5.0.26074

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment