split not working on table with implicit self-referencing foreign key

[GoogleCodeExporter]

Which version of common_schema are you using? (specify
revision+distribution)
1.3.1

Which component is failing? (specify the view, function, etc.)
split()

What is the expected output? What do you see instead?
I ran an update using split and expected to see updated rows, but nothing was 
updated. I think split failed to pick the proper column to split on. I dropped 
a bunch of columns from the relevant table to figure out which one caused the 
problem, and discovered that it was a self-referential implicit foreign key. A 
column that had the name of the table within its name and a unique index on it.

Can you provide with sample data?
Steps to reproduce (using sakila db):
1) This split works in a default sakila installation:
call common_schema.run("
split(sakila.film) { 
select 
$split_step as step, $split_columns as columns, 
$split_min as min_value, $split_max as max_value, 
$split_range_start as range_start, $split_range_end as range_end
}" );

2) Add a new column and unique index:
alter table sakila.film add most_similar_film_id smallint unsigned null;
alter table sakila.film add unique key most_similar_film_id 
(most_similar_film_id);

3) Now this fails:
call common_schema.run("
split(sakila.film) { 
select 
$split_step as step, $split_columns as columns, 
$split_min as min_value, $split_max as max_value, 
$split_range_start as range_start, $split_range_end as range_end
}" );

(As of version 1.1), please provide output of
SELECT * FROM common_schema.status

                       project_name: common_schema
                            version: 1.3.1
                           revision: 437
                       install_time: 2013-03-19 12:05:01
                    install_success: 1
          base_components_installed: 1
 innodb_plugin_components_installed: 1
percona_server_components_installed: 1
              install_mysql_version: 5.5.29-log
                   install_sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Please provide any additional information below.
More detail on my original issue is available here:
http://mechanics.flite.com/blog/2013/03/18/helping-common-schema-help-me/

Original issue reported on code.google.com by ike.wal...@flite.com on 19 Mar 2013 at 5:21

[GoogleCodeExporter]

Good catch.
Some clarifications:
- The problem has nothing to do with foreign keys
- Has nothing to do with name of the column
- Comes from a UNIQUE KEY on a NULLable column, where all values are indeed NULL

Solution is to:
- prefer not-nullable keys to nullable keys
- prefer PK over "normal" keys on similar data types (also implies the above)

Original comment by shlomi.n...@gmail.com on 19 Mar 2013 at 8:25

• Changed state: Accepted

[GoogleCodeExporter]

Here's an immediate fix to the _split_generate_dependency_tables() routine; 
will incorporate with next release.

Solves the example with the "film" table. Let me know if also solves the 
problem you describe in your blog.

Original comment by shlomi.n...@gmail.com on 19 Mar 2013 at 8:38

Attachments:

• _split_generate_dependency_tables.sql

[GoogleCodeExporter]

This issue was closed by revision r442.

Original comment by shlomi.n...@gmail.com on 20 Mar 2013 at 8:03

• Changed state: Fixed

[GoogleCodeExporter]

Yes, this resolves the problem as originally reported on my blog.

Thanks!

Original comment by ike.wal...@flite.com on 27 Mar 2013 at 8:30