Fixed count subquery from disabled test case

[dbsrgits/DBIx-Class.git] / t / 73oracle.t

diff --git a/t/73oracle.t b/t/73oracle.t
index f1c1ca0..d6e90e3 100644 (file)
--- a/t/73oracle.t
+++ b/t/73oracle.t
@@ -376,6 +376,22 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
         ],
     });
 
+    $schema->resultset('Artist')->create(
+        {
+            name     => 'cycle-root',
+            children => [
+                {
+                    name     => 'cycle-child1',
+                    children => [ { name => 'cycle-grandchild' } ],
+                },
+                { name => 'cycle-child2' },
+            ],
+        }
+    );
+
+    $schema->resultset('Artist')->find({ name => 'cycle-root' })
+      ->update({ parentid => \'artistid' });
+
     # select the whole tree
     {
       my $rs = $schema->resultset('Artist')->search({}, {
@@ -389,7 +405,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
           SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
             FROM artist me
           START WITH name = ?
-          CONNECT BY parentid = PRIOR( artistid )
+          CONNECT BY parentid = PRIOR artistid 
         )',
         [ [ name => 'root'] ],
       );
@@ -406,7 +422,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
           SELECT COUNT( * )
             FROM artist me
           START WITH name = ?
-          CONNECT BY parentid = PRIOR( artistid )
+          CONNECT BY parentid = PRIOR artistid 
         )',
         [ [ name => 'root'] ],
       );
@@ -428,7 +444,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
           SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
             FROM artist me
           START WITH name = ?
-          CONNECT BY parentid = PRIOR( artistid )
+          CONNECT BY parentid = PRIOR artistid 
           ORDER SIBLINGS BY name DESC
         )',
         [ [ name => 'root'] ],
@@ -444,8 +460,8 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
     # get the root node
     {
       my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
-        start_with => { name => 'greatgrandchild' },
-        connect_by => { artistid => { -prior => \ 'parentid' } },
+        start_with => { name => 'root' },
+        connect_by => { parentid => { -prior => \ 'artistid' } },
       });
 
       is_same_sql_bind (
@@ -455,9 +471,9 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
             FROM artist me
           WHERE ( parentid IS NULL )
           START WITH name = ?
-          CONNECT BY artistid = PRIOR( parentid )
+          CONNECT BY parentid = PRIOR artistid 
         )',
-        [ [ name => 'greatgrandchild'] ],
+        [ [ name => 'root'] ],
       );
 
       is_deeply(
@@ -486,7 +502,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
             LEFT JOIN cd cds ON cds.artist = me.artistid
           WHERE ( cds.title LIKE ? )
           START WITH me.name = ?
-          CONNECT BY parentid = PRIOR( artistid )
+          CONNECT BY parentid = PRIOR artistid 
         )',
         [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
       );
@@ -506,7 +522,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
             LEFT JOIN cd cds ON cds.artist = me.artistid
           WHERE ( cds.title LIKE ? )
           START WITH me.name = ?
-          CONNECT BY parentid = PRIOR( artistid )
+          CONNECT BY parentid = PRIOR artistid 
         )',
         [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
       );
@@ -517,9 +533,9 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
     # combine a connect by with order_by
     {
       my $rs = $schema->resultset('Artist')->search({}, {
-        start_with => { name => 'greatgrandchild' },
-        connect_by => { artistid => { -prior => \ 'parentid' } },
-        order_by => { -asc => 'name' },
+        start_with => { name => 'root' },
+        connect_by => { parentid => { -prior => \ 'artistid' } },
+        order_by => { -asc => [ 'LEVEL', 'name' ] },
       });
 
       is_same_sql_bind (
@@ -528,15 +544,15 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
           SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
             FROM artist me
           START WITH name = ?
-          CONNECT BY artistid = PRIOR( parentid )
-          ORDER BY name ASC
+          CONNECT BY parentid = PRIOR artistid 
+          ORDER BY LEVEL ASC, name ASC
         )',
-        [ [ name => 'greatgrandchild' ] ],
+        [ [ name => 'root' ] ],
       );
 
       is_deeply (
         [ $rs->get_column ('name')->all ],
-        [ qw/child1 grandchild greatgrandchild root/ ],
+        [ qw/root child1 child2 grandchild greatgrandchild/ ],
         'Connect By with a order_by - result name ok'
       );
     }
@@ -545,44 +561,43 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
     # limit a connect by
     {
       my $rs = $schema->resultset('Artist')->search({}, {
-        start_with => { name => 'greatgrandchild' },
-        connect_by => { artistid => { -prior => \ 'parentid' } },
-        order_by => { -desc => 'name' },
+        start_with => { name => 'root' },
+        connect_by => { parentid => { -prior => \ 'artistid' } },
+        order_by => { -asc => 'name' },
         rows => 2,
       });
 
       is_same_sql_bind (
         $rs->as_query,
         '( 
-            SELECT * FROM (
-                  SELECT A.*, ROWNUM r FROM (
+            SELECT artistid, name, rank, charfield, parentid FROM (
+                  SELECT artistid, name, rank, charfield, parentid, ROWNUM rownum__index FROM (
                       SELECT 
-                          me.artistid AS col1,
-                          me.name AS col2,
-                          me.rank AS col3,
-                          me.charfield AS col4,
-                          me.parentid AS col5 
+                          me.artistid,
+                          me.name,
+                          me.rank,
+                          me.charfield,
+                          me.parentid 
                       FROM artist me 
                       START WITH name = ? 
-                      CONNECT BY artistid = PRIOR( parentid )
-                      ORDER BY name DESC
-                  ) A
-                  WHERE ROWNUM < 3
-              ) B
-              WHERE r >= 1 
+                      CONNECT BY parentid = PRIOR artistid
+                      ORDER BY name ASC 
+                  ) me 
+            ) me
+            WHERE rownum__index BETWEEN 1 AND 2
         )',
-        [ [ name => 'greatgrandchild' ] ],
+        [ [ name => 'root' ] ],
       );
 
       is_deeply (
         [ $rs->get_column ('name')->all ],
-        [qw/root greatgrandchild/],
+        [qw/child1 child2/],
         'LIMIT a Connect By query - correct names'
       );
 
       # TODO: 
       # prints "START WITH name = ? 
-      # CONNECT BY artistid = PRIOR( parentid )"
+      # CONNECT BY artistid = PRIOR parentid "
       # after count_subq, 
       # I will fix this later...
       # 
@@ -590,18 +605,17 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
       #   $rs->count_rs->as_query,
       #   '( 
       #       SELECT COUNT( * ) FROM (
-      #           SELECT * FROM (
-      #               SELECT A.*, ROWNUM r FROM (
+      #           SELECT artistid FROM (
+      #               SELECT artistid, ROWNUM rownum__index FROM (
       #                   SELECT 
-      #                       me.artistid AS col1 
+      #                       me.artistid
       #                   FROM artist me 
       #                   START WITH name = ? 
-      #                   CONNECT BY artistid = PRIOR( parentid ) 
-      #               ) A
-      #               WHERE ROWNUM < 3
-      #           ) B
-      #           WHERE r >= 1
-      #       ) count_subq 
+      #                   CONNECT BY artistid = PRIOR parentid 
+      #               ) me
+      #           ) me 
+      #           WHERE rownum__index BETWEEN 1 AND 2
+      #       ) me
       #   )',
       #   [ [ name => 'greatgrandchild' ] ],
       # );
@@ -609,28 +623,48 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
       # is( $rs->count, 2, 'Connect By; LIMIT count ok' );
     }
 
-    # select the whole tree with nocylce
+    # select the whole cycle tree without nocylce
     {
       my $rs = $schema->resultset('Artist')->search({}, {
-        nocycle    => 1,
-        start_with => { name => 'root' },
+        start_with => { name => 'cycle-root' },
+        connect_by => { parentid => { -prior => \ 'artistid' } },
+      });
+      eval { $rs->get_column ('name')->all };
+      if ( $@ =~ /ORA-01436/ ){ # ORA-01436:  CONNECT BY loop in user data
+        pass "connect by initify loop detection without nocycle";
+      }else{
+        fail "connect by initify loop detection without nocycle, not detected by oracle";
+      }
+    }
+
+    # select the whole cycle tree with nocylce
+    {
+      my $rs = $schema->resultset('Artist')->search({}, {
+        connect_by_nocycle    => 1,
+        start_with => { name => 'cycle-root' },
+        '+select'  => [ \ 'CONNECT_BY_ISCYCLE' ],
         connect_by => { parentid => { -prior => \ 'artistid' } },
       });
 
       is_same_sql_bind (
         $rs->as_query,
         '(
-          SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
+          SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
             FROM artist me
           START WITH name = ?
-          CONNECT BY NOCYCLE parentid = PRIOR( artistid )
+          CONNECT BY NOCYCLE parentid = PRIOR artistid 
         )',
-        [ [ name => 'root'] ],
+        [ [ name => 'cycle-root'] ],
       );
       is_deeply (
         [ $rs->get_column ('name')->all ],
-        [ qw/root child1 grandchild greatgrandchild child2/ ],
-        'got artist tree with nocycle',
+        [ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ],
+        'got artist tree with nocycle (name)',
+      );
+      is_deeply (
+        [ $rs->get_column ('CONNECT_BY_ISCYCLE')->all ],
+        [ qw/1 0 0 0/ ],
+        'got artist tree with nocycle (CONNECT_BY_ISCYCLE)',
       );
 
 
@@ -640,12 +674,12 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
           SELECT COUNT( * )
             FROM artist me
           START WITH name = ?
-          CONNECT BY NOCYCLE parentid = PRIOR( artistid )
+          CONNECT BY NOCYCLE parentid = PRIOR artistid 
         )',
-        [ [ name => 'root'] ],
+        [ [ name => 'cycle-root'] ],
       );
 
-      is( $rs->count, 5, 'Connect By Nocycle count ok' );
+      is( $rs->count, 4, 'Connect By Nocycle count ok' );
     }
 }