select contiguous addresses that start on a bit boundary

[John Stile <john@xxxxxxxxxx>]

I need help creating a select that returns 4 records that have
contiguous addresses that start on a bit boundary.

If 4 do not exist, I need a return of zero records.

I would like to do this in one statement and I do not have ownership of
this mysql server, so fancy views, temporary tables, indexing, etc are
outside my permission level.

I am also not the only consumer of this database, so altering it for my
needs could hurt the other consumers.

Below I specify the issue and where I am.

Thank you for your attention.

#
# Create problem set
# - This has non-contiguous addresses
# - This has one status not 0
# - This has contiguous addresses that start before the bit boundary
#
CREATE TABLE addresses ( address BIGINT(20), status INT );
INSERT INTO addresses
VALUES (100000000001,0),
       (100000000003,0),
       (100000000004,0),
       (100000000005,1),
       (100000000006,0),
       (100000000007,0),
       (100000000008,0),
       (100000000009,0),
       (100000000010,0),
       (100000000011,0),
       (100000000013,0),
       (100000000014,0),
       (100000000015,0),
       (100000000016,0),
       (100000000017,0);
#
# This shows the bit boundary, where the start is  (address & 3) = 0
#
select address, (address & 3) as boundary from addresses where address
>0 and status=0 order by address limit 10  ;
+--------------+----------+
| address      | boundary |
+--------------+----------+
| 100000000001 |        1 |
| 100000000003 |        3 |
| 100000000004 |        0 |
| 100000000006 |        2 |
| 100000000007 |        3 |
| 100000000008 |        0 |
| 100000000009 |        1 |
| 100000000010 |        2 |
| 100000000011 |        3 |
| 100000000013 |        1 |
+--------------+----------+
10 rows in set (0.00 sec)
#
# This shows contiguous add, but they do not stat on the bit  boundary
#
select c1.address, (address & 3) as boundary  from addresses c1 where 4
= ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
BETWEEN c1.address AND (c1.address + 3)  ) limit 10;

+--------------+----------+
| address      | boundary |
+--------------+----------+
| 100000000006 |        2 |
| 100000000007 |        3 |
| 100000000008 |        0 |
| 100000000013 |        1 |
| 100000000014 |        2 |
+--------------+----------+
5 rows in set (0.00 sec)



I can't seem to add my ((address & 3) = 0) condition to the correct location to get the desired
result. I don't understand how I can use c1.address in the BETWEEN, and
yet I can't seem to make ((address & 3) = 0) work anywhere.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql