Please use the Apache issue tracking system for new NetBeans issues ( !!
Bug 157022 - Update statements only affect the first 21 rows
Update statements only affect the first 21 rows
Product: db
Classification: Unclassified
Component: Show Data
PC Windows XP
: P2 with 1 vote (vote)
: 6.x
Assigned To: Jiri Rechtacek
: 156835 166554 (view as bug list)
Depends on:
  Show dependency treegraph
Reported: 2009-01-19 05:31 UTC by grimlock81
Modified: 2009-07-20 15:58 UTC (History)
5 users (show)

See Also:
Issue Type: DEFECT


Note You need to log in before you can comment on or make changes to this bug.
Description grimlock81 2009-01-19 05:31:27 UTC
Product Version: NetBeans IDE 6.5 (Build 200811100001)
Java: 1.6.0_11; Java HotSpot(TM) Client VM 11.0-b16
System: Windows XP version 5.1 running on x86; Cp1252; en_AU (nb)

The execution of an update statement will only affect the first 21 rows of the table, even if there are more than 21
rows in the table that should be affected. This is related to the default page size, which has a default value of 20.

Test case performed against a SQL Server 2005 instance.

-- Start Netbeans IDE
-- Connect to the database
-- Choose Execute Command... from the popup menu.
-- Execute the following statements
--Create a new table
create table test_page_size (
    id int

--Insert 32 rows. Run this statement 32 times
insert into test_page_size values (1)

--Update all rows. Output is (21 rows affected)
update test_page_size set id = 999

--Select all rows - verify that only first 21 rows have been updated to 999 while the remaining rows still have a value
of 1.
select * from test_page_size

-- The expected behaviour is that all the rows should have been updated to 999
Comment 1 David Vancouvering 2009-01-20 19:09:13 UTC
I am pretty sure this has to do with page size settings used by the data view (note the default is to have a page size
of 20).  Ahi, can you investigate please?
Comment 2 Jiri Rechtacek 2009-02-11 10:45:33 UTC
Fixed in NB7.0, now it works for me in NetBeans IDE Dev (Build 090205)
Comment 3 Jiri Rechtacek 2009-02-24 14:15:42 UTC
*** Issue 156835 has been marked as a duplicate of this issue. ***
Comment 4 grimlock81 2009-06-09 07:03:46 UTC
Ran NB 6.7 RC 2 and this issue is still unresolved.
Comment 5 Petr Blaha 2009-06-09 08:36:26 UTC
I can't reproduce the bug the the RC2 build with MySQL server and embedded MySQL JDBC driver that is in IDE. It seems
that it's related to SQL Server 2005 only because this bug and also issue #156835 are reproducible on this database
server. MS SQL server isn't supported database in NB 6.7, see the document I'm closing the bug as WONTFIX.
Comment 6 grimlock81 2009-06-09 09:04:16 UTC
Okay that's understandable but the original bug was reported against 6.5 and SQL Server 2005 is listed under
Comment 7 Petr Blaha 2009-06-09 15:06:51 UTC
Percentage of the projects with MS SQL database in NetBeans is 7%
( Therefore, I'm reopening the bug and downgrading
to P3 for evaluation. 
Comment 8 Marian Mirilovic 2009-06-09 15:08:54 UTC
Comment 9 Jiri Rechtacek 2009-06-16 14:16:50 UTC
*** Issue 166554 has been marked as a duplicate of this issue. ***
Comment 10 Jiri Rechtacek 2009-06-16 14:19:29 UTC
Confirmed the problem is not reproducible with JavaDB neither with MySQL.
Comment 11 nleck 2009-06-16 14:23:52 UTC
no one uses JavaDB and most enterprise projects don't use MySQL.

How about *real* databases like Oracle, Sybase or MSSQL ? 

This is a really nasty bug as there was no indication that something was wrong. 
Comment 12 Marian Mirilovic 2009-06-16 14:30:33 UTC
Agreed with nleck - would be nice to fix that for NB 6.7 Patch 1
Comment 13 Jiri Rechtacek 2009-06-16 15:42:05 UTC
nleck, we spoke about DB engines bundles with NetBeans IDE. If you think they're fake db, it's your opinion. Anyway,
NetBeans is open source, nobody will obstruct you to fix it yourself and provide the patch back in NetBeans sources.
Otherwise, it might be welcome. On the other hand, any offensive comments won't be welcome never.
Comment 14 nleck 2009-06-16 23:50:12 UTC
I mean you didn't test on the largest databases by market share ie. Oracle, Sybase and MSSQL. 

Since these three are the biggest by market share it is most likely that is what people will be using. We use Oracle,
postgres and Sybase.

This bug caused me to lose ( well had to get back from backup) a fair amount of data. There was no indication that the
statements had failed and when selecting back before commit I could see data ( first page of). 

I'm sorry if these comments were offensive but I think this is the same problem we have been having in #161367 & #166714
the IDE hasn't been tested on large projects or in this case the largest databases. 

This problem exists on Sybase, Postgres and from comment below MSSQL as well. I haven't tested other database.

Sample SQL is :-

     SELECT * 
     INTO zz_person
     FROM person

In the table person there is thousands of records. The resulting table zz_person there is only 20

If not fixed then it should be noted in "known limitations" 
Comment 15 Jiri Skrivanek 2009-06-17 11:24:05 UTC
I tried Oracle 10 and it works without problems.
Comment 16 nleck 2009-06-17 11:46:08 UTC
cool... that means the only know ones are Postgres, Sybase and MSSQL. 

I just tested HSQLDB and that works also.
Comment 17 Petr Blaha 2009-06-17 12:35:32 UTC
Please, stop the sarcasm that doesn't help us. We will need to install MSSQL/Sybase and debug the issue now and include
the fix in patch1. Jirko, would you add the debugging messages that would bring the light what would be issue?
Comment 18 nleck 2009-06-17 12:44:08 UTC
You're miss reading my comments. I think this is a serious issue but I was *not* being sarcastic. 

I thought that was valuable information. From the comments below we have tested this on Oracle, Postgres, Sybase, MSSQL,
JavaDB, HSQLDB and MYSQL. Of these Postgres, Sybase, MSSQL is having trouble the others work fine.

Comment 19 Jiri Rechtacek 2009-06-18 17:39:49 UTC
The problem was reproduced with MSSQL server. Now I'm going to investigate what goes wrong...
Comment 20 Jiri Rechtacek 2009-06-25 15:57:42 UTC
The problem is not reproducible with MSSQL/jtds-1.2.2 outside NetBeans IDE. It's definitely a problem in DB tools in IDE. 
Comment 21 Jiri Rechtacek 2009-06-29 14:47:31 UTC
Comment 22 Quality Engineering 2009-06-30 05:43:54 UTC
Integrated into 'main-golden', will be available in build *200906300200* on (upload may still be in progress)
User: Jiri Rechtacek <>
Log: #157022: Update statements only affect the first 21 rows
Comment 23 Jaroslav Pospisil 2009-07-02 15:51:08 UTC
Comment 24 pgebauer 2009-07-03 16:18:02 UTC
The fix has been ported into the release67_fixes repository.
Comment 25 nleck 2009-07-20 06:17:39 UTC
Just tested 6.7.1rc on a PostgreSQL database.

The following SQL only inserted 20 rows from a table of ~70 rows.

     select * into zz_dns from aspc_dns
Comment 26 Jiri Rechtacek 2009-07-20 09:33:51 UTC
Fixed at least in NB6.8. I don't know more about build schedule for NB6.7.1
Comment 27 nleck 2009-07-20 10:20:21 UTC
The whiteboard status says 6.7 patch 1 

Jiri Kovalsky is check the port to the 6.7.1 branch

Comment 28 Petr Blaha 2009-07-20 13:33:48 UTC
The original issue with update/insert statements is already resolved in NB 6.7.1 but the case that was brought by nleck
above is different bug with same symptom. Tracking this as issue #168812.

By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2014, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo