Opened 7 years ago

Closed 7 years ago

#3731 closed defect (fixed)

SELECT * FROM testgeom WHERE ST_Intersects() and the back-end crashes

Reported by: zhuodao Owned by: pramsey
Priority: critical Milestone: PostGIS 2.1.9
Component: postgis Version: 2.2.x
Keywords: ST_Intersects estimate_selectivity not a number Cc:

Description

I try to run some special sql as follow on PostgreSQL 9.4.10 and PostGis 2.2.2 installed from source code, and the back-end crashes:

 select the_geom from testgeom where ST_Intersects(the_geom,ST_GeomFromText( 'POLYGON((80.4748535 32.2128011,80.4858398 32.2128011,80.4858398 32.2035053,80.4748535 32.2035053,80.4748535 32.2128011))',4326));

The table testgeom contain a column of geometry TYPE like this:

gds=# \d testgeom;
  Column  |   Type   | Modifiers
----------+----------+-----------
	 the_geom | geometry |

And it has three records.

I get the backtrace:

#0  0x00007f081fca48a5 in raise () from /lib64/libc.so.6
#1  0x00007f081fca6085 in abort () from /lib64/libc.so.6
#2  0x00000000008f7e5e in ExceptionalCondition (
    conditionName=0xa86098 "!(rel->rows > 0 || ((rel)->cheapest_total_path != ((void *)0) && (((((const Node*)(((rel)->cheapest_total_path)))->type) == T_AppendPath) && ((AppendPath *) ((rel)->cheapest_total_path))->subpaths == "..., errorType=0xa8600b "FailedAssertion", fileName=0xa86000 "allpaths.c", lineNumber=350)
    at assert.c:54
#3  0x00000000006d5930 in set_rel_size (root=0x2c6c138, rel=0x2c834c0, rti=5, rte=0x2c7bc60) at allpaths.c:350

I found rel→rows not a number and the divisor is zero in the gserialized_estimate.c:

cell_size[d] = (max[d] - min[d]) / nd_stats->size[d]; 

The nd_stats→size comes from the column stanumbersN table pg_statistic like this:

stanumbers1 | {2,0,0,0,0,80.4356,32.1458,0,0,80.5024,32.3317,0,0,7,7,7,7,1,7,7}

And I found function compute_gserialized_stats_mode in the gserialized_estimate.c:1425

histo_cells_target = Min(histo_cells_target, (int)(total_rows/5));

When total_rows is greater than 0 and less than 5, histo_cells_target will be zero and it will cause the error. But I wonder why this histo_cells_target can be zero. Is it a bug ?

And can I fix it as follow?

histo_cells_target = Min(histo_cells_target, Max(1, (int)(total_rows/5)));

By the way, run the same sql on the PostGis 2.1.7 and PostgreSQL 9.4.10 return a right result.

Change History (12)

comment:1 by pramsey, 7 years ago

Thanks

comment:2 by zhuodao, 7 years ago

  • Yes. I use 2.2.5 and the issue persist.
  • Run pg_dump -t testgeom, return result as follow:
    COPY testgeom (the_geom) FROM stdin;
    0102000020E6100000220000005B7F09E42B1C5440B3B112F3AC1F4040C75F6245301C54408EDE78900120404074A95B87341C544081A13F1B562040408210DABC3B1C544054064813F3204040C076B49A431C5440331DF8209D2140409279E40F461C5440F54B4023C32140400B440DAE521C54405EB3A6C3D4224040B1AAB9375A1C54408DC83D7E6B234040BF11386D611C5440338D0509FA2340400C42F5E6621C54409EEAF3C51C244040A9BF7F6F6A1C544051163A86B2244040C0B05F24721C5440C2FC70EB492540403C9AB01E731C54405849F98360254040FA58EAEE741C544008C598EC8625404061C6B181761C54403A1AB0798F254040891A5434791C5440D7CFCDC495254040CB4B4044911C544098D80095BF2540403D37C00E941C5440FF8448C0C72540409926CF69941C54403674B33FD0254040AE20B4BB931C5440EB2EBBEDE3254040B8644D13931C5440708C4BF22F264040D595AEE4931C54409CEB0B53A126404034F326B7941C5440148B4AB0D926404093AF5F0B951C5440347AFB94DF26404055E2D79B9A1C5440E4B9635614274040016EF520AD1C54401B990D53D7274040847CB716B91C5440DEF19A155928404039AB2E75CA1C5440F33405D701294040B7960B8DCE1C54400390C12729294040B7960B8DCE1C54402E6E695632294040B7960B8DCE1C54407E1AF7E637294040CCE6376DC21C54408AE9219E952940405C53E607AA1C5440E2EBE724462A4040E9C7D3B0A71C54403930140B572A4040
    0102000020E6100000320000005AF14FA121205440AA122353D71240401DBCC0F61D205440AE3C50C0D91240401D80A2EC0C2054407B14AE47E11240409E337935062054409841C6EDE11240409A71E0F600205440E1F5B250DF12404019C46AA0F71F5440DE3BAC68DA124040DAFC4323ED1F54408662C0CCD6124040CC3A4E97E61F544045912022D6124040011B6BDAE01F5440324B6D6FD81240401FAF3012D41F5440060EAA05DD1240402DBD4FB8CB1F5440A75171B1DC1240400BC8C98AC21F5440B9D9A360DB124040710CD840C01F5440FB68C40EDB124040BC36C071BE1F5440DEF19A15D91240409DA85B2CBB1F54401C01A281D4124040EEFD154BB41F5440372AA2ECCE1240407694394CAA1F5440FCC7DF49CD12404032AA2DF1971F5440D4F5E1ACCA124040CDCCCCCC8C1F5440280D350AC912404016E079B1891F5440346809AEC9124040610A62E2871F5440CDC883A1CC124040F4EAF383801F5440B923B5B3DC12404015E29178791F54403564D91FEA124040D9DC033A741F544034FD2B8EF71240408B4BFA20701F544015B377A905134040BF6F536E681F544077808A571713404020F03CC85D1F5440934E87C225134040470E647F511F5440FF54A20D3C1340402FBC372B471F5440DD551709501340409DE39299411F54405F4F2AD05D13404014AE47E13A1F5440DE936677711340400ECF9E0D311F54406F686724841340405D24E5D5161F54404040AD58B213404091ABA289011F5440026FF55AD8134040EB44F6FFF91E5440760E4C0BEA134040D13606B6ED1E54402C9496DB071440406D5B31AFE51E54405249F8391914404055B01922DF1E544059CD29B723144040ECADBB58DC1E54406734A8EC2A144040E108D661DA1E544045DE4976321440406EBE9515D81E5440AFD8BA2F421440409CBB65A0D51E54405D24E5D556144040F9165255D31E54400CCF2AB75D1440401A0EF049CC1E5440AF6DDD0F70144040C076B49AC31E544051AD742D901440402E9E0F09BE1E54408687D5C4A7144040917F4502B61E5440CD82BB79CB144040C75F6245B01E54404B5B1AC7E114404041D1C0B8A91E5440758567D7F7144040B17071D8A61E5440F4616A0901154040
    0102000020E61000005E000000B17071D8A61E5440F4616A0901154040C142DE59981E54407DA1576E2F154040CD4A28B78C1E54406CC351874F154040DD1AE09B891E5440D9E6081157154040E23B9415811E54408774B2B36C15404022C5DF7A7D1E5440448676CA7A15404090EC3AE9771E5440319796898915404003940A53691E5440A06C0C32B0154040FC1322015F1E5440A5D3A170C91540404C6BF4E6471E5440DB3A93910E164040CC20E3F6301E54401CF8288E491640403A4A562C1B1E5440660C7BF37E164040B53B20E0FB1D54400469AD05C9164040BA62A9A2F61D5440EEB73486D21640409A2F485DDF1D54404AFDE528FA164040A0BF9678BC1D544000C17F67391740406C00BA58A61D54408260A7BB5F17404033601C9E961D54403E1350977B174040B89367E2861D544029BDC2C49717404066E54D347E1D54404D6E0CE8A2174040E954E872791D5440DFEB3923A9174040577C43E1731D5440876921F9B11740409443F6076B1D5440CB532D54C4174040EFDED59B661D5440145FED28CE17404008ED518F5F1D54406AD12B33DF1740408B62C1165E1D544062EEDE7AE2174040E9B964A05B1D5440197C7114E6174040B3DB5F24511D5440DE7082E3F01740405ECA6DB9421D5440E5F4B360FB17404015BFADE4381D54403CC60DA6FE17404025899080321D54403F88A6E40318404065B31B642F1D544072499BCB0918404062F182252A1D544080B0190111184040EFAA8B04281D544067FB884614184040C3716C7F1F1D5440413A6503231840407E24FCBD1A1D544027116598281840405F969778171D54402B89EC832C18404025EE0CAE141D5440CF8E4C6E311840409E5FD6BC111D5440D5127EEB3B184040AFE57C4E0D1D5440932442024A184040EF0F08320A1D5440A8B81E434F1840407CC91011081D54404CBE7E2D54184040D0DC1A43061D5440DEF19A155918404096D91822041D54408D9CE0F65F184040B1A92D54021D544061A19C8965184040FB17528FFE1C5440D969390A6F184040132C2F75E71C5440A73D67039718404076091C43DF1C5440C34DE36AA6184040D46CF507D01C544098045E1CCB1840404C3B4E34BC1C5440952EBB2F001940407838AA68A61C544040035E243C194040FD0C352B971C544048C5DE0F6819404070B40495881C544015F0DF7A9C194040A95EA7267B1C5440E1A67135D3194040C636A9686C1C5440F7855F03051A40403A3FC571601C5440190A33C8381A4040BAEE528F591C54403B9EE0AC521A40409277CC48561C54408E4E86785E1A4040911680A9531C54405BE46403651A404010690A534A1C544010555B5E771A40401B7154B03E1C54401E13AD058B1A404041E866A02E1C54400341800C9D1A40403CCBE2B4161C5440D273C940B71A4040C886035B041C54409DEC0B8DCC1A404082D846FAFC1B5440AB538AC2D31A40405A61C0B3F91B5440A4703D0AD71A404058007414F71B54405BFECFA3DA1A40403AD35B6EF61B544018B9C048DC1A4040737511F0F51B5440E28C0695DD1A4040AC17C771F51B54401C314834E01A40400B753F44F61B5440DDADD117E71A40401F5EBE92F71B54409F9ECAABEB1A4040B7BCAC68F71B54407A86D3F6ED1A4040737511F0F51B5440EFD6E88BF31A40402383BB37EF1B54409A1669A0011B40405BC4241AEC1B54408727895F101B404046DBA5CBEA1B54407F443CA7131B404020C122F9E91B5440FB2D8DA1141B40406BEF5355E81B544018A5B69A161B4040B6BEC42FE71B54405249F839191B4040604A6E5EE61B5440260C35D01D1B404028A8B8DCE61B5440173E0909241B4040115600DFEF1B5440CA3F0A095E1B404019DCBD79FD1B54404332EC49C31B40402481A370FF1B5440F1758499D71B4040579D0FAE041C54408AA12812441C4040048C9199091C54406FF940D9B11C40402E64647F0F1C5440E6255A4D321D4040EF9A253B151C5440BBE97D80C11D40408A5CECCD1A1C54409C2DA4EC421E4040F45E4A971D1C5440A7B1BD16741E404080106666281C5440F25B19F9611F40403ECB560B2A1C5440B4D4724E891F40405B7F09E42B1C5440B3B112F3AC1F4040
    \.
    

in reply to:  1 comment:3 by zhuodao, 7 years ago

Replying to pramsey:

Thanks

  • Yes. I use 2.2.5 and the issue persist.
  • Run pg_dump -t testgeom, return result as follow:
    COPY testgeom (the_geom) FROM stdin;
    0102000020E6100000220000005B7F09E42B1C5440B3B112F3AC1F4040C75F6245301C54408EDE78900120404074A95B87341C544081A13F1B562040408210DABC3B1C544054064813F3204040C076B49A431C5440331DF8209D2140409279E40F461C5440F54B4023C32140400B440DAE521C54405EB3A6C3D4224040B1AAB9375A1C54408DC83D7E6B234040BF11386D611C5440338D0509FA2340400C42F5E6621C54409EEAF3C51C244040A9BF7F6F6A1C544051163A86B2244040C0B05F24721C5440C2FC70EB492540403C9AB01E731C54405849F98360254040FA58EAEE741C544008C598EC8625404061C6B181761C54403A1AB0798F254040891A5434791C5440D7CFCDC495254040CB4B4044911C544098D80095BF2540403D37C00E941C5440FF8448C0C72540409926CF69941C54403674B33FD0254040AE20B4BB931C5440EB2EBBEDE3254040B8644D13931C5440708C4BF22F264040D595AEE4931C54409CEB0B53A126404034F326B7941C5440148B4AB0D926404093AF5F0B951C5440347AFB94DF26404055E2D79B9A1C5440E4B9635614274040016EF520AD1C54401B990D53D7274040847CB716B91C5440DEF19A155928404039AB2E75CA1C5440F33405D701294040B7960B8DCE1C54400390C12729294040B7960B8DCE1C54402E6E695632294040B7960B8DCE1C54407E1AF7E637294040CCE6376DC21C54408AE9219E952940405C53E607AA1C5440E2EBE724462A4040E9C7D3B0A71C54403930140B572A4040
    0102000020E6100000320000005AF14FA121205440AA122353D71240401DBCC0F61D205440AE3C50C0D91240401D80A2EC0C2054407B14AE47E11240409E337935062054409841C6EDE11240409A71E0F600205440E1F5B250DF12404019C46AA0F71F5440DE3BAC68DA124040DAFC4323ED1F54408662C0CCD6124040CC3A4E97E61F544045912022D6124040011B6BDAE01F5440324B6D6FD81240401FAF3012D41F5440060EAA05DD1240402DBD4FB8CB1F5440A75171B1DC1240400BC8C98AC21F5440B9D9A360DB124040710CD840C01F5440FB68C40EDB124040BC36C071BE1F5440DEF19A15D91240409DA85B2CBB1F54401C01A281D4124040EEFD154BB41F5440372AA2ECCE1240407694394CAA1F5440FCC7DF49CD12404032AA2DF1971F5440D4F5E1ACCA124040CDCCCCCC8C1F5440280D350AC912404016E079B1891F5440346809AEC9124040610A62E2871F5440CDC883A1CC124040F4EAF383801F5440B923B5B3DC12404015E29178791F54403564D91FEA124040D9DC033A741F544034FD2B8EF71240408B4BFA20701F544015B377A905134040BF6F536E681F544077808A571713404020F03CC85D1F5440934E87C225134040470E647F511F5440FF54A20D3C1340402FBC372B471F5440DD551709501340409DE39299411F54405F4F2AD05D13404014AE47E13A1F5440DE936677711340400ECF9E0D311F54406F686724841340405D24E5D5161F54404040AD58B213404091ABA289011F5440026FF55AD8134040EB44F6FFF91E5440760E4C0BEA134040D13606B6ED1E54402C9496DB071440406D5B31AFE51E54405249F8391914404055B01922DF1E544059CD29B723144040ECADBB58DC1E54406734A8EC2A144040E108D661DA1E544045DE4976321440406EBE9515D81E5440AFD8BA2F421440409CBB65A0D51E54405D24E5D556144040F9165255D31E54400CCF2AB75D1440401A0EF049CC1E5440AF6DDD0F70144040C076B49AC31E544051AD742D901440402E9E0F09BE1E54408687D5C4A7144040917F4502B61E5440CD82BB79CB144040C75F6245B01E54404B5B1AC7E114404041D1C0B8A91E5440758567D7F7144040B17071D8A61E5440F4616A0901154040
    0102000020E61000005E000000B17071D8A61E5440F4616A0901154040C142DE59981E54407DA1576E2F154040CD4A28B78C1E54406CC351874F154040DD1AE09B891E5440D9E6081157154040E23B9415811E54408774B2B36C15404022C5DF7A7D1E5440448676CA7A15404090EC3AE9771E5440319796898915404003940A53691E5440A06C0C32B0154040FC1322015F1E5440A5D3A170C91540404C6BF4E6471E5440DB3A93910E164040CC20E3F6301E54401CF8288E491640403A4A562C1B1E5440660C7BF37E164040B53B20E0FB1D54400469AD05C9164040BA62A9A2F61D5440EEB73486D21640409A2F485DDF1D54404AFDE528FA164040A0BF9678BC1D544000C17F67391740406C00BA58A61D54408260A7BB5F17404033601C9E961D54403E1350977B174040B89367E2861D544029BDC2C49717404066E54D347E1D54404D6E0CE8A2174040E954E872791D5440DFEB3923A9174040577C43E1731D5440876921F9B11740409443F6076B1D5440CB532D54C4174040EFDED59B661D5440145FED28CE17404008ED518F5F1D54406AD12B33DF1740408B62C1165E1D544062EEDE7AE2174040E9B964A05B1D5440197C7114E6174040B3DB5F24511D5440DE7082E3F01740405ECA6DB9421D5440E5F4B360FB17404015BFADE4381D54403CC60DA6FE17404025899080321D54403F88A6E40318404065B31B642F1D544072499BCB0918404062F182252A1D544080B0190111184040EFAA8B04281D544067FB884614184040C3716C7F1F1D5440413A6503231840407E24FCBD1A1D544027116598281840405F969778171D54402B89EC832C18404025EE0CAE141D5440CF8E4C6E311840409E5FD6BC111D5440D5127EEB3B184040AFE57C4E0D1D5440932442024A184040EF0F08320A1D5440A8B81E434F1840407CC91011081D54404CBE7E2D54184040D0DC1A43061D5440DEF19A155918404096D91822041D54408D9CE0F65F184040B1A92D54021D544061A19C8965184040FB17528FFE1C5440D969390A6F184040132C2F75E71C5440A73D67039718404076091C43DF1C5440C34DE36AA6184040D46CF507D01C544098045E1CCB1840404C3B4E34BC1C5440952EBB2F001940407838AA68A61C544040035E243C194040FD0C352B971C544048C5DE0F6819404070B40495881C544015F0DF7A9C194040A95EA7267B1C5440E1A67135D3194040C636A9686C1C5440F7855F03051A40403A3FC571601C5440190A33C8381A4040BAEE528F591C54403B9EE0AC521A40409277CC48561C54408E4E86785E1A4040911680A9531C54405BE46403651A404010690A534A1C544010555B5E771A40401B7154B03E1C54401E13AD058B1A404041E866A02E1C54400341800C9D1A40403CCBE2B4161C5440D273C940B71A4040C886035B041C54409DEC0B8DCC1A404082D846FAFC1B5440AB538AC2D31A40405A61C0B3F91B5440A4703D0AD71A404058007414F71B54405BFECFA3DA1A40403AD35B6EF61B544018B9C048DC1A4040737511F0F51B5440E28C0695DD1A4040AC17C771F51B54401C314834E01A40400B753F44F61B5440DDADD117E71A40401F5EBE92F71B54409F9ECAABEB1A4040B7BCAC68F71B54407A86D3F6ED1A4040737511F0F51B5440EFD6E88BF31A40402383BB37EF1B54409A1669A0011B40405BC4241AEC1B54408727895F101B404046DBA5CBEA1B54407F443CA7131B404020C122F9E91B5440FB2D8DA1141B40406BEF5355E81B544018A5B69A161B4040B6BEC42FE71B54405249F839191B4040604A6E5EE61B5440260C35D01D1B404028A8B8DCE61B5440173E0909241B4040115600DFEF1B5440CA3F0A095E1B404019DCBD79FD1B54404332EC49C31B40402481A370FF1B5440F1758499D71B4040579D0FAE041C54408AA12812441C4040048C9199091C54406FF940D9B11C40402E64647F0F1C5440E6255A4D321D4040EF9A253B151C5440BBE97D80C11D40408A5CECCD1A1C54409C2DA4EC421E4040F45E4A971D1C5440A7B1BD16741E404080106666281C5440F25B19F9611F40403ECB560B2A1C5440B4D4724E891F40405B7F09E42B1C5440B3B112F3AC1F4040
    \.
    

However, I think zero should be banned to be a divisor.

Thanks.

comment:4 by zhuodao, 7 years ago

Priority: mediumcritical

comment:5 by pramsey, 7 years ago

Confirmed crasher on all versions. Working on it now.

comment:6 by pramsey, 7 years ago

comment:7 by pramsey, 7 years ago

Resolution: fixed
Status: newclosed

Thanks, your analysis of the problem was correct, the small data size and relative uniformity led to a zero-ed out stats histogram and eventual memory fault.

comment:8 by robe, 7 years ago

Resolution: fixed
Status: closedreopened

cough cough you are missing a news item for this (for 2.1-2.3) and if not too much trouble, can you create a test for this at least at 2.4?

Last edited 7 years ago by robe (previous) (diff)

comment:9 by pramsey, 7 years ago

In 15762:

News item (references #3731)

comment:10 by pramsey, 7 years ago

In 15763:

News item (references #3731)

comment:11 by pramsey, 7 years ago

In 15764:

News item (references #3731)

comment:12 by pramsey, 7 years ago

Resolution: fixed
Status: reopenedclosed
Note: See TracTickets for help on using tickets.