{"action":"created","issue":{"url":"https://api.github.com/repos/pg-strom/devel/issues/247","repository_url":"https://api.github.com/repos/pg-strom/devel","labels_url":"https://api.github.com/repos/pg-strom/devel/issues/247/labels{/name}","comments_url":"https://api.github.com/repos/pg-strom/devel/issues/247/comments","events_url":"https://api.github.com/repos/pg-strom/devel/issues/247/events","html_url":"https://github.com/pg-strom/devel/issues/247","id":181713938,"number":247,"title":"LATERAL JOIN segfault / illegal access","user":{"login":"spd010273","id":8760274,"avatar_url":"https://avatars.githubusercontent.com/u/8760274?v=3","gravatar_id":"","url":"https://api.github.com/users/spd010273","html_url":"https://github.com/spd010273","followers_url":"https://api.github.com/users/spd010273/followers","following_url":"https://api.github.com/users/spd010273/following{/other_user}","gists_url":"https://api.github.com/users/spd010273/gists{/gist_id}","starred_url":"https://api.github.com/users/spd010273/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/spd010273/subscriptions","organizations_url":"https://api.github.com/users/spd010273/orgs","repos_url":"https://api.github.com/users/spd010273/repos","events_url":"https://api.github.com/users/spd010273/events{/privacy}","received_events_url":"https://api.github.com/users/spd010273/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-10-07T16:32:15Z","updated_at":"2016-10-10T12:52:42Z","closed_at":null,"body":"I get either segmentation faults or illegal memory access when using lateral joins, specifically LEFT JOIN LATERAL. \r\n\r\nTest case:\r\n\r\n```\r\nCREATE TABLE t1 AS( SELECT md5( ( random() * 1000 )::VARCHAR ) AS foo, generate_series( 1, 10000 ) AS id );\r\nCREATE TABLE t2 AS( SELECT md5( ( random() * 1000 )::VARCHAR ) AS bar, generate_series( 10000, 1, -1 ) AS id );\r\n\r\nSELECT t1.foo,\r\n           case when t.id IS NULL\r\n                then 'no'\r\n                else t2.bar\r\n                 end\r\n      FROM t1\r\n LEFT JOIN LATERAL (\r\n                         SELECT one.id\r\n                           FROM t1 one\r\n                           JOIN t2 two\r\n                             ON two.id = one.id\r\n                          WHERE one.foo ILIKE 'a%'\r\n                    ) t\r\n        ON t.id = t1.id\r\n      JOIN t2\r\n        ON t2.id = t1.id;\r\n```\r\n\r\nIllegal access error is as follows:\r\n\r\n```\r\nWARNING:  failed on cuCtxSynchronize: CUDA_ERROR_ILLEGAL_ADDRESS - an illegal memory access was encountered\r\nWARNING:  AbortTransaction while in ABORT state\r\nERROR:  failed on cuCtxGetLimit: CUDA_ERROR_ILLEGAL_ADDRESS - an illegal memory access was encountered\r\nERROR:  failed on cuMemFree: CUDA_ERROR_ILLEGAL_ADDRESS - an illegal memory access was encountered\r\nTime: 3851.390 ms\r\n```\r\n\r\nSegmentation fault is as follows:\r\n```\r\n2016-10-07 11:44:28 EDT [20013-18] LOG:  server process (PID 23461) was terminated by signal 11: Segmentation fault\r\n2016-10-07 11:44:28 EDT [20013-19] DETAIL:  Failed process was running: <REDACTED>\r\n2016-10-07 11:44:28 EDT [20013-20] LOG:  terminating any other active server processes\r\n2016-10-07 11:44:28 EDT [23459-2] WARNING:  terminating connection because of crash of another server process\r\n2016-10-07 11:44:28 EDT [23459-3] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exi\r\n2016-10-07 11:44:28 EDT [23459-4] HINT:  In a moment you should be able to reconnect to the database and repeat your command.\r\n2016-10-07 11:44:28 EDT [23471-1] postgres@thd_test FATAL:  the database system is in recovery mode\r\n2016-10-07 11:44:28 EDT [20013-21] LOG:  all server processes terminated; reinitializing\r\n2016-10-07 11:44:28 EDT [23472-1] LOG:  database system was interrupted; last known up at 2016-10-07 11:42:29 EDT\r\n2016-10-07 11:44:28 EDT [23472-2] LOG:  database system was not properly shut down; automatic recovery in progress\r\n2016-10-07 11:44:28 EDT [23472-3] LOG:  invalid record length at 0/90A1EDC0\r\n2016-10-07 11:44:28 EDT [23472-4] LOG:  redo is not required\r\n```\r\n\r\nI am still trying to work out a reliable, generic test case to force a segmentation fault.\r\n\r\nHere's the redacted query with altered table names:\r\n\r\n```\r\n    SELECT a.id AS reset_request,\r\n           COALESCE( h.classification, 'None' ) AS dept\r\n      FROM ONLY table_one a\r\nINNER JOIN table_two b\r\n        ON b.id = a.id\r\nINNER JOIN table_three c\r\n        ON c.id = b.id\r\n       AND c.boolean_val IS TRUE\r\nINNER JOIN table_four d\r\n        ON d.date_bound_a <= a.created\r\n       AND d.date_bound_b >= a.created\r\n       AND d.date_bound_c <= now() - interval '7 days'\r\n       AND d.date_bound_d >= now() - interval '7 days'\r\n LEFT JOIN LATERAL (\r\n                      SELECT rs.id,\r\n                             array_to_string( array_agg( DISTINCT d.number ), ',' ) AS classification\r\n                        FROM ONLY table_five e\r\n                  INNER JOIN table_six f\r\n                          ON c.id = rs.derived_id\r\n                  INNER JOIN table_seven g\r\n                          ON d.id = c.id\r\n                       WHERE rs.id = a.id\r\n                    GROUP BY rs.id\r\n           ) h\r\n        ON a.id = h.id\r\n```\r\n\r\nTo give an idea of the data set size:\r\n```\r\nSELECT COUNT(id) FROM ONLY table_one;\r\n5216090\r\nSELECT COUNT(id) FROM table_two;\r\n26690\r\nSELECT COUNT(id) FROM table_three;\r\n498\r\nSELECT COUNT(id) FROM table_four;\r\n10591\r\nSELECT COUNT(id) FROM ONLY table_five;\r\n303439\r\nSELECT COUNT(id) FROM table_six;\r\n291\r\nSELECT COUNT(id) FROM table_seven;\r\n25\r\n```\r\n\r\nHardware / settings is as follows:\r\nPG-Strom version 1.0devel built for PostgreSQL 9.5\r\nCUDA Runtime version: 8.0.0\r\nNVIDIA driver version: 367.48\r\nGPU0 GeForce GTX 970 (1664 CUDA cores, 1342MHz), L2 1792KB, RAM 4036MB (256bits, 3505MHz), capability 5.2\r\nGPU1 GeForce GTX 760 (6 SMs, 1071MHz), L2 512KB, RAM 1998MB (256bits, 3004MHz), capability 3.0, NOT SUPPORTED\r\n\r\n\r\n PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.2.1-22ubuntu2) 5.2.1 20151010, 64-bit\r\nLinux chris-workstation 4.2.0-42-generic #49-Ubuntu SMP Tue Jun 28 21:26:26 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux\r\n\r\nshared_buffers: 12GB\r\npg_strom.gpu_setup_cost: 500\r\npg_strom.chunk_size: 15MB\r\npg_strom.program_cache_size: 48MB\r\n\r\nMachine is running Kubuntu 15.04\r\n"},"comment":{"url":"https://api.github.com/repos/pg-strom/devel/issues/comments/252609979","html_url":"https://github.com/pg-strom/devel/issues/247#issuecomment-252609979","issue_url":"https://api.github.com/repos/pg-strom/devel/issues/247","id":252609979,"user":{"login":"spd010273","id":8760274,"avatar_url":"https://avatars.githubusercontent.com/u/8760274?v=3","gravatar_id":"","url":"https://api.github.com/users/spd010273","html_url":"https://github.com/spd010273","followers_url":"https://api.github.com/users/spd010273/followers","following_url":"https://api.github.com/users/spd010273/following{/other_user}","gists_url":"https://api.github.com/users/spd010273/gists{/gist_id}","starred_url":"https://api.github.com/users/spd010273/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/spd010273/subscriptions","organizations_url":"https://api.github.com/users/spd010273/orgs","repos_url":"https://api.github.com/users/spd010273/repos","events_url":"https://api.github.com/users/spd010273/events{/privacy}","received_events_url":"https://api.github.com/users/spd010273/received_events","type":"User","site_admin":false},"created_at":"2016-10-10T12:52:42Z","updated_at":"2016-10-10T12:52:42Z","body":"Okay this fixed the illegal access error (thank you much!) but I am still able to cause a seg fault with the 2nd query listed here. I'm going to work today on getting a generic test case with data."}}
{"actor":{"display_login":"spd010273"}}