Fix self-referential resultset update/delete on MySQL (aggravated by 31073ac7)

[dbsrgits/DBIx-Class.git] / t / sqlmaker / mysql.t

diff --git a/t/sqlmaker/mysql.t b/t/sqlmaker/mysql.t
new file mode 100644 (file)
index 0000000..9de4c7f
--- /dev/null
+++ b/t/sqlmaker/mysql.t
@@ -0,0 +1,93 @@
+use strict;
+use warnings;
+
+use Test::More;
+
+use lib qw(t/lib);
+use DBICTest;
+use DBICTest::Schema;
+use DBIC::SqlMakerTest;
+use DBIC::DebugObj;
+
+my $schema = DBICTest::Schema->connect (DBICTest->_database, { quote_char => '`' });
+# cheat
+require DBIx::Class::Storage::DBI::mysql;
+bless ( $schema->storage, 'DBIx::Class::Storage::DBI::mysql' );
+
+# check that double-subqueries are properly wrapped
+{
+  my ($sql, @bind);
+  my $debugobj = DBIC::DebugObj->new (\$sql, \@bind);
+  my $orig_debugobj = $schema->storage->debugobj;
+  my $orig_debug = $schema->storage->debug;
+
+  $schema->storage->debugobj ($debugobj);
+  $schema->storage->debug (1);
+
+  # the expected SQL may seem wastefully nonsensical - this is due to
+  # CD's tablename being \'cd', which triggers the "this can be anything"
+  # mode, and forces a subquery. This in turn forces *another* subquery
+  # because mysql is being mysql
+  # Also we know it will fail - never deployed. All we care about is the
+  # SQL to compare
+  eval { $schema->resultset ('CD')->update({ genreid => undef }) };
+  is_same_sql_bind (
+    $sql,
+    \@bind,
+    'UPDATE cd SET `genreid` = ? WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )',
+    [ 'NULL' ],
+    'Correct update-SQL with double-wrapped subquery',
+  );
+
+  # same comment as above
+  eval { $schema->resultset ('CD')->delete };
+  is_same_sql_bind (
+    $sql,
+    \@bind,
+    'DELETE FROM cd WHERE `cdid` IN ( SELECT * FROM ( SELECT `me`.`cdid` FROM cd `me` ) `_forced_double_subquery` )',
+    [],
+    'Correct delete-SQL with double-wrapped subquery',
+  );
+
+  # and a really contrived example (we test it live in t/71mysql.t)
+  my $rs = $schema->resultset('Artist')->search({ name => { -like => 'baby_%' } });
+  my ($count_sql, @count_bind) = @${$rs->count_rs->as_query};
+  eval {
+    $schema->resultset('Artist')->search(
+      { artistid => {
+        -in => $rs->get_column('artistid')
+                    ->as_query
+      } },
+    )->update({ name => \[ "CONCAT( `name`, '_bell_out_of_', $count_sql )", @count_bind ] });
+  };
+
+  is_same_sql_bind (
+    $sql,
+    \@bind,
+    q(
+      UPDATE `artist`
+        SET `name` = CONCAT(`name`, '_bell_out_of_', (
+          SELECT *
+            FROM (
+              SELECT COUNT( * )
+                FROM `artist` `me`
+                WHERE `name` LIKE ?
+            ) `_forced_double_subquery`
+        ))
+      WHERE
+        `artistid` IN (
+          SELECT *
+            FROM (
+              SELECT `me`.`artistid`
+                FROM `artist` `me`
+              WHERE `name` LIKE ?
+            ) `_forced_double_subquery` )
+    ),
+    [ ("'baby_%'") x 2 ],
+  );
+
+  $schema->storage->debugobj ($orig_debugobj);
+  $schema->storage->debug ($orig_debug);
+}
+
+done_testing;