Bug #31207 Test "join_nested" shows different strategy on IA64 CPUs / Intel's ICC compiler
Submitted: 26 Sep 2007 14:26 Modified: 20 Dec 2007 0:51
Reporter: Joerg Bruehe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.22-rc OS:Any (IA64 / ICC only)
Assigned to: Alexey Kopytov CPU Architecture:Any

[26 Sep 2007 14:26] Joerg Bruehe
Description:
Found in the release builds of 5.1.22-rc,
but only on machines with the IA64 CPU and using the ICC compiler:

-------------------------------------------------------
*** /PATH/mysql-test/r/join_nested.result
--- /PATH/mysql-test/r/join_nested.reject
***************
*** 546,553
  1     SIMPLE  t0      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
  1     SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer
  1     SIMPLE  t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
- 1     SIMPLE  t3      ALL     NULL    NULL    NULL    NULL    2       100.00
  1     SIMPLE  t4      ALL     NULL    NULL    NULL    NULL    2       100.00
  1     SIMPLE  t5      ALL     NULL    NULL    NULL    NULL    3       100.00
  1     SIMPLE  t7      ALL     NULL    NULL    NULL    NULL    2       100.00
  1     SIMPLE  t6      ALL     NULL    NULL    NULL    NULL    3       100.00
--- 546,553
  1     SIMPLE  t0      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
  1     SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer
  1     SIMPLE  t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
  1     SIMPLE  t4      ALL     NULL    NULL    NULL    NULL    2       100.00
+ 1     SIMPLE  t3      ALL     NULL    NULL    NULL    NULL    2       100.00
  1     SIMPLE  t5      ALL     NULL    NULL    NULL    NULL    3       100.00
  1     SIMPLE  t7      ALL     NULL    NULL    NULL    NULL    2       100.00
  1     SIMPLE  t6      ALL     NULL    NULL    NULL    NULL    3       100.00
***************
*** 805,816
  WHERE t1.a <= 2;
  a     b       a       b       a       b       a       b
  1     3       3       3       NULL    NULL    NULL    NULL
- 2     2       3       3       NULL    NULL    NULL    NULL
  1     3       4       2       1       2       3       2
  1     3       4       2       1       2       4       2
  2     2       4       2       1       2       3       2
  2     2       4       2       1       2       4       2
- 1     3       5       3       NULL    NULL    NULL    NULL
  2     2       5       3       NULL    NULL    NULL    NULL
  SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t1, (t3, t4)
--- 805,816
  WHERE t1.a <= 2;
  a     b       a       b       a       b       a       b
  1     3       3       3       NULL    NULL    NULL    NULL
  1     3       4       2       1       2       3       2
  1     3       4       2       1       2       4       2
+ 1     3       5       3       NULL    NULL    NULL    NULL
+ 2     2       3       3       NULL    NULL    NULL    NULL
  2     2       4       2       1       2       3       2
  2     2       4       2       1       2       4       2
  2     2       5       3       NULL    NULL    NULL    NULL
  SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t1, (t3, t4)
***************
*** 820,831
  WHERE t1.a <= 2;
  a     b       a       b       a       b       a       b
  1     3       3       3       NULL    NULL    NULL    NULL
- 2     2       3       3       NULL    NULL    NULL    NULL
  1     3       4       2       1       2       3       2
  1     3       4       2       1       2       4       2
  2     2       4       2       1       2       3       2
  2     2       4       2       1       2       4       2
- 1     3       5       3       NULL    NULL    NULL    NULL
  2     2       5       3       NULL    NULL    NULL    NULL
  EXPLAIN EXTENDED
  SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
--- 820,831
  WHERE t1.a <= 2;
  a     b       a       b       a       b       a       b
  1     3       3       3       NULL    NULL    NULL    NULL
  1     3       4       2       1       2       3       2
  1     3       4       2       1       2       4       2
+ 1     3       5       3       NULL    NULL    NULL    NULL
+ 2     2       3       3       NULL    NULL    NULL    NULL
  2     2       4       2       1       2       3       2
  2     2       4       2       1       2       4       2
  2     2       5       3       NULL    NULL    NULL    NULL
  EXPLAIN EXTENDED
  SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
***************
*** 835,844
  ON t3.a=1 AND t2.b=t4.b
  WHERE t1.a <= 2;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
! 1     SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where
! 1     SIMPLE  t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using join buffer
  1     SIMPLE  t3      ALL     NULL    NULL    NULL    NULL    2       100.00
  1     SIMPLE  t4      ALL     NULL    NULL    NULL    NULL    2       100.00
  Warnings:
  Note  1003    select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`
t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left
join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) where (`test`.`t1`.
`a` <= 2)
  CREATE INDEX idx_b ON t2(b);
--- 835,844
  ON t3.a=1 AND t2.b=t4.b
  WHERE t1.a <= 2;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
! 1     SIMPLE  t2      ALL     NULL    NULL    NULL    NULL    3       100.00
  1     SIMPLE  t3      ALL     NULL    NULL    NULL    NULL    2       100.00
  1     SIMPLE  t4      ALL     NULL    NULL    NULL    NULL    2       100.00
+ 1     SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer
  Warnings:
  Note  1003    select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`
t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left
join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) where (`test`.`t1`.
`a` <= 2)
  CREATE INDEX idx_b ON t2(b);
-------------------------------------------------------

How to repeat:
Run the test using an ICC/IA64 server.
[15 Oct 2007 12:54] Alexey Kopytov
This bug is a combination of two problems:

1. IA64/ICC MySQL binaries use glibc's qsort(), not the one in mysys/ for some reason. This needs to be investigated further, since the problem occurs only on binaries compiled with icc version 10, downgrading the compiler to version 9 solves it. Perhaps the 'weak' symbol attribute is disregarded on this combination of platform/toolchain.

2. The order relation implemented by join_tab_cmp() is not transitive, i.e. it is possible to choose a, b and c such that (a < b) && (b < c) but (c < a). This implies that result of a sort using the relation implemented by join_tab_cmp() depends on the order in which elements are compared, i.e. the result is implementation-specific.

Regarding the failing join_nested test case, choose_plan() uses qsort() to pre-sort the join tables using join_tab_cmp() as a compare function. However, since the glibc'c version of qsort() is used, the results of the sorting are different from what would be returned by the mysys version of qsort().
[15 Oct 2007 12:55] Alexey Kopytov
See also bug #20530.
[17 Oct 2007 15:49] Sergey Petrunya
Ok to push after the adding appropriate comment to join_tab_cmp(), as discussed on  irc.
[17 Oct 2007 16:09] 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/35763

ChangeSet@1.2527, 2007-10-17 20:08:58+04:00, kaa@polly.(none) +21 -0
  Fix for bug #31207: Test "join_nested" shows different strategy on IA64
  CPUs / Intel's ICC compile
  
  The bug is a combination of two problems:
  
  1. IA64/ICC MySQL binaries use glibc's qsort(), not the one in mysys.
  
  2. The order relation implemented by join_tab_cmp() is not transitive,
  i.e. it is possible to choose such a, b and c that (a < b) && (b < c)
  but (c < a). This implies that result of a sort using the relation
  implemented by join_tab_cmp() depends on the order in which
  elements are compared, i.e. the result is implementation-specific. Since
  choose_plan() uses qsort() to pre-sort the
  join tables using join_tab_cmp() as a compare function, the results of
  the sorting may vary depending on qsort() implementation.
  
  It is neither possible nor important to implement a better ordering
  algorithm in join_tab_cmp(). Therefore the only way to fix it is to
  force our own qsort() to be used by renaming it to my_qsort(), so we don't depend
  on linker to decide that.
  
  This patch also "fixes" bug #20530: qsort redefinition violates the
  standard.
[18 Oct 2007 11:17] 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/35832

ChangeSet@1.2588, 2007-10-18 15:17:21+04:00, kaa@polly.(none) +3 -0
  Post-merge changes for bug #31207: Test "join_nested" shows different strategy on IA64 CPUs / Intel's ICC compiler.
[18 Oct 2007 11:35] 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/35833

ChangeSet@1.2618, 2007-10-18 15:34:46+04:00, kaa@polly.(none) +1 -0
  Post-merge fixes for bug #31207: Test "join_nested" shows different strategy on IA64 CPUs / Intel's ICC compiler.
[22 Oct 2007 18:36] 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/36072

ChangeSet@1.2543, 2007-10-22 22:35:08+04:00, kaa@polly.(none) +1 -0
  Fixed the Windows build failures introduced by the patch for bug #31207: Test "join_nested" shows different strategy on IA64 CPUs / Intel's ICC compiler.
[7 Dec 2007 23:07] Bugs System
Pushed into 6.0.5-alpha
[7 Dec 2007 23:09] Bugs System
Pushed into 5.1.23-rc
[7 Dec 2007 23:10] Bugs System
Pushed into 5.0.54
[20 Dec 2007 0:51] Paul Dubois
Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

The mysys library had a qsort() function, causing conflicts between
its use and the qsort() in glibc. Resolved by renaming the mysys
version to my_qsort().