innodb_stats_method=nulls_ignored works incorrectly

Bug #892405 reported by Oleg Tsarev on 2011-11-19
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Invalid
Low
Unassigned
5.1
Invalid
Low
Unassigned
5.5
Invalid
Low
Unassigned
Rnt-5.1
Fix Released
Undecided
Unassigned

Bug Description

MySQL dev-team implemented option innodb_stats_method incorrectly.
On nulls_ignored value we have incorrect statistics.
Test-cases in related branches:
oleg.tsarev (0) /storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0-release/mysql-test$ ./mysql-test-run --force percona_stats_null_innodb percona_stats_null_myisam --retry=0
Logging: /storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0/mysql-test/mysql-test-run.pl --force percona_stats_null_innodb percona_stats_null_myisam --retry=0
111119 2:40:08 [Note] Flashcache bypass: disabled
111119 2:40:08 [Note] Flashcache setup error is : open flash device failed

MySQL Version 5.5.17
Too long tmpdir path '/storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0-release/mysql-test/var/tmp' creating a shorter one...
 - using tmpdir: '/tmp/kJuXcQL9OJ'

Checking supported features...
 - skipping ndbcluster
 - skipping SSL, mysqld not compiled with SSL
Collecting tests...
vardir: /storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0-release/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0-release/mysql-test/var'...
Installing system database...
Using server port 33614

==============================================================================

TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] - 'localhost:13000' was not free
worker[1] - 'localhost:13010' was not free
worker[1] - 'localhost:13020' was not free
worker[1] Using MTR_BUILD_THREAD 303, with reserved ports 13030..13039
main.percona_stats_null_myisam [ pass ] 19686
main.percona_stats_null_innodb [ fail ]
        Test ended at 2011-11-19 02:41:01

CURRENT_TEST: main.percona_stats_null_innodb
--- /storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0/mysql-test/r/percona_stats_null_innodb.result 2011-11-19 03:39:22.627538466 +0300
+++ /storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0/mysql-test/r/percona_stats_null_innodb.reject 2011-11-19 03:41:00.882779055 +0300
@@ -15,4 +15,17 @@
 ANALYZE TABLE t;
 Table Op Msg_type Msg_text
 test.t analyze status OK
+Failed with '--innodb_stats_on_metadata=0 --innodb_stats_method=nulls_ignored --default-storage-engine=InnoDB'
+SELECT count(*) FROM t WHERE id IS NULL;
+count(*)
+94425
+SELECT count(*) FROM t WHERE id IS NOT NULL;
+count(*)
+5664
+SHOW INDEXES FROM t;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
+t 1 t 1 id A 100536 NULL NULL YES BTREE
+
+We are expected cardinality approximately equal to not null count, but this is false
+
 DROP TABLE t;

mysqltest: Result length mismatch

 - saving '/storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0-release/mysql-test/var/log/main.percona_stats_null_innodb/' to '/storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0-release/mysql-test/var/log/main.percona_stats_null_innodb/'
--------------------------------------------------------------------------
The servers were restarted 1 times
Spent 19.686 of 53 seconds executing testcases

Completed: Failed 1/2 tests, 50.00% were successful.

Failing test(s): main.percona_stats_null_innodb

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

mysql-test-run: *** ERROR: there were failing test cases
oleg.tsarev (0) /storage/dev/percona/innodb_stats_5.5/Percona-Server-5.5.17-rel21.0-release/mysql-test$

Related branches

Oleg Tsarev (tsarev) on 2012-01-02
tags: added: cr i19957
Stewart Smith (stewart) wrote :

So... this is Closed in upstream MySQL and it appears we just have the upstream fix now. Closing as Invalid.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1896

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.