Bug #59659 for DBIx-Class: quoting bit fields breaks them

# Fri Jul 23 11:38:02 2010 RIZEN [...] cpan.org - Ticket created

Subject:	quoting bit fields breaks them
MIME-Version:	1.0
Content-Type:	text/plain; charset="UTF-8"
X-Mailer:	MIME-tools 5.427 (Entity 5.427)
Content-Disposition:	inline
Content-Transfer-Encoding:	binary
X-RT-Original-Encoding:	utf-8
Content-Length:	376

Download (untitled) / with headers
text/plain 376b

If you create a MySQL table with a field type of 'bit' and then do a query like: $rs->search({ bit_field => 0 }); That will generate SQL like: select * from test where bit_field = ?: '0' However, that will not work, because '0' is not the same as 0. So you'll always get 0 records back. The SQL generated must not quote the 0. select * from test where bit_field = ?: 0

# Fri Jul 23 12:43:10 2010 ribasushi [...] leporine.io - Correspondence added

MIME-Version:	1.0
X-Mailer:	MIME-tools 5.427 (Entity 5.427)
Content-Disposition:	inline
Content-Type:	text/plain; charset="UTF-8"
Message-ID:	<rt-3.8.HEAD-2217-1279903390-668.59659-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding:	binary
X-RT-Original-Encoding:	utf-8
Content-Length:	708

Download (untitled) / with headers
text/plain 708b

On Fri Jul 23 11:38:02 2010, RIZEN wrote: Show quoted text

> If you create a MySQL table with a field type of 'bit' and then do a > query like: > > $rs->search({ bit_field => 0 }); > > That will generate SQL like: > > select * from test where bit_field = ?: '0' > > However, that will not work, because '0' is not the same as 0. So > you'll always get 0 records > back. The SQL generated must not quote the 0. > > select * from test where bit_field = ?: 0

Quoting is irrelevant to bind variables. The ''s are added by the *STDERR display code* so you can read a string with a traling space e.g. The value being bound is 0, not '0'. Do you have an actual non-working case, or were you just alarmed by the debug output?

# Fri Jul 23 12:43:11 2010 The RT System itself - Status changed from 'new' to 'open'

# Fri Jul 23 12:56:36 2010 jt [...] plainblack.com - Correspondence added

From jt [...] plainblack.com Fri Jul 23 12:	54:45 2010
MIME-Version:	1.0 (Apple Message framework v1081)
X-Spam-Status:	No, score=-7.932 tagged_above=-99.9 required=10 tests=[AWL=2.667, BAYES_00=-2.599, RCVD_IN_DNSWL_HI=-8] autolearn=ham
X-Eon-Sig:	AQLogdxMScm4O94rjQIAAAAB,4765c700cdc62fd12ef1497872c8ddbc
In-Reply-To:	<rt-3.8.HEAD-2217-1279903391-1149.59659-6-0 [...] rt.cpan.org>
X-Mailer:	Apple Mail (2.1081)
X-Spam-Flag:	NO
References:	<RT-Ticket-59659 [...] rt.cpan.org> <rt-3.8.HEAD-2217-1279903391-1149.59659-6-0 [...] rt.cpan.org>
X-Proofpoint-Spam-Details:	rule=notspam policy=default score=0 spamscore=0 ipscore=0 phishscore=0 bulkscore=0 adultscore=0 classifier=spam adjust=0 reason=mlx engine=6.0.2-1004200000 definitions=main-1007230065
X-Virus-Scanned:	Debian amavisd-new at bestpractical.com
Content-Type:	text/plain; charset="utf-8"
Message-ID:	<C7A594B6-BC32-4364-9B5A-5C2A6B28ECFF [...] plainblack.com>
X-RT-Original-Encoding:	utf-8
X-Spam-Score:	-7.932
Received:	from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 276C161E013 for <cpan-bug+DBIx-Class [...] hipster.bestpractical.com>; Fri, 23 Jul 2010 12:54:45 -0400 (EDT)
Received:	from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id RQRKAxeN-FIs for <cpan-bug+DBIx-Class [...] hipster.bestpractical.com>; Fri, 23 Jul 2010 12:54:43 -0400 (EDT)
Received:	from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id 1BF83240AD9 for <bug-DBIx-Class [...] rt.cpan.org>; Fri, 23 Jul 2010 12:54:42 -0400 (EDT)
Received:	(qmail 27832 invoked by uid 103); 23 Jul 2010 16:56:33 -0000
Received:	from x16.dev (10.0.100.26) by x1.dev with QMQP; 23 Jul 2010 16:56:33 -0000
Received:	from imta-38.everyone.net (HELO imta-38.everyone.net) (216.200.145.38) by 16.mx.develooper.com (qpsmtpd/0.80) with ESMTP; Fri, 23 Jul 2010 09:56:29 -0700
Received:	from pps.filterd (omta001 [127.0.0.1]) by imta-38.everyone.net (8.14.3/8.14.3) with SMTP id o6NGuLoG011173 for <bug-DBIx-Class [...] rt.cpan.org>; Fri, 23 Jul 2010 09:56:25 -0700
Received:	from dm0205.mta.everyone.net (sj1-slb03-gw2.sj2.proofpoint.com [172.16.1.96]) by imta-38.everyone.net with ESMTP id q7eser942-1 for <bug-DBIx-Class [...] rt.cpan.org>; Fri, 23 Jul 2010 09:56:24 -0700
Received:	by dm0205.mta.everyone.net (EON-AUTHRELAY2[SSL] - 475a1d05) id dm0205.4c48d1a6.201c87 for <bug-DBIx-Class [...] rt.cpan.org>; Fri, 23 Jul 2010 09:56:24 -0700
Delivered-To:	cpan-bug+DBIx-Class [...] hipster.bestpractical.com
X-Eon-DM:	dm0205
Subject:	Re: [rt.cpan.org #59659] quoting bit fields breaks them
Return-Path:	<jt [...] plainblack.com>
X-Spam-Check-BY:	16.mx.develooper.com
X-Original-To:	cpan-bug+DBIx-Class [...] hipster.bestpractical.com
X-RT-Mail-Extension:	dbix-class
Date:	Fri, 23 Jul 2010 11:56:23 -0500
X-Spam-Level:
To:	bug-DBIx-Class [...] rt.cpan.org
Content-Transfer-Encoding:	quoted-printable
From:	JT Smith <jt [...] plainblack.com>
RT-Message-ID:	<rt-3.8.HEAD-2217-1279904196-300.59659-0-0 [...] rt.cpan.org>
Content-Length:	582

Download (untitled) / with headers
text/plain 582b

Show quoted text

> Do you have an actual non-working case, or were you just alarmed by the > debug output?

I have an actual non-working case. Table looks like this: create table user ( id int not null primary key, verified bit not null default 0 ... ); my $inactive_count = $schema->resultset('User')->search( { verified => 0 } )->count; The above always returns 0. As a work around, I've changed the 'verified' field to a tinyint, and everything started working just fine. For my purposes there's no problem changing to a tinyint. I just thought you guys would want to know about this case.

# Thu Jan 13 17:50:53 2011 ribasushi [...] leporine.io - Correspondence added

MIME-Version:	1.0
X-Mailer:	MIME-tools 5.427 (Entity 5.427)
Content-Disposition:	inline
Content-Type:	text/plain; charset="UTF-8"
Message-ID:	<rt-3.8.HEAD-9644-1294959053-122.59659-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding:	binary
X-RT-Original-Encoding:	utf-8
Content-Length:	47

Download (untitled) / with headers
text/plain 47b

Blocked by lack of functionality as per RT64756

# Thu Jan 13 17:50:54 2011 ribasushi [...] leporine.io - Status changed from 'open' to 'stalled'

# Thu Jan 13 17:50:54 2011 ribasushi [...] leporine.io - Dependency on ticket #64756 added