Hello,
I have a class with a "Unique" index (pxfactidIndex) on a %Numeric property (pxfactid) (partially-edit code snippet below):
Property pxfactid As %Library.Numeric(MAXVAL = 9223372036854775807, MINVAL = -9223372036854775808, SCALE = 0) [ SqlColumnNumber = 7 ]
Index pxfactidIndex On pxfactid [ Unique ]
Storage Default
{
<Data name="FactDefaultData">
<Value name="1">
<Value>pysubjectid</Value>
</Value>
...
<Value name="6">
<Value>pxfactid</Value>
</Value>
...
</Data>
<DataLocation>^CRMBI.FactD</DataLocation>
<DefaultData>FactDefaultData</DefaultData>
<ExtentLocation>^CRMBI.Fact</ExtentLocation>
<ExtentSize>3521840</ExtentSize>
<IdFunction>sequence</IdFunction>
<IdLocation>^CRMBI.FactD</IdLocation>
<Index name="DDLBEIndex">
<Location>^CRMBI.FactI.2</Location>
</Index>
<Index name="IDKEY">
<Location>^CRMBI.FactD</Location>
</Index>
<Index name="pxfactidIndex">
<Location>^CRMBI.Fact.4</Location>
</Index>
...
<IndexLocation>^CRMBI.FactI</IndexLocation>
<Property name="%%ID">
<AverageFieldSize>4.99</AverageFieldSize>
<Histogram>$lb(.06666666666666666667,1,0,$lb(27673,228574,456707,618301,892202,1107091,1405679,1604163,1738513,2015749,2270724,2530345,2830827,3047237,3296375,3504324),$lb(1,0,0,0,0,1,1,1,0,1,1,1,0,1,1),$lb(842479159,926299955,842544439,842151989,875902519,875902519,909195315,909195315,943272498,943272498,825307191,825243440,875574582,875574582,909128753,909128753,926103605,825701176,842019125,808531255,842477623,842477623,892547123,892547123,942878776,842543920,858797111,808728370,842610227,842610227,892351539,859123764))</Histogram>
<Selectivity>1</Selectivity>
</Property>
<Property name="pxfactid">
<AverageFieldSize>9.99</AverageFieldSize>
<Histogram>$lb(.06666666666666666667,1,0,$lb(-8377896048695389137,-7487164305435710908,-6406061662044370629,-5112561079609519767,-4588722295094185245,-4512887624516713113,-3293381287254976544,-1753938466698510428,-1561375857877981192,-211989232600898713,2857966673366783778,4457490938253726327,5246678144060189771,6591622321393794159,8944949574919674333,8944949574921469469),$lb(1,1,1,1,3,1,1,2,1,0,0,0,0,0,11),$lb(758657847,942880567,926169143,926169143,909389878,909389878,892416306,892416306,875903032,943208242,825374776,875901234,858929459,858929459,825701683,926233401,892743987,825570865,842084665,758264113,842544439,842544439,875836727,875836727,892482614,892482614,909457713,909457713,943273012,825833015,842085430,943273012))</Histogram>
<Selectivity>0.0000283942484610317%</Selectivity>
</Property>
I see the following odd behavior when counting how many records have that property set to null:
- The first query (17) returns the number of records in the table. The 2nd query (18) returns what I expect: all records have non-null values for that "pxfactid" property. However, when I run the 3rd query (19) which counts all records with non-null values in that field, I get a much smaller, unexpected, number.
.png)
- The 4th query (20) counts the number of records in a subquery that returns all non-null values for that property in the table and returns the expected number that matches the total number of records in the table.
.png)
Does anyone have any idea of what type of coding error would produce the odd behavior seen in query 19 (wrong number of records with non-null values in that indexed property)?
Note that the values in that field have a large range; here's a sample of some of them:
.png)
Thanks
.png)