Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (nike.apache.org: domain of mightye@gmail.com designates
 209.85.212.178 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CA+7vkToU0P4QoCwo3qC3=9wPKoW_yQpxwdQMQrjqgX-sFXNQxA@mail.gmail.com>
References: 
 <CA+7vkToU0P4QoCwo3qC3=9wPKoW_yQpxwdQMQrjqgX-sFXNQxA@mail.gmail.com>
From: Eric Stevens <mightye@gmail.com>
Date: Tue, 4 Jun 2013 14:12:03 -0400
Message-ID: 
 <CAORswtyr52DDt3OddxF=nwB2Xpo14VHYjqaP2C2z_XLYUf6-DA@mail.gmail.com>
Subject: Re: CQL 3 returning duplicate keys
To: user@cassandra.apache.org
Content-Type: multipart/alternative; boundary=089e01228514df901904de58058f

--089e01228514df901904de58058f
Content-Type: text/plain; charset=UTF-8

If this is a standard column family, not a CQL3 table, then using CQL3 will
not give you the results you expect.

>From cassandra-cli, let's set up some test data:

[default@unknown] create keyspace test;
[default@unknown] use test;
[default@test] create column family test;
[default@test] set test['a1']['c1'] = 'a1c1';
[default@test] set test['a1']['c2'] = 'a1c2';
[default@test] set test['a2']['c1'] = 'a2c1';
[default@test] set test['a2']['c2'] = 'a2c2';

Two rows with two columns each, right?  Not as far as CQL3 is concerned:

cqlsh> use test;
cqlsh:test> select * from test;

 key | column1 | value
-----+---------+--------
  a2 |    0xc1 | 0xa2c1
  a2 |    0xc2 | 0xa2c2
  a1 |    0xc1 | 0xa1c1
  a1 |    0xc2 | 0xa1c2

Basically for CQL3, without the additional metadata and enforcement that is
established by having created the column family as a CQL3 table, CQL will
treat each key/column pair as a separate row for CQL purposes.  This is
most likely at least in part due to the fact that CQL3 tables *cannot have
arbitrary columns *like standard column families can.  It wouldn't know
what columns are available for display.  This also exposes some of the
underlying structure behind CQL3 tables.

CQL 3 is not reverse compatible with CQL 2 for most things.  If you cannot
migrate your data to a CQL3 table.

The equivalent structure in CQL3 tables

cqlsh:test> create table test3 (key text PRIMARY KEY, c1 text, c2 text);
cqlsh:test> INSERT INTO test3(key, c1, c2) VALUES ('a1', 'a1c1', 'a1c2');
cqlsh:test> INSERT INTO test3(key, c1, c2) VALUES ('a2', 'a2c1', 'a2c2');
cqlsh:test> select * from test3;

 key | c1   | c2
-----+------+------
  a2 | a2c1 | a2c2
  a1 | a1c1 | a1c2

This comes with many important restrictions, one of which as mentioned is
that you cannot have arbitrary columns in a CQL3 table, just like you
cannot in a traditional relational database.  Likewise you cannot use
traditional approaches to populating data into a CQL3 table:

[default@test] get test3['a1'];
test3 not found in current keyspace.
[default@test] set test3['a3']['c1'] = 'a3c1';
test3 not found in current keyspace.
[default@test] describe test3;
WARNING: CQL3 tables are intentionally omitted from 'describe' output.


On Tue, Jun 4, 2013 at 12:56 PM, ekaqu something <ekaqu1028@gmail.com>wrote:

> I run a 1.1 cluster and currently testing out a 1.2 cluster.  I have
> noticed that with 1.2 it switched to CQL3 which is acting differently than
> I would expect.  When I do "select key from \"cf\";" I get many many
> duplicate keys.  When I did the same with CQL 2 I only get the keys
> defined.  This seems to also be the case for count(*), in cql2 it would
> return the number of keys i have, in 3 it returns way more than i really
> have.
>
> $ cqlsh `hostname` <<EOF
> use keyspace;
> select count(*) from "cf";
> EOF
>
>
>  count
> -------
>  10000
>
> Default LIMIT of 10000 was used. Specify your own LIMIT clause to get more
> results.
>
> $ cqlsh `hostname` -3 <<EOF
> use keyspace;
> select count(*) from "cf";
> EOF
>
>
>  count
> -------
>  10000
>
> Default LIMIT of 10000 was used. Specify your own LIMIT clause to get more
> results.
>
>
> $ cqlsh `hostname` -2 <<EOF
> use keyspace;
> select count(*) from cf;
> EOF
>
>
>  count
> -------
>   1934
>
> 1934 rows have really been inserted. Is there something up with cql3 or is
> there something else going on?
>
> Thanks for your time reading this email.
>

--089e01228514df901904de58058f
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">If this is a standard column family, not a CQL3 table, the=
n using CQL3 will not give you the results you expect.<div><br></div><div s=
tyle>From cassandra-cli, let&#39;s set up some test data:</div><div style>

<br></div><div><div>[default@unknown] create keyspace test;</div></div><div=
><div>[default@unknown] use test;</div><div>[default@test] create column fa=
mily test;</div><div><div>[default@test] set test[&#39;a1&#39;][&#39;c1&#39=
;] =3D &#39;a1c1&#39;;</div>

</div></div><div><div>[default@test] set test[&#39;a1&#39;][&#39;c2&#39;] =
=3D &#39;a1c2&#39;;</div></div><div><div>[default@test] set test[&#39;a2=
9;][&#39;c1&#39;] =3D &#39;a2c1&#39;;</div></div><div><div>[default@test] s=
et test[&#39;a2&#39;][&#39;c2&#39;] =3D &#39;a2c2&#39;;</div>

</div><div><br></div><div style>Two rows with two columns each, right? =C2=
=A0Not as far as CQL3 is concerned:</div><div style><br></div><div style><d=
iv>cqlsh&gt; use test;</div><div>cqlsh:test&gt; select * from test;</div><d=
iv>

<br></div><div><font face=3D"courier new, monospace">=C2=A0key | column1 | =
value</font></div><div><font face=3D"courier new, monospace">-----+--------=
-+--------</font></div><div><font face=3D"courier new, monospace">=C2=A0 a2=
 | =C2=A0 =C2=A00xc1 | 0xa2c1</font></div>

<div><font face=3D"courier new, monospace">=C2=A0 a2 | =C2=A0 =C2=A00xc2 | =
0xa2c2</font></div><div><font face=3D"courier new, monospace">=C2=A0 a1 | =
=C2=A0 =C2=A00xc1 | 0xa1c1</font></div><div><font face=3D"courier new, mono=
space">=C2=A0 a1 | =C2=A0 =C2=A00xc2 | 0xa1c2</font></div>

<div><br></div><div style>Basically for CQL3, without the additional metada=
ta and enforcement that is established by having created the column family =
as a CQL3 table, CQL will treat each key/column pair as a separate row for =
CQL purposes. =C2=A0This is most likely at least in part due to the fact th=
at CQL3 tables <i>cannot have arbitrary columns </i>like standard column fa=
milies can. =C2=A0It wouldn&#39;t know what columns are available for displ=
ay. =C2=A0This also exposes some of the underlying structure behind CQL3 ta=
bles.</div>

<div style><br></div><div style>CQL 3 is not reverse compatible with CQL 2 =
for most things. =C2=A0If you cannot migrate your data to a CQL3 table.</di=
v><div style><br></div><div style>The equivalent structure in CQL3 tables=
=C2=A0</div>

<div style><br></div></div><div style>cqlsh:test&gt; create table test3 (ke=
y text PRIMARY KEY, c1 text, c2 text);<br></div><div><div>cqlsh:test&gt; IN=
SERT INTO test3(key, c1, c2) VALUES (&#39;a1&#39;, &#39;a1c1&#39;, &#39;a1c=
2&#39;);</div>

<div>cqlsh:test&gt; INSERT INTO test3(key, c1, c2) VALUES (&#39;a2&#39;, &#=
39;a2c1&#39;, &#39;a2c2&#39;);</div></div><div><div>cqlsh:test&gt; select *=
 from test3;</div><div><br></div><div><font face=3D"courier new, monospace"=
>=C2=A0key | c1 =C2=A0 | c2</font></div>

<div><font face=3D"courier new, monospace">-----+------+------</font></div>=
<div><font face=3D"courier new, monospace">=C2=A0 a2 | a2c1 | a2c2</font></=
div><div><font face=3D"courier new, monospace">=C2=A0 a1 | a1c1 | a1c2</fon=
t></div></div>

<div><br></div><div style>This comes with many important restrictions, one =
of which as mentioned is that you cannot have arbitrary columns in a CQL3 t=
able, just like you cannot in a traditional relational database. =C2=A0Like=
wise you cannot use traditional approaches to populating data into a CQL3 t=
able:</div>

<div style><br></div><div style><div>[default@test] get test3[&#39;a1&#39;]=
;</div><div>test3 not found in current keyspace.</div><div><div>[default@te=
st] set test3[&#39;a3&#39;][&#39;c1&#39;] =3D &#39;a3c1&#39;;</div><div>
test3 not found in current keyspace.</div>
</div><div><div>[default@test] describe test3;</div><div>WARNING: CQL3 tabl=
es are intentionally omitted from &#39;describe&#39; output.<br></div></div=
><div><br></div></div><div><br></div></div><div class=3D"gmail_extra"><br>

<br><div class=3D"gmail_quote">On Tue, Jun 4, 2013 at 12:56 PM, ekaqu somet=
hing <span dir=3D"ltr">&lt;<a href=3D"mailto:ekaqu1028@gmail.com" target=3D=
"_blank">ekaqu1028@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"=
gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-=
left:1ex">

<div dir=3D"ltr">I run a 1.1 cluster and currently testing out a 1.2 cluste=
r. =C2=A0I have noticed that with 1.2 it switched to CQL3 which is acting d=
ifferently than I would expect. =C2=A0When I do &quot;select key from \&quo=
t;cf\&quot;;&quot; I get many many duplicate keys. =C2=A0When I did the sam=
e with CQL 2 I only get the keys defined. =C2=A0This seems to also be the c=
ase for count(*), in cql2 it would return the number of keys i have, in 3 i=
t returns way more than i really have.<div>


<br></div><div><div>$ cqlsh `hostname` &lt;&lt;EOF</div><div>use keyspace;<=
/div><div>select count(*) from &quot;cf&quot;;</div><div>EOF</div><div><br>=
</div><div><br></div><div>=C2=A0count</div><div>-------</div><div>=C2=A0100=
00</div>


<div><br></div><div>Default LIMIT of 10000 was used. Specify your own LIMIT=
 clause to get more results.</div><div><br></div><div><div>$ cqlsh `hostnam=
e` -3 &lt;&lt;EOF</div><div>use keyspace;</div><div>select count(*) from &q=
uot;cf&quot;;</div>


<div>EOF</div><div><br></div><div><br></div><div>=C2=A0count</div><div>----=
---</div><div>=C2=A010000</div><div><br></div><div>Default LIMIT of 10000 w=
as used. Specify your own LIMIT clause to get more results.</div><div><br><=
/div>


</div>
<div><br></div><div><div>$ cqlsh `hostname` -2 &lt;&lt;EOF</div><div>use ke=
yspace;</div><div>select count(*) from cf;</div><div>EOF</div><div><br></di=
v><div><br></div><div>=C2=A0count</div><div>-------</div><div>=C2=A0 1934</=
div></div>


<div><br></div><div>1934 rows have really been inserted. Is there something=
 up with cql3 or is there something else going on?</div></div><div><br></di=
v><div>Thanks for your time reading this email.</div>

</div>
</blockquote></div><br></div>

--089e01228514df901904de58058f--