Problem with DBIx-Custom and MariaDB

[sseide]

Hello,

i have an program using your DBIx-Custom library and DBIx::Custom::NotExists whenever i do not have values for an row. This is working fine with mysql 5.5 as backend database but does not work with mariadb 10.1 (the new replacement for mysql on most linux distros)

A sample code is:

my $where = $dbi->where();
my $param = { 
	startDate => $start_date // $dbi->not_exists,
    	endDate => $end_date // $dbi->not_exists
};
$where->clause( ['and', 'shows.start_time >= :startDate', 'shows.start_time param( $param );
my $result = $dbi->select( table => 'shows', where => $where);

The resulting sql statements (checked in the server logfiles) whenever endDate is not set will be for MySQL 5.5:

select * from shows where show.start_time >= '2017-11-11'

The same for MariaDB 10.1:

select * from shows where shows.start_time >= '2017-11-11' and shows.endTime 
As you can see with MariaDB the where clause is not omitted and the string of your object is bound as parameter.
It is reproducible every time - i just start the program an either point to an mysql server or an mariadb-server and the results change as mentioned above...
Do you have an ideas where this different behavior comes from?
Debian 9.1
Perl 5.25
libdbi-perl 1.636-1
libdbd-mysql-perl 4.041-2
DBIx::Custom 0.41
DBIx::Connector 0.56
Thanks,
Stefan Seide

[yuki-kimoto]

I don't know the different of this now.

but, DBIx::NotExists object is only work well in array reference.

my $where = $dbi->where(); my $param = { startDate => $start_date // $dbi->not_exists, endDate => $end_date // $dbi->not_exists }; $where->clause( ['and', 'shows.start_time >= :startDate', 'shows.start_time <= :endDate'); $where->param( $param ); my $result = $dbi->select( table => 'shows', where => $where);

The above has wrong part. Correct is

my $param = { defined $start_data => ('shows.startDate' => $start_date),

    defined $end_date => ('shows.endDate' => $end_data)

};

or

my $param = {};

if (defined $start_data) {

$param->{'shows.startDate'} = $start_data;

}

if (defined $end_data) {

$param->{'shows.endDate'} = $end_data;

}

2017-11-23 20:36 GMT+09:00 sseide [email protected]:

Hello,

i have an program using your DBIx-Custom library and DBIx::Custom::NotExists whenever i do not have values for an row. This is working fine with mysql 5.5 as backend database but does not work with mariadb 10.1 (the new replacement for mysql on most linux distros)

A sample code is:

my $where = $dbi->where(); my $param = { startDate => $start_date // $dbi->not_exists, endDate => $end_date // $dbi->not_exists }; $where->clause( ['and', 'shows.start_time >= :startDate', 'shows.start_time <= :endDate'); $where->param( $param ); my $result = $dbi->select( table => 'shows', where => $where);

The resulting sql statements (checked in the server logfiles) whenever endDate is not set will be for MySQL 5.5:

select * from shows where show.start_time >= '2017-11-11'

The same for MariaDB 10.1:

select * from shows where shows.start_time >= '2017-11-11' and shows.endTime <= 'DBIx::Custom::NotExists=HASH(0x55fd2d068b98)'

As you can see with MariaDB the where clause is not omitted and the string of your object is bound as parameter.

It is reproducible every time - i just start the program an either point to an mysql server or an mariadb-server and the results change as mentioned above...

Do you have an ideas where this different behavior comes from?

Debian 9.1 Perl 5.25 libdbi-perl 1.636-1 libdbd-mysql-perl 4.041-2 DBIx::Custom 0.41 DBIx::Connector 0.56

Thanks, Stefan Seide

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/yuki-kimoto/DBIx-Custom/issues/13, or mute the thread https://github.com/notifications/unsubscribe-auth/AAE_ws-4nIvfxog7Kw-C4YEwDViLVSJfks5s5Vi_gaJpZM4Qoncb .

[sseide]

Many thanks, change the code to your second example, that is working again.

But none the less found my way shorter and easier to read :) And it worked well with MySQL. Don't now what the difference is with MariaDB...

Shall it be closed or left open as a reminder to check for differences with MySQL / MariaDB?

[sseide]

The following fix seems to get it working again for me - now the NotExists works on booth MySQL and MariaDB without an Array reference.

unified Diff of Where.pm (Version 0.41)

--- Where.pm	2017-11-23 15:29:23.754074744 +0100
+++ Where-fix.pm	2017-11-23 15:29:12.742077560 +0100
@@ -117,7 +117,9 @@
           $pushed = 1 if exists $param->{$column}->[$count - 1]
             && ref $param->{$column}->[$count - 1] ne 'DBIx::Custom::NotExists'
         }
-        elsif ($count == 1) { $pushed = 1 }
+        elsif ($count == 1) {
+          $pushed = 1 if ref $param->{$column} ne 'DBIx::Custom::NotExists'
+        }
       }
       push @$where, $clause if $pushed;
     }

[yuki-kimoto]

thanks, sseide.

I will check the fixed code.

2017-11-23 23:33 GMT+09:00 sseide [email protected]:

The following fix seems to get it working again for me - now the NotExists works on booth MySQL and MariaDB without an Array reference.

unified Diff of Where.pm (Version 0.41)

--- Where.pm 2017-11-23 15:29:23.754074744 +0100 +++ Where-fix.pm 2017-11-23 15:29:12.742077560 +0100 @@ -117,7 +117,9 @@ $pushed = 1 if exists $param->{$column}->[$count - 1] && ref $param->{$column}->[$count - 1] ne 'DBIx::Custom::NotExists' }

•    elsif ($count == 1) { $pushed = 1 }
  

•    elsif ($count == 1) {
  

•      $pushed = 1 if ref $param->{$column} ne 'DBIx::Custom::NotExists'
  

•    }
   }
   push @$where, $clause if $pushed;
  

  }

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/yuki-kimoto/DBIx-Custom/issues/13#issuecomment-346633483, or mute the thread https://github.com/notifications/unsubscribe-auth/AAE_wiChAzZ7I0oh8FYEuZYDCa2C9ZSEks5s5YJBgaJpZM4Qoncb .