Bugs item #2783425, was opened at 2009-04-29 08:33
Message generated for change (Comment added) made by mlkersten
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2783425&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: SQL CVS Head
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Stefan de Konink (skinkie)
Assigned to: Niels Nes (nielsnes)
Summary: Invalid results for primary key check
Initial Comment:
CREATE TABLE "sys"."way_nds" (
"way" int,
"idx" int,
"to_node" int
);
[copy into]
sql>ALTER TABLE way_nds ADD CONSTRAINT pk_way_nds PRIMARY KEY (way, idx);
!SQLException:assert:UPDATE: PRIMARY KEY constraint 'way_nds.pk_way_nds'
violated
0 tuples
sql>select count(way), way, idx from way_nds group by way, idx having
count(way) > 1;
0 tuples
The above error seems to be impossible since the second query is a check for it.
----------------------------------------------------------------------
>Comment By: Martin Kersten (mlkersten)
Date: 2009-06-07 21:46
Message:
As the new test illustrates, it at least uncovers an error in counting
groups with NULLs.
NULLs are allowed here, because there is no enforced KEY constraint on the
table.
The table may have contained nulls that have been produced 'unnoticed' by
the table loader.
It may also be a partial correct administration of the failing ALTER,....
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2009-06-07 21:20
Message:
In the way_nds table I know almost beyond reasonable doubt that that table
doesn't contain null values; because that file is generated and should have
influenced the primary keys of the way table and node table...
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-06-07 20:09
Message:
ps: you find the respective test in
sql/src/test/BugTracker-2009/Tests/primekeyconstraint.SF-2783425.*
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-06-07 20:07
Message:
Aha --- the key seems to be in the info that was lacking in the original
bug report: the data contains NULL values!
I cannot recall it exactly from my head but we'd need to check in the SQL
semantics for both key constraints and (grouped) counts;
I assume, the former does not allow NULLs --- in that case the error
message would be to be expected;
while the latter does ignore / not distinguish NULLs --- in that case the
result of the second query would be corrent, too.
In other words, I'm not sure that the given query is indeed a proper check
for the primary key constraint, given SQL semantics ...
----------------------------------------------------------------------
Comment By: Martin Kersten (mlkersten)
Date: 2009-06-07 08:41
Message:
A test has been added to the test suite.
It indicates that counting compound groups with NULLs leads to problems.
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2009-05-13 22:24
Message:
I am using 32bit oids, on a 64bit compilation.
CREATE TABLE way_nds (way integer, idx integer, to_node integer);
COPY 370896230 RECORDS INTO way_nds from
'/export/data1/konink/way_nds.csv' USING DELIMITERS ',', '\n', '''';
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2783425&group_id=56967
------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
