Perl,DBI和SQL：为什么NOT在某些SQL查询中不起作用？
发布时间：2020-12-15 22:06:10 所属栏目：大数据 来源：网络整理
导读：我有一个最奇怪的问题,我对SQL的非常基本的知识必定是非常错误的,但我无法理解下面说明的行为. 我有这个文件test.csv id,fieldA,0B,1C,2D,"0"E,"1"F,"2"G,H,""I," " 而这个测试代码： #! /usr/bin/perluse strict;use warnings;use DBI;use Devel::VersionDu
|
我有一个最奇怪的问题,我对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 "nn";
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',' ' 5 rows Condition #2 is field = 0 OR field = 1: 'A','1' 'D','1' 4 rows Condition #3 is NOT (field = 0 OR field = 1): 'A','1' 4 rows Condition #4 is NOT field = 0 OR field = 1: 'B','2' 'E',' ' 7 rows Condition #5 is field <> 0: 'B',' ' 7 rows Condition #6 is NOT field <> 0: 'A','0' 'D','0' 2 rows Perl version: v5.16.3 on MSWin32 (C:Program FilesPerl64binperl.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？！ 顺便说一句,我读了这篇可怕的帖子Perl DBD::CSV – SQL Syntax – “AND” clause is not working properly并添加了Devel::VersionDump以检查我是否有类似的问题,但在我看来,所有相关的软件包都是最新的.因此,我真的不知道这个. 解决方法
我确认这是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打开一张票 – 当你引用这个帖子时,测试用例被证明:) 干杯,延 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |