Bug #50330 sys_vars.sql_select_limit_basic fails on 32 bit OS without big tables support
Submitted: 14 Jan 2010 13:47 Modified: 15 Jan 2010 11:08
Reporter: Olav Sandstå Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.5.99-m3 (Celosia) OS:Any (32 bit)
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[14 Jan 2010 13:47] Olav Sandstå
Description:
When running the MTR test sys_vars.sql_select_limit_basic using a binary built for Solaris 32 bit with the Sun Studio compiler it fails with the following diff:

--- /home/os136802/mysql/develop/repo/bug50221/mysql-test/suite/sys_vars/r/sql_select_limit_basic.result        Tue Jan 12 20:14:19 2010
+++ /home/os136802/mysql/develop/repo/bug50221/mysql-test/suite/sys_vars/r/sql_select_limit_basic.reject        Thu Jan 14 16:38:46 2010
@@ -1,25 +1,25 @@
 SET @start_global_value = @@global.sql_select_limit;
 SELECT @start_global_value;
 @start_global_value
-18446744073709551615
+4294967295
 select @@global.sql_select_limit;
 @@global.sql_select_limit
-18446744073709551615
+4294967295
 select @@session.sql_select_limit;
 @@session.sql_select_limit
-18446744073709551615
+4294967295
 show global variables like 'sql_select_limit';
 Variable_name  Value
-sql_select_limit       18446744073709551615
+sql_select_limit       4294967295
 show session variables like 'sql_select_limit';
 Variable_name  Value
-sql_select_limit       18446744073709551615
+sql_select_limit       4294967295
 select * from information_schema.global_variables where variable_name='sql_select_limit';
 VARIABLE_NAME  VARIABLE_VALUE
-SQL_SELECT_LIMIT       18446744073709551615
+SQL_SELECT_LIMIT       4294967295
 select * from information_schema.session_variables where variable_name='sql_select_limit';
 VARIABLE_NAME  VARIABLE_VALUE
-SQL_SELECT_LIMIT       18446744073709551615
+SQL_SELECT_LIMIT       4294967295
 set global sql_select_limit=10;
 select @@global.sql_select_limit;
 @@global.sql_select_limit
@@ -37,4 +37,4 @@
 SET @@global.sql_select_limit = @start_global_value;
 SELECT @@global.sql_select_limit;
 @@global.sql_select_limit
-18446744073709551615
+4294967295

How to repeat:
1. Use the latest source version from mysql-next-mr-bugfixing

2. Compile it like this (or something similar) using Sun Studio:

   ./BUILD/autorun.sh
   ./configure CC=/opt/studio12/SUNWspro/bin/cc CXX=/opt/studio12/SUNWspro/bin/CC CFLAGS="-Xa -m32 -mt -g" CXXFLAGS="-m32 -mt -g" --prefix=/home/os136802/install/mysql-next-mr --with-client-ldflags=-static --with-plugins=myisam,innobase --enable-dtrace --with-debug
    make

3. Run the test:

     cd mysql-test
     ./mysql-test-run.pl sys_vars.sql_select_limit_basic
[14 Jan 2010 13:48] Olav Sandstå
Similar failures are also seen when running:

  sys_vars.max_join_size_basic
  sys_vars.sql_max_join_size_basic
[15 Jan 2010 8:17] John Embretsen
Some other failures in the sys_vars suite on other platforms are mentioned in Bug#50331 ("Four tests in sys_vars failed on Solaris and Windows.").
[15 Jan 2010 11:08] Sveta Smirnova
Thank you for the report.

Verified as described.
[22 Jan 2010 8:54] Olav Sandstå
Note that if I instead of the configure command given above use the following command to configure a 32 bit binary the test passes:

./configure CC=/opt/studio12/SUNWspro/bin/cc CXX=/opt/studio12/SUNWspro/bin/CC CFLAGS="-g -O2 -Xa -xstrconst -mt -D_FORTEC_ -m32" CXXFLAGS="-g0 -O2 -noex -mt -D_FORTEC_ -m32" --enable-thread-safe-client --enable-local-infile --with-pic --with-client-ldflags=-static --with-mysqld-ldflags=-static --with-zlib-dir=bundled --without-ndb-debug --with-big-tables --with-ssl --with-readline --with-embedded-server --with-archive-storage-engine --with-blackhole-storage-engine --with-csv-storage-engine --with-example-storage-engine --with-federated-storage-engine --with-partition --with-extra-charsets=all --with-innodb --with-ndbcluster --with-libevent
[22 Jan 2010 9:44] Olav Sandstå
If I add the option --with-big-tables to the configure command then the test runs successfully.
[22 Jan 2010 10:06] Olav Sandstå
The documentation for sql_select_limit ( http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html ) says the following:

  "The default value for a new connection is the maximum number of rows that the server allows per table, which depends on the server configuration and may be affected if the server build was configured with --with-big-tables. Typical default values are (2^32)–1 or (2^64)–1."

The result file for this test assumes always the last one.
[29 Sep 2010 10:41] Olav Sandstå
Some ideas a fix:

-the test should be able to check if the server was built with support for "big tables" and only be run if that is the case. 

-the test should accept both (2^32 -1) and (2^64 -1) as correct values

An alternative for solving this is also:

-"depricate" the support for building the server without support for "big tables" (ie. remove the --with-big-tables" option from configure and let --with-big-tables be the default.
[29 Sep 2010 10:43] Olav Sandstå
One observation:

It seems like for autotools/configure the default option is to build the mysqld without the "--with-big-tables" support. When using cmake the default is to build the server "--with-big-tables" support (and I do not even know how to build it without big-tables support using cmake).
[29 Sep 2010 17:05] Olav Sandstå
The same test failure occurs when running the test on 32 bit Linux with a mysqld compiled without support for "big tables".

Howto repeat:

 1. Check out latest source from mysql-trunk-bugfixing

 2. Do the following (ensure you do not use cmake): 

    ./BUILD/autorun.sh
    ./configure
    make
    cd mysql-test
    ./mtr sql_select_limit_basic