<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FWIW SQL Server abides by the NULL on NULL input rule. I can't afford Oracle spatial so can't test that </span><span style='font-size:11.0pt;font-family:Wingdings;color:#1F497D'>J</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Not that it matters, just saying </span><span style='font-size:11.0pt;font-family:Wingdings;color:#1F497D'>J</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:9.5pt;font-family:Consolas;color:blue'>DECLARE</span><span style='font-size:9.5pt;font-family:Consolas'> @g <span style='color:blue'>geometry</span><span style='color:gray'>;</span> <o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:9.5pt;font-family:Consolas'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:9.5pt;font-family:Consolas;color:blue'>SET</span><span style='font-size:9.5pt;font-family:Consolas'> @g <span style='color:gray'>=</span> <span style='color:blue'>geometry</span><span style='color:gray'>::</span>STGeomFromText<span style='color:gray'>(</span><span style='color:red'>'POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'</span><span style='color:gray'>,</span> 0<span style='color:gray'>);</span> <o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:9.5pt;font-family:Consolas'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:9.5pt;font-family:Consolas;color:blue'>SELECT</span><span style='font-size:9.5pt;font-family:Consolas'> @g<span style='color:gray'>.</span>STUnion<span style='color:gray'>(NULL);</span> <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Returns NULL<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Though the behavior of returning the first non-null geometry for ST_Union and ST_Collect does sound appealing.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>But then it violates my feeling it should behave like 'something' || NULL = NULL<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>But pramsey and Bjorn shot that symmetry out of the park when they changed ST_MVT.. to not return NULL for convenience.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>My only concern with changing would be if anybody is relying on this NULL on NULL input behavior for these functions and how badly.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I'm fine with change in 2.5 with a big … BREAKING change note, but not for older versions as it is a clear behavior change not resulting from fixing a real bug.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I'm a little less okay with it for ST_Difference<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-devel [mailto:postgis-devel-bounces@lists.osgeo.org] <b>On Behalf Of </b>Darafei "Kom?pa" Praliaskouski<br><b>Sent:</b> Tuesday, December 19, 2017 8:04 AM<br><b>To:</b> PostGIS Development Discussion <postgis-devel@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-devel] ST_Union, ST_Collect and ST_Difference strictness<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><p class=MsoNormal style='margin-left:.5in'>Hi,<o:p></o:p></p><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>Thanks for the document.<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>I've found just a line of RETURNS NULL ON NULL INPUT on pages 44-45.<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>It feels to me that this line is there as a result of copy-paste - there is no explanation in text why it matters or should be that way. <o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>What would be the way to get comments on this from people designing the spec?<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>It does not define ST_Collect, so this behavior can be applied to it not violating the spec.<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><div><p class=MsoNormal style='margin-left:.5in'>пн, 18 дек. 2017 г. в 19:40, Daniel Baston <<a href="mailto:dbaston@gmail.com" target="_blank">dbaston@gmail.com</a>>:<o:p></o:p></p></div></div><div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><p class=MsoNormal style='margin-left:.5in'>I think strictness of these functions may be specified by OGC standards...<o:p></o:p></p><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><a href="http://www.opengeospatial.org/standards/sfs" target="_blank">http://www.opengeospatial.org/standards/sfs</a><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>Dan<o:p></o:p></p></div></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><div><p class=MsoNormal style='margin-left:.5in'>On Mon, Dec 18, 2017 at 9:03 AM, Darafei "Komяpa" Praliaskouski <<a href="mailto:me@komzpa.net" target="_blank">me@komzpa.net</a>> wrote:<o:p></o:p></p></div></div><div><div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>Hi all,</span><o:p></o:p></p><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>I propose to change strictness for ST_Union, ST_Collect and ST_Difference to non-strict.</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>It's common in my workflows to have constructs like </span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>ST_Difference(a.geom, (select ST_Union(geom) from clipping_table b where ST_Intersects(a.geom, b.geom)))</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>Right now it has not evident side effect of geometries disappearing if ST_Union had no rows to collect. It surely can be fixed with coalesce, but you have to first know of such behavior with NULLs. :)<br><br>So, I propose non-strict handling of nulls:</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>ST_Union(A, NULL) => A,</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>ST_Union(NULL, A) => A,</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>ST_Union(NULL, NULL) => NULL,</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>ST_Collect(A, NULL) => A,</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>ST_Collect(NULL, A) => A,</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>ST_Collect(NULL, NULL) => NULL,</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>ST_Difference(A, NULL) = A,</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>ST_Difference(NULL, *) = NULL.</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:black'>any thoughts?</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div></div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></blockquote></div></div><div><div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><p class=MsoNormal style='margin-left:.5in'>_______________________________________________<br>postgis-devel mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><o:p></o:p></p></blockquote></div></div><p class=MsoNormal style='margin-left:.5in'>_______________________________________________<br>postgis-devel mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><o:p></o:p></p></blockquote></div></div></div></body></html>