## Best index for column where values are mostly the same – Postgres

We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to efficiently pull out rows containing any one of these identifiers. Given the value will be 0 or 1 say (I don’t have figures yet) 99% of the time, how might it best be indexed to query against the minority case? Am I even right in thinking the volume of common values will be an issue?

           Column           |  Type   |     Modifiers ----------------------------+---------+--------------------  event_value                | integer | not null 

There are currently no indexes on this column. And I don’t envisage the need to regularly select just the 0 or 1 values.

The table is of a reasonable size, currently 30 million rows and growing fast.

I appreciate this isn’t the best use of the column, but that can’t change in the short term.

## Datetime conversion fails when filtered index is placed on partition column

I’ve got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.

The index’s filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error. I’ve managed to reproduce it on a test table.

Does anyone have any ideas on why this happens?

use YOUR_DATABASE ;  go  select @@VERSION ;  -- Microsoft SQL Server 2016 (SP2-CU1) ...  -----------------------------------------------------------------------------  -- CREATE DATETIME PARTITIONED TABLE  set language english ;  drop table if exists dbo.test_of_filtered_idx ;  drop partition scheme ps_test_of_filtered_idx ;  drop partition function pf_test_of_filtered_idx ;  go    set language english ;  go  create partition function pf_test_of_filtered_idx (datetime)     as range right      for values ('1999-11-01 00:00:00.000' , '1999-12-01 00:00:00.000' , '2000-01-01 00:00:00.000' , '2000-02-01 00:00:00.000')    create partition scheme ps_test_of_filtered_idx    as partition pf_test_of_filtered_idx    ALL to ([primary])    create table dbo.test_of_filtered_idx  (    id int not null identity (1,1) ,    dt datetime not null ,    payload char(127) not null default(replicate('A' , 127)) ,    constraint PK__test_of_filtered_idx primary key clustered (id , dt)  ) on [ps_test_of_filtered_idx] (dt) ;  go  -----------------------------------------------------------------------------  -- INSERT TEST ROW AND CREATE DATETIME FILTERED INDEX  set language russian ;  go  insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;  go  set language english ;  go  create index IXF__test_of_filtered_idx__dt    on dbo.test_of_filtered_idx    (dt) include (id)    where dt >= '1999-12-10 00:00:00.000'       and dt < '2000-01-20 00:00:00.000' ;  go  -----------------------------------------------------------------------------  -- CHECK   set language english ;  go  insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('12/15/1999 00:00:00.000' /* native american datetime format MDY */) ;  go  -- GET ERROR  set language russian ;  go  insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;  -- Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.  -- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.  go  -----------------------------------------------------------------------------  -- DROP INDEX AND GET ERRORFREE INSERTION  set language english ;  go  drop index IXF__test_of_filtered_idx__dt    on dbo.test_of_filtered_idx  ;  go  set language russian ;  go  insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;  go  ----------------------------------------------------------------------------- 

## SQL Outer Join while conditioning on an extra column

I am trying to pull up an outer join between two tables, on a specifc column “Identifier”, however, I would only like to carry out the outer joins on the rows where additional column “Date” matches between the tables. Example of what I am trying to achieve is, for the two tables below:

+------------+---------------+-------------------+-----------------+ | Date       | Identifier    | TableA-Attribute1 |TableA-Attribute2| +------------+---------------+-------------------+-----------------+ | date1      |         Id1   | a                 | x               | | date1      |         Id2   | b                 | y               | | date1      |         Id3   | c                 | z               | | date2      |         Id1   | d                 | x               | | date2      |         Id2   | e                 | y               | +------------+---------------+-------------------+-----------------+   +------------+---------------+-------------------+-----------------+ | Date       | Identifier    | TableB-Attribute1 |TableB-Attribute2| +------------+---------------+-------------------+-----------------+ | date1      |         Id1   | aa                | xx              | | date1      |         Id2   | bb                | yy              | | date1      |         Id3   | cc                | zz              | | date1      |         Id4   | dd                | xx              | | date2      |         Id1   | ee                | yy              | +------------+---------------+-------------------+-----------------+ 

I need to end up with something that looks like this:

+------------+---------------+---------+-------+--------+-------+ | Date       | Identifier    | A-Attr1 |A-Attr2|B-Attr1 |B-Attr2| +------------+---------------+---------+-------+--------+-------+ | date1      |         Id1   | a       | x     |aa      |xx     | | date1      |         Id2   | b       | y     |bb      |yy     | | date1      |         Id3   | c       | z     |cc      |zz     | | date1      |         Id4   | -       | -     |dd      |xx     | | date2      |         Id1   | d       | x     |ee      |yy     | | date2      |         Id2   | e       | y     |-       |-      | +------------+---------------+---------+-------+--------+-------+ 

I was trying to do something like this

go select * from table1 full outer join (select * from table2 where table1.date=table2.date)

however, that unfortunately does not seem to be working.

## How to do group by based on another column in SELECT?

I have a table on which I do a SELECT statement. It is joined to some other tables. With the SELECT one column is got but a sub query. like (select top 1 .. from..) for each row. Now I need to do an aggregation based on that column, so I have to group by id(which I also select) the amounts that I’ve got in that column got by the subquery. Is it possible and how?

## Different column data in same row

Current Output:

Need Output Like below:

## How to “group” based on consecutive occurrences of an element in a column in postgres

I have a table that, when ordered, describes a path by point descriptions in each row. A column tells whether a row is a special (stop) point within that path and is empty otherwise. The path starts and ends with such a row and has many in between. What I want to do is create line segments from the point rows that start at such a row and also end at such a row.

point  stop   1     yes   2         3     yes   4   5   6     yes 

I want to call ST_Makeline from PostGIS to turn the ordered points into these line segments. How can I enforce that ST_Makeline, being a “summary” function is only called for the inclusive point groups 1 to 3 and 3 to 6?

## User or group column is populated with incorrect values when adding data from CSOM

I am working on a console application that updates list items using managed CSOM. I have a user or group column in this list that can contain multiple values. Now, I am using below code to update the value of this column:

// Get the user   User newUser = web.EnsureUser("LoginName");   context.Load(newUser);   context.ExecuteQuery();   FieldUserValue userValue = new FieldUserValue();   userValue.LookupId = newUser.Id;  // Update the list item   listItem["User"] = userValue; 

The issue is if I assign the value of user1 to this column, the value that I see after the column is updated is another user; like user2. I don’t know why it is populating this code with some other user?

Any idea on this issue?

## How to have a value different from 0 in netstat’s Send-Q column for a socket in the LISTEN state?

I am currently trying to solve a Capture The Flag challenge that involves trying to escalate privileges by taking advantage of an exploit in a bash script.

The script first does the following to get all sockets with TCP protocol in the LISTEN state:

output=$($  _netstat -ntpl 2> /dev/null | $_egrep '^t')  and then it parses the output line by line. One of the things it does for each line is this: if [[ "$  cur_syn" == "0" ||  "$max_syn" != "$  cur_syn" ]]     then    continue fi 

$cur_syn is the value of the Recv-Q column as returned by netstat, and $ max_syn is the value of the Send-Q column.

So, only a socket that is in the LISTEN state and with Recv-Q != 0 and Recv-Q==Send-Q will pass these checks.

netstat‘s man states that:

Recv-Q Established: The count of bytes not copied by the user program connected to this socket. Listening: Since Kernel 2.6.18 this column contains the current syn backlog.

and

Send-Q Established: The count of bytes not acknowledged by the remote host. Listening: Since Kernel 2.6.18 this column contains the maximum size of the syn backlog.

The thing is, I seem not to be able to create a socket that has a Send-Q different from 0.

If my interpretation is correct, the Send-Q value for a socket that is listening is the max size of the backlog, which is the backlog param in C’s listen(2) function. But even when I create a listening server socket with a backlog of size 3, netstat still reports the Send-Q as being 0! What am I doing wrong?

FYI, I have managed to make the Recv-Q change by having multiple clients connect to a server socket that has received a SIGSTOP. Recv-Q goes up all the way to maximum size of the syn backlog + 1, and then all connections are refused. But alas,Send-Q remains unchanged.

## Rank of certain ‘Kronecker Column Hadamard Row Sum’ of matrices

Define Kronecker Column Hadamard Row Sum of two matrices $$M_1$$ and $$M_2$$ of size $$n_1\times m$$ and $$n_2\times m$$ respectively to be the $$n_1n_2\times m$$ matrix whose $$((i-1)n_1+(j-1))$$th row is sum of $$i$$th row of $$M_1$$ and $$j$$th row of $$M_2$$.

Take $$n$$ different matrices $$M_1,\dots,M_n$$ with ranks $$r_1$$ through $$r_n$$ respectively and sizes $$m_1\times m$$ through $$m_n\times m$$ respectively and with $$\prod_{i=1}^n m_i.

1. How large can the rank of Kronecker Column Hadamard Row Sum of $$M_1,\dots,M_n$$ be?

2. If each of $$M_1,\dots,M_n$$ is picked uniformly from matrices with ranks $$r_1$$ through $$r_n$$ respectively and sizes $$m_1\times m$$ through $$m_n\times m$$ respectively then what is the expected rank of Kronecker Row Direct Sum of $$M_1,\dots,M_n$$ and would an inequality like Chebyshev’s expected to hold?