search

sagarswathi / h2database

Automatically exported from code.google.com/p/h2database
0 stars 1 forks source link

select returns data, which doesn't exists #381

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago 
What steps will reproduce the problem?
1. connect to attached db (user sa without password)

2. execute:
SELECT * FROM CARD_1000101
WHERE question like '%Foto%' 
AND cardid = 1000354

3. Look what you see in column cardid. I see there '934818', but not 1000354, 
like I would expect.

Trouble is in fact, that SELECT * FROM CARD_1000101 returns also row with 
cardid = 934818, while
SELECT * FROM CARD_1000101 WHERE cardid = 934818
returns nothing.

It looks like in reality this row should have cardid = 1000354, but it is not 
returned by SELECT * FROM CARD_1000101 .

Original issue reported on code.google.com by dobr...@gmail.com on 24 Feb 2012 at 3:41

GoogleCodeExporter commented 8 years ago 
It looks like the index on cardid is broken. The query uses this index, and 
returns the wrong row. I can't say how this happened unfortunately... To fix 
the problem, you would need to backup the database to a SQL script file (using 
SCRIPT TO or the Script tool), and then re-create it (using RUNSCRIPT or the 
RunScript tool).

The database was create using H2 version 1.3.153 from 2011-03-14. Looking at 
the change log at http://www.h2database.com/html/changelog.html , a known 
problem that could have caused it was fixed in version 1.3.163 (2011-12-30): 
"On out of disk space, the database could get corrupt sometimes, if later write 
operations succeeded. The same problem happened on other kinds of I/O 
exceptions (where one or some of the writes fail, but subsequent writes 
succeed). Now the file is closed on the first unsuccessful write operation, so 
that later requests fail consistently." - but I don't know if this occurred in 
your case.

Original comment by thomas.t...@gmail.com on 7 Mar 2012 at 7:43

GoogleCodeExporter commented 8 years ago 
Thanks for pointing this out,Thomas. We'll go with a more recent version to 
avoid this problem.

Original comment by alex.par...@gmail.com on 8 Mar 2012 at 3:43

GoogleCodeExporter commented 8 years ago

Original comment by thomas.t...@gmail.com on 18 Mar 2012 at 5:40