SELECT FROM view shows 4 rows, but INSERT INTO ... SELECT FROM v

ghz 8months ago ⋅ 66 views

SELECT FROM view shows 4 rows, but INSERT INTO ... SELECT FROM view only inserts 2 rows

I have a table with 4 records, it's cidrs do not intersect.

CREATE TABLE `mit_service_collapsed` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(16) NOT NULL DEFAULT 'empty',
  `cidr` varchar(5) NOT NULL DEFAULT 'empty',
  `type` varchar(20) NOT NULL DEFAULT 'empty',
  `ioc` text,
  `time` timestamp NULL DEFAULT NULL,
  `reason` text,
  `asn` varchar(30) NOT NULL DEFAULT 'empty',
  `city` varchar(100) NOT NULL DEFAULT 'empty',
  `country` varchar(100) NOT NULL DEFAULT 'empty',
  `country_iso` varchar(10) NOT NULL DEFAULT 'empty',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `delete_reason` text,
  `delete_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql> select * from mit_service_collapsed;
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
| id | ip           | cidr | type  | ioc                       | time                | reason      | asn   | city         | country       | country_iso | deleted          | delete_reason | delete_time |
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
|  1 | 100.11.12.20 | 30   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
|  3 | 100.11.12.28 | 31   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
|  4 | 100.11.12.24 | 31   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
|  5 | 100.11.12.27 | 32   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
4 rows in set (0,00 sec)
mysql> show warnings;
Empty set (0,01 sec)

collapsed is a VIEW, inside which ip collapsion is done.

mysql> select * from collapsed;
+--------------+------+-------+---------------------------+---------------------+-------------+--------------+---------------+-------------+-------+
| ip           | cidr | type  | ioc                       | time                | reason      | city         | country       | country_iso | asn   |
+--------------+------+-------+---------------------------+---------------------+-------------+--------------+---------------+-------------+-------+
| 100.11.12.20 | 30   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | Collegeville | United States | US          | AS701 |
| 100.11.12.24 | 31   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | Collegeville | United States | US          | AS701 |
| 100.11.12.27 | 32   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | Collegeville | United States | US          | AS701 |
| 100.11.12.28 | 31   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | Collegeville | United States | US          | AS701 |
+--------------+------+-------+---------------------------+---------------------+-------------+--------------+---------------+-------------+-------+
4 rows in set (0,01 sec)

Here is the collapsion result, 4 records that don`t intersect, therefore there are 4 records after collapsion too.

But when I try to insert these 4 records to the second table, I get only two records remaining:

mysql> insert into mit_service_collapsed_second 
   (ip,cidr,type,ioc,time,reason,city,country,country_iso,asn)
  select ip,cidr,type,ioc,time,reason,city,country,country_iso,asn 
  from collapsed;
Query OK, 2 rows affected (0,01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from mit_service_collapsed_second;
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
| id | ip           | cidr | type  | ioc                       | time                | reason      | asn   | city         | country       | country_iso | deleted          | delete_reason | delete_time |
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
|  1 | 100.11.12.20 | 30   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
|  2 | 100.11.12.24 | 31   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
2 rows in set (0,00 sec)

Definition of the second table is the same as the first one:

mysql> CREATE TABLE `mit_service_collapsed_second` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `ip` varchar(16) NOT NULL DEFAULT 'empty',
    ->   `cidr` varchar(5) NOT NULL DEFAULT 'empty',
    ->   `type` varchar(20) NOT NULL DEFAULT 'empty',
    ->   `ioc` text,
    ->   `time` timestamp NULL DEFAULT NULL,
    ->   `reason` text,
    ->   `asn` varchar(30) NOT NULL DEFAULT 'empty',
    ->   `city` varchar(100) NOT NULL DEFAULT 'empty',
    ->   `country` varchar(100) NOT NULL DEFAULT 'empty',
    ->   `country_iso` varchar(10) NOT NULL DEFAULT 'empty',
    ->   `deleted` bit(1) NOT NULL DEFAULT b'0',
    ->   `delete_reason` text,
    ->   `delete_time` timestamp NULL DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
    -> ;

Here is the VIEW creation code:

CREATE VIEW collapsed AS
WITH
cte1 AS (                                -- find ip range start and ip_range to find ip range end then
  SELECT DISTINCT
     id,
     ip,
     cidr,
     type,
     ioc,
     time,
     reason,
     city, 
     country,
     country_iso,
     asn,
     ( ( 0xFFFFFFFF << (32 - cidr) ) & INET_ATON(ip) ) AS range_start,        -- also bitmask_on_ip
     pow(2,(32 - cidr)) AS ip_range              -- here ip_range is equal to 1 if cidr is 32, because 2**0=1
  FROM  mit_service_collapsed 
  WHERE deleted=b'0'                       -- and no deleted records
  ),
cte2 AS (                                -- find ip range end
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     city, 
     country,
     country_iso,
     asn,
     range_start,
     if(ip_range!=1,range_start+ip_range-1,range_start) AS range_end     
  FROM cte1
  ORDER BY range_start
  ),
cte3 AS (                                -- find max ip range end
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     city,
     country,
     country_iso,
     asn,
     range_start,
     range_end, 
     MAX(range_end) OVER(ROWS UNBOUNDED PRECEDING) AS max_end
  FROM cte2
  ),
cte4 AS (                                -- find min ip range start
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     country,
     city,
     country_iso,
     asn,
     range_start,
     range_end,
     MIN(range_start) OVER(PARTITION BY max_end) AS min_start,
     max_end
  FROM cte3
  ORDER BY cidr
  ),                              
 cte5 AS (
     SELECT ip,     -- этот блок удаляет записи, которые попадают на границу и потому остаются.
     cidr,
     type,
     ioc, 
     time,
     reason,     
     country,
     city,
     country_iso,
     asn,
     range_start,
     range_end,
     min_start,
     MAX(range_end) OVER(PARTITION BY range_start) AS max_max_end
FROM cte4)
SELECT ip,      -- delete all rows, which are inside bigger range
    cidr,
    type,
    ioc,
    time,
    reason,     
    city,
    country,
    country_iso,
    asn
  FROM cte5
WHERE range_start=min_start AND max_max_end=range_end;

I expected to get 4 records in the second table (mit_service_collapsed_second), but get only 2 records.

Also, right after INSERT:

mysql> show warnings;
Empty set (0,01 sec)

Answers

It seems like the issue might be related to your SQL query that you're using to insert data from the collapsed view into the mit_service_collapsed_second table.

From the data you've shown, it appears that the collapsed view contains 4 distinct records. However, after inserting data into the mit_service_collapsed_second table, you're only seeing 2 records.

One possibility is that your INSERT query is not inserting all the records from the collapsed view into the mit_service_collapsed_second table. To troubleshoot this, you can try the following:

  1. Check the INSERT query: Double-check your INSERT query to ensure that it's correctly selecting and inserting all the records from the collapsed view into the mit_service_collapsed_second table.

  2. Verify data consistency: Verify that the data in the collapsed view is consistent with what you expect. You can do this by running a SELECT query directly on the collapsed view and comparing the results with the data you expect to be inserted into the mit_service_collapsed_second table.

  3. Check for any filtering conditions: Make sure that there are no unintentional filtering conditions in your INSERT query that might be excluding certain records from being inserted into the mit_service_collapsed_second table.

  4. Debug the INSERT process: If the issue persists, you can try debugging the INSERT process by inserting data into a temporary table instead of directly into mit_service_collapsed_second. This can help you identify any potential issues with the INSERT query itself.

By carefully reviewing your INSERT query and verifying the data consistency, you should be able to identify and resolve the issue causing only 2 records to be inserted into the mit_service_collapsed_second table.

#mysql