sql - Perl、DBI 和 SQL : Why NOT does not work in some SQL queries?

Question

我有一个最奇怪的问题，我对 SQL 的极其基础的知识一定是非常错误的，但我无法理解下面所示的行为。

我有这个文件 test.csv

id,field
A,0
B,1
C,2
D,"0"
E,"1"
F,"2"
G,
H,""
I," "

而这个测试代码:

#! /usr/bin/perl

use strict;
use warnings;
use DBI;
use Devel::VersionDump qw(dump_versions);

my $dbh = DBI->connect ("dbi:CSV:");
$dbh->{RaiseError} = 1;
$dbh->{TraceLevel} = 0;
my $i = 0;

foreach my $cond ("TRUE",
                  "field <> 0 AND field <> 1",
                  "field = 0 OR field = 1",
                  "NOT (field = 0 OR field = 1)",
                  "NOT field = 0 OR field = 1",
                  "field <> 0",
                  "NOT field <> 0",
                  ) {
  print "Condition #" . $i++ . " is $cond:\n";
  my $sth = $dbh->prepare("SELECT * FROM test.csv WHERE $cond");
  $sth->execute();
  $sth->dump_results();
};

print "\n\n";
dump_versions();

运行时，这是输出:

Condition #0 is TRUE:
'A', '0'
'B', '1'
'C', '2'
'D', '0'
'E', '1'
'F', '2'
'G', ''
'H', ''
'I', ' '
9 rows
Condition #1 is field <> 0 AND field <> 1:
'C', '2'
'F', '2'
'G', ''
'H', ''
'I', ' '
5 rows
Condition #2 is field = 0 OR field = 1:
'A', '0'
'B', '1'
'D', '0'
'E', '1'
4 rows
Condition #3 is NOT (field = 0 OR field = 1):
'A', '0'
'B', '1'
'D', '0'
'E', '1'
4 rows
Condition #4 is NOT field = 0 OR field = 1:
'B', '1'
'C', '2'
'E', '1'
'F', '2'
'G', ''
'H', ''
'I', ' '
7 rows
Condition #5 is field <> 0:
'B', '1'
'C', '2'
'E', '1'
'F', '2'
'G', ''
'H', ''
'I', ' '
7 rows
Condition #6 is NOT field <> 0:
'A', '0'
'D', '0'
2 rows


Perl version: v5.16.3 on MSWin32 (C:\Program Files\Perl64\bin\perl.exe)
ActivePerl::Config                                     -  Unknown
ActiveState::Path                                      -     1.01
AutoLoader                                             -     5.73
C:::Program Files::Perl64::site::lib::sitecustomize.pl -  Unknown
Carp                                                   -     1.26
Class::Struct                                          -     0.63
Clone                                                  -     0.34
Config                                                 -  Unknown
Config_git.pl                                          -  Unknown
Config_heavy.pl                                        -  Unknown
Cwd                                                    -     3.40
DBD::CSV                                               -     0.41
DBD::File                                              -     0.42
DBI                                                    -    1.631
DBI::DBD::SqlEngine                                    -     0.06
DBI::SQL::Nano                                         - 1.015544
Data::Dumper                                           -    2.139
Devel::VersionDump                                     -     0.02
DynaLoader                                             -     1.14
Encode                                                 -     2.49
Encode::Alias                                          -     2.16
Encode::Config                                         -     2.05
Encode::Encoding                                       -     2.05
Errno                                                  -     1.15
Exporter                                               -     5.67
Exporter::Heavy                                        -     5.67
Fcntl                                                  -     1.11
File::Basename                                         -     2.84
File::Spec                                             -     3.40
File::Spec::Unix                                       -     3.40
File::Spec::Win32                                      -     3.40
File::stat                                             -     1.05
IO                                                     -  1.25_06
IO::Dir                                                -      1.1
IO::File                                               -     1.16
IO::Handle                                             -     1.33
IO::Seekable                                           -      1.1
List::Util                                             -     1.27
Math::BigFloat                                         -    1.997
Math::BigInt                                           -    1.998
Math::BigInt::Calc                                     -    1.997
Math::Complex                                          -     1.59
Math::Trig                                             -     1.23
Params::Util                                           -     1.07
SQL::Dialects::AnyData                                 -    1.405
SQL::Dialects::Role                                    -    1.405
SQL::Eval                                              -    1.405
SQL::Parser                                            -    1.405
SQL::Statement                                         -    1.405
SQL::Statement::Function                               -    1.405
SQL::Statement::Functions                              -    1.405
SQL::Statement::Operation                              -    1.405
SQL::Statement::Placeholder                            -    1.405
SQL::Statement::RAM                                    -    1.405
SQL::Statement::Term                                   -    1.405
SQL::Statement::TermFactory                            -    1.405
SQL::Statement::Util                                   -    1.405
Scalar::Util                                           -     1.27
SelectSaver                                            -     1.02
Symbol                                                 -     1.07
Text::CSV_XS                                           -     1.07
Tie::Hash                                              -     1.04
Time::HiRes                                            -   1.9725
Win32                                                  -     0.47
XSLoader                                               -     0.16
base                                                   -     2.18
bytes                                                  -     1.04
constant                                               -     1.25
integer                                                -     1.00
overload                                               -     1.18
overloading                                            -     0.02
sort                                                   -     2.01
strict                                                 -     1.07
unicore::Heavy.pl                                      -  Unknown
unicore::lib::Perl::Word.pl                            -  Unknown
unicore::lib::Perl::_PerlIDS.pl                        -  Unknown
utf8                                                   -     1.09
utf8_heavy.pl                                          -  Unknown
vars                                                   -     1.02
warnings                                               -     1.13
warnings::register                                     -     1.02

条件 #0 显示了完整的数据集并且很好。

条件 #1 只是一些复合条件并且工作正常。

条件 #2 是相反的条件(用于反转它的基本逻辑规则)，并且也能正常工作。

然而，条件#3 应该与#2 相反，因此等于#1，但结果与#2 相同: 我无法理解这个 .

条件 #4 表明，省略括号，NOT 确实可以正常工作，但当然这个查询与之前的任何查询都不同。

条件 #5 和 #6 显示了一种情况，即 NOT 的行为完全符合人们的预期。

那么，为什么 NOT 在复合条件上表现得好像根本没有指定 NOT 一样？!

顺便说一句，我读了这篇可怕的帖子 Perl DBD::CSV - SQL Syntax - "AND" clause is not working properly并添加 Devel::VersionDump检查我是否有类似的问题，但在我看来，所有相关的软件包都是最新的。因此，我真的对此一无所知。

Answer 1

我确认这是 SQL::Parser 的错误:

'where_clause' => HASH(0x7f9686737480)
  'arg1' => HASH(0x7f9686808248)
     'arg1' => HASH(0x7f96866b50f8)
        'fullorg' => 'field'
        'type' => 'column'
        'value' => 'field'
     'arg2' => HASH(0x7f968588dfe0)
        'fullorg' => 0
        'type' => 'number'
        'value' => 0
     'neg' => 0
     'nots' => HASH(0x7f96866b55d8)
          empty hash
     'op' => '='
  'arg2' => HASH(0x7f9684498ce0)
     'arg1' => HASH(0x7f96845fb798)
        'fullorg' => 'field'
        'type' => 'column'
        'value' => 'field'
     'arg2' => HASH(0x7f96866b5158)
        'fullorg' => 1
        'type' => 'number'
        'value' => 1
     'neg' => 0
     'nots' => HASH(0x7f96866b55a8)
          empty hash
     'op' => '='
  'neg' => 0
  'nots' => HASH(0x7f9686808320)
       empty hash
  'op' => 'OR'

最上面的“否定”应该是 1。请在 https://rt.cpan.org/Dist/Display.html?Name=SQL-Statement 开票。 - 当您引用此线程时，测试用例已被证明:)

干杯，
延斯