| Bug #31237 | Test "ndb_views" fails because of differing order of select results | ||
|---|---|---|---|
| Submitted: | 27 Sep 2007 13:04 | Modified: | 14 May 2008 15:51 |
| Reporter: | Joerg Bruehe | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Tests: Cluster | Severity: | S3 (Non-critical) |
| Version: | 5.1.19, 6.0.4-alpha and up | OS: | Any |
| Assigned to: | Matthias Leich | CPU Architecture: | Any |
[28 Sep 2007 21:01]
Matthias Leich
Problem of many if not all tests: CREATE VIEW v1 <no full ordering of result set> LIMIT ...; The VIEW SELECT causes in case of NDB "random" result sets because NDB - partitions its tables over the nodes - runs parallel query Rough thinkable example with - two nodes - table content 1,2,3,4,5,6 --------------------------- CREATE VIEW v1 AS SELECT f1 FROM t1 LIMIT 2; Node 1 contains the rows 1,3,5 of table t1 Node 2 contains the rows 2,4,6 of table t1 Outcome variants: 1) Node 1 is fastest 1,3,5,2,4,6 LIMIT shrinks it to 1,3 2) Node 2 is fastest 2,4,6,1,3,5 LIMIT shrinks it to 2,4 I guess the number of different variants before application of LIMIT is about (#nodes)! = #nodes * (#nodes - 1) * (#nodes - 2) * .... * 1 SELECT * FROM v1 <whatever ORDER BY>; 1) 1,3 2) 2,4
[5 Oct 2007 13:34]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/34976 ChangeSet@1.2574, 2007-10-05 15:38:34+02:00, mleich@four.local.lan +5 -0 1. Fix for Bug#31237 Test "ndb_views" fails because of differing order of select results 2. Cleanup of test
[5 Oct 2007 18:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/35008 ChangeSet@1.2574, 2007-10-05 20:06:41+02:00, mleich@four.local.lan +5 -0 1. Fix for Bug#31237 Test "ndb_views" fails because of differing order of select results 2. Cleanup of test
[8 Oct 2007 13:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/35101 ChangeSet@1.2605, 2007-10-08 16:57:13+02:00, mleich@four.local.lan +5 -0 Post merge fix for
[8 Oct 2007 15:34]
Matthias Leich
There is no documentation needed. The bug fix affects MySQL 5.1 and 5.2.
[1 Nov 2007 10:14]
Bugs System
Pushed into 6.0.4-alpha
[1 Nov 2007 10:16]
Bugs System
Pushed into 5.1.23-rc
[3 Jan 2008 19:40]
Joerg Bruehe
The above fix is said to be pushed into 6.0.4, but in these builds the test still fails - it just affects different parts: (lines are truncated) @@ -1927,15 +1927,15 @@ f59 f60 f61 f62 f63 f64 f65 ... SELECT * FROM test.tb2 where f59 = 30 ; f59 f60 f61 f62 f63 f64 f65 ... -30 4 0000000004 0000000000000000000000000000000000000000000000000000000000000004 0000000004 000 30 74 NULL NULL NULL NULL NULL ... +30 4 0000000004 0000000000000000000000000000000000000000000000000000000000000004 0000000004 000 UPDATE tb2 SET f59 = 100 where f59 = 30 ; affected rows: 2 info: Rows matched: 2 Changed: 2 Warnings: 0 SELECT * FROM tb2 where f59 = 100 ; f59 f60 f61 f62 f63 f64 f65 ... -100 4 0000000004 0000000000000000000000000000000000000000000000000000000000000004 0000000004 000 100 74 NULL NULL NULL NULL NULL ... +100 4 0000000004 0000000000000000000000000000000000000000000000000000000000000004 0000000004 000 SELECT * FROM test.v1 order by f59 ; f59 f60 f61 f62 f63 f64 f65 ... drop view if exists test.v1 ; @@ -11098,7 +11098,7 @@ ALTER TABLE t1 CHANGE COLUMN f4 f4 CHAR(8); Warnings: Warning 1265 Data truncated for column 'f4' at row 2 -Warning 1265 Data truncated for column 'f4' at row 5 +Warning 1265 Data truncated for column 'f4' at row 4 INSERT INTO t1 SET f1 = 3, f4 = '<-- 10 -->', report = 't1 3'; Warnings: Warning 1265 Data truncated for column 'f4' at row 1 mysqltest: Result content mismatch
[3 Jan 2008 19:56]
Joerg Bruehe
And this here is the same problem, found both in 6.0.4 (now) and 5.1.19 and up (previously, not reported back then). funcs_1.ndb_trig_0102 [ fail ] --- /PATH/mysql-test/suite/funcs_1/r/ndb_trig_0102.result +++ /PATH/mysql-test/suite/funcs_1/r/ndb_trig_0102.reject @@ -121,8 +121,8 @@ set @test_after = 0; select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1'; f121 f122 f142 f144 f134 -Test 3.5.1.1 Second Row 2 0000000006 2 Test 3.5.1.1 Before Update Trigger 27 0000000008 1 +Test 3.5.1.1 Second Row 2 0000000006 2 select @test_before, @test_after; @test_before @test_after 0 0 mysqltest: Result content mismatch
[4 Jan 2008 10:17]
Joerg Bruehe
Yet another: --- /PATH/mysql-test/suite/funcs_1/r/ndb_trig_1011ext.result +++ /PATH/mysql-test/suite/funcs_1/r/ndb_trig_1011ext.reject @@ -108,8 +108,8 @@ Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000 select f121, f122, f151, f163 from vw11; f121 f122 f151 f163 -Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000 Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000 +Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000 set @test_var=0; Select @test_var as 'before delete'; before delete
[4 Jan 2008 13:00]
Matthias Leich
1. The patch pushed around 8 Oct 2007 fixed the
problems reported till that date and some
other non reported weaknesses around result
sorting in advance.
I also run this test several times and did
not found additional issues.
But as result sorting in case of NDB is
affected by load on testing engine, OS
scheduling etc. it is not unlikely that
some other weak points do not show up.
2. If we say the core of this bug
- are the problems reported till October
than the bug was fixed
- is it's title = any possible difference
in result set ordering even if it is
to that date unknown than the bug was
of course not complete fixed.
3. ndb_trig_0102, ndb_trig_1011ext are
different tests than the "ndb_view"
mentioned within the bug title.
4. WL#4084 Review and fix all disabled tests
in the "funcs_1" test suite
does a lot more than said within its title
and will hopefully fix the problems with
ndb_views, ndb_trig_0102, ndb_trig_1011ext.
[30 Apr 2008 16:52]
Matthias Leich
I made some stability tests with ndb_views and found a new instability: @@ -22497,7 +22497,7 @@ ALTER TABLE t1 CHANGE COLUMN f4 f4 CHAR(8); Warnings: Warning 1265 Data truncated for column 'f4' at row 2 -Warning 1265 Data truncated for column 'f4' at row 5 +Warning 1265 Data truncated for column 'f4' at row 4 INSERT INTO t1 SET f1 = 3, f4 = '<-- 10 -->', report = 't1 3'; Warnings: Warning 1265 Data truncated for column 'f4' at row 1
[5 May 2008 17:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/46362 ChangeSet@1.2607, 2008-05-05 19:49:17+02:00, mleich@five.local.lan +5 -0 Fix for the latest instability found within funcs_1: ndb_views Bug#31237 Test "ndb_views" fails because of differing order of select results
[5 May 2008 18:00]
Matthias Leich
Changeset was reviewed Horst Hunger. I got the approval via Chat.
[6 May 2008 8:43]
Matthias Leich
Pushed into mysql-5.1-bugteam mysql-6.0-bugream Weakness within tests, therefore no documentation is needed.
[14 May 2008 15:20]
Bugs System
Pushed into 5.1.25-rc
[14 May 2008 15:51]
Paul DuBois
Test suite changes. No changelog entry needed.
[22 May 2008 9:50]
Bugs System
Pushed into 6.0.6-alpha
Description: Found during the build of 5.1.22-rc. For reference, this is a part of "ndb_views.result": ===== 191 CREATE VIEW v1 AS select f59,f60,f61 192 FROM test.tb2 limit 100; 193 select * FROM v1 order by f59,f60,f61 limit 0,10; 194 f59 f60 f61 195 1 1 0000000001 196 2 2 0000000002 197 3 3 0000000003 198 4 4 0000000004 199 4 74 NULL 200 5 5 0000000005 201 6 6 0000000006 202 7 7 0000000007 203 8 8 0000000008 204 9 9 0000000009 205 CREATE or REPLACE VIEW v1 AS select f59,f60,f61 206 FROM test.tb2 limit 4,3; 207 select * FROM v1 order by f59,f60,f61 limit 0,10; 208 f59 f60 f61 209 10 10 0000000010 210 19 18 0000000014 211 24 51654 NULL 212 CREATE or REPLACE VIEW v1 AS select distinct f59 213 FROM test.tb2 limit 4,3; 214 select * FROM v1 order by f59 limit 0,10; 215 f59 216 15 217 107 218 209 219 ALTER VIEW v1 AS select f59 220 FROM test.tb2 limit 6,2; 221 select * FROM v1 order by f59 limit 0,10; 222 f59 223 10 224 34 225 CREATE or REPLACE VIEW v1 AS select f59 226 from tb2 order by f59 limit 100; 227 select * FROM v1 order by f59 limit 0,10; 228 f59 229 1 230 2 231 3 232 4 233 4 234 5 235 6 236 7 237 8 238 9 ===== Now, the starts of 4 sample difference reports: *************** *** 213,221 FROM test.tb2 limit 4,3; select * FROM v1 order by f59 limit 0,10; f59 ! 15 ! 107 ! 209 ALTER VIEW v1 AS select f59 FROM test.tb2 limit 6,2; select * FROM v1 order by f59 limit 0,10; --- 213,221 FROM test.tb2 limit 4,3; select * FROM v1 order by f59 limit 0,10; f59 ! 10 ! 19 ! 24 ALTER VIEW v1 AS select f59 FROM test.tb2 limit 6,2; select * FROM v1 order by f59 limit 0,10; *************** BSD 6.0 (x86) *************** *** 206,227 FROM test.tb2 limit 4,3; select * FROM v1 order by f59,f60,f61 limit 0,10; f59 f60 f61 ! 10 10 0000000010 ! 19 18 0000000014 ! 24 51654 NULL CREATE or REPLACE VIEW v1 AS select distinct f59 FROM test.tb2 limit 4,3; select * FROM v1 order by f59 limit 0,10; f59 ! 15 ! 107 ! 209 ALTER VIEW v1 AS select f59 FROM test.tb2 limit 6,2; select * FROM v1 order by f59 limit 0,10; f59 ! 10 ! 34 CREATE or REPLACE VIEW v1 AS select f59 from tb2 order by f59 limit 100; select * FROM v1 order by f59 limit 0,10; --- 206,227 FROM test.tb2 limit 4,3; select * FROM v1 order by f59,f60,f61 limit 0,10; f59 f60 f61 ! 207 205 0000000206 ! 209 208 0000000204 ! 500 NULL 0000000900 CREATE or REPLACE VIEW v1 AS select distinct f59 FROM test.tb2 limit 4,3; select * FROM v1 order by f59 limit 0,10; f59 ! 8 ! 441 ! 3330 ALTER VIEW v1 AS select f59 FROM test.tb2 limit 6,2; select * FROM v1 order by f59 limit 0,10; f59 ! 109 ! 207 CREATE or REPLACE VIEW v1 AS select f59 from tb2 order by f59 limit 100; select * FROM v1 order by f59 limit 0,10; *************** Solaris 9 SPARC (32 bit) *************** *** 206,227 FROM test.tb2 limit 4,3; select * FROM v1 order by f59,f60,f61 limit 0,10; f59 f60 f61 ! 10 10 0000000010 ! 19 18 0000000014 ! 24 51654 NULL CREATE or REPLACE VIEW v1 AS select distinct f59 FROM test.tb2 limit 4,3; select * FROM v1 order by f59 limit 0,10; f59 ! 15 ! 107 ! 209 ALTER VIEW v1 AS select f59 FROM test.tb2 limit 6,2; select * FROM v1 order by f59 limit 0,10; f59 ! 10 ! 34 CREATE or REPLACE VIEW v1 AS select f59 from tb2 order by f59 limit 100; select * FROM v1 order by f59 limit 0,10; --- 206,227 FROM test.tb2 limit 4,3; select * FROM v1 order by f59,f60,f61 limit 0,10; f59 f60 f61 ! 207 205 0000000206 ! 209 208 0000000204 ! 500 NULL 0000000900 CREATE or REPLACE VIEW v1 AS select distinct f59 FROM test.tb2 limit 4,3; select * FROM v1 order by f59 limit 0,10; f59 ! 8 ! 441 ! 3330 ALTER VIEW v1 AS select f59 FROM test.tb2 limit 6,2; select * FROM v1 order by f59 limit 0,10; f59 ! 8 ! 22 CREATE or REPLACE VIEW v1 AS select f59 from tb2 order by f59 limit 100; select * FROM v1 order by f59 limit 0,10; *************** HP-UX 11.11 HP-PA (32 bit) *************** *** 206,227 FROM test.tb2 limit 4,3; select * FROM v1 order by f59,f60,f61 limit 0,10; f59 f60 f61 ! 10 10 0000000010 ! 19 18 0000000014 ! 24 51654 NULL CREATE or REPLACE VIEW v1 AS select distinct f59 FROM test.tb2 limit 4,3; select * FROM v1 order by f59 limit 0,10; f59 ! 15 ! 107 ! 209 ALTER VIEW v1 AS select f59 FROM test.tb2 limit 6,2; select * FROM v1 order by f59 limit 0,10; f59 ! 10 ! 34 CREATE or REPLACE VIEW v1 AS select f59 from tb2 order by f59 limit 100; select * FROM v1 order by f59 limit 0,10; --- 206,227 FROM test.tb2 limit 4,3; select * FROM v1 order by f59,f60,f61 limit 0,10; f59 f60 f61 ! 8 8 0000000008 ! 441 16546 NULL ! 3330 764376 NULL CREATE or REPLACE VIEW v1 AS select distinct f59 FROM test.tb2 limit 4,3; select * FROM v1 order by f59 limit 0,10; f59 ! 8 ! 441 ! 3330 ALTER VIEW v1 AS select f59 FROM test.tb2 limit 6,2; select * FROM v1 order by f59 limit 0,10; f59 ! 109 ! 207 CREATE or REPLACE VIEW v1 AS select f59 from tb2 order by f59 limit 100; select * FROM v1 order by f59 limit 0,10; *************** Solaris 10 SPARC (32 bit) Other platforms show different results again, it seems wasteful to list them all here - but they are available in the archived build+test results: log/test_failures/funcs_1.ndb_views.* How to repeat: Run the "funcs_1" suite. Suggested fix: 1) Do a "select * from test.tb2" before the creation of the views, to ensure / document the base table contains exactly the expected data. 2) I do not understand why a CREATE VIEW v1 AS select <<list>> FROM <<table>> limit 4,3; is expected to contain a specific set of data (assuming the table has more than 7 rows, which it does), so why should the result of select * FROM v1 order by <<list>> ... be some well-defined result ? IMO, it is not specified which 3 rows of the base table belong to the view.