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

[firebird-automations]

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: FirebirdSQL/firebird@cc6d7212d88e76c7c9a6c7a106bfc53ce9661c77 FirebirdSQL/firebird@3a3a5b80f545af14cfa5b58bb2d898ecf78f4a63

====== 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]

Modified by: @dyemanov

security: Developers \[ 10012 \] =\>

[firebird-automations]

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]

Modified by: @dyemanov

assignee: Dmitry Yemanov \[ dimitr \]

[firebird-automations]

Modified by: @dyemanov

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

[firebird-automations]

Modified by: @dyemanov

Version: 3\.0 Initial \[ 10301 \] =\>

[firebird-automations]

Modified by: @dyemanov

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

[firebird-automations]

Modified by: @dyemanov

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

[firebird-automations]

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]

Modified by: @pcisar

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

[firebird-automations]

Modified by: @pavel-zotov

status: Closed \[ 6 \] =\> Closed \[ 6 \] QA Status: Done successfully

[firebird-automations]

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