looping infinitely when using sqlcmd inside a while-loop body

Question

I have a bash script which uses sqlcmd for fetching data from database in a while-loop body:

#!/bin/bash
tmpfile=$(mktemp)
echo -e "1\n2" > tmpfile
echo "---------------content from the tmp file--------------"
cat tmpfile
echo "------------------------------------------------------"
while read -r line || [[ -n $line ]]
do
  echo "${line}"
  echo "   ------------sqlcmd operation start------------"
  sqlcmd -Q "set nocount on;select 'dummy'"  
  echo -e "   ------------sqlcmd operation done------------\n\n"
done < tmpfile
echo "-----------------------script done---------------------"

It is expected to interate based on the line of content in the file.txt, that is twice in this cases, but acutally it will interate the first line infinitely:

I got the anwser about how to resolve the infinite loop from here loop variables stuck as constant when using sqlcmd

and here Unexpected behavior of file descriptor and/or I/O streams after running sqlcmd with -i option (read sql from file) in ksh creates infinite loop.

Simply put, it can be resolved if we redirect the input stream, for example:

#!/bin/bash
tmpfile=$(mktemp)
echo -e "1\n2" > tmpfile
echo "---------------content from the tmp file--------------"
cat tmpfile
echo "------------------------------------------------------"
while read -r line || [[ -n $line ]]
do
  echo "${line}"
  echo "   ------------sqlcmd operation start------------"
  sqlcmd -Q "set nocount on;select 'dummy'" < /dev/null  
  echo -e "   ------------sqlcmd operation done------------\n\n"
done < tmpfile
echo "-----------------------script done---------------------"

Below script which is just replace sqlcmd with other command, e.g. date then it is normal. I think it can prove this issue is relative to sqlcmd:

#!/bin/bash
tmpfile=$(mktemp)
echo -e "1\n2" > tmpfile
echo "---------------content from the tmp file--------------"
cat tmpfile
echo "------------------------------------------------------"
while read -r line || [[ -n $line ]]
do
  echo "${line}"
  echo "   ------------sqlcmd operation start------------"
  #sqlcmd -Q "set nocount on;select 'dummy'"
  date
  echo -e "   ------------sqlcmd operation done------------\n\n"
done < tmpfile
echo "-----------------------script done---------------------"

More interesting, if the content of the file is less than 4 charaters(including invisible char), it woudn't cause infinite iteration:

#!/bin/bash
tmpfile=$(mktemp)
#less than 4 char -- normal
echo -ne "123" > tmpfile
echo "---------------content from the tmp file--------------"
cat tmpfile
echo "------------------------------------------------------"
while read -r line || [[ -n $line ]]
do
  echo "${line}"
  echo "   ------------sqlcmd operation start------------"
  sqlcmd -Q "set nocount on;select 'dummy'"
  #date
  echo -e "   ------------sqlcmd operation done------------\n\n"
done < tmpfile
echo "-----------------------script done---------------------"

Infinite when there is more than 3 charaters:

#!/bin/bash
tmpfile=$(mktemp)
#more than 3 chars -- infinite
echo -ne "1234" > tmpfile
echo "---------------content from the tmp file--------------"
cat tmpfile
echo "------------------------------------------------------"
while read -r line || [[ -n $line ]]
do
  echo "${line}"
  echo "   ------------sqlcmd operation start------------"
  sqlcmd -Q "set nocount on;select 'dummy'"
  #date
  echo -e "   ------------sqlcmd operation done------------\n\n"
done < tmpfile
echo "-----------------------script done---------------------"

All in all, I'm curious how sqlcmd leads to the infinite looping. I can understand that the sqlcmd inherits the input stream from the outsiede while loop, and as it inhereists the input stream, if it slurps the conent then it should lead to less iteration for the outside while loop, but how it is possible cause looping infinitely?

Answer 1

My best guess is that sqlcmd uses lseek to set the file offset for its standard input back to the start of the file. Certainly, doing that would cause the infinite loop problem. This Shellcheck-clean code uses perl to reproduce the problem:

#! /bin/bash -p

while read -r line || [[ -n $line ]]; do
    printf '%s\n' "$line"
    perl -e 'seek(STDIN, 0, 0)'
done <"${BASH_SOURCE[0]}"

Running a file containing this code with Bash generates output

#! /bin/bash -p
#! /bin/bash -p
#! /bin/bash -p
...

until it is interrupted.

One way to verify this theory would be to run the shell program with strace (strace -f -o strace.out SHELLPROG). I expect the output to include something like this for the sqlcmd process:

... lseek(0, 0, SEEK_SET)           = 0