[
https://issues.apache.org/jira/browse/DERBY-4032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12668064#action_12668064
]
Chip Hartney commented on DERBY-4032:
-------------------------------------
Here are the DDLs for the tables in question....
CREATE TABLE APP."Product" (
"Id" INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1),
"Num" VARCHAR(16) NOT NULL CONSTRAINT "Product_AK_Num" UNIQUE,
"Category" VARCHAR(4) NOT NULL,
"UnitPrice" DOUBLE NOT NULL,
"PackSize" INTEGER,
"SizeSetNo" INTEGER CONSTRAINT "Product_FK_SizeSetNo" REFERENCES
APP."SizeSet" ("No") ON DELETE RESTRICT,
"SeasonId" INTEGER CONSTRAINT "Product_FK_SeasonId" REFERENCES
APP."Season" ("Id") ON DELETE RESTRICT,
"ReplProdId" INTEGER CONSTRAINT "Product_FK_ReplacementProductId"
REFERENCES APP."Product" ("Id") ON DELETE RESTRICT,
"Descr" VARCHAR(30) NOT NULL,
"FOB" VARCHAR(20),
"IsHangingFlag" SMALLINT NOT NULL DEFAULT 0,
"IsCustSpecFlag" SMALLINT NOT NULL DEFAULT 0
)
CREATE TABLE TEMP."ZJVINV2" (
"STYLE" CHAR(16) NOT NULL,
"COLOR-SW" SMALLINT NOT NULL,
"SIZE-SET" INTEGER NOT NULL,
"CLASS" CHAR(4) NOT NULL,
"DESCRIPTION" CHAR(30) NOT NULL,
"CASE-QTY" INTEGER NOT NULL,
"P2" DOUBLE NOT NULL,
"RED-PRICE" DOUBLE NOT NULL,
"SEASON" INTEGER NOT NULL,
"MIN-QTY" INTEGER NOT NULL,
"MULTIPLIER" INTEGER NOT NULL,
"SUBST" CHAR(16) NOT NULL
)
When I reload the DB with the original data, the error goes away. The error is
reproducible in the current DB (without reloading), so I conclude that the DB
came to be in a bad state through usage. I do not know what usage caused it to
come to be in that state nor what that state is. But the problem cannot be
reproduced through a simple script which creates and loads the tables.
> Record not found in some SQL
> ----------------------------
>
> Key: DERBY-4032
> URL: https://issues.apache.org/jira/browse/DERBY-4032
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.2.1
> Environment: Windows XP
> Reporter: Chip Hartney
>
> Per discussion at
> http://www.nabble.com/Record-not-found-in-some-SQL---Bug--td21700110.html...
> I have a "Product" table with a "Num" column that contains a record that is
> only accessible by some SQL and not others. I have tested this by JDBC
> access from my Java app as well was from IJ directly.
> ij> select "Num", length("Num") as "Len" from app."Product" where "Num" like
> 'HG1549%';
> Num |Len
> ----------------------------
> HG15490 |7
> HG15493 |7
> HG15497 |7 <== Found as expected
> HG15499 |7
> 4 rows selected
> ij> select "Num" from app."Product" where "Num" = 'HG15490';
> Num
> ----------------
> HG15490 <== Found as expected
> 1 row selected
> ij> select "Num" from app."Product" where "Num" = 'HG15493';
> Num
> ----------------
> HG15493 <== Found as expected
> 1 row selected
> ij> select "Num" from app."Product" where "Num" = 'HG15499';
> Num
> ----------------
> HG15499 <== Found as expected
> 1 row selected
> ij> select "Num" from app."Product" where "Num" = 'HG15497';
> Num
> ----------------
> 0 rows selected <== Not found!!!
> What could possibly hide the 'HG15497' record from the last SELECT?
> And it's not just a matter of equality versus inequality...as the following
> SQL does return the record:
> SELECT I."STYLE" FROM TEMP."ZJVINV2" AS I INNER JOIN APP."Product" AS P
> ON I."STYLE" = P."Num" WHERE I."STYLE" = 'HG15497';
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
